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
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');
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');
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');
No comments:
Post a Comment