-- 查看最耗资源的查询
SELECT TOP 20
qs.total_worker_time / qs.execution_count AS avg_cpu_time,
qs.total_elapsed_time / qs.execution_count AS avg_duration,
qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
qs.execution_count,
SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) AS query_text,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY avg_logical_reads DESC;-- 获取缺失索引信息
SELECT
migs.avg_total_user_cost,
migs.avg_user_impact,
migs.last_user_seek,
mid.statement AS table_name,
mid.equality_columns,
mid.inequality_columns,
mid.included_columns
FROM sys.dm_db_missing_index_group_stats migs
INNER JOIN sys.dm_db_missing_index_groups mig
ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid
ON mig.index_handle = mid.index_handle
WHERE migs.avg_user_impact > 80 -- 影响程度大于80%
ORDER BY migs.avg_user_impact DESC;-- 获取有问题的执行计划
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
qp.query_plan,
st.text,
cp.usecounts,
cp.size_in_bytes
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE qp.query_plan.exist('//Warnings') = 1
AND qp.query_plan.exist('//Warnings/MissingIndexes') = 1;-- 分析执行计划中的警告详情
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
n.value('(@StatementText)[1]', 'VARCHAR(4000)') AS sql_text,
n.value('(//MissingIndexGroup/@Impact)[1]', 'FLOAT') AS impact,
DB_ID(REPLACE(REPLACE(n.value('(//MissingIndex/@Database)[1]', 'VARCHAR(128)'),
'[', ''), ']', '')) AS database_id,
n.query('.') AS query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
CROSS APPLY qp.query_plan.nodes('//StmtSimple') AS qn(n)
WHERE n.exist('//MissingIndex') = 1;-- 查看当前正在执行的查询
SELECT
r.session_id,
r.status,
r.cpu_time,
r.logical_reads,
r.writes,
t.text AS query_text,
p.query_plan,
r.wait_type,
r.wait_time
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) p
WHERE r.status = 'running'
ORDER BY r.logical_reads DESC;-- 创建扩展事件会话监控高成本查询
CREATE EVENT SESSION [HighCostQueries] ON SERVER
ADD EVENT sqlserver.sql_statement_completed(
ACTION(sqlserver.sql_text, sqlserver.plan_handle)
WHERE ([duration] > 1000000)) -- 超过1秒的查询
ADD TARGET package0.event_file(SET filename=N'C:\temp\HighCostQueries.xel');
GO
ALTER EVENT SESSION [HighCostQueries] ON SERVER STATE = START;-- 查看索引使用情况
SELECT
o.name AS table_name,
i.name AS index_name,
i.type_desc,
s.user_seeks,
s.user_scans,
s.user_lookups,
s.user_updates,
s.last_user_seek,
s.last_user_scan
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON s.object_id = i.object_id
AND s.index_id = i.index_id
INNER JOIN sys.objects o ON i.object_id = o.object_id
WHERE s.database_id = DB_ID()
AND o.type = 'U' -- 用户表
ORDER BY s.user_seeks + s.user_scans DESC;-- 查找可能不需要的索引
SELECT
o.name AS table_name,
i.name AS index_name,
i.type_desc,
s.user_seeks,
s.user_scans,
s.user_lookups,
s.user_updates
FROM sys.indexes i
INNER JOIN sys.objects o ON i.object_id = o.object_id
LEFT JOIN sys.dm_db_index_usage_stats s ON i.object_id = s.object_id
AND i.index_id = s.index_id AND s.database_id = DB_ID()
WHERE o.type = 'U'
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0
AND (s.user_seeks = 0 OR s.user_seeks IS NULL)
AND (s.user_scans = 0 OR s.user_scans IS NULL)
AND (s.user_lookups = 0 OR s.user_lookups IS NULL)
ORDER BY o.name, i.name;-- 综合诊断：高成本查询 + 缺失索引
SELECT TOP 20
'High Cost Query' AS issue_type,
qs.total_worker_time / qs.execution_count AS avg_cpu_time,
qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) AS query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE qs.execution_count > 10
AND qs.total_logical_reads / qs.execution_count > 1000
UNION ALL
SELECT TOP 20
'Missing Index' AS issue_type,
migs.avg_total_user_cost,
migs.avg_user_impact,
'Table: ' + mid.statement +
' | Equality: ' + ISNULL(mid.equality_columns, '') +
' | Inequality: ' + ISNULL(mid.inequality_columns, '') +
' | Included: ' + ISNULL(mid.included_columns, '') AS details
FROM sys.dm_db_missing_index_group_stats migs
INNER JOIN sys.dm_db_missing_index_groups mig
ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid
ON mig.index_handle = mid.index_handle
WHERE migs.avg_user_impact > 50
ORDER BY 2 DESC;-- 生成索引创建语句
SELECT
'CREATE INDEX IX_' +
REPLACE(REPLACE(REPLACE(
ISNULL(mid.equality_columns, '') +
ISNULL(mid.inequality_columns, ''),
'[', ''), ']', ''), ', ', '_') +
'_' + CAST(migs.index_group_handle AS VARCHAR) AS index_name,
'ON ' + mid.statement +
' (' + ISNULL(mid.equality_columns, '') +
CASE WHEN mid.equality_columns IS NOT NULL
AND mid.inequality_columns IS NOT NULL THEN ','
ELSE '' END +
ISNULL(mid.inequality_columns, '') + ')' +
ISNULL(' INCLUDE (' + mid.included_columns + ')', '') +
';' AS create_index_statement,
migs.avg_user_impact,
migs.avg_total_user_cost
FROM sys.dm_db_missing_index_group_stats migs
INNER JOIN sys.dm_db_missing_index_groups mig
ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid
ON mig.index_handle = mid.index_handle
WHERE migs.avg_user_impact > 80
ORDER BY migs.avg_user_impact DESC;通过这些方法和脚本，你可以系统性地识别需要添加索引的查询，并做出明智的索引优化决策。
