#title Performance Health Status - DMV(2005)
[[TableOfContents]]

==== 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;
}}}
attachment:track_waitstats_2005.sql
attachment:get_waitstats_2005.sql

||Wait Type||원인||해결||
||OLEDB||OLE DB 공급자호출 (4 Part Name 호출, 원격프로시저호출, OPENQUERY, OPENROWSET, DMV호출, 프로파일러추적)||OLE DB를 사용한 어플리케이션, 쿼리튜닝||
||CXPACKET||병렬처리로 인한 대기||병렬처리를 발생시키는 쿼리 튜닝, 누락된 인덱스 추가||
||PAGEIOLATCH_*, PAGELATCH_*||PAGEIOLATCH_*: 데이터 페이지의 I/O가 완료할 때까지 대기[[BR]]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
==== 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;
}}}

==== 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;
}}}

==== 데이터베이스별 인덱스 누락수 ====
{{{
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;
}}}

==== 모든 데이터베이스에 대해 가장 비용이 높은 누락된 인덱스 ====
{{{
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;
}}}

==== 가장 비용이 높은 사용되지 않는 인덱스 ====
{{{
-- 필요한 테이블 구조만 작성합니다.
-- 참고: 이 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
}}}

==== 사용되고 있는 인덱스 중 기본 테이블을 변경할 때 비용이 가능 높은 인덱스 ====
{{{
-- 필요한 테이블 구조만 작성합니다.
-- 참고: 이 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
}}}

==== 자주 사용되는 인덱스 ====
{{{
-- 필요한 테이블 구조만 작성합니다.
-- 참고: 이 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
}}}

==== 논리적으로 조각화가 가장 심한 인덱스 확인 ====
{{{
-- 필요한 테이블 구조만 작성합니다.
-- 참고: 이 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
}}}

==== 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;
}}}

==== 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;
}}}

==== 가장 비용이 높은 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;
}}}

==== 가장 자주 실행되는 쿼리 확인 ====
{{{
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;
}}}

==== 가장 자주 블로킹 당하는 쿼리 확인 ====
{{{
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;
}}}

==== 계획이 가장 적게 재사용되는 쿼리 확인 ====
{{{
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;
}}}

==== 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
}}}

==== 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')
}}}

==== 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
}}}

==== 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
}}}

==== 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
}}}

==== 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
}}}

==== 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
}}}

==== Retrieve Index Usage Statistics ====
{{{
select 
	* 
from 
	sys.dm_db_index_usage_stats 
order by 
	user_updates desc
}}}

==== 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
}}}

==== 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
}}}

==== 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
}}}

==== 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
}}}

==== 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
}}}

==== 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
}}}

==== 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
}}}

==== 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
}}}

==== 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
}}}

==== 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
}}}

==== 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
}}}

==== 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
}}}

==== 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
}}}

==== 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
}}}
==== 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
}}}

==== 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
}}}

==== 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
}}}

==== 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
}}}

==== 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
}}}

==== 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
}}}

==== 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
}}}

==== 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
}}}

==== 마지막으로 통계한 날짜 ====
{{{
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
}}}

==== 현재 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];
}}}

==== 객체의 메모리 및 디스크 사용 모니터링 ====
{{{
--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;
}}}

==== 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
}}}

==== Source Code Download 및 참고자료 ====
 * attachment:dmv_2005.zip
 * attachment:Performance_Tuning_Waits_Queues_.zip
 * [http://www.sommarskog.se/sqlutil/aba_lockinfo.html aba_lockinfo]