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.
DBCC UPDATEUSAGE
? I've never heard of it in my 10 years as a DBA, and after some quick reading, Microsoft's best practice advisement is to not run it routinely.sp_spaceused
procedure, etc, which will automatically be updated by the background processes of SQL Server anyway. If you guys aren't immediately dependent on thesp_spaceused
procedure after youDROP
and re-create the tables, then I don't think there's a need for you to be running this at all. It'll be just a waste of resources and cause contention.