모니터링 DMV 2

/* 쿼리 레코드로 인한 대기 시간 증가 */

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;

/* 가장 많은 읽기 및 쓰기를 사용하는 데이터베이스 확인 */

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;

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 100    

        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 100 * 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 100

        [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 100 * 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 100

        [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 100 * FROM #TempUsage ORDER BY [Usage] DESC

-- 임시 테이블 정리

DROP TABLE #TempUsage

/* 논리적으로 조각난 인덱스 */

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 100

        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 100 * FROM #TempFragmentation ORDER BY [Fragmentation %] DESC

-- 임시 테이블 정리

DROP TABLE #TempFragmentation

/* I/O 비용이 높은 쿼리 */

SELECT TOP 100 

 [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 100 

 [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 100 

 [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

AND qt.dbid = DB_ID(N'Petz_Test')

ORDER BY [Average CLR Time] DESC;

/* 가장 많이 실행된 쿼리 */

SELECT TOP 100 

 [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

WHERE qt.dbid = DB_ID(N'Petz_Test')

ORDER BY [Execution count] DESC;

/* 차단당하는 쿼리 */

SELECT TOP 100 

 isnull(object_name(qt.objectid, qt.dbid), qt.text) as 'PlanQuery'

,[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

WHERE qt.dbid = DB_ID(N'Petz_Test')

ORDER BY [Average Time Blocked] DESC;

/* 가장 적게 재사용되는 계획 */

SELECT TOP 100

 [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

AND qt.dbid = DB_ID(N'Petz_Test')

ORDER BY [Plan usage] ASC;