今天遇到一个SQL Performance Tuning需求：
对于用户表的索引进行优化，剔出那些重复的索引，添加必要的索引。
那么，我们必须先把表中的索引给找出来，SQL Server数据库和其他数据库一样，都有强大的元数据表，所以我们也可以从元数据表中得到索引(INDEX)的信息。 要完成这个查询需求，会涉及到master数据库中的几个视图(VIEW)：
SELECT '['+Sch.name+'].['+ Tab.[name]+']' AS TableName,
Ind.[name] AS IndexName,
SUBSTRING(( SELECT ', ' + AC.name
FROM sys.[tables] AS T
INNER JOIN sys.[indexes] I
ON T.[object_id] = I.[object_id]
INNER JOIN sys.[index_columns] IC
ON I.[object_id] = IC.[object_id]
AND I.[index_id] = IC.[index_id]
INNER JOIN sys.[all_columns] AC
ON T.[object_id] = AC.[object_id]
AND IC.[column_id] = AC.[column_id]
WHERE Ind.[object_id] = I.[object_id]
AND Ind.index_id = I.index_id
AND IC.is_included_column = 0
ORDER BY IC.key_ordinal
FOR XML PATH('')
), 2, 8000) AS KeyColumns,
SUBSTRING(( SELECT ', ' + AC.name
FROM sys.[tables] AS T
INNER JOIN sys.[indexes] I
ON T.[object_id] = I.[object_id]
INNER JOIN sys.[index_columns] IC
ON I.[object_id] = IC.[object_id]
AND I.[index_id] = IC.[index_id]
INNER JOIN sys.[all_columns] AC
ON T.[object_id] = AC.[object_id]
AND IC.[column_id] = AC.[column_id]
WHERE Ind.[object_id] = I.[object_id]
AND Ind.index_id = I.index_id
AND IC.is_included_column = 1
ORDER BY IC.key_ordinal
FOR XML PATH('')
), 2, 8000) AS IncludedColumns,
Ind.type_desc AS IndexType,
CASE Ind.is_primary_key
WHEN 0 THEN 'N'
WHEN 1 THEN 'Y'
END AS IsPrimaryKey,
CASE Ind.is_unique
WHEN 0 THEN 'N'
WHEN 1 THEN 'Y'
END AS IsUnique
FROM sys.[indexes] Ind
INNER JOIN sys.[tables] AS Tab
ON Tab.[object_id] = Ind.[object_id]
INNER JOIN sys.[schemas] AS Sch
ON Sch.[schema_id] = Tab.[schema_id]
WHERE Ind.[name] is not null
ORDER BY TableName DESC
上边这段代码的难点可能就是FOR XML PATH的用法。可以参考下面这篇博文：
在此我们拿Northwind数据库来演示代码执行后的结果。
| TableName | IndexName | KeyColumns | IncludeColumns | IndexType | IsPrimaryKey | IsUnique |
| [dbo].[Territories] | PK_Territories | TerritoryID | NULL | NONCLUSTERED | Y | Y |
| [dbo].[Suppliers] | PK_Suppliers | SupplierID | NULL | CLUSTERED | Y | Y |
| [dbo].[Suppliers] | CompanyName | CompanyName | NULL | NONCLUSTERED | N | N |
| [dbo].[Suppliers] | PostalCode | PostalCode | NULL | NONCLUSTERED | N | N |
| [dbo].[Shippers] | PK_Shippers | ShipperID | NULL | CLUSTERED | Y | Y |
| [dbo].[Region] | PK_Region | RegionID | NULL | NONCLUSTERED | Y | Y |
| [dbo].[Products] | PK_Products | ProductID | NULL | CLUSTERED | Y | Y |
| [dbo].[Products] | CategoriesProducts | CategoryID | NULL | NONCLUSTERED | N | N |
此内容由惯性聚合(RSS阅读器)自动聚合整理，仅供阅读参考。 原文来自 — 版权归原作者所有。