Using DBCC UPDATEUSAGE

Using DBCC UPDATEUSAGE

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:

USE MyDB;
GO
DBCC UPDATEUSAGE(0); -- Execute for the current database
GO
DBCC UPDATEUSAGE(MyDB);  -- Execute using the database name
GO
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;
GO
-- 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');