(─━┘_└━─)/ 머리만1톤(Head1Ton)

tempdb 공간 사용 현황 확인하기

SELECT          

           SUM (user_object_reserved_page_count)*8 AS user_objects_kb,

           SUM (internal_object_reserved_page_count)*8 AS internal_objects_kb,

           SUM (version_store_reserved_page_count)*8 AS version_store_kb,

           SUM (unallocated_extent_page_count)*8 AS freespace_kb

FROM sys.dm_db_file_space_usage

WHERE database_id = 2;

GO

실행 소요 시간이 가장 긴 상위 50개의 쿼리 정보 조회하기

SELECT TOP 50

             sum(qs.total_worker_time) as total_cpu_time,

             sum(qs.execution_count) as total_execution_count,

             count(*) as  '#_statements',

             qt.dbid, qt.objectid, qs.sql_handle,

             qt.[text]

FROM sys.dm_exec_query_stats as qs

CROSS APPLY sys.dm_exec_sql_text (qs.sql_handle) as qt

GROUP BY qt.dbid,qt.objectid, qs.sql_handle,qt.[text]

ORDER BY sum(qs.total_worker_time) DESC,qs.sql_handle;

GO

CPU Time 순으로 상위 50개 쿼리 정보 조회하기

SELECT TOP 50

qs.total_worker_time/qs.execution_count as [Avg CPU Time],

             substring (qt.text,qs.statement_start_offset/2,

             (case when qs.statement_end_offset = -1

             then len (convert (nvarchar(max), qt.text)) * 2

             else qs.statement_end_offset end -qs.statement_start_offset)/2)

             as query_text,

             qt.dbid,

             qt.objectid

FROM sys.dm_exec_query_stats qs

cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt

ORDER BY [Avg CPU Time] DESC;

GO

평균 I/O가 높은 상위 50개 쿼리 정보 조회하기

SELECT TOP 50

(qs.total_logical_reads + qs.total_logical_writes) /qs.execution_count AS [Avg IO],

SUBSTRING(qt.text,qs.statement_start_offset/2,

                           (case when qs.statement_end_offset = -1

                           then len(convert(nvarchar(max), qt.text)) * 2

                           else qs.statement_end_offset end -qs.statement_start_offset)/2)

                           AS query_text,

                           qt.dbid, dbname=db_name(qt.dbid),

                           qt.objectid,

                           qs.sql_handle,

                           qs.plan_handle

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt

ORDER BY [Avg IO] DESC;

GO

CLR 내에서의 평균 사용 시간 확인하기

SELECT TOP 5 creation_time, last_execution_time, total_clr_time,

total_clr_time/execution_count AS [Avg CLR Time], last_clr_time,

execution_count, (SELECT SUBSTRING(text, statement_start_offset/2,

(CASE WHEN statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max),

text)) * 2

ELSE statement_end_offset END - statement_start_offset)/2)

FROM sys.dm_exec_sql_text(sql_handle)) AS query_text

FROM sys.dm_exec_query_stats

ORDER BY [Avg CLR Time] DESC;

GO

병렬로 실행 중인 프로세스 확인하기

SELECT r.session_id,

             r.request_id,

             max(isnull(exec_context_id, 0)) AS number_of_workers,

             r.sql_handle,

             r.statement_start_offset,

             r.statement_end_offset,

             r.plan_handle

FROM sys.dm_exec_requests r

             join sys.dm_os_tasks t ON r.session_id = t.session_id

             join sys.dm_exec_sessions s ON r.session_id = s.session_id

WHERE s.is_user_process = 0x1

GROUP BY r.session_id, r.request_id, r.sql_handle, r.plan_handle,

             r.statement_start_offset, r.statement_end_offset

HAVING max(isnull(exec_context_id, 0)) > 0;

GO

재컴파일 확인하기

SELECT TOP 25

             sql_text.text,

             sql_handle,

             plan_generation_num,

             execution_count,

             dbid,

             objectid

FROM sys.dm_exec_query_stats a

             CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sql_text

WHERE plan_generation_num >1

ORDER BY plan_generation_num DESC;

GO

현재 실행중인 프로세스의 세션 정보, 쿼리문, CPU 시간 정보 확인하기

SELECT r.session_id

             ,status

             ,wait_type

             ,substring(qt.text,r.statement_start_offset/2,

                           (case when r.statement_end_offset = -1

                           then len(convert(nvarchar(max), qt.text)) * 2

                           else r.statement_end_offset end -                                                          r.statement_start_offset)/2)

             as query_text   --- 현재 실행 중인 일괄 처리 또는 프로시저

             ,qt.dbid   ,qt.objectid

             ,r.cpu_time

             ,r.total_elapsed_time

             ,r.reads

             ,r.writes

             ,r.logical_reads

             ,r.scheduler_id

FROM sys.dm_exec_requests r

CROSS APPLY sys.dm_exec_sql_text(sql_handle) as qt

WHERE r.session_id > 50

ORDER BY r.scheduler_id, r.status, r.session_id;

GO

시스템의 대기 정보 확인하기

SELECT * FROM sys.dm_os_wait_stats

ORDER BY wait_time_ms DESC;

GO

 래치 대기 정보 확인하기

SELECT * FROM sys.dm_os_latch_stats;

GO

대기 통계, 래치 대기 통계 재설정하기

DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);

GO

DBCC SQLPERF('sys.dm_os_latch_stats', CLEAR);

GO

I/O 대기 확인하기

SELECT database_id

                           , file_id

                           , io_stall,io_pending_ms_ticks

                           , scheduler_address

FROM sys.dm_io_virtual_file_stats (NULL, NULL) AS t1,

     sys.dm_io_pending_io_requests AS t2

WHERE t1.file_handle = t2.io_handle;

GO

SELECT * FROM sys.dm_io_virtual_file_stats (NULL, NULL);

GO

SELECT * FROM sys.dm_io_pending_io_requests;

GO

I/O 할당 대기 확인하기

-- Tempdb (DBID=2)

SELECT session_id, wait_duration_ms, resource_description

FROM sys.dm_os_waiting_tasks

WHERE wait_type LIKE 'PAGE%LATCH_%'

AND resource_description LIKE '2:%';

GO

-- 사용자 DB (다음에서 dbid 부분을 db_id() 값을 입력하여 실행하면 됨 )

SELECT session_id, wait_duration_ms, resource_description

FROM sys.dm_os_waiting_tasks

WHERE wait_type LIKE 'PAGE%LATCH_%'

AND resource_description LIKE '5:%';

GO