Fix Incorrect Row Count with sys.dm_db_partition_stats in SQL Server

If you are checking the table count with DMV – sys.dm_db_partition_stats, then there are chances that you are not seeing the accurate value. The values displayed by sys.dm_db_partition_stats can be approximate but not as accurate as to count(*). Additionally, some of them prefer to use this DMV to get the count faster rather than a direct SELECT Count(*) on a table name.

Further, these count mismatch scenarios occur only when the old databases are restored with outdated database usage statistics.

Let’s look at a simple example of count mismatch between SELECT count(*) and SELECT using a sys.dm_db_partition_stats DMV.

From the image, if you just notice, the count from DMV has lesser records than the count(*) though we had no fresh insertion on the current database, SQLArena_TEST.

Fix: This can be fixed using the DBCC command as below:

DBCC UPDATEUSAGE (SQLArena_TEST_OldRestore) --your desired database name

Note: DBCC UPDATEUSAGE command is to scan all the pages and update them to proper space allocation. So, running this scan can cause blockings while in production hours. Please ensure to run the script only during a maintenance window or off hours of production to avoid impacts on applications. 


Post a Comment