MSSQL 튜닝 관련 SQL

MSSQL  튜닝 관련 SQL

/* 
table rowcount
*/

SELECT o.NAME,
       i.rows
FROM   sysindexes i
       INNER JOIN sysobjects o
               ON i.id = o.id
WHERE  i.indid < 2
       AND o.xtype = 'U'
ORDER  BY i.rows DESC ;



/* 
table size mb
*/

SELECT CONVERT(VARCHAR(30), Min(o.NAME)) AS t_name,
       sum(reserved) * 8192.0 /1024 / 1024 AS t_size_mb
FROM   sysindexes i
       INNER JOIN sysobjects o
               ON o.id = i.id
WHERE  i.indid IN ( 0, 1, 255 )
       AND o.xtype = 'U'
GROUP  BY i.id
ORDER  BY t_size_mb 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 [total cost] DESC; 

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

  

/* 
IO 비용이 높은 쿼리
IO는 쿼리가 수행하는 읽기/쓰기 횟수를 측정한 값 기록 (데이터베이스 보고서에도 존재)
*/

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

 

 

/*
index 조각, 단편화, reindex
*/
SELECT  
    s.object_id,  
t.name as table_name,
    s.index_id,  
i.name as index_name,
    s.partition_number AS partition_number,  
    s.avg_fragmentation_in_percent AS avg_frag
FROM sys.tables t, sys.indexes i, sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, NULL) s
where 1=1
and t.object_id = i.object_id
and i.object_id = s.object_id
and i.index_id = s.index_id
AND s.index_id > 0
and s.avg_fragmentation_in_percent > 10.0 
order by 1,2,3,4

DBCC DBREINDEX(FI_MVASSET,' ', 80)