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.
http://www.sqlsoldier.com/wp/sqlserver/bugsysdm_db_session_space_usagereportingextremelyhightempdballocations

http://vickyharp.com/2013/05/sql-server-bug-incorrect-values-in-sys-dm_db_session_space_usage-for-deferred-drops/

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.



****
SELECT * FROM
(SELECT
    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'
 UNION ALL
 SELECT
    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