상세 컨텐츠

본문 제목

IO-비용-실행-빈도-블로킹-Recompiled-누락된-인덱스-조회-쿼리

DataBase/Tunning

by 탑~! 2016. 6. 28. 10:19

본문

Problem

성능상의 문제가 있어서 I/O 비용이 높은 쿼리, 자주 실행되는 쿼리, 블로킹 발생 쿼리를 확인 하고 싶습니다.

 

Solution

1. I/O 비용이 가장 높은 쿼리 확인하기

01SELECT TOP 15
02        [Average IO] = (total_logical_reads + total_logical_writes)
03                / qs.execution_count,
04        [Total IO] = (total_logical_reads + total_logical_writes),
05        [Execution COUNT] = qs.execution_count,
06        [Individual Query] = SUBSTRING (qt.TEXT,
07                qs.statement_start_offset/2,
08               (CASE
09                WHEN qs.statement_end_offset = -1 
10                        THEN LEN(CONVERT(NVARCHAR(MAX), qt.TEXT)) * 2 
11                ELSE
12                        qs.statement_end_offset
13               END - qs.statement_start_offset)/2)
14        ,[Parent Query] = qt.TEXT,
15        DatabaseName = DB_NAME(qt.dbid)
16FROM sys.dm_exec_query_stats qs
17CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
18ORDER BY [Average IO] DESC;

 

 

 

2. 가장 자주 실행되는 쿼리 확인하기

01SELECT TOP 15
02    [Execution COUNT] = execution_count,
03    [Individual Query] = SUBSTRING (qt.TEXT,
04                qs.statement_start_offset/2,
05                (CASE
06                 WHEN qs.statement_end_offset = -1 
07                       THEN LEN(CONVERT(NVARCHAR(MAX), qt.TEXT)) * 2
08                 ELSE
09                       qs.statement_end_offset
10                END - qs.statement_start_offset)/2) ,
11    [Parent Query] = qt.TEXT,
12    DatabaseName = DB_NAME(qt.dbid)
13FROM sys.dm_exec_query_stats qs
14CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
15ORDER BY [Execution COUNT] DESC;

 

 

3. 가장 자주 블로킹 당하는 쿼리 확인하기

01SELECT TOP 15
02        [Average TIME Blocked] = (total_elapsed_time - total_worker_time)
03                / qs.execution_count,
04        [Total TIME Blocked] = total_elapsed_time - total_worker_time,
05        [Execution COUNT] = qs.execution_count,
06        [Individual Query] = SUBSTRING (qt.TEXT,
07                qs.statement_start_offset/2,
08                (CASE
09                WHEN qs.statement_end_offset = -1
10                       THEN LEN(CONVERT(NVARCHAR(MAX), qt.TEXT)) * 2
11                ELSE
12                        qs.statement_end_offset
13                END - qs.statement_start_offset)/2),
14        [Parent Query] = qt.TEXT,
15        DatabaseName = DB_NAME(qt.dbid)
16FROM sys.dm_exec_query_stats qs
17CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
18ORDER BY [Average TIME Blocked] DESC;
19<p></p>

 

 

 

4. Recompiled

01SELECT TOP 15
02    sql_handle,
03    plan_generation_num,
04    SUBSTRING(TEXT,qs.statement_start_offset/2,
05        (CASE
06                WHEN qs.statement_end_offset = -1
07                THEN LEN(CONVERT(NVARCHAR(MAX), TEXT)) * 2
08        ELSE
09                        qs.statement_end_offset
10                END  - qs.statement_start_offset)/2)
11        AS stmt_executing,
12    execution_count,
13    dbid,
14    objectid
15FROM sys.dm_exec_query_stats AS qs
16Cross apply sys.dm_exec_sql_text(sql_handle) sql_text
17WHERE plan_generation_num >1
18ORDER BY sql_handle, plan_generation_num

 

 

 

5. 누락된 인덱스 확인하기

01SELECT TOP 30
02    [Total Cost] = ROUND(avg_total_user_cost
03                * avg_user_impact
04        * (user_seeks + user_scans),0),
05    avg_user_impact,
06    TableName = STATEMENT,
07    [EqualityUsage] = equality_columns,
08    [InequalityUsage] = inequality_columns,
09    [Include Cloumns] = included_columns
10FROM sys.dm_db_missing_index_groups g
11INNER JOIN sys.dm_db_missing_index_group_stats s
12        ON s.group_handle = g.index_group_handle
13INNER JOIN sys.dm_db_missing_index_details d
14        ON d.index_handle = g.index_handle
15ORDER BY [Total Cost] DESC;

 

 

※ 누락된 인덱스 쿼리를 활용하여, 인덱스 생성 쿼리 만들기

01SELECT TOP 30
02    [Total Cost] = ROUND(avg_total_user_cost
03                * avg_user_impact
04                * (user_seeks + user_scans),0),
05    avg_user_impact,
06    TableName = STATEMENT,
07    [EqualityUsage] = equality_columns,
08    [InequalityUsage] = inequality_columns,
09    [Include Cloumns] = included_columns
10    ,REPLACE(REPLACE(SUBSTRING(
11                SUBSTRING(STATEMENT,CHARINDEX('.',STATEMENT)+1
12    ,LEN(STATEMENT)),CHARINDEX('.',
13                SUBSTRING(STATEMENT,CHARINDEX('.',STATEMENT)+1
14    ,LEN(STATEMENT)))+1,LEN(
15                SUBSTRING(STATEMENT,CHARINDEX('.',STATEMENT)+1
16    ,LEN(STATEMENT)))),'[',''),']','') AS TABLENAME
17    ,'CREATE INDEX IDX'+CONVERT(VARCHAR,(
18                SELECT COUNT(*)
19        FROM sys.indexes
20        WHERE OBJECT_NAME(OBJECT_ID)=
21                        REPLACE(REPLACE(SUBSTRING(SUBSTRING(
22                        STATEMENT,CHARINDEX('.',STATEMENT)+1,
23                        LEN(STATEMENT)),CHARINDEX('.',SUBSTRING(
24                        STATEMENT,CHARINDEX('.',STATEMENT)+1,
25                        LEN(STATEMENT)))+1,LEN(SUBSTRING(
26                        STATEMENT,CHARINDEX('.',STATEMENT)+1,
27                        LEN(STATEMENT)))),'[',''),']','')) )+'_'+
28                        REPLACE(REPLACE(SUBSTRING(SUBSTRING(
29                        STATEMENT,CHARINDEX('.',STATEMENT)+1,
30                        LEN(STATEMENT)),CHARINDEX('.',
31                        SUBSTRING(STATEMENT,CHARINDEX('.',
32                        STATEMENT)+1,LEN(STATEMENT)))+1,LEN(
33                        SUBSTRING(STATEMENT,CHARINDEX('.',
34                        STATEMENT)+1,LEN(STATEMENT)))),'[',''),']','')
35                        +' ON '+STATEMENT+'('+EQUALITY_COLUMNS+')'
36            AS INDEX_STMT
37FROM sys.dm_db_missing_index_groups g
38INNER JOIN sys.dm_db_missing_index_group_stats s
39        ON s.group_handle = g.index_group_handle
40INNER JOIN sys.dm_db_missing_index_details d
41        ON d.index_handle = g.index_handle
42WHERE STATEMENT LIKE '%sale%'
43ORDER BY [Total Cost] DESC;


-- 누락된 인덱스 확인하기
SELECT TOP 30 
    [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 TableName, [Total Cost] DESC;


--  I/O 비용이 가장 높은 쿼리 확인하기

SELECT TOP 15 
        [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;


--  가장 자주 실행되는 쿼리 확인하기
SELECT TOP 15 
    [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 15 
        [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; 



-- Recompiled
SELECT TOP 15 
    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 


-- 누락된 인덱스 쿼리를 활용하여, 인덱스 생성 쿼리 만들기
SELECT TOP 30 
    [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
    ,REPLACE(REPLACE(SUBSTRING(
                SUBSTRING(STATEMENT,CHARINDEX('.',STATEMENT)+1
    ,LEN(STATEMENT)),CHARINDEX('.',
                SUBSTRING(STATEMENT,CHARINDEX('.',STATEMENT)+1
    ,LEN(STATEMENT)))+1,LEN(
                SUBSTRING(STATEMENT,CHARINDEX('.',STATEMENT)+1
    ,LEN(STATEMENT)))),'[',''),']','') AS TABLENAME
    ,'CREATE INDEX IDX'+CONVERT(VARCHAR,(
                SELECT COUNT(*) 
        FROM sys.indexes 
        WHERE OBJECT_NAME(OBJECT_ID)=
                        REPLACE(REPLACE(SUBSTRING(SUBSTRING(
                        STATEMENT,CHARINDEX('.',STATEMENT)+1,
                        LEN(STATEMENT)),CHARINDEX('.',SUBSTRING(
                        STATEMENT,CHARINDEX('.',STATEMENT)+1,
                        LEN(STATEMENT)))+1,LEN(SUBSTRING(
                        STATEMENT,CHARINDEX('.',STATEMENT)+1,
                        LEN(STATEMENT)))),'[',''),']','')) )+'_'+
                        REPLACE(REPLACE(SUBSTRING(SUBSTRING(
                        STATEMENT,CHARINDEX('.',STATEMENT)+1,
                        LEN(STATEMENT)),CHARINDEX('.',
                        SUBSTRING(STATEMENT,CHARINDEX('.',
                        STATEMENT)+1,LEN(STATEMENT)))+1,LEN(
                        SUBSTRING(STATEMENT,CHARINDEX('.',
                        STATEMENT)+1,LEN(STATEMENT)))),'[',''),']','')
                        +' ON '+STATEMENT+'('+EQUALITY_COLUMNS+')'
            AS INDEX_STMT
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 
WHERE STATEMENT LIKE '%sale%'
ORDER BY [Total Cost] DESC;

출처 : http://iheedol.tistory.com/entry


저작자표시 비영리 변경금지

'DataBase > Tunning' 카테고리의 다른 글

SQL Server Connection 정보 모니터링을 위한 자동 수집기  (0) 2016.06.28
SQL Server 모니터링 체크리스트  (0) 2016.06.28

관련글 더보기

티스토리툴바