TEMPDB의 페이지 사용량 측정

SQL SERVER 2005 이상

실행 되는 SESSION TEMPDB의 페이지 사용량 측정.

세가지 쿼리다 결과는 같음.

--각 세션별 사용되는 Page, Plan, Statement
WITH BASE AS (
  SELECT session_id, request_id,
 SUM(internal_objects_alloc_page_count) AS [internal object pages alloc used],
 (SUM(internal_objects_alloc_page_count)*1.0/128) AS [internal object alloc space in MB],
 SUM(internal_objects_dealloc_page_count) AS [internal object pages dealloc used],
 (SUM(internal_objects_dealloc_page_count)*1.0/128) AS [internal object dealloc space in MB],
 SUM(user_objects_alloc_page_count) AS [user object pages alloc used],
 (SUM(user_objects_alloc_page_count)*1.0/128) AS [user object alloc space in MB],
 SUM(user_objects_dealloc_page_count) AS [user object pages dealloc used],
 (SUM(user_objects_dealloc_page_count)*1.0/128) AS [user object dealloc space in MB]
  FROM sys.dm_db_task_space_usage 
  GROUP BY session_id, request_id
)
SELECT object_schema_name(R3.objectid,R3.dbid) + '.' + object_name(R3.objectid,R3.dbid) as [object_name], R1.session_id, R1.request_id, 
    R1.[internal object pages alloc used], R1.[internal object alloc space in MB],
 R1.[internal object pages dealloc used], R1.[internal object dealloc space in MB],
 R1.[user object pages alloc used], R1.[user object alloc space in MB],
 R1.[user object pages dealloc used], R1.[user object dealloc space in MB],
    R2.statement_start_offset, R2.statement_end_offset, R4.QUERY_PLAN, R3.text
FROM BASE R1
INNER JOIN sys.dm_exec_requests R2 ON R1.session_id = R2.session_id and R1.request_id = R2.request_id
OUTER APPLY sys.dm_exec_sql_text(R2.sql_handle) AS R3
OUTER APPLY sys.dm_exec_query_plan(R2.plan_handle) AS R4
WHERE R1.session_id > 50
ORDER BY 4 desc

select top 50   
     u.session_id,    
    s.host_name,    
    s.login_name,  
    s.status,  
    s.program_name, 
    sum(u.user_objects_alloc_page_count+u.internal_objects_alloc_page_count)*8 tempdb_space_alloc_kB,    
    sum(u.user_objects_dealloc_page_count+u.internal_objects_dealloc_page_count)*8 tempdb_space_dealloc_kB,   
    sum(u.user_objects_alloc_page_count+u.internal_objects_alloc_page_count -u.user_objects_dealloc_page_count - u.internal_objects_dealloc_page_count)*8 remaining_tempdb_space_alloc_kB    
from sys.dm_db_session_space_usage as u   
    join sys.dm_exec_sessions as s on s.session_id = u.session_id   
where u.database_id = 2   
group by u.session_id, s.host_name,  s.status, s.login_name, s.program_name   
order by 7 desc  

SELECT task.session_id, ses.host_name, ses.login_name
 ,ses.login_time, task.request_id, task.alloc,task.dealloc
    ,object_name(qt.objectid, qt.dbid) as 'SPname'
    ,req.cpu_time,req.logical_reads,req.reads, req.writes  
    ,substring(qt.text,req.statement_start_offset/2,
        (case when req.statement_end_offset = -1
        then len(convert(nvarchar(max), qt.text)) * 2
        else req.statement_end_offset end - req.statement_start_offset)/2) as query
    ,req.statement_start_offset, req.statement_end_offset, req.plan_handle, req.sql_handle
FROM 
       (
             Select session_id, request_id,
                    SUM(internal_objects_alloc_page_count) /128 as alloc,
                    SUM (internal_objects_dealloc_page_count)/128 as dealloc 
              FROM sys.dm_db_task_space_usage  with (nolock)
             GROUP BY session_id, request_id
    ) AS task
inner join  sys.dm_exec_requests AS req  with (nolock) 
       on task.session_id = req.session_id and task.request_id = req.request_id
inner join sys.dm_exec_sessions as ses with(nolock) on req.session_id = ses.session_id
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS qt  
ORDER BY task.alloc DESC