sys.dm_db_partition_stats (Transact-SQL) - SQL Server | Microsoft Learn
SELECT
OBJECT_NAME(object_id) AS TableName,
SUM(row_count) AS RowCount
FROM sys.dm_db_partition_stats
WHERE index_id IN (0,1)
GROUP BY object_id
ORDER BY RowCount DESC;
If
sys.dm_db_partition_stats (or sys.partitions) is giving inaccurate row counts, it’s usually not a bug—it’s due to how SQL Server maintains metadata. Here’s what’s happening and how to deal with it:Metadata Lag
- Row counts come from internal allocation metadata, not real-time scans.
- Updates happen during:
- checkpoints
- index rebuild/reorg
- statistics updates
👉 Under heavy INSERT/DELETE, the count can drift.
⚠️ Common Causes of Wrong Counts
| Cause | Effect |
|---|---|
| Heavy DML (INSERT/DELETE) | DMV lag |
| Truncated tables | mismatch temporarily |
| Bulk operations | metadata delay |
| Disabled/rebuilt indexes | stale counts |
| Partition switching | incorrect totals |
| Heaps with forwarding records | inaccuracies |
✅ How to Fix / Improve Accuracy
DBCC UPDATEUSAGE (YourDatabaseName) WITH COUNT_ROWS;
