When to Use DBCC UPDATEUSAGE in SQL Server?

Question

Jignesh Kumar · Answer

DBCC UPDATEUSAGE is a SQL Server command that fixes and updates system records about the number of pages and rows in a table or index. It is useful when these counts are incorrect, as such errors can impact query performance and space usage reports.
example : DBCC UPDATEUSAGE (‘AdventureWorks’) WITH NO_INFOMSGS;

Adarsh Nigam · Answer

Use DBCC UPDATEUSAGE in SQL Server to correct and update the page and row count inaccuracies for tables and indexes. This is particularly useful when:
After Significant Data Changes: When there have been substantial insertions, deletions, or updates that could lead to inaccurate statistics.
After Upgrades or Migrations: Post upgrade or migration of databases where metadata may have become inconsistent.
Suspected Metadata Issues: When you notice discrepancies in the space usage reports provided by SQL Server.
It helps ensure accurate space usage reporting, which is crucial for performance tuning and capacity planning.

When to Use DBCC UPDATEUSAGE in SQL Server?

2 Answers