Wednesday, March 2, 2016

Five Facts about DBCC UPDATEUSAGE

DBCC UPDATEUSAGE is probably very rare one. When I was asked about it Google gave me not a lot of links and all links were not very descriptive. Most of those links simply mimic MSDN documentation: https://msdn.microsoft.com/en-us/library/ms188414.aspx

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:



USE AdventureWorks2014
GO
PRINT 'Make Fake statistics -------------------------------------------------------------'
UPDATE STATISTICS [Person].[Person] WITH ROWCOUNT = 1000000, PAGECOUNT = 2000000;
GO
SELECT p.rows, a.total_pages, a.used_pages, a.data_pages
FROM sys.indexes i
INNER JOIN sys.partitions AS p ON i.object_id = p.object_id and i.index_id = p.index_id
INNER JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
WHERE i.name = 'PK_Person_BusinessEntityID' and a.type = 1;
GO
PRINT 'Estimate space usage --------------------------------------------------------------'
DBCC UPDATEUSAGE ('AdventureWorks2014','Person.Person','PK_Person_BusinessEntityID') WITH ESTIMATEONLY;
GO
PRINT 'Fix space usage -------------------------------------------------------------------'
DBCC UPDATEUSAGE ('AdventureWorks2014','Person.Person','PK_Person_BusinessEntityID');
GO
SELECT p.rows, a.total_pages, a.used_pages, a.data_pages
FROM sys.indexes i
INNER JOIN sys.partitions AS p ON i.object_id = p.object_id and i.index_id = p.index_id
INNER JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
WHERE i.name = 'PK_Person_BusinessEntityID' and a.type = 1;
GO
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:
As you can see, UPDATEUSAGE found three problems with that table, while was running in "ESTIMATEONLY" mode. When it run in "Fix" mode it also found and fixed only three problems. The number of rows was still left unfixed.

To fix Row Counting problem we have to run it with additional "COUNT_ROWS" option:


DBCC UPDATEUSAGE ('AdventureWorks2014','Person.Person','PK_Person_BusinessEntityID') WITH ESTIMATEONLY;
GO
DBCC UPDATEUSAGE ('AdventureWorks2014','Person.Person','PK_Person_BusinessEntityID') WITH COUNT_ROWS;
GO
SELECT p.rows, a.total_pages, a.used_pages, a.data_pages
FROM sys.indexes i
INNER JOIN sys.partitions AS p ON i.object_id = p.object_id and i.index_id = p.index_id
INNER JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
WHERE i.name = 'PK_Person_BusinessEntityID' and a.type = 1;
GO
As the result I've got following:
As you can see, "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


BEGIN TRAN

SELECT * FROM Person.Person WITH (ROWLOCK,XLOCK)
WHERE BusinessEntityID = 10
------------ Stop Script Here and run DBCC UPDATEUSAGE in another Tab
ROLLBACK
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


PRINT 'Make Fake statistics -------------------------------------------------------------'
UPDATE STATISTICS [Person].[Person] WITH ROWCOUNT = 1000000, PAGECOUNT = 2000000;
GO
SELECT p.rows, a.total_pages, a.used_pages, a.data_pages
FROM sys.indexes i
INNER JOIN sys.partitions AS p ON i.object_id = p.object_id and i.index_id = p.index_id
INNER JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
WHERE i.name = 'PK_Person_BusinessEntityID' and a.type = 1;
GO
PRINT 'Estimate space usage --------------------------------------------------------------'
DBCC UPDATEUSAGE ('AdventureWorks2014','Person.Person','PK_Person_BusinessEntityID') WITH ESTIMATEONLY;
GO
-- DBCC UPDATEUSAGE ('AdventureWorks2014','Person.Person','PK_Person_BusinessEntityID');
PRINT 'Trying to fix space usage -------------------------------------------------------------------'
UPDATE STATISTICS [Person].[Person] [PK_Person_BusinessEntityID] WITH FULLSCAN;
GO
SELECT p.rows, a.total_pages, a.used_pages, a.data_pages
FROM sys.indexes i
INNER JOIN sys.partitions AS p ON i.object_id = p.object_id and i.index_id = p.index_id
INNER JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
WHERE i.name = 'PK_Person_BusinessEntityID' and a.type = 1;
GO
PRINT 'Estimate space usage again --------------------------------------------------------------'
DBCC UPDATEUSAGE ('AdventureWorks2014','Person.Person','PK_Person_BusinessEntityID') WITH ESTIMATEONLY;
GO

In that scrip I've tried to fix existing problems by simple Statistics' Update.
As you can see from the screenshot, it did not help at all.

The next try with Index Reorganize:


PRINT 'Make Fake statistics -------------------------------------------------------------'
UPDATE STATISTICS [Person].[Person] WITH ROWCOUNT = 1000000, PAGECOUNT = 2000000;
GO
SELECT p.rows, a.total_pages, a.used_pages, a.data_pages
FROM sys.indexes i
INNER JOIN sys.partitions AS p ON i.object_id = p.object_id and i.index_id = p.index_id
INNER JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
WHERE i.name = 'PK_Person_BusinessEntityID' and a.type = 1;
GO
PRINT 'Estimate space usage --------------------------------------------------------------'
DBCC UPDATEUSAGE ('AdventureWorks2014','Person.Person','PK_Person_BusinessEntityID') WITH ESTIMATEONLY;
GO
-- DBCC UPDATEUSAGE ('AdventureWorks2014','Person.Person','PK_Person_BusinessEntityID');
PRINT 'Trying to fix space usage -------------------------------------------------------------------'
ALTER INDEX [PK_Person_BusinessEntityID] ON [Person].[Person] REORGANIZE;
GO
SELECT p.rows, a.total_pages, a.used_pages, a.data_pages
FROM sys.indexes i
INNER JOIN sys.partitions AS p ON i.object_id = p.object_id and i.index_id = p.index_id
INNER JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
WHERE i.name = 'PK_Person_BusinessEntityID' and a.type = 1;
GO
PRINT 'Estimate space usage again --------------------------------------------------------------'
DBCC UPDATEUSAGE ('AdventureWorks2014','Person.Person','PK_Person_BusinessEntityID') WITH ESTIMATEONLY;
GO
And it also had no results.

As the last resort I've tried Index Rebuild:


PRINT 'Make Fake statistics -------------------------------------------------------------'
UPDATE STATISTICS [Person].[Person] WITH ROWCOUNT = 1000000, PAGECOUNT = 2000000;
GO
SELECT p.rows, a.total_pages, a.used_pages, a.data_pages
FROM sys.indexes i
INNER JOIN sys.partitions AS p ON i.object_id = p.object_id and i.index_id = p.index_id
INNER JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
WHERE i.name = 'PK_Person_BusinessEntityID' and a.type = 1;
GO
PRINT 'Estimate space usage --------------------------------------------------------------'
DBCC UPDATEUSAGE ('AdventureWorks2014','Person.Person','PK_Person_BusinessEntityID') WITH ESTIMATEONLY;
GO
-- DBCC UPDATEUSAGE ('AdventureWorks2014','Person.Person','PK_Person_BusinessEntityID');
PRINT 'Trying to fix space usage -------------------------------------------------------------------'
ALTER INDEX [PK_Person_BusinessEntityID] ON [Person].[Person] REBUILD;
GO
SELECT p.rows, a.total_pages, a.used_pages, a.data_pages
FROM sys.indexes i
INNER JOIN sys.partitions AS p ON i.object_id = p.object_id and i.index_id = p.index_id
INNER JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
WHERE i.name = 'PK_Person_BusinessEntityID' and a.type = 1;
GO
PRINT 'Estimate space usage again --------------------------------------------------------------'
DBCC UPDATEUSAGE ('AdventureWorks2014','Person.Person','PK_Person_BusinessEntityID') WITH ESTIMATEONLY;
GO
As you can see, Index Rebuild almost did the trick! It fixed our faked counts for pages and records.



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:

5. You have to run "DBCC UPDATEUSAGE" when you have an error associated with it.

If anybody have interesting experience with UPDATEUSAGE procedure, please post it here for everybody's advantage.

 

3 comments:

  1. I normally run dbcc udateusage when attaching a database received from a vendor or foreign source.

    ReplyDelete
  2. DBCC UPDATEUSAGE is holding exclusive lock and database and getting blocked by all other queries. DBCC UPDATEUSAGE is supposed to be doing shared lock on object. This is accruing in sql server 2017. Any thoughts on this. Thank you.

    ReplyDelete
    Replies
    1. I did not play with DBCC UPDATEUSAGE in 2017. On Microsoft's page regarding this command:
      https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-updateusage-transact-sql?view=sql-server-2017
      It clearly states: "Do not run DBCC UPDATEUSAGE routinely...."

      It makes sense to use it only after massive schema updates.

      Maybe in your case it found a problem, but couldn't acquire an exclusive lock for fixing it. Also, how do you see that UPDATEUSAGE is holding exclusive lock?

      Delete