SQL Server: DBCC UPDATEUSAGE does not return

Question

I am an experienced programmer (not a DBA) and would appreciate troubleshooting guidance if no immediate answer is possible.

The database itself is relatively small - a couple of million rows spread over 500 tables - and is used primarily to cache static data owned by a remote mainframe system. It is also used for audit trail / logging and caching of transient data during capture.

The server is Microsoft SQL Server 2016 Enterprise and the database is run in 2008 compatibility mode

A housekeeping job runs every 24 hours, during which static content is refreshed (involving DROP, (re)CREATE of several hundred tables), followed by DBCC UPDATEUSAGE (DBName) WITH COUNT_ROWS

Some time back, our support team started to report deadlock messages in the log file. The housekeeping job is always "chosen as victim".

Diagnostic efforts have shown that with DBCC UPDATEUSAGE (DBName) WITH COUNT_ROWS removed, no deadlock occurs. When DBCC UPDATEUSAGE (DBName, TableName) WITH COUNT_ROWS is run (manually from SSMS) over all tables, one at a time, it always completes, always very quickly, with no errors, but when run on the database as a whole, it always fails to return and has to be aborted after several hours.

This situation occurs on only one production server (out of 4) and does not occur in the similarly set up test environment. The fact that I am investigating on a production server limits my ability to experiment.

Guidance on how to diagnose from a locking, resource contention, corruption (or any other) point-of-view would be appreciated.

A second and possibly related issue

Select name from DBName..sysobjects where type = 'U' and name = 'Tablename' returns a value for name when it is clear (from SSMS) that said table does not exist.
SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'Tablename' seems always correct where the query against sysobjects is quite often incorrect.

FINAL UPDATE

Giving up and moving on.

No answer, no clues. The company's DBAs have a weekly housekeeping routine that runs quickly and error free, it includes DBCC CheckDB, rebuild indexes and update statistics.

I note the often-repeated advice that DBCC UPDATEUSAGE shouldn't be necessary, even when current (2022) documentation says "Consider running DBCC UPDATEUSAGE routinely (for example, weekly) only if the database undergoes frequent Data Definition Language (DDL) modifications, such as CREATE, ALTER, or DROP statements" which does apply to our routine. (I know I know, daily is not weekly.)

The facts remain that on one server of four set up the same way (2 centres, 2 disaster recovery mirrors), DBCC UPDATEUSAGE (database) goes almost immediately into a suspended state and stays there, whereas 400+ DBCC UPDATEUSAGE (database, table) for every table in the database completes in about 3 seconds.

There has to be a sensible explanation, it probably has to do with contention for something other than a table, it's obviously not obvious. Will check back periodically.

Answer 1

DBCC UPDATEUSAGE shouldn't be needed as of SQL Server 2000. In earlier versions, the meta-data for space usage could be old. If you update your post with link to the documentation and add a comment below that you added the link we can report to MS and see if they can clarify/rectify this.

Is the table a heap table? I've seen DBCC UPDATEUSAGE take ages on heap tables due to them not releasing space when deleting data (in some circumstances). If so, consider making it a clustered table, if that is an option.

If it isn't a heap, then it smells like blocking to me. Did you do anything to find out if blocking is indeed the problem? Using sp_whoisactive, for instance.

For the record, it seems you might have more legacy stuff going on here judging not only by using DBCC UPDATEUSAGE but also since you are using sysobjects (which was been deprecated in 2005, and we now have sys.objects etc).