[Performance Tuning] Performance Health Status - DMV(2005)

Contents

1 Wait Type

2 I/O(읽기)

3 I/O(쓰기)

4 데이터베이스별 인덱스 누락수

5 모든 데이터베이스에 대해 가장 비용이 높은 누락된 인덱스

6 가장 비용이 높은 사용되지 않는 인덱스

7 사용되고 있는 인덱스 중 기본 테이블을 변경할 때 비용이 가능 높은 인덱스

8 자주 사용되는 인덱스

9 논리적으로 조각화가 가장 심한 인덱스 확인

10 I/O 비용이 가장 높은 쿼리 확인

11 CPU 비용이 가장 높은 쿼리 확인

12 가장 비용이 높은 CLR 쿼리 확인

13 가장 자주 실행되는 쿼리 확인

14 가장 자주 블로킹 당하는 쿼리 확인

15 계획이 가장 적게 재사용되는 쿼리 확인

16 Retrieve Buffer Counts by Object and Index

17 Determine CPU Resources Required for Optimization

18 Retrieve Parallel Statements With the Highest Worker Time

19 Retrieve Statements with the Highest Plan Re-Use Counts

20 Retrieve Statements with the Lowest Plan Re-Use Counts

21 Determine Index Cost Benefits

22 List Indexes With the Most Contention

23 Retrieve Index Usage Statistics

24 Retrieve Tables, Indexes, Files, and File Groups Information

25 Calculate Average Stalls

26 List Rarely-Used Indexes

27 List Statements By Input/Output Usage

28 Compare Single-Use and Re-Used Plans

29 List Statements By Plan Re-Use Count

30 List Real Time Tempdb Task Usage

31 List Real-Time Tempdb Statements

32 Retrieve a SQL Statement with a Specified .SQL_Handle

33 List Runnable Queues

34 List Recompiled Statements

35 List Currently-Executing Parallel Plans

36 List Cached Plans Where Worker Time Exceeds Elapsed Time

37 List Real-Time Blocker and Waiter Statements

38 Report Blocker and Waiter SQL Statements

39 Compare Signal Waits and Resource Waits

40 List Currently-Executing Statements

41 List Scheduler Wait List Information

42 List Schedulers, Workers, and Runnable Queues

43 List Session and Scheduler ID Information

44 List SQLOS Execution Model Information

45 List Statements from a Specified Waiter List

46 마지막으로 통계한 날짜

47 현재 Buffer의 Clean/Dirty Page

48 객체의 메모리 및 디스크 사용 모니터링

49 Cache Object Monitoring Script

50 Source Code Download 및 참고자료

[edit]

1 Wait Type #

SELECT TOP 10
 [Wait type] = wait_type,
 [Wait time (s)] = wait_time_ms / 1000,
 [% waiting] = CONVERT(DECIMAL(12,2), wait_time_ms * 100.0 
               / SUM(wait_time_ms) OVER())
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE '%SLEEP%' 
ORDER BY wait_time_ms DESC;

track_waitstats_2005.sql
get_waitstats_2005.sql

Wait Type	원인	해결
OLEDB	OLE DB 공급자호출 (4 Part Name 호출, 원격프로시저호출, OPENQUERY, OPENROWSET, DMV호출, 프로파일러추적)	OLE DB를 사용한 어플리케이션, 쿼리튜닝
CXPACKET	병렬처리로 인한 대기	병렬처리를 발생시키는 쿼리 튜닝, 누락된 인덱스 추가
PAGEIOLATCH_*, PAGELATCH_*	PAGEIOLATCH_*: 데이터 페이지의 I/O가 완료할 때까지 대기 PAGELATCH_* : 동일 페이지 Insert경합, 자동증가, 페이지 분할등	메모리증설, 쿼리튜닝, 인덱스 추가, 디스크 증설, 데이터파일 추가
WRITELOG, IO_COMPLETION	비-데이터 페이지(트랜잭션 로그등) 의 IO가 완료할 때까지 대기	* IO의분산, IO 대역폭의 추가, 트랜잭션 로그 드라이브 분리
LCK_*	다른 세션에 의해 개체(행, 페이지, 테이블)의 잠금 설정	공유 잠금에 대해서는 트랜잭션 격리수준 조정, 트랜잭션의 지속시간 최소화
Latch_*	버퍼 페이지를 제외한 나머지 내부 캐시의 경합해결을 위한 동기화 개체	메모리 압박
CMEMTHREAD	동시에 여러 개의 쿼리 실행하여, 메모리 할당 곤란	AdHoc Query 최소화
RESOURCE_SEMAPHORE	쿼리를 실행하기 위한 메모리 부족	메모리증설, 대량 메모리요청 작업(해싱, 정렬등) 쿼리튜닝

- http://support.microsoft.com/kb/822101
- http://msdn.microsoft.com/ko-kr/library/ms179984.aspx

[edit]

2 I/O(읽기) #

SELECT TOP 10 
        [Total Reads] = SUM(total_logical_reads)
        ,[Execution count] = SUM(qs.execution_count)
        ,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
GROUP BY DB_NAME(qt.dbid)
ORDER BY [Total Reads] DESC;

[edit]

3 I/O(쓰기) #

SELECT TOP 10 
        [Total Writes] = SUM(total_logical_writes)
        ,[Execution count] = SUM(qs.execution_count)
        ,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
GROUP BY DB_NAME(qt.dbid)
ORDER BY [Total Writes] DESC;

[edit]

4 데이터베이스별 인덱스 누락수 #

SELECT 
    DatabaseName = DB_NAME(database_id)
    ,[Number Indexes Missing] = count(*) 
FROM sys.dm_db_missing_index_details
GROUP BY DB_NAME(database_id)
ORDER BY 2 DESC;

[edit]

5 모든 데이터베이스에 대해 가장 비용이 높은 누락된 인덱스 #

SELECT  TOP 10 
        [Total Cost]  = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0) 
        , avg_user_impact
        , TableName = statement
        , [EqualityUsage] = equality_columns 
        , [InequalityUsage] = inequality_columns
        , [Include Cloumns] = included_columns
FROM        sys.dm_db_missing_index_groups g 
INNER JOIN    sys.dm_db_missing_index_group_stats s 
       ON s.group_handle = g.index_group_handle 
INNER JOIN    sys.dm_db_missing_index_details d 
       ON d.index_handle = g.index_handle
ORDER BY [Total Cost] DESC;

[edit]

6 가장 비용이 높은 사용되지 않는 인덱스 #

-- 필요한 테이블 구조만 작성합니다.
-- 참고: 이 SQL은 다음 단계에서 지정된 데이터베이스 루프 내에 있어야 합니다.
SELECT TOP 1
        DatabaseName = DB_NAME()
        ,TableName = OBJECT_NAME(s.[object_id])
        ,IndexName = i.name
        ,user_updates    
        ,system_updates    
        -- 기타 유용한 필드를 아래에 나열
        --, *
INTO #TempUnusedIndexes
FROM   sys.dm_db_index_usage_stats s 
INNER JOIN sys.indexes i ON  s.[object_id] = i.[object_id] 
    AND s.index_id = i.index_id 
WHERE  s.database_id = DB_ID()
    AND OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0
    AND    user_seeks = 0
    AND user_scans = 0 
    AND user_lookups = 0
    AND s.[object_id] = -999  -- 테이블 구조를 얻기 위한 임시 값
;

-- 서버의 모든 데이터베이스를 대상으로 반복합니다.
EXEC sp_MSForEachDB    'USE [?]; 
-- 테이블이 이미 있는 경우
INSERT INTO #TempUnusedIndexes 
SELECT TOP 10    
        DatabaseName = DB_NAME()
        ,TableName = OBJECT_NAME(s.[object_id])
        ,IndexName = i.name
        ,user_updates    
        ,system_updates    
FROM   sys.dm_db_index_usage_stats s 
INNER JOIN sys.indexes i ON  s.[object_id] = i.[object_id] 
    AND s.index_id = i.index_id 
WHERE  s.database_id = DB_ID()
    AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0
    AND    user_seeks = 0
    AND user_scans = 0 
    AND user_lookups = 0
    AND i.name IS NOT NULL    -- HEAP 인덱스 무시
ORDER BY user_updates DESC
;
'

-- 레코드 선택
SELECT TOP 10 * FROM #TempUnusedIndexes ORDER BY [user_updates] DESC
-- 임시 테이블 정리
DROP TABLE #TempUnusedIndexes

[edit]

7 사용되고 있는 인덱스 중 기본 테이블을 변경할 때 비용이 가능 높은 인덱스 #

-- 필요한 테이블 구조만 작성합니다.
-- 참고: 이 SQL은 다음 단계에서 지정된 데이터베이스 루프 내에 있어야 합니다.
SELECT TOP 1
        [Maintenance cost]  = (user_updates + system_updates)
        ,[Retrieval usage] = (user_seeks + user_scans + user_lookups)
        ,DatabaseName = DB_NAME()
        ,TableName = OBJECT_NAME(s.[object_id])
        ,IndexName = i.name
INTO #TempMaintenanceCost
FROM   sys.dm_db_index_usage_stats s 
INNER JOIN sys.indexes i ON  s.[object_id] = i.[object_id] 
    AND s.index_id = i.index_id
WHERE s.database_id = DB_ID() 
    AND OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0
    AND (user_updates + system_updates) > 0 – 활성 행에 대해서만 보고
    AND s.[object_id] = -999  -- 테이블 구조를 얻기 위한 임시 값
;

-- 서버의 모든 데이터베이스를 대상으로 반복합니다.
EXEC sp_MSForEachDB    'USE [?]; 
-- 테이블이 이미 있는 경우
INSERT INTO #TempMaintenanceCost 
SELECT TOP 10
        [Maintenance cost]  = (user_updates + system_updates)
        ,[Retrieval usage] = (user_seeks + user_scans + user_lookups)
        ,DatabaseName = DB_NAME()
        ,TableName = OBJECT_NAME(s.[object_id])
        ,IndexName = i.name
FROM   sys.dm_db_index_usage_stats s 
INNER JOIN sys.indexes i ON  s.[object_id] = i.[object_id] 
    AND s.index_id = i.index_id
WHERE s.database_id = DB_ID() 
    AND i.name IS NOT NULL    -- HEAP 인덱스 무시
    AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0
    AND (user_updates + system_updates) > 0 -- 활성 행에 대해서만 보고
ORDER BY [Maintenance cost]  DESC
;
'

-- 레코드 선택
SELECT TOP 10 * FROM #TempMaintenanceCost 
ORDER BY [Maintenance cost]  DESC
-- 임시 테이블 정리
DROP TABLE #TempMaintenanceCost

[edit]

8 자주 사용되는 인덱스 #

-- 필요한 테이블 구조만 작성합니다.
-- 참고: 이 SQL은 다음 단계에서 지정된 데이터베이스 루프 내에 있어야 합니다.
SELECT TOP 1
        [Usage] = (user_seeks + user_scans + user_lookups)
        ,DatabaseName = DB_NAME()
        ,TableName = OBJECT_NAME(s.[object_id])
        ,IndexName = i.name
INTO #TempUsage
FROM   sys.dm_db_index_usage_stats s 
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] 
    AND s.index_id = i.index_id 
WHERE   s.database_id = DB_ID() 
    AND OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0
    AND (user_seeks + user_scans + user_lookups) > 0 
-- 활성 행에 대해서만 보고
    AND s.[object_id] = -999  -- 테이블 구조를 얻기 위한 임시 값
;

-- 서버의 모든 데이터베이스를 대상으로 반복합니다.
EXEC sp_MSForEachDB    'USE [?]; 
-- 테이블이 이미 있는 경우
INSERT INTO #TempUsage 
SELECT TOP 10
        [Usage] = (user_seeks + user_scans + user_lookups)
        ,DatabaseName = DB_NAME()
        ,TableName = OBJECT_NAME(s.[object_id])
        ,IndexName = i.name
FROM   sys.dm_db_index_usage_stats s 
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] 
    AND s.index_id = i.index_id 
WHERE   s.database_id = DB_ID() 
    AND i.name IS NOT NULL    -- HEAP 인덱스 무시
    AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0
    AND (user_seeks + user_scans + user_lookups) > 0 -- 활성 행에 대해서만 보고
ORDER BY [Usage]  DESC
;
'

-- 레코드 선택
SELECT TOP 10 * FROM #TempUsage ORDER BY [Usage] DESC
-- 임시 테이블 정리
DROP TABLE #TempUsage

[edit]

9 논리적으로 조각화가 가장 심한 인덱스 확인 #

-- 필요한 테이블 구조만 작성합니다.
-- 참고: 이 SQL은 다음 단계에서 지정된 데이터베이스 루프 내에 있어야 합니다.
SELECT TOP 1 
        DatbaseName = DB_NAME()
        ,TableName = OBJECT_NAME(s.[object_id])
        ,IndexName = i.name
        ,[Fragmentation %] = ROUND(avg_fragmentation_in_percent,2)
INTO #TempFragmentation
FROM sys.dm_db_index_physical_stats(db_id(),null, null, null, null) s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] 
    AND s.index_id = i.index_id 
WHERE s.[object_id] = -999  -- 테이블 구조를 얻기 위한 임시 값
;

-- 서버의 모든 데이터베이스를 대상으로 반복합니다.
EXEC sp_MSForEachDB    'USE [?]; 
-- 테이블이 이미 있는 경우
INSERT INTO #TempFragmentation 
SELECT TOP 10
        DatbaseName = DB_NAME()
        ,TableName = OBJECT_NAME(s.[object_id])
        ,IndexName = i.name
        ,[Fragmentation %] = ROUND(avg_fragmentation_in_percent,2)
FROM sys.dm_db_index_physical_stats(db_id(),null, null, null, null) s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] 
    AND s.index_id = i.index_id 
WHERE s.database_id = DB_ID() 
      AND i.name IS NOT NULL    -- HEAP 인덱스 무시
    AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0
ORDER BY [Fragmentation %] DESC
;
'

-- 레코드 선택
SELECT TOP 10 * FROM #TempFragmentation ORDER BY [Fragmentation %] DESC
-- 임시 테이블 정리
DROP TABLE #TempFragmentation

[edit]

10 I/O 비용이 가장 높은 쿼리 확인 #

SELECT TOP 10 
 [Average IO] = (total_logical_reads + total_logical_writes) / qs.execution_count
,[Total IO] = (total_logical_reads + total_logical_writes)
,[Execution count] = qs.execution_count
,[Individual Query] = 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) 
        ,[Parent Query] = qt.text
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Average IO] DESC;

[edit]

11 CPU 비용이 가장 높은 쿼리 확인 #

SELECT TOP 10 
 [Average CPU used] = total_worker_time / qs.execution_count
,[Total CPU used] = total_worker_time
,[Execution count] = qs.execution_count
,[Individual Query] = 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)
,[Parent Query] = qt.text
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Average CPU used] DESC;

[edit]

12 가장 비용이 높은 CLR 쿼리 확인 #

SELECT TOP 10 
 [Average CLR Time] = total_clr_time / execution_count 
,[Total CLR Time] = total_clr_time 
,[Execution count] = qs.execution_count
,[Individual Query] = 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)
,[Parent Query] = qt.text
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats as qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
WHERE total_clr_time <> 0
ORDER BY [Average CLR Time] DESC;

[edit]

13 가장 자주 실행되는 쿼리 확인 #

SELECT TOP 10 
 [Execution count] = execution_count
,[Individual Query] = 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)
,[Parent Query] = qt.text
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Execution count] DESC;

[edit]

14 가장 자주 블로킹 당하는 쿼리 확인 #

SELECT TOP 10 
 [Average Time Blocked] = (total_elapsed_time - total_worker_time) / qs.execution_count
,[Total Time Blocked] = total_elapsed_time - total_worker_time 
,[Execution count] = qs.execution_count
,[Individual Query] = 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) 
,[Parent Query] = qt.text
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Average Time Blocked] DESC;

[edit]

15 계획이 가장 적게 재사용되는 쿼리 확인 #

SELECT TOP 10
 [Plan usage] = cp.usecounts
,[Individual Query] = 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)
,[Parent Query] = qt.text
,DatabaseName = DB_NAME(qt.dbid)
,cp.cacheobjtype
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
INNER JOIN sys.dm_exec_cached_plans as cp on qs.plan_handle=cp.plan_handle
WHERE cp.plan_handle=qs.plan_handle
ORDER BY [Plan usage] ASC;

[edit]

16 Retrieve Buffer Counts by Object and Index #

select b.database_id, db=db_name(b.database_id)
		,p.object_id
		,object_name(p.object_id) as objname
		,p.index_id
		,buffer_count=count(*)
from sys.allocation_units a,
		sys.dm_os_buffer_descriptors b,
		sys.partitions p
where a.allocation_unit_id = b.allocation_unit_id
and a.container_id = p.hobt_id
and b.database_id = db_id()
group by b.database_id,p.object_id, p.index_id
order by buffer_count desc

[edit]

17 Determine CPU Resources Required for Optimization #

Select * from sys.dm_exec_query_optimizer_info
where counter in ('optimizations','elapsed time','trivial plan','tables','insert stmt','update stmt','delete stmt')

[edit]

18 Retrieve Parallel Statements With the Highest Worker Time #

SELECT TOP 50 qs.total_worker_time,
			qs.total_elapsed_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, 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
where qs.total_worker_time > qs.total_elapsed_time
ORDER BY 
       qs.total_worker_time DESC

[edit]

19 Retrieve Statements with the Highest Plan Re-Use Counts #

SELECT TOP 100
        qs.sql_handle
		,qs.plan_handle
		,cp.cacheobjtype
		,cp.usecounts
		,cp.size_in_bytes  
		,qs.statement_start_offset
		,qs.statement_end_offset
		,qt.dbid
		,qt.objectid
		,qt.text
		,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 statement
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
inner join sys.dm_exec_cached_plans as cp on qs.plan_handle=cp.plan_handle
where cp.plan_handle=qs.plan_handle
--and qt.dbid = db_id()
ORDER BY [dbid],[Usecounts] DESC

[edit]

20 Retrieve Statements with the Lowest Plan Re-Use Counts #

SELECT TOP 50
        cp.cacheobjtype
		,cp.usecounts
		,size=cp.size_in_bytes  
		,stmt_start=qs.statement_start_offset
		,stmt_end=qs.statement_end_offset
		,qt.dbid
		,qt.objectid
		,qt.text
		,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 statement
		,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
inner join sys.dm_exec_cached_plans as cp on qs.plan_handle=cp.plan_handle
where cp.plan_handle=qs.plan_handle
and qt.dbid is NULL
ORDER BY [usecounts],[statement] asc

[edit]

21 Determine Index Cost Benefits #

/*
select 'object'=object_name(o.object_id), o.index_id
		, reads=range_scan_count + singleton_lookup_count
		, 'leaf_writes'=leaf_insert_count+leaf_update_count+ leaf_delete_count 
		, 'leaf_page_splits' = leaf_allocation_count
		, 'nonleaf_writes'=nonleaf_insert_count + nonleaf_update_count + nonleaf_delete_count
		, 'nonleaf_page_splits' = nonleaf_allocation_count
from sys.dm_db_index_operational_stats (10,NULL,NULL,NULL) o
where objectproperty(o.object_id,'IsUserTable') = 1
order by reads desc, leaf_writes, nonleaf_writes
go
*/
declare @dbid int
select @dbid = db_id('Northwind')
--- sys.dm_db_index_usage_stats
select 'object' = object_name(object_id),index_id
		,'user reads' = user_seeks + user_scans + user_lookups
		,'system reads' = system_seeks + system_scans + system_lookups
		,'user writes' = user_updates
		,'system writes' = system_updates
from sys.dm_db_index_usage_stats
where objectproperty(object_id,'IsUserTable') = 1
and database_id = @dbid
order by 'user reads' desc

select 'object'=object_name(o.object_id), o.index_id
		, 'usage_reads'=user_seeks + user_scans + user_lookups
		, 'operational_reads'=range_scan_count + singleton_lookup_count
		, range_scan_count
		, singleton_lookup_count
		, 'usage writes' =  user_updates
		, 'operational_leaf_writes'=leaf_insert_count+leaf_update_count+ leaf_delete_count 
		, leaf_insert_count,leaf_update_count,leaf_delete_count 
		, 'operational_leaf_page_splits' = leaf_allocation_count
		, 'operational_nonleaf_writes'=nonleaf_insert_count + nonleaf_update_count + nonleaf_delete_count
		, 'operational_nonleaf_page_splits' = nonleaf_allocation_count
from sys.dm_db_index_operational_stats (@dbid,NULL,NULL,NULL) o
	,sys.dm_db_index_usage_stats u
where objectproperty(o.object_id,'IsUserTable') = 1
and u.object_id = o.object_id
and u.index_id = o.index_id
order by operational_reads desc, operational_leaf_writes, operational_nonleaf_writes
go

[edit]

22 List Indexes With the Most Contention #

declare @dbid int
select @dbid = db_id()
Select dbid=database_id, objectname=object_name(s.object_id)
	, indexname=i.name, i.index_id	--, partition_number
	, row_lock_count, row_lock_wait_count
	, [block %]=cast (100.0 * row_lock_wait_count / (1 + row_lock_count) as numeric(15,2))
	, row_lock_wait_in_ms
	, [avg row lock waits in ms]=cast (1.0 * row_lock_wait_in_ms / (1 + row_lock_wait_count) as numeric(15,2))
from sys.dm_db_index_operational_stats (@dbid, NULL, NULL, NULL) s
	,sys.indexes i
where objectproperty(s.object_id,'IsUserTable') = 1
and i.object_id = s.object_id
and i.index_id = s.index_id
order by row_lock_wait_count desc

[edit]

23 Retrieve Index Usage Statistics #

select 
	* 
from 
	sys.dm_db_index_usage_stats 
order by 
	user_updates desc

[edit]

24 Retrieve Tables, Indexes, Files, and File Groups Information #

select 'table_name'=object_name(i.id)
		,i.indid
		,'index_name'=i.name
		,i.groupid
		,'filegroup'=f.name
		,'file_name'=d.physical_name
		,'dataspace'=s.name
from	sys.sysindexes i
		,sys.filegroups f
		,sys.database_files d
		,sys.data_spaces s
where objectproperty(i.id,'IsUserTable') = 1
and f.data_space_id = i.groupid
and f.data_space_id = d.data_space_id
and f.data_space_id = s.data_space_id
order by f.name,object_name(i.id),groupid
go

[edit]

25 Calculate Average Stalls #

select database_id, file_id
	,io_stall_read_ms
	,num_of_reads
	,cast(io_stall_read_ms/(1.0+num_of_reads) as numeric(10,1)) as 'avg_read_stall_ms'
	,io_stall_write_ms
	,num_of_writes
	,cast(io_stall_write_ms/(1.0+num_of_writes) as numeric(10,1)) as 'avg_write_stall_ms'
	,io_stall_read_ms + io_stall_write_ms as io_stalls
	,num_of_reads + num_of_writes as total_io
	,cast((io_stall_read_ms+io_stall_write_ms)/
         (1.0+num_of_reads + num_of_writes) as numeric(10,1)) as 'avg_io_stall_ms'
from sys.dm_io_virtual_file_stats(null,null)
order by avg_io_stall_ms desc

[edit]

26 List Rarely-Used Indexes #

declare @dbid int
select @dbid = db_id()
select objectname=object_name(s.object_id), s.object_id
	, indexname=i.name, i.index_id
	, user_seeks, user_scans, user_lookups, user_updates
from sys.dm_db_index_usage_stats s,
	sys.indexes i
where database_id = @dbid 
and objectproperty(s.object_id,'IsUserTable') = 1
and i.object_id = s.object_id
and i.index_id = s.index_id
order by (user_seeks + user_scans + user_lookups + user_updates) asc

[edit]

27 List Statements By Input/Output Usage #

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

[edit]

28 Compare Single-Use and Re-Used Plans #

declare @single int, @reused int, @total int

select @single=
	sum(case(usecounts)
		when 1 then 1
		else 0
	end),
	@reused=
	sum(case(usecounts)
		when 1 then 0
		else 1
	end),
	@total=count(usecounts)
from sys.dm_exec_cached_plans

select 
'Single use plans (usecounts=1)'= @single,
'Re-used plans (usecounts>1)'= @reused,
're-use %'=cast(100.0*@reused / @total as dec(5,2)),
'total usecounts'=@total


select 'single use plan size'=sum(cast(size_in_bytes as bigint))
from sys.dm_exec_cached_plans
where usecounts = 1

[edit]

29 List Statements By Plan Re-Use Count #

SELECT TOP 50
        qs.sql_handle
		,qs.plan_handle
		,cp.cacheobjtype
		,cp.usecounts
		,cp.size_in_bytes  
		,qs.statement_start_offset
		,qs.statement_end_offset
		,qt.dbid
		,qt.objectid
		,qt.text
		,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 statement
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
inner join sys.dm_exec_cached_plans as cp on qs.plan_handle=cp.plan_handle
where cp.plan_handle=qs.plan_handle
--and qt.dbid = db_id()
ORDER BY [Usecounts] DESC

[edit]

30 List Real Time Tempdb Task Usage #

SELECT t1.session_id,
(t1.internal_objects_alloc_page_count + task_alloc) as allocated,
(t1.internal_objects_dealloc_page_count + task_dealloc) as
 deallocated 
from sys.dm_db_session_space_usage as t1, 
(select session_id, 
   sum(internal_objects_alloc_page_count)
   			as task_alloc,
   sum (internal_objects_dealloc_page_count) as 
		task_dealloc 
      from sys.dm_db_task_space_usage group by session_id) as t2
where t1.session_id = t2.session_id and t2.session_id >50
order by allocated DESC

[edit]

31 List Real-Time Tempdb Statements #

SELECT t1.session_id,
(t1.internal_objects_alloc_page_count + task_alloc) as allocated,
(t1.internal_objects_dealloc_page_count + task_dealloc) as deallocated
	, t3.sql_handle, t3.statement_start_offset
	, t3.statement_end_offset, t3.plan_handle
from sys.dm_db_session_space_usage as t1, 
		sys.dm_exec_requests t3,
(select session_id, 
   sum(internal_objects_alloc_page_count) as task_alloc,
   sum (internal_objects_dealloc_page_count) as task_dealloc
      from sys.dm_db_task_space_usage group by session_id) as t2
where t1.session_id = t2.session_id and t1.session_id >50
and t1.database_id = 2   --- tempdb is database_id=2
and t1.session_id = t3.session_id
order by allocated DESC

[edit]

32 Retrieve a SQL Statement with a Specified .SQL_Handle #

create proc get_sql_text (@sql_handle varbinary(64)=NULL
					,@stmtstart int=NULL
					,@stmtend int =NULL)
as

if @sql_handle is NULL 
	or @stmtstart is NULL 
	or @stmtend is NULL
begin
	print 'you must provide sqlhandle, stmtstart, and stmtend'
	return -999
end

select 
		 substring(qt.text,s.statement_start_offset/2, 
			(case when s.statement_end_offset = -1 
			then len(convert(nvarchar(max), qt.text)) * 2 
			else s.statement_end_offset end -s.statement_start_offset)/2) 
		as "SQL statement"
		,s.statement_start_offset
		,s.statement_end_offset
		,batch=qt.text
		,qt.dbid
		,qt.objectid
		,s.execution_count
		,s.total_worker_time
		,s.total_elapsed_time
		,s.total_logical_reads
		,s.total_physical_reads
		,s.total_logical_writes
from sys.dm_exec_query_stats s
cross apply sys.dm_exec_sql_text(s.sql_handle) as qt
where s.sql_handle = @sql_handle
and s.statement_start_offset = @stmtstart
and s.statement_end_offset = @stmtend
go
exec get_sql_text @sql_handle = 0x0300050014ba910b5a89af00bb9600000100000000000000,@stmtstart = 84,@stmtend = 210
go

--Retrieve SQL Text and XML Plans
select 
(select text from sys.dm_exec_sql_text(put_sql_handle_here)) as sql_text
,(select query_plan from sys.dm_exec_query_plan(put_plan_handle_here)) as query_plan
go

[edit]

33 List Runnable Queues #

select scheduler_id, session_id, status, command 
from sys.dm_exec_requests
where status = 'runnable'
and session_id > 50
order by scheduler_id

[edit]

34 List Recompiled Statements #

select top 25
	--sql_text.text,
	sql_handle,
	plan_generation_num,
	substring(text,qs.statement_start_offset/2, 
			(case when qs.statement_end_offset = -1 
			then len(convert(nvarchar(max), text)) * 2 
			else qs.statement_end_offset end - qs.statement_start_offset)/2) 
		as stmt_executing,
	execution_count,
	dbid,
	objectid 
from sys.dm_exec_query_stats as qs
	Cross apply sys.dm_exec_sql_text(sql_handle) sql_text
where plan_generation_num >1
order by sql_handle, plan_generation_num

[edit]

35 List Currently-Executing Parallel Plans #

select 
  qs.sql_handle, 
  qs.statement_start_offset, 
  qs.statement_end_offset, 
  q.dbid,
  q.objectid,
  q.number,
  q.encrypted,
  q.text
from sys.dm_exec_query_stats qs
	cross apply sys.dm_exec_sql_text(qs.plan_handle) as q
where qs.total_worker_time > qs.total_elapsed_time

[edit]

36 List Cached Plans Where Worker Time Exceeds Elapsed Time #

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

[edit]

37 List Real-Time Blocker and Waiter Statements #

select t1.resource_type
	,db_name(resource_database_id) as [database]
	,t1.resource_associated_entity_id as [blk object]
	,t1.request_mode
	,t1.request_session_id   -- spid of waiter
	,(select text from sys.dm_exec_requests as r  --- get sql for waiter
		cross apply sys.dm_exec_sql_text(r.sql_handle) 
		where r.session_id = t1.request_session_id) as waiter_text
	,t2.blocking_session_id  -- spid of blocker
     ,(select text from sys.sysprocesses as p		--- get sql for blocker
		cross apply sys.dm_exec_sql_text(p.sql_handle) 
		where p.spid = t2.blocking_session_id) as blocker_text
	from 
	sys.dm_tran_locks as t1, 
	sys.dm_os_waiting_tasks as t2
where 
	t1.lock_owner_address = t2.resource_address
go

[edit]

38 Report Blocker and Waiter SQL Statements #

if exists (select 1 from sysobjects where name = 'sp_block_info')
	drop proc sp_block_info
go
create proc sp_block_info
as

select t1.resource_type as [lock type]
	,db_name(resource_database_id) as [database]
	,t1.resource_associated_entity_id as [blk object]
	,t1.request_mode as [lock req]			-- lock requested
	,t1.request_session_id as [waiter sid]  -- spid of waiter
	,t2.wait_duration_ms as [wait time]	
	,(select text from sys.dm_exec_requests as r  --- get sql for waiter
		cross apply sys.dm_exec_sql_text(r.sql_handle) 
		where r.session_id = t1.request_session_id) as waiter_batch
	,(select 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) 
		from sys.dm_exec_requests as r
		cross apply sys.dm_exec_sql_text(r.sql_handle) as qt
		where r.session_id = t1.request_session_id) as waiter_stmt    
		--- this is the statement executing right now
	 ,t2.blocking_session_id as [blocker sid] -- spid of blocker
     ,(select text from sys.sysprocesses as p		--- get sql for blocker
		cross apply sys.dm_exec_sql_text(p.sql_handle) 
		where p.spid = t2.blocking_session_id) as blocker_stmt
	from 
	sys.dm_tran_locks as t1, 
	sys.dm_os_waiting_tasks as t2
where 
	t1.lock_owner_address = t2.resource_address
go
exec sp_block_info

[edit]

39 Compare Signal Waits and Resource Waits #

Select signal_wait_time_ms=sum(signal_wait_time_ms)
	,'%signal waits' = cast(100.0 * sum(signal_wait_time_ms) / sum (wait_time_ms) as numeric(20,2))
	,resource_wait_time_ms=sum(wait_time_ms - signal_wait_time_ms)
	,'%resource waits'= cast(100.0 * sum(wait_time_ms - signal_wait_time_ms) / 
	  sum (wait_time_ms) as numeric(20,2))
From sys.dm_os_wait_stats

[edit]

40 List Currently-Executing Statements #

select r.session_id
		,status
		,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   --- this is the statement executing right now
		,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

[edit]

41 List Scheduler Wait List Information #

select 
	scheduler_id,
	current_tasks_count,
	runnable_tasks_count,
	current_workers_count,
	active_workers_count,
	work_queue_count,
	load_factor
from sys.dm_os_schedulers
where scheduler_id < 255

[edit]

42 List Schedulers, Workers, and Runnable Queues #

select 
	scheduler_id,
	current_tasks_count,
	runnable_tasks_count,
	current_workers_count,
	active_workers_count,
	work_queue_count,
	load_factor,
	status
from sys.dm_os_schedulers
--where scheduler_id < 255
order by scheduler_id

[edit]

43 List Session and Scheduler ID Information #

select 
	scheduler_id,
	current_tasks_count,
	runnable_tasks_count,
	current_workers_count,
	active_workers_count,
	work_queue_count,
	load_factor,
	status
from sys.dm_os_schedulers
--where scheduler_id < 255
order by scheduler_id

select r.session_id
		,status
		,wait_type
		,r.scheduler_id
		,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 stmt_executing
		,r.sql_handle
		,qt.dbid
		,qt.objectid
		,r.cpu_time
		,r.total_elapsed_time
		,r.reads
		,r.writes
		,r.logical_reads
		,r.plan_handle
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

[edit]

44 List SQLOS Execution Model Information #

select distinct s.scheduler_id as sched
	, r.session_id as sid
	, w.exec_context_id as eid
	--, w.blocking_exec_context_id as beid
	, r.status
	, r.wait_type
	, s.runnable_tasks_count as runnable
	, s.active_workers_count as act_workers
	, s.current_workers_count as cur_workers
from sys.dm_os_schedulers s
left outer join sys.dm_exec_requests r
on s.scheduler_id = r.scheduler_id
left outer join sys.dm_os_waiting_tasks w
on r.session_id = w.session_id
where r.session_id > 50
order by s.scheduler_id
	, r.session_id
	, w.exec_context_id
	, r.status
	, r.wait_type
	, s.runnable_tasks_count
	, s.active_workers_count
go
select distinct session_id, exec_context_id, count(*)
from sys.dm_os_waiting_tasks
where session_id > 50
group by session_id, exec_context_id
order by session_id, exec_context_id

[edit]

45 List Statements from a Specified Waiter List #

select 
	    r.wait_type
		,r.wait_time
        ,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, dbname=db_name(qt.dbid)
		,qt.objectid
		,r.sql_handle
		,r.plan_handle
FROM sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text(r.sql_handle) as qt
where r.session_id > 50

[edit]

46 마지막으로 통계한 날짜 #

select 
	object_name(object_id) obj_name
,	name as stats_name
,    stats_date(object_id, stats_id) as statistics_update_date
from sys.stats 
where object_id > 100
go

[edit]

47 현재 Buffer의 Clean/Dirty Page #

SELECT
   (CASE WHEN ([is_modified] = 1) THEN 'Dirty' ELSE 'Clean' END) AS 'PageState',
   (CASE WHEN ([database_id] = 32767) THEN 'Resource Database' ELSE DB_NAME (database_id) END) AS 'DatabaseName',
   COUNT (*) AS 'PageCount'
FROM sys.dm_os_buffer_descriptors
   GROUP BY [database_id], [is_modified]
   ORDER BY [database_id], [is_modified];

[edit]

48 객체의 메모리 및 디스크 사용 모니터링 #

--ref: http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1334856,00.html
--귀찮아서 대충 했다.
use master;

create table #temp
(
	db_name nvarchar(255)
,	schema_name nvarchar(255)
,	table_name nvarchar(255)
,	memory_space_MB int
,	storage_space_MB bigint
,	percentage_of_object_in_memory numeric(18,2)
)

declare 
	@dbname nvarchar(500)
,	@sql nvarchar(4000)

declare cur cursor for
	select 
		name
	from sys.sysdatabases;

open cur;

fetch next from cur into @dbname
while @@fetch_status not in (-1, -2)
begin

	set @sql = '
		use ' + @dbname + ';
		select 
			db_name() dbname
		,	schema_name(sys.tables.schema_id) schemaname
		,	sys.tables.name tablename
		,	sum(a.page_id)*8/1024 as mmb
		,	sum(sys.allocation_units.data_pages)*8/1024 as smb
		,	case 
					when sum(sys.allocation_units.data_pages) <> 0 then 
						sum(a.page_id)/cast(sum(sys.allocation_units.data_pages) as numeric(18,2)) 
			end as ''obj_in_memory''
		from 
			(
			select 
				database_id, 
				allocation_unit_id, 
				count(page_id) page_id 
			from sys.dm_os_buffer_descriptors 
			group by database_id, allocation_unit_id) a
				inner join sys.allocation_units 
					on a.allocation_unit_id = sys.allocation_units.allocation_unit_id 
				inner join sys.partitions 
					on (sys.allocation_units.type in (1,3)
					and sys.allocation_units.container_id = sys.partitions.hobt_id)
					or (sys.allocation_units.type = 2 
					and sys.allocation_units.container_id = sys.partitions.partition_id)
				inner join sys.tables 
					on sys.partitions.object_id = sys.tables.object_id
					and sys.tables.is_ms_shipped = 0
		where a.database_id = db_id()
		group by schema_name(sys.tables.schema_id), sys.tables.name
		';
		insert #temp exec(@sql);
		fetch next from cur into @dbname
end

select 
	db_name
,	schema_name
,	table_name
,	sum(memory_space_MB) memory_space_MB
,	sum(storage_space_MB) storage_space_MB
,	sum(percentage_of_object_in_memory) percentage_of_object_in_memory
from #temp 
where memory_space_MB > 0
group by 
	db_name
,	schema_name
,	table_name
union all
select
	'TOTAL'
,	''
,	''
,	sum(memory_space_MB) memory_space_MB
,	sum(storage_space_MB) storage_space_MB
,	sum(percentage_of_object_in_memory) percentage_of_object_in_memory
from #temp 
where memory_space_MB > 0

drop table #temp
close cur;
deallocate cur;

[edit]

49 Cache Object Monitoring Script #

--DBCC FREEPROCCACHE
select
	c.name db_name
,	b.name object_name
,	a.objtype object_type
,	a.cacheobjtype cache_object_type
,	a.refcounts reference_counts
,	a.usecounts use_counts
,	a.pagesused pages_used
,	a.sqlbytes sql_bytes
,	a.sql
from master..syscacheobjects a left join master..sysobjects b
on a.objid = b.id left join master..sysdatabases c
on a.dbid = c.dbid
order by 1, 2, 3, 5 desc, 6 desc

[edit]

50 Source Code Download 및 참고자료 #

• dmv_2005.zip
  
• Performance_Tuning_Waits_Queues_.zip
  
• aba_lockinfo