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.
0 comments:
Post a Comment