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
What's the reason behind... Please...
ReplyDelete