튜닝을 위한 쿼리들

1. Blocking 프로세스 찾기

SELECT SPID

FROM   MASTER.DBO.SYSPROCESSES (NOLOCK)

WHERE  SPID IN

(

       SELECT BLOCKED

       FROM   MASTER.DBO.SYSPROCESSES (NOLOCK)

)

AND BLOCKED = 0

2. 오래 수행되는 트랜잭션 찾기

SELECT spid, cmd, status, loginame, open_tran,

            DATEDIFF(s, last_batch, GETDATE()) AS [wait time(s)]

FROM     MASTER.DBO.SYSPROCESSES p WITH (NOLOCK)

WHERE   open_tran > 0

AND      spid > 50

AND      DATEDIFF(s, last_batch, GETDATE()) > 20

AND EXISTS

(

       SELECT *

       FROM   MASTER.DBO.SYSLOCKINFO

       WHERE  req_spid = p.spid

       AND    rsc_type <> 2

)

3. Wait하고 있는 쿼리 확인

SELECT spid

            ,waittime/1000.0 AS [wait_min]

            ,lastwaittype

            ,waitresource

FROM   MASTER.DBO.SYSPROCESSES

WHERE  spid > 50

AND    ((waittype BETWEEN 1 AND 32) AND waittime > 10000) -- 잠금> 10초

OR      ((waittype BETWEEN 1056 AND 1061) AND waittime > 1000) -- 데이터페이지I/O 대기> 1초

OR      (waittype = 2048 AND waittime > 10000) -- Network I/O 블록킹> 10초

OR      (waittype = 129 AND waittime > 500) -- Log I/O > 0.5초

4. 서버대기원인

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;

5. 읽기 및 쓰기

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;

6. 누락된 인덱스

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;

7. 비용이 높은 누락된 인덱스

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;

8. 사용되지 않는 인덱스

-- 필요한테이블구조만작성합니다.
-- 참고: 이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

9. 사용 비용이 높은 인덱스

-- 필요한테이블구조만작성합니다.
-- 참고: 이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

10. 자주 사용되는 인덱스

-- 필요한테이블구조만작성합니다.
-- 참고: 이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

11. 논리적으로 조각난 인덱스

-- 필요한테이블구조만작성합니다.
-- 참고: 이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

12. 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;

13. 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;

14. 비용이 높은 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;

15. 가장 많이 실행된 쿼리

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;

16. 차단 당하는 쿼리

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;

17. 가장 적게 재사용되는 계획

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 onqs.plan_handle=cp.plan_handle

WHERE cp.plan_handle=qs.plan_handle

ORDER BY [Plan usage] ASC;

참고 : http://msdn.microsoft.com/ko-kr/magazine/cc135978.aspx