DBCC UPDATEUSAGE – Is it important post SQL Server version upgrade?

As part of migration from SQL server 2008 R2 to SQL server 2014, we listed out a list of items to be taken care post upgrade. There was a question in the panel on DBCC UPDATEUSAGE – should we really do this as part of post migration. Here are few points that I support to do this operation.

What is DBCC UPDATEUSAGE doing?

UPDATEUSAGE is a DBCC command that reports and correct inaccurate pages and rows in the catalog views.These catalogs will be used to report information for sp_spaceused and therefore it is very important to update those information using UPDATEUSAGE command.

Why do we need post migration?

When we are upgrading a SQL Server version, this is mandatory to update these catalogs, as these catalogs may be used internally for various purposes. UPDATEUSAGE command primarily updates page allocations of data/index. In addition, the command can also be used to update the row counts as well with an optional parameter – WITH COUNT_ROWS.

DBCC UPDATEUSAGE(0) WITH COUNT_ROWS

Few other details –

It is to be noted that you should not get confused by the SELECT COUNT() method and other methods that uses catalogs to get the row count information. SELECT COUNT always reads the from underlying objects, hence the information will be always accurate. Read more about it in the below link.

https://sqlzealots.com/2017/01/29/sql-server-count-table-rows-for-all-databases-using-sp_msforeachdb-and-sp_msforeachtable/

DBCC UPDATEUSAGE holds a shared lock on the object, hence this may cause a blocking scenario in a highly concurrent environment. This is not advisable to run the command on production drectly , rather to plan these to happen on maintenance window.

The other way to invoke the UPDATEUSAGE is with sp_spaceuded as below:

sp_spaceused @updateusage=true

Hope you enjoyed this blog post, let me know your thoughts on the topic.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s