SQL Server 性能优化之——重复索引

CREATE TABLE Test_Table( Col1 int NOT NULL PRIMARY KEY, Col2 varchr(30) NOT NULL, Col3 varchr(30) NOT NULL, Col4 varchr(30) NOT NULL,)

SELECT
sch.name + '.' + t.name AS [Table Name],
i.name AS [Index Name],
i.type_desc,
ISNULL(user_updates,0) AS [Total Writes],
ISNULL(user_seeks + user_scans + user_lookups,0) AS [Total Reads],
s.last_user_seek,
s.last_user_scan ,
s.last_user_lookup,
ISNULL(user_updates,0) - ISNULL((user_seeks + user_scans + user_lookups),0) AS [Difference],
p.reserved_page_count * 8.0 / 1024 as SpaceInMB
FROM sys.indexes AS i WITH (NOLOCK)
LEFT OUTER JOIN sys.dm_db_index_usage_stats AS sWITH (NOLOCK) ON s.object_id = i.object_id AND i.index_id = s.index_id AND s.database_id=db_id() AND objectproperty(s.object_id,'IsUserTable') = 1
INNER JOINsys.tablesAS tWITH (NOLOCK) ON i.object_id = t.object_id
INNER JOINsys.schemasAS schWITH (NOLOCK) ON t.schema_id = sch.schema_id
LEFT OUTER JOIN sys.dm_db_partition_statsAS pWITH (NOLOCK) ON i.index_id = p.index_id and i.object_id = p.object_id
WHERE (1=1)
--AND ISNULL(user_updates,0) >= ISNULL((user_seeks + user_scans + user_lookups),0) --显示包含没有使用的约束在内的所有约束
--AND ISNULL(user_updates,0) - ISNULL((user_seeks + user_scans + user_lookups),0)>0 --仅仅显示那些已经使用的索引
--AND i.index_id > 1-- 非第一索引
--AND i.is_primary_key<>1-- 不是作为主键被定义的
--AND i.is_unique_constraint<>1 -- 不是UniqueConstraints
ORDER BY [Table Name], [index name]

/* 执行这个脚本后，索引将会以三个报表的实行展现出来
请看下面:
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'