核心 DMV/DMF 分類
- 等待統計:
sys.dm_os_wait_stats
- 查詢統計:
sys.dm_exec_query_stats、sys.dm_exec_sql_text、sys.dm_exec_query_plan
- 索引相關:
sys.dm_db_missing_index_*、sys.dm_db_index_usage_stats、sys.dm_db_index_physical_stats
- IO/資源:
sys.dm_io_virtual_file_stats、sys.dm_os_performance_counters
實戰查詢腳本庫
1. 系統等待類型 Top 10(排除 SLEEP)
SELECT TOP 10
wait_type AS [Wait Type],
wait_time_ms / 1000.0 AS [Wait Time (s)],
CONVERT(DECIMAL(12,2), wait_time_ms * 100.0 / SUM(wait_time_ms) OVER()) AS [% Waiting]
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE '%SLEEP%'
ORDER BY wait_time_ms DESC;
2. 邏輯讀/寫最多的資料庫 Top 10
-- 讀
SELECT TOP 10
DB_NAME(qt.dbid) AS DatabaseName,
SUM(qs.total_logical_reads) AS [Total Reads],
SUM(qs.execution_count) AS [Execution Count]
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
GROUP BY DB_NAME(qt.dbid)
ORDER BY [Total Reads] DESC;
-- 寫
SELECT TOP 10
DB_NAME(qt.dbid) AS DatabaseName,
SUM(qs.total_logical_writes) AS [Total Writes],
SUM(qs.execution_count) AS [Execution Count]
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
GROUP BY DB_NAME(qt.dbid)
ORDER BY [Total Writes] DESC;
3. 缺失索引分析
-- 資料庫層級缺失索引數量
SELECT DB_NAME(database_id) AS DatabaseName, COUNT(*) AS [Missing Index Count]
FROM sys.dm_db_missing_index_details
GROUP BY DB_NAME(database_id)
ORDER BY 2 DESC;
-- 詳細缺失索引清單(含預估效益)
SELECT TOP 10
ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans), 0) AS [Total Cost],
avg_user_impact,
statement AS TableName,
equality_columns AS [Equality Columns],
inequality_columns AS [Inequality Columns],
included_columns AS [Included Columns]
FROM sys.dm_db_missing_index_groups g
JOIN sys.dm_db_missing_index_group_stats s ON s.group_handle = g.index_group_handle
JOIN sys.dm_db_missing_index_details d ON d.index_handle = g.index_handle
ORDER BY [Total Cost] DESC;
4. 維護成本最高的未使用索引 Top 10(全伺服器)
CREATE TABLE #TempMaintenanceCost (
[Maintenance Cost] BIGINT,
[Retrieval Usage] BIGINT,
DatabaseName SYSNAME,
TableName SYSNAME,
IndexName SYSNAME
);
EXEC sp_MSForEachDB '
USE [?];
INSERT INTO #TempMaintenanceCost
SELECT TOP 10
(user_updates + system_updates),
(user_seeks + user_scans + user_lookups),
DB_NAME(),
OBJECT_NAME(s.[object_id]),
i.name
FROM sys.dm_db_index_usage_stats s
JOIN sys.indexes i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
WHERE s.database_id = DB_ID()
AND i.name IS NOT NULL
AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0
AND (user_updates + system_updates) > 0
ORDER BY (user_updates + system_updates) DESC;
';
SELECT TOP 10 * FROM #TempMaintenanceCost ORDER BY [Maintenance Cost] DESC;
DROP TABLE #TempMaintenanceCost;
5. 使用率最高的索引 Top 10
CREATE TABLE #TempUsage (
[Usage] BIGINT,
DatabaseName SYSNAME,
TableName SYSNAME,
IndexName SYSNAME
);
EXEC sp_MSForEachDB '
USE [?];
INSERT INTO #TempUsage
SELECT TOP 10
(user_seeks + user_scans + user_lookups),
DB_NAME(),
OBJECT_NAME(s.[object_id]),
i.name
FROM sys.dm_db_index_usage_stats s
JOIN sys.indexes i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
WHERE s.database_id = DB_ID()
AND i.name IS NOT NULL
AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0
AND (user_seeks + user_scans + user_lookups) > 0
ORDER BY (user_seeks + user_scans + user_lookups) DESC;
';
SELECT TOP 10 * FROM #TempUsage ORDER BY [Usage] DESC;
DROP TABLE #TempUsage;
6. 碎片最高的索引 Top 10
CREATE TABLE #TempFrag (
DatabaseName SYSNAME,
TableName SYSNAME,
IndexName SYSNAME,
[Fragmentation %] DECIMAL(5,2)
);
EXEC sp_MSForEachDB '
USE [?];
INSERT INTO #TempFrag
SELECT TOP 10
DB_NAME(),
OBJECT_NAME(s.[object_id]),
i.name,
ROUND(avg_fragmentation_in_percent, 2)
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, ''LIMITED'') s
JOIN sys.indexes i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
WHERE i.name IS NOT NULL
AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0
AND avg_fragmentation_in_percent > 10
ORDER BY avg_fragmentation_in_percent DESC;
';
SELECT TOP 10 * FROM #TempFrag ORDER BY [Fragmentation %] DESC;
DROP TABLE #TempFrag;
使用建議
- 定期(每日/每週)排程執行上述查詢,存入效能基線資料表。
- 結合
Query Store(SQL 2016+)進行歷史趨勢分析。
- 缺失索引建議需人工審核,避免過度索引導致寫入效能下降。
- 碎片 > 30% 採
REBUILD,10-30% 採 REORGANIZE,並更新統計資料。