Thursday, August 28, 2014

SQL Server 2014 dbcc checkdb: More pages deallocated from tempdb than allocated?

I've been testing checkdb in SQL Server 2014.  I started by testing checkdb with physical_only.

As I'm testing, I have perfmon running, collecting all kinds of good stuff.  I also peek in from time to time in the DMVs, watching things that I'm curious about.  The tempdb footprint of checkdb is certainly something I am curious about.

I remember @SQLSoldier Robert Davis and Vicky Harp blogging about a condition earlier where a system process would be credited with deferred drop tempdb deallocations rather than the task that originally requested the tempdb allocations. Check that behavior out at the links below.

This looks like something almost opposite: in this case, the deallocations attributed to checkdb are higher than allocations:-)
Not alarmingly so, mind you.  The deallocations are greater than allocations by 256 - 8 = 248 8k pages.  Not much.  Just enough to make me curious.

    req.session_id, req.start_time, req.percent_complete,
    tasku.user_objects_alloc_page_count AS user_alloc,
    tasku.user_objects_dealloc_page_count AS user_dealloc,
    tasku.internal_objects_alloc_page_count AS int_alloc,
    tasku.internal_objects_dealloc_page_count AS int_dealloc
 FROM sys.dm_exec_requests AS req
 JOIN sys.dm_db_task_space_usage AS tasku
    ON tasku.session_id = req.session_id
 WHERE req.command = 'DBCC TABLE CHECK'
    req.session_id, req.start_time, req.percent_complete,
    sessu.user_objects_alloc_page_count AS user_alloc,
    sessu.user_objects_dealloc_page_count AS user_dealloc,
    sessu.internal_objects_alloc_page_count AS int_alloc,
    sessu.internal_objects_dealloc_page_count AS int_dealloc
 FROM sys.dm_db_session_space_usage AS sessu
 JOIN sys.dm_exec_requests AS req
    ON req.session_id = sessu.session_id
 WHERE req.command = 'DBCC TABLE CHECK'
) AS comp
WHERE comp.int_alloc + comp.user_alloc > 0