I've been asked about UPDATEUSAGE because it run for few hours on one of our servers and produced a lot of blocking. That question was: Do we really need that procedure at all?
In order to answer that question we have to really understand what that procedure is responsible for and what it is doing.
I've started my research by restoring "AdventureWorks" database and created following scrip to see how UPDATEUSAGEwill behave on the biggest table in that sample database:
As the first step I faked statistics, to make picture worse, then I've captured Index allocations.
Then I've used here undocumented option "WITH ESTIMATEONLY" just to check what it will be doing.
After that I've run UPDATEUSAGE to fix all problems and captured the Index allocation again
Here are my results:
To fix Row Counting problem we have to run it with additional "COUNT_ROWS" option:
As the result I've got following:
ESTIMATEONLY" option did not find any problems and only the option "COUNT_ROWS" helped to identify and fix an error.
So, from that test we can learn following fact:
1. DBCC UPDATEUSAGE has to be used with "COUNT_ROWS" option to fix all possible problems.My next test was to check locking. I
Obviously, that script produced Exclusive Lock on one particular row, which held UPDATEUSAGE procedure until I release the transaction by rolling it back.
That gave us another two facts:
2. DBCC UPDATEUSAGE does a full scan of an Index.
3. Because of #2, it is not worth to run that job twice without "COUNT_ROWS" option and with it.After knowing what UPDATEUSAGE is doing we can try answer the question: Can we live without it or not?
For that we will run following script
In that scrip I've tried to fix existing problems by simple Statistics' Update.
The next try with Index Reorganize:
And it also had no results.
As the last resort I've tried Index Rebuild:
Rebuild did not fix overestimated numbers of used and total pages. That is actually strange, because it had all information for it.
From this test we've learn one more fact:
4. If amount of Space, used by your data is not critical and you are running Index Rebuild on the regular basis, you do not really have to run DBCC UPDATEUSAGE.As the last check, I've looked at any error messages suggesting to run UPDATEUSAGE and I've found two of them:
Error 2508: The %.*ls count for object "%.*ls", index ID %d, partition ID %I64d, alloc unit ID %I64d (type %.*ls) is incorrect. Run DBCC UPDATEUSAGE.
Error 20558: Table '%s' passed full rowcount validation after failing the fast check. DBCC UPDATEUSAGE will be initiated automatically.
I've never had these kind of messages, but I'm pretty sure they are supposed to be produced by DBCC CHECKDB or one of its components.
That is the last piece of information: