日常我们开发.NET应用时会使用SQLServer数据库，对于SQLServer数据库的日常开发有一些技能和工具，准备给大家分享一下。
一、场景1：SQLServer死锁分析
执行以下SQL，启用SQLServer死锁日志输出EXEC sp_altermessage 1205, 'WITH_LOG', 'true' DBCC TRACEON(1204,-1) DBCC TRACEON(1222,-1)
运行一段时间后，系统出现死锁问题，可以从以下位置查看SQLServer异常日志的死锁信息
Program Files\Microsoft SQL Server\MSSQL. n \MSSQL\LOG\ERRORLOG
二、场景2：SQLServer索引锁片收集
SELECT S.name as 'Schema', T.name as 'Table', I.name as 'Index', DDIPS.avg_fragmentation_in_percent, DDIPS.page_count FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS DDIPS INNER JOIN sys.tables T on T.object_id = DDIPS.object_id INNER JOIN sys.schemas S on T.schema_id = S.schema_id INNER JOIN sys.indexes I ON I.object_id = DDIPS.object_id AND DDIPS.index_id = I.index_id WHERE DDIPS.database_id = DB_ID() and I.name is not null AND DDIPS.avg_fragmentation_in_percent > 0 ORDER BY DDIPS.avg_fragmentation_in_percent desc
三、场景3：SQLServer重复索引分析
/* 执行这个脚本后，索引将会以三个报表的实行展现出来
1. 列出所有索引和约束的关键信息
2. 列出表潜在的冗余索引
3. 列出表潜在的反向索引
*/
-- 创建一个存放索引信息的表
DECLARE @AllIndexes TABLE (
[Table ID] [int] NOT NULL,
[Schema] [sysname] NOT NULL,
[Table Name] [sysname] NOT NULL,
[Index ID] [int] NULL,
[Index Name] [nvarchar](128) NULL,
[Index Type] [varchar](12) NOT NULL,
[Constraint Type] [varchar](11) NOT NULL,
[Object Type] [varchar](10) NOT NULL,
[AllColName] [nvarchar](2078) NULL,
[ColName1] [nvarchar](128) NULL,
[ColName2] [nvarchar](128) NULL,
[ColName3] [nvarchar](128) NULL,
[ColName4] [nvarchar](128) NULL,
[ColName5] [nvarchar](128) NULL,
[ColName6] [nvarchar](128) NULL,
[ColName7] [nvarchar](128) NULL,
[ColName8] [nvarchar](128) NULL,
[ColName9] [nvarchar](128) NULL,
[ColName10] [nvarchar](128) NULL
)
-- 加载索引信息到下面语句
INSERT INTO @AllIndexes
([Table ID],[Schema],[Table Name],[Index ID],[Index Name],[Index Type],[Constraint Type],[Object Type]
,[AllColName],[ColName1],[ColName2],[ColName3],[ColName4],[ColName5],[ColName6],[ColName7],[ColName8],
[ColName9],[ColName10])
SELECT o.[object_id] AS [Table ID] ,u.[name] AS [Schema],o.[name] AS [Table Name],
i.[index_id] AS [Index ID]
, CASE i.[name]
WHEN o.[name] THEN '** Same as Table Name **'
ELSE i.[name] END AS [Index Name],
CASE i.[type]
WHEN 1 THEN 'CLUSTERED'
WHEN 0 THEN 'HEAP'
WHEN 2 THEN 'NONCLUSTERED'
WHEN 3 THEN 'XML'
ELSE 'UNKNOWN' END AS [Index Type],
CASE
WHEN (i.[is_primary_key]) = 1 THEN 'PRIMARY KEY'
WHEN (i.[is_unique]) = 1 THEN 'UNIQUE'
ELSE '' END AS [Constraint Type],
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 AS [Object Type],
(SELECT COALESCE(c1.[name],'') FROM [sys].[columns] AS c1 INNER JOIN [sys].[index_columns] AS ic1
ON c1.[object_id] = ic1.[object_id] AND c1.[column_id] = ic1.[column_id] AND ic1.[key_ordinal] = 1
WHERE ic1.[object_id] = i.[object_id] AND ic1.[index_id] = i.[index_id]) +
CASE
WHEN INDEX_COL('[' + u.[name] + '].['+ o.[name] + ']', i.[index_id], 2) IS NULL THEN ''
ELSE ', '+INDEX_COL('[' + u.[name] + '].['+ o.[name] + ']', i.[index_id],2) END +
CASE
WHEN INDEX_COL('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id], 3) IS NULL THEN ''
ELSE ', '+INDEX_COL('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id],3) END +
CASE
WHEN INDEX_COL('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id], 4) IS NULL THEN ''
ELSE ', '+INDEX_COL('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id],4) END +
CASE
WHEN INDEX_COL('[' + u.[name] + '].['+ o.[name] + ']', i.[index_id], 5) IS NULL THEN ''
ELSE ', '+INDEX_COL('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id],5) END +
CASE
WHEN INDEX_COL('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id], 6) IS NULL THEN ''
ELSE ', '+INDEX_COL('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id],6) END +
CASE
WHEN INDEX_COL('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id], 7) IS NULL THEN ''
ELSE ', '+INDEX_COL('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id], 7) END +
CASE
WHEN INDEX_COL('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id],8) IS NULL THEN ''
ELSE ', '+INDEX_COL('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id],8) END +
CASE
WHEN INDEX_COL('[' + u.[name] + '].['+ o.[name] + ']', i.[index_id], 9) IS NULL THEN ''
ELSE ', '+INDEX_COL('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id],9) END +
CASE
WHEN INDEX_COL('[' + u.[name] + '].['+ o.[name] + ']', i.[index_id], 10) IS NULL THEN ''
ELSE ', '+INDEX_COL('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id],10) END AS [AllColName],
(SELECT COALESCE(c1.[name],'') FROM [sys].[columns] AS c1 INNER JOIN [sys].[index_columns] AS ic1
ON c1.[object_id] = ic1.[object_id] AND c1.[column_id] = ic1.[column_id] AND ic1.[key_ordinal] = 1
WHERE ic1.[object_id] = i.[object_id] AND ic1.[index_id] = i.[index_id]) AS [ColName1],
CASE
WHEN INDEX_COL('[' + u.[name] + '].['+ o.[name] + ']', i.[index_id], 2) IS NULL THEN ''
ELSE INDEX_COL('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id],2) END AS [ColName2],
CASE
WHEN INDEX_COL('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id], 3) IS NULL THEN ''
ELSE INDEX_COL('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id],3) END AS [ColName3],
CASE
WHEN INDEX_COL('[' + u.[name] + '].['+ o.[name] + ']', i.[index_id], 4) IS NULL THEN ''
ELSE INDEX_COL('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id],4) END AS [ColName4],
CASE
WHEN INDEX_COL('[' + u.[name] + '].['+ o.[name] + ']', i.[index_id], 5) IS NULL THEN ''
ELSE INDEX_COL('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id],5) END AS [ColName5],
CASE
WHEN INDEX_COL('[' + u.[name] + '].['+ o.[name] + ']', i.[index_id], 6) IS NULL THEN ''
ELSE INDEX_COL('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id],6) END AS [ColName6],
CASE
WHEN INDEX_COL('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id], 7) IS NULL THEN ''
ELSE INDEX_COL('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id],7) END AS [ColName7],
CASE
WHEN INDEX_COL('[' + u.[name] + '].['+ o.[name] + ']', i.[index_id],8) IS NULL THEN ''
ELSE INDEX_COL('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id],8) END AS [ColName8],
CASE
WHEN INDEX_COL('[' + u.[name] + '].['+ o.[name] + ']', i.[index_id], 9) IS NULL THEN ''
ELSE INDEX_COL('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id],9) END AS [ColName9],
CASE
WHEN INDEX_COL('[' + u.[name] + '].['+ o.[name] + ']', i.[index_id], 10) IS NULL THEN ''
ELSE INDEX_COL('[' + u.[name] + '].[' + o.[name] + ']', i.[index_id],10) END AS [ColName10]
FROM [sys].[objects] AS o WITH (NOLOCK)
LEFT OUTER JOIN [sys].[indexes] AS i WITH (NOLOCK)
ON o.[object_id] = i.[object_id]
JOIN [sys].[schemas] AS u WITH (NOLOCK)
ON o.[schema_id] = u.[schema_id]
WHERE o.[type] = 'U' --AND i.[index_id] < 255
AND o.[name] NOT IN ('dtproperties')
AND i.[name] NOT LIKE '_WA_Sys_%'
-----------
SELECT 'Listing All Indexes' AS [Comments]
SELECT I.*
FROM @AllIndexes AS I
ORDER BY [Table Name]
-----------
SELECT 'Listing Possible Redundant Index keys' AS [Comments]
SELECT DISTINCT I.[Table Name], I.[Index Name] ,I.[Index Type], I.[Constraint Type], I.[AllColName]
FROM @AllIndexes AS I
JOIN @AllIndexes AS I2
ON I.[Table ID] = I2.[Table ID]
AND I.[ColName1] = I2.[ColName1]
AND I.[Index Name] <> I2.[Index Name]
AND I.[Index Type] <> 'XML'
ORDER BY I.[Table Name], I.[AllColName]
----------
SELECT 'Listing Possible Reverse Index keys' AS [Comments]
SELECT DISTINCT I.[Table Name], I.[Index Name], I.[Index Type], I.[Constraint Type], I.[AllColName]
FROM @AllIndexes AS I
JOIN @AllIndexes AS I2
ON I.[Table ID] = I2.[Table ID]
AND I.[ColName1] = I2.[ColName2]
AND I.[ColName2] = I2.[ColName1]
AND I.[Index Name] <> I2.[Index Name]
AND I.[Index Type] <> 'XML'
以上SQL语句，比较常用，分享给大家。
周国庆
2022/11/14
标签：index,常见问题,name,INDEX,SQLServer,COL,NET,NULL,id From： https://www.cnblogs.com/tianqing/p/16887038.html