I/O 비용, 실행 빈도, 블로킹, Recompiled, 누락된 인덱스 조회 쿼리

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

 

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

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

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; 

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

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;

 

4. 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 

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

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