티스토리 뷰
데이터베이스별 누락된 인덱스
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;
테이블 정보 확인
'Database' 카테고리의 다른 글
[MSSQL] 튜닝에 필요한 명령어 (0) | 2014.11.13 |
---|---|
[MSSQL] 테이블 정보 확인 (0) | 2014.11.13 |
Database 정규화(1) (0) | 2013.03.05 |
[MSSQL] Query 실행 시간 출력 (0) | 2013.02.21 |
[Oracle] 힌트 사용법 및 종류 (0) | 2013.02.20 |