SQL Server 在 .NET 项目中的典型运维诊断技巧
在 .NET 应用程序与 SQL Server 协同工作的环境中，数据库层面的性能瓶颈往往直接影响系统稳定性。以下针对三个高频运维场景提供可落地的诊断方案。
一、死锁追踪与日志采集
当并发事务相互阻塞时，需开启 SQL Server 的追踪标记以捕获详细信息：
-- 启用死锁事件的日志记录
EXEC sp_altermessage 1205, 'WITH_LOG', 'true';
GO
-- 激活死锁图与扩展信息输出
DBCC TRACEON(1204, -1);
DBCC TRACEON(1222, -1);
追踪生效后，死锁详情会写入错误日志。通过分析 ERRORLOG 文件（默认路径：%ProgramFiles%\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\）可定位冲突的资源与语句。
二、索引碎片度巡检
索引碎片过高会导致查询性能劣化，以下脚本按碎片率降序列出需要关注的索引：
SELECT
SCH.name AS schema_name,
TBL.name AS table_name,
IDX.name AS index_name,
PHYS.avg_fragmentation_in_percent AS fragmentation_pct,
PHYS.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) AS PHYS
INNER JOIN sys.tables TBL ON TBL.object_id = PHYS.object_id
INNER JOIN sys.schemas SCH ON TBL.schema_id = SCH.schema_id
INNER JOIN sys.indexes IDX
ON IDX.object_id = PHYS.object_id
AND IDX.index_id = PHYS.index_id
WHERE PHYS.database_id = DB_ID()
AND IDX.name IS NOT NULL
AND PHYS.avg_fragmentation_in_percent > 0
ORDER BY PHYS.avg_fragmentation_in_percent DESC;
三、冗余索引识别
重复或反向的索引会增加维护开销并拖慢 DML 操作。以下方案通过临时表聚合元数据，输出三类诊断结果：完整索引清单、潜在冗余索引、列序反转索引。
DECLARE @IndexSummary TABLE (
tbl_id INT NOT NULL,
schema_nm SYSNAME NOT NULL,
tbl_nm SYSNAME NOT NULL,
idx_id INT NULL,
idx_nm NVARCHAR(128) NULL,
idx_kind VARCHAR(12) NOT NULL,
cstr_kind VARCHAR(11) NOT NULL,
obj_kind VARCHAR(10) NOT NULL,
all_cols NVARCHAR(2078) NULL,
col_1 NVARCHAR(128) NULL,
col_2 NVARCHAR(128) NULL,
col_3 NVARCHAR(128) NULL,
col_4 NVARCHAR(128) NULL,
col_5 NVARCHAR(128) NULL,
col_6 NVARCHAR(128) NULL,
col_7 NVARCHAR(128) NULL,
col_8 NVARCHAR(128) NULL,
col_9 NVARCHAR(128) NULL,
col_10 NVARCHAR(128) NULL
);
INSERT INTO @IndexSummary
(tbl_id, schema_nm, tbl_nm, idx_id, idx_nm, idx_kind,
cstr_kind, obj_kind, all_cols, col_1, col_2, col_3,
col_4, col_5, col_6, col_7, col_8, col_9, col_10)
SELECT
o.object_id,
u.name,
o.name,
i.index_id,
CASE i.name
WHEN o.name THEN '** 与表同名 **'
ELSE i.name
END,
CASE i.type
WHEN 1 THEN 'CLUSTERED'
WHEN 0 THEN 'HEAP'
WHEN 2 THEN 'NONCLUSTERED'
WHEN 3 THEN 'XML'
ELSE 'UNKNOWN'
END,
CASE
WHEN i.is_primary_key = 1 THEN 'PRIMARY KEY'
WHEN i.is_unique = 1 THEN 'UNIQUE'
ELSE ''
END,
CASE
WHEN i.is_unique_constraint = 1 OR i.is_primary_key = 1 THEN 'CONSTRAINT'
WHEN i.type = 0 THEN 'HEAP'
WHEN i.type = 3 THEN 'XML INDEX'
ELSE 'INDEX'
END,
-- 拼接全部索引列
COALESCE(
(SELECT c.name
FROM sys.columns c
JOIN sys.index_columns ic
ON c.object_id = ic.object_id AND c.column_id = ic.column_id
WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.key_ordinal = 1),
''
) +
ISNULL(', ' + INDEX_COL('[' + u.name + '].[' + o.name + ']', i.index_id, 2), '') +
ISNULL(', ' + INDEX_COL('[' + u.name + '].[' + o.name + ']', i.index_id, 3), '') +
ISNULL(', ' + INDEX_COL('[' + u.name + '].[' + o.name + ']', i.index_id, 4), '') +
ISNULL(', ' + INDEX_COL('[' + u.name + '].[' + o.name + ']', i.index_id, 5), '') +
ISNULL(', ' + INDEX_COL('[' + u.name + '].[' + o.name + ']', i.index_id, 6), '') +
ISNULL(', ' + INDEX_COL('[' + u.name + '].[' + o.name + ']', i.index_id, 7), '') +
ISNULL(', ' + INDEX_COL('[' + u.name + '].[' + o.name + ']', i.index_id, 8), '') +
ISNULL(', ' + INDEX_COL('[' + u.name + '].[' + o.name + ']', i.index_id, 9), '') +
ISNULL(', ' + INDEX_COL('[' + u.name + '].[' + o.name + ']', i.index_id, 10), ''),
-- 分列存储
(SELECT COALESCE(c.name, '')
FROM sys.columns c
JOIN sys.index_columns ic
ON c.object_id = ic.object_id AND c.column_id = ic.column_id
WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.key_ordinal = 1),
ISNULL(INDEX_COL('[' + u.name + '].[' + o.name + ']', i.index_id, 2), ''),
ISNULL(INDEX_COL('[' + u.name + '].[' + o.name + ']', i.index_id, 3), ''),
ISNULL(INDEX_COL('[' + u.name + '].[' + o.name + ']', i.index_id, 4), ''),
ISNULL(INDEX_COL('[' + u.name + '].[' + o.name + ']', i.index_id, 5), ''),
ISNULL(INDEX_COL('[' + u.name + '].[' + o.name + ']', i.index_id, 6), ''),
ISNULL(INDEX_COL('[' + u.name + '].[' + o.name + ']', i.index_id, 7), ''),
ISNULL(INDEX_COL('[' + u.name + '].[' + o.name + ']', i.index_id, 8), ''),
ISNULL(INDEX_COL('[' + u.name + '].[' + o.name + ']', i.index_id, 9), ''),
ISNULL(INDEX_COL('[' + u.name + '].[' + o.name + ']', i.index_id, 10), '')
FROM sys.objects o WITH (NOLOCK)
LEFT JOIN sys.indexes i WITH (NOLOCK) ON o.object_id = i.object_id
JOIN sys.schemas u WITH (NOLOCK) ON o.schema_id = u.schema_id
WHERE o.type = 'U'
AND o.name NOT IN ('dtproperties')
AND i.name NOT LIKE '_WA_Sys_%';
-- 结果1：全部索引概览
SELECT '索引完整清单' AS report_type;
SELECT * FROM @IndexSummary ORDER BY tbl_nm;
-- 结果2：前缀列重复的冗余索引
SELECT '潜在冗余索引' AS report_type;
SELECT DISTINCT
a.tbl_nm, a.idx_nm, a.idx_kind, a.cstr_kind, a.all_cols
FROM @IndexSummary a
JOIN @IndexSummary b
ON a.tbl_id = b.tbl_id
AND a.col_1 = b.col_1
AND a.idx_nm <> b.idx_nm
AND a.idx_kind <> 'XML'
ORDER BY a.tbl_nm, a.all_cols;
-- 结果3：列顺序反转的索引对
SELECT '潜在反向索引' AS report_type;
SELECT DISTINCT
a.tbl_nm, a.idx_nm, a.idx_kind, a.cstr_kind, a.all_cols
FROM @IndexSummary a
JOIN @IndexSummary b
ON a.tbl_id = b.tbl_id
AND a.col_1 = b.col_2
AND a.col_2 = b.col_1
AND a.idx_nm <> b.idx_nm
AND a.idx_kind <> 'XML';
