RiCo技術農場

select * from testsort
order by c5

主要使用 dm_db_task_space_usage

;WITH task_space_usage AS (
    -- SUM alloc/delloc pages
    SELECT session_id,
           request_id,
           SUM(internal_objects_alloc_page_count) AS alloc_pages,
           SUM(internal_objects_dealloc_page_count) AS dealloc_pages
    FROM sys.dm_db_task_space_usage WITH (NOLOCK)
    WHERE session_id =57--<> @@SPID
    GROUP BY session_id, request_id
)
SELECT TSU.session_id,
       TSU.alloc_pages * 1.0 / 128 AS [internal object MB space],
       TSU.dealloc_pages * 1.0 / 128 AS [internal object dealloc MB space],
       EST.text,
       -- Extract statement from sql text
       ISNULL(
           NULLIF(
               SUBSTRING(
                   EST.text, 
                   ERQ.statement_start_offset / 2, 
                   CASE WHEN ERQ.statement_end_offset < ERQ.statement_start_offset THEN 0 ELSE( ERQ.statement_end_offset - ERQ.statement_start_offset ) / 2 END
               ), ''
           ), EST.text
       ) AS [statement text],
       EQP.query_plan
FROM task_space_usage AS TSU
INNER JOIN sys.dm_exec_requests ERQ WITH (NOLOCK)
    ON  TSU.session_id = ERQ.session_id
    AND TSU.request_id = ERQ.request_id
OUTER APPLY sys.dm_exec_sql_text(ERQ.sql_handle) AS EST
OUTER APPLY sys.dm_exec_query_plan(ERQ.plan_handle) AS EQP
WHERE EST.text IS NOT NULL OR EQP.query_plan IS NOT NULL
ORDER BY 3 DESC, 5 DESC

搭配dm_io_virtual_file_stats 和 database_files 可進一步確認讀和寫大小

declare @read   bigint, @write  bigint;        
select  @read = SUM(num_of_bytes_read), @write = SUM(num_of_bytes_written) 
from    tempdb.sys.database_files AS DBFiles
join    sys.dm_io_virtual_file_stats(2, NULL) AS FS
        on FS.file_id = DBFiles.file_id
where   DBFiles.type_desc = 'ROWS'

--查詢測試
select * from testsort
order by c5

select  internal_useMB = 
            (
            select  internal_objects_alloc_page_count / 128.0
            from    sys.dm_db_task_space_usage
            where   session_id = 56--@@SPID
            ),
tempdb_readMB = (SUM(num_of_bytes_read) - @read) / 1024.0 / 1024.0, 
tempdb_writeMB = (SUM(num_of_bytes_written) - @write) / 1024.0 / 1024.0
        
from    tempdb.sys.database_files AS DBFiles
join    sys.dm_io_virtual_file_stats(2, NULL) AS FS
        on FS.file_id = DBFiles.file_id
where   DBFiles.type_desc = 'ROWS'

參考

TempDB usage per active session