DBCC UPDATEUSAGE is a command available in SQL Server that is used to update the page and row count metadata for database objects like tables and indexes. The metadata is stored in system tables and views like sys.dm_db_index_physical_stats and is responsible for driving space usage reporting from commands like sp_spaceused. Over time, these usage statistics can become outdated, particularly in highly transactional databases with frequent DDL operations. DBCC UPDATEUSAGE rescans the allocated pages to update the metadata and ensure its accuracy.
During the recent Comprehensive Database Performance Health Check, I had the great pleasure of discussing this topic with my client, and here are the notes I sent them to understand this topic. Let me know your thoughts.
When Should UPDATEUSAGE Be Used?
Microsoft does not recommend routinely running DBCC UPDATEUSAGE. SQL Server’s background processes typically correct the space allocation metadata under regular operation. However, running frequent UPDATEUSAGE scans can cause unnecessary overhead and resource contention in most databases.
UPDATEUSAGE may be beneficial for databases with extremely high transaction volume or frequent DDL changes like CREATE, ALTER, and DROP statements. In such scenarios, the constant changes can outpace the auto updates. The official guidance suggests considering DBCC UPDATEUSAGE on a weekly basis at most, and only if needed for rapidly evolving schemas. However, many experts advise against even this frequency in most databases. UPDATEUSAGE should be used judiciously, not scheduled blindly.
How Does UPDATEUSAGE Work?
DBCC UPDATEUSAGE accepts a database name, table name, or index name to scan. If a name is not provided, it runs against the current database. It reads through every allocated data page sequentially to count rows, pages, index keys, and other allocation details.
This process directly updates the affected metadata tables and views to match the actual on-disk state. The locks required can limit concurrency on active systems. Heap tables without clustered indexes are especially slow to update.
After DBCC UPDATEUSAGE, space usage reports from sp_spaceused will reflect the latest allocation details. The updated counts for rows, reserved pages, used pages, and index keys will be accurate.
When Not to Use DBCC UPDATEUSAGE
Because DBCC UPDATEUSAGE has overhead, it should not be run after minor DDL changes. Only use it when schema changes are extensive enough to significantly throw off the metadata.
For instance, the updated stats could take time to propagate after a massive table rebuild. DBCC UPDATEUSAGE can accelerate that metadata refresh. However, day-to-day inserts, updates, and deletes don’t require rescanning.
Also, one should avoid overuse on OLTP systems where contention risks outweigh accuracy benefits. Unless space usage must be perfect in real-time, lean on auto-updates for most databases. Schedule judiciously based on how stale stats impact operations.
Diagnosing DBCC UPDATEUSAGE Issues
If DBCC UPDATEUSAGE stalls or never returns, treat it as a symptom of an underlying issue, not normal behavior. Investigate waits and blocking during the hang. Additionally, check for data corruption using DBCC CHECKDB.
A database-level UPDATEUSAGE failing while individual tables succeed might indicate corruption. Or possibly an archaic heap structure allowing space leaks. Review best practices if deterioration is ruled out.
Overall, DBCC UPDATEUSAGE should be a surgical tool for unusual cases, not a routine task. Let SQL Server handle everyday metadata updates automatically in the background. Stick to official guidance limiting use for exceptionally volatile or unique databases. Treat failures or blocks as prompts for deeper diagnosis, not routine events.
Conclusion
DBCC UPDATEUSAGE can be an invaluable troubleshooting resource when database metadata becomes incredibly stale after significant DDL churn. However, it should be used surgically when truly needed, not on an arbitrary schedule. Frequent scanning risks unnecessary contention without clear benefits for most databases. Reserve DBCC UPDATEUSAGE for exceptional cases where space allocation data requires assistance catching up after extensive changes. And always diagnose the underlying root cause if it fails or hangs unexpectedly. Limiting to targeted scenarios reduces overhead while providing a metadata refresh option for unusual cases needing it
You can always reach out to me on Twitter.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
