[MSSQL] 쿼리 튜닝을 위한 쿼리 및 시스템 테이블

데이터베이스별 누락된 인덱스

SQL Server가 쿼리를 처리할 때 최적화 프로그램은 쿼리를 수행하기 위해 사용하려고 시도한 인덱스에 대한 기록을 보관합니다. 이러한 인덱스가 없는 경우 SQL Server는 누락된 인덱스에 대한 기록을 만듭니다.

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;

sys.dm_db_missing_index_details

누락된 인덱스 기록 테이블

 

비용이 높은 누락된 인덱스

인덱스가 쿼리 성능에 미치는 영향의 정도는 다양합니다. 서버에 있는 모든 데이터베이스에 대해 가장 비용이 높은 누락된 인덱스에 대한 조사를 수행하여 어떤 누락된 인덱스를 추가했을 때 성능에 긍정적인 영향이 있을지 알아낼 수 있습니다.

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;

sys.dm_db_missing_index_details	누락된 인덱스 기록 테이블
sys.dm_db_missing_index_group_stats	특정 누락된 인덱스를 사용하려고 시도한 횟수
sys.dm_db_missing_index_groups	쿼리에서 요구되는 열과 같은 누락된 인덱스의 구조에 대해 세부사항을 제공

 

 

 

 

I/O 비용이 높은 쿼리

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;

-------------------------------------------------------------------------

SELECT TOP 20 SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,

        ((CASE qs.statement_end_offset

          WHEN -1 THEN DATALENGTH(qt.text)

         ELSE qs.statement_end_offset

         END - qs.statement_start_offset)/2)+1),

qs.execution_count,

qs.total_logical_reads, qs.last_logical_reads,

qs.min_logical_reads, qs.max_logical_reads,

qs.total_elapsed_time, qs.last_elapsed_time,

qs.min_elapsed_time, qs.max_elapsed_time,

qs.last_execution_time,

qp.query_plan

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp

WHERE qt.encrypted=0

ORDER BY qs.total_logical_reads DESC

sys.dm_exec_query_stats	실제 및 논리 읽기/쓰기와 쿼리 실행 횟수
sys.dm_exec_sql_text	I/O가 연관된 SQL  일괄 처리에 대한 정보 포함

 

 

 

 

 

 

CPU 비용이 높은 쿼리

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;

비용이 높은 CLR 쿼리

저장 프로시저, 함수 및 트리거를 포함하여 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;

 

가장 많이 실행된 쿼리

 자주 실행되는 쿼리의 성능을 개선하면 가끔 실행되는 큰 쿼리를 최적화하는 것보다 더 많은 성능 개선 효과를 거둘 수 있다.

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;

 

 

차단당하는 쿼리

가장 많이 차단당하는 쿼리는 일반적으로 오래 실행되는 쿼리

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;

테이블 정보 확인