튜닝을 위한 쿼리들

1. Blocking 프로세스 찾기

SELECTSPID

FROM   MASTER.DBO.SYSPROCESSES(NOLOCK)

WHERE  SPIDIN

(

       SELECTBLOCKED

       FROM   MASTER.DBO.SYSPROCESSES(NOLOCK)

)

ANDBLOCKED= 0

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

SELECTspid,cmd,status,loginame,open_tran,

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

FROM     MASTER.DBO.SYSPROCESSESpWITH (NOLOCK)

WHERE   open_tran> 0

AND      spid> 50

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

ANDEXISTS

(

       SELECT*

       FROM   MASTER.DBO.SYSLOCKINFO

       WHERE  req_spid=p.spid

       AND    rsc_type<> 2

)

3. Wait하고 있는 쿼리 확인

SELECTspid

            ,waittime/1000.0 AS[wait_min]

            ,lastwaittype

            ,waitresource

FROM   MASTER.DBO.SYSPROCESSES

WHERE  spid> 50

AND    ((waittypeBETWEEN 1 AND 32)ANDwaittime> 10000)-- 잠금> 10초

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

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

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

4. 서버대기원인

SELECTTOP 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())

FROMsys.dm_os_wait_stats

WHEREwait_typeNOTLIKE'%SLEEP%'

ORDERBYwait_time_msDESC;

5. 읽기 및 쓰기

SELECTTOP 10

        [Total Reads]=SUM(total_logical_reads)

        ,[Execution count]=SUM(qs.execution_count)

        ,DatabaseName=DB_NAME(qt.dbid)

FROMsys.dm_exec_query_statsqs

CROSSAPPLYsys.dm_exec_sql_text(qs.sql_handle)asqt

GROUPBYDB_NAME(qt.dbid)

ORDERBY[Total Reads]DESC;

 

SELECTTOP 10

        [Total Writes]=SUM(total_logical_writes)

        ,[Execution count]=SUM(qs.execution_count)

        ,DatabaseName=DB_NAME(qt.dbid)

FROMsys.dm_exec_query_statsqs

CROSSAPPLYsys.dm_exec_sql_text(qs.sql_handle)asqt

GROUPBYDB_NAME(qt.dbid)

ORDERBY[Total Writes]DESC;

6. 누락된 인덱스

SELECT

    DatabaseName=DB_NAME(database_id)

    ,[Number Indexes Missing]=count(*)

FROMsys.dm_db_missing_index_details

GROUPBYDB_NAME(database_id)

ORDERBY 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_groupsg

INNERJOIN    sys.dm_db_missing_index_group_statss

       ONs.group_handle=g.index_group_handle

INNERJOIN    sys.dm_db_missing_index_detailsd

       ONd.index_handle=g.index_handle

ORDERBY[Total Cost]DESC;

8. 사용되지 않는 인덱스

-- 필요한테이블구조만작성합니다.
-- 참고: 이SQL은다음단계에서지정된데이터베이스루프내에있어야합니다.

SELECTTOP 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_statss

INNERJOINsys.indexesiON  s.[object_id]=i.[object_id]

    ANDs.index_id=i.index_id

WHERE  s.database_id=DB_ID()

    ANDOBJECTPROPERTY(s.[object_id],'IsMsShipped')= 0

    AND    user_seeks= 0

    ANDuser_scans= 0

    ANDuser_lookups= 0

    ANDs.[object_id]=-999  -- 테이블구조를얻기위한임시값

;

-- 서버의모든데이터베이스를대상으로반복합니다.

EXECsp_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

;

'

 

-- 레코드선택

SELECTTOP 10 *FROM#TempUnusedIndexesORDERBY[user_updates]DESC

-- 임시테이블정리

DROPTABLE#TempUnusedIndexes

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

-- 필요한테이블구조만작성합니다.
-- 참고: 이SQL은다음단계에서지정된데이터베이스루프내에있어야합니다.

SELECTTOP 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_statss

INNERJOINsys.indexesiON  s.[object_id]=i.[object_id]

    ANDs.index_id=i.index_id

WHEREs.database_id=DB_ID()

    ANDOBJECTPROPERTY(s.[object_id],'IsMsShipped')= 0

    AND(user_updates+system_updates)> 0 -- 활성행에대해서만보고

    ANDs.[object_id]=-999  -- 테이블구조를얻기위한임시값

;

-- 서버의모든데이터베이스를대상으로반복합니다.

EXECsp_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

;

'

 

-- 레코드선택

SELECTTOP 10 *FROM#TempMaintenanceCost

ORDERBY[Maintenance cost]  DESC

-- 임시테이블정리

DROPTABLE#TempMaintenanceCost

10. 자주 사용되는 인덱스

-- 필요한테이블구조만작성합니다.
-- 참고: 이SQL은다음단계에서지정된데이터베이스루프내에있어야합니다.

SELECTTOP 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_statss

INNERJOINsys.indexesiONs.[object_id]=i.[object_id]

    ANDs.index_id=i.index_id

WHERE   s.database_id=DB_ID()

    ANDOBJECTPROPERTY(s.[object_id],'IsMsShipped')= 0

    AND(user_seeks+user_scans+user_lookups)> 0

-- 활성행에대해서만보고

    ANDs.[object_id]=-999  -- 테이블구조를얻기위한임시값

;

-- 서버의모든데이터베이스를대상으로반복합니다.

EXECsp_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

;

'

-- 레코드선택

SELECTTOP 10 *FROM#TempUsageORDERBY[Usage]DESC

-- 임시테이블정리

DROPTABLE#TempUsage

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

-- 필요한테이블구조만작성합니다.
-- 참고: 이SQL은다음단계에서지정된데이터베이스루프내에있어야합니다.

SELECTTOP 1

DatbaseName=DB_NAME()

,TableName=OBJECT_NAME(s.[object_id])

,IndexName=i.name

,[Fragmentation %]=ROUND(avg_fragmentation_in_percent,2)

INTO#TempFragmentation

FROMsys.dm_db_index_physical_stats(db_id(),null,null,null,null)s

INNERJOINsys.indexesiONs.[object_id]=i.[object_id]

ANDs.index_id=i.index_id

WHEREs.[object_id]=-999-- 테이블구조를얻기위한임시값

;

-- 서버의모든데이터베이스를대상으로반복합니다.

EXECsp_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

;

'

-- 레코드선택

SELECTTOP 10 *FROM#TempFragmentationORDERBY[Fragmentation %]DESC

-- 임시테이블정리

DROPTABLE#TempFragmentation

12. I/O 비용이 높은 쿼리

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

(CASEWHENqs.statement_end_offset=-1

THENLEN(CONVERT(NVARCHAR(MAX),qt.text))* 2

ELSEqs.statement_end_offsetEND-qs.statement_start_offset)/2)

,[Parent Query]=qt.text

,DatabaseName=DB_NAME(qt.dbid)

FROMsys.dm_exec_query_statsqs

CROSSAPPLYsys.dm_exec_sql_text(qs.sql_handle)asqt

ORDERBY[Average IO]DESC;

13. CPU비용이 높은 쿼리

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

(CASEWHENqs.statement_end_offset=-1

THENLEN(CONVERT(NVARCHAR(MAX),qt.text))* 2

ELSEqs.statement_end_offsetEND-

qs.statement_start_offset)/2)

,[Parent Query]=qt.text

,DatabaseName=DB_NAME(qt.dbid)

FROMsys.dm_exec_query_statsqs

CROSSAPPLYsys.dm_exec_sql_text(qs.sql_handle)asqt

ORDERBY[Average CPU used]DESC;

14. 비용이 높은 CLR쿼리

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

(CASEWHENqs.statement_end_offset=-1

THENLEN(CONVERT(NVARCHAR(MAX),qt.text))* 2

ELSEqs.statement_end_offsetEND-qs.statement_start_offset)/2)

,[Parent Query]=qt.text

,DatabaseName=DB_NAME(qt.dbid)

FROMsys.dm_exec_query_statsasqs

CROSSAPPLYsys.dm_exec_sql_text(qs.sql_handle)asqt

WHEREtotal_clr_time<> 0

ORDERBY[Average CLR Time]DESC;

15. 가장 많이 실행된 쿼리

SELECTTOP 10

 [Execution count]=execution_count

,[Individual Query]=SUBSTRING(qt.text,qs.statement_start_offset/2,

         (CASEWHENqs.statement_end_offset=-1

            THENLEN(CONVERT(NVARCHAR(MAX),qt.text))* 2

          ELSEqs.statement_end_offsetEND-qs.statement_start_offset)/2)

,[Parent Query]=qt.text

,DatabaseName=DB_NAME(qt.dbid)

FROMsys.dm_exec_query_statsqs

CROSSAPPLYsys.dm_exec_sql_text(qs.sql_handle)asqt

ORDERBY[Execution count]DESC;

16. 차단 당하는 쿼리

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

         (CASEWHENqs.statement_end_offset=-1

            THENLEN(CONVERT(NVARCHAR(MAX),qt.text))* 2

          ELSEqs.statement_end_offsetEND-qs.statement_start_offset)/2)

,[Parent Query]=qt.text

,DatabaseName=DB_NAME(qt.dbid)

FROMsys.dm_exec_query_statsqs

CROSSAPPLYsys.dm_exec_sql_text(qs.sql_handle)asqt

ORDERBY[Average Time Blocked]DESC;

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

SELECTTOP 10

 [Plan usage]=cp.usecounts

,[Individual Query]=SUBSTRING(qt.text,qs.statement_start_offset/2,

         (CASEWHENqs.statement_end_offset=-1

            THENLEN(CONVERT(NVARCHAR(MAX),

qt.text))* 2 ELSEqs.statement_end_offsetEND-

qs.statement_start_offset)/2)

,[Parent Query]=qt.text

,DatabaseName=DB_NAME(qt.dbid)

,cp.cacheobjtype

FROMsys.dm_exec_query_statsqs

CROSSAPPLYsys.dm_exec_sql_text(qs.sql_handle)ASqt

INNERJOINsys.dm_exec_cached_plansascponqs.plan_handle=cp.plan_handle

WHEREcp.plan_handle=qs.plan_handle

ORDERBY[Plan usage]ASC;

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