I've got a nightly maintenance job (largely taken from the ola hallengren maintenance package) that runs database backups and runs an an integrity check on every database.

The integrity check part is running online, against production dbs, and usually completes in about ~2.5 hours - but sometimes it runs for much, much longer (12+ hours in some instances).

There aren't any other scheduled jobs or other activity that is running on the days where it hangs vs. the days where it doesn't, and the db it's usually hanging on is only about 170gb.

I took a look at my wait stats:enter image description here

And my write stalls: enter image description here

But I'm not a DBA by training and am not sure what to actually do about this information, or if it's even where I should be looking for this particular issue.

  • You should look for column granted_query_memory from DMV sys.dm_exec_requests and columns requested_memory_kb,granted_memory_kb,required_memory_kb from DMV sys.dm_exec_query_memory_grants. Run the DMV's next time when you run the checkdb. This is to check if checkdb is requested more memory than required. – Shanky May 28 at 8:40
  • Took a look at this. requested_memory_kb = 31678040, granted_memory_kb = 31678040, required_memory_kb = 512 from sys.dm_exec_query_memory_grants. granted_query_memory = 3959755 from sys.dm_exec_requests – rheinz May 29 at 13:07
  • What is total size of DB and what is size of biggest DB. What is max server memory allocated. What is total RAM on system ? – Shanky May 29 at 13:28
  • Total size of all DB's on server is ~500gb, but the particular database it is choking on is 169.6gb. Max server memory allocated to SQL is 224gb, total VM memory is 265gb. – rheinz Jun 2 at 15:01
  • Ok so from memory grant it seems fine. I would look at disk latency on tempdb. I believe this may be causing the issue. – Shanky Jun 3 at 8:34

Was there an error from CHECKDB for those occasions when it took so long? Anything useful in the errorlog file for those occasions?

If CHECKDB find an error, it might do a "deep dive" to further investigate (traverse the linked list for the index instead of just checking a bitmap, mentioned here: https://www.sqlskills.com/blogs/paul/checkdb-from-every-angle-complete-description-of-all-checkdb-stages/).

| improve this answer | |

I looks like 'Avg Stall(ms)' is very high, I believe that the recommendation is for reads to be <15ms and writes <1ms, and the average's at 355ms for some of the temp files..

Maby you could measure disk latency with perfmon while DBCC is running: https://docs.microsoft.com/sv-se/archive/blogs/askcore/measuring-disk-latency-with-windows-performance-monitor-perfmon

If the disks are a problem then keeping data, logs and tempdb files on different (physical) disks might help.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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