萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 伺服器教學

善用 DMV/DMF 深度剖析 SQL Server 效能瓶頸

核心 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,並更新統計資料。
copyright © 萬盛學電腦網 all rights reserved