Monday, September 30, 2019

Observing the [DBCC CHECKDB] parallel object check

This is a query i use to keep tabs on all of the workers when i run [dbcc checkdb] at high DOP :-)

Note: this query won't catch the initial portions of checkdb that always run at DOP 1 - the checkalloc, checkcatalog, etc.


;WITH owt AS
(SELECT exec_context_id, wait_type, 
        MAX(wait_duration_ms) wait_duration_ms
 FROM sys.dm_os_waiting_tasks owt 
 GROUP BY exec_context_id, wait_type)
SELECT capture_tm = getdate(), owt.wait_type, count(*) waiters, 
       MIN(owt.wait_duration_ms) min_wait_ms, 
       MAX(owt.wait_duration_ms) max_wait_ms
FROM sys.dm_exec_requests req
JOIN sys.dm_os_tasks ot ON req.session_id = ot.session_id
LEFT OUTER JOIN owt
     ON ot.session_id = owt.session_id AND ot.exec_context_id = owt.exec_context_id
WHERE req.command IN ('dbcc table check') 
GROUP BY owt.wait_type
ORDER BY waiters DESC;

q

No comments:

Post a Comment