When DBCC UpdateUsage is run, page and row counts are updated. When this information is incorrect, it can give incorrect reports for how much space is used by tables and indexes. The most likely cause for incorrect usage stats is when a database is upgraded to a new compatibility level of SQL Server (i.e. 2000 to 2005). Therefore it’s important to run this command after the statistics have been updated.

It may not be a bad idea to run on a weekly or monthly schedule if your database undergoes frequent DDL modifications.

To execute you can run one of the following commands:

DBCC UPDATEUSAGE(0); -- Execute for the current database
DBCC UPDATEUSAGE(MyDB);  -- Execute using the database name
DBCC UPDATEUSAGE(MyDB);  -- Execute using the database ID

This may take some time, so make sure to run it in a non production environment or during off hours as it may cause blocking.

You can also run for a specific table or index. If you run for a table, then the usage stats are updated for all indexes on that table.

-- Update for a table (and it's indexes)
DBCC UPDATEUSAGE(AdventureWorks, 'Sales.SalesOrderDetail');

-- Update usage for a single index
DBCC UPDATEUSAGE(AdventureWorks, 'Sales.SalesOrderDetail', 'IX_SalesOrderDetail_ProductID');

That’s basically it. Now you may be running these commands and are not seeing any output. Well, that’s because the usage stats are already correct. But if you want to spoof the stats in order to see the output, you can use these undocumented options:

USE Adventureworks;
-- Override existing page and rowcount values
-- Do not do this in prod
UPDATE STATISTICS Sales.SalesOrderDetail WITH ROWCOUNT = 20000, pagecount = 10000;

-- Now when you run the update usage stats, it will show an update
DBCC UPDATEUSAGE(AdventureWorks, 'Sales.SalesOrderDetail');

Featured Articles

Learn Index Tuning!

Index Tuning Video Training
only $49
You'll learn everything practical there is to know about index tuning with this downloadable video.
Learn more

 Site Author

  • Thanks for visiting!