1

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.

Improve this question
5
  • Well the first question I have is why do you think you need to run 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.
    – J.D.
    Nov 27, 2022 at 13:27
  • @J.D. Two reasons (1) I inherited the script, so am happy to second guess if there is good foundation but (2) Microsoft's best practice has 2 statements - the first is what everyone quotes and the second that applies here "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." I think it is safe to assume that it was introduced originally because of the daily DROP and CREATE of (up to) 448 tables.
    – AlanK
    Nov 27, 2022 at 15:05
  • @J.D. I have made another discovery which might be related and direct further diagnosis. I have edited the question. Please see the new section "A second and possibly related issue". Many thanks for taking an interest.
    – AlanK
    Nov 27, 2022 at 15:18
  • I'm not sure at the moment what to say about the additional discovery you added (but if you do find anything else out, please continue to update your Post accordingly). I did see the second statement in Microsoft's best practices, but even that states "weekly" (you guys are doing it daily), and also the docs mentioned this procedure is typically for databases that are highly transactional and have a lot of data (the opposite of how you've described your database).
    – J.D.
    Nov 27, 2022 at 16:46
  • Additionally, the point of this procedure is to update meta-data consumed by the 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 the sp_spaceused procedure after you DROP 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.
    – J.D.
    Nov 27, 2022 at 16:47

1 Answer 1

Reset to default
0

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).

Improve this answer
2
  • Thanks for the interest but I cannot mark as answer. In rough order of mention... (1) https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-updateusage-transact-sql?view=sql-server-ver16 See "Best Practices", (2) no not a heap table, not any individual table (I have run UPDATEUSAGE on all individually), database as a whole, (3) agreed, sp_whoisactive identifies 3 processes, all services, common to 4 servers, problem exists on 1 server only, (4) indeed lots of legacy stuff, DB originally 2000, upgraded to 2008, run in compatibility mode subsequently.
    – AlanK
    Dec 3, 2022 at 4:45
  • I've opened a discussion regarding the documentation, we'll see if/what comes out of it. In your case, I understand that you are more interested in why this happens on one instance and not the updateusage command itself, I should add. Heap was just a wild guess. You didn't provide info enough regarding blocking for me to comment on that. If it is a brocking situation, then this need to be identified. If it isn't then you have something else troublesome going on (would smell to me like a data corruption issue).
    – Tibor Karaszi
    Dec 4, 2022 at 14:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.