原文:MS SQL巡檢系列——檢查重復(fù)索引

前言感想：一時(shí)興起，突然想寫一個(gè)關(guān)于MS SQL的巡檢系列方面的文章，因?yàn)槲矣X得這方面的知識(shí)分享是有價(jià)值，也是非常有意義的。一方面，很多經(jīng)驗(yàn)不足的人，對(duì)于巡檢有點(diǎn)茫然，不知道要從哪些方面巡檢，另外一方面，網(wǎng)上關(guān)于MS SQL巡檢方面的資料好像也不是特別多。寫這個(gè)系列只是一個(gè)分享，自己的初衷是一個(gè)知識(shí)梳理、總結(jié)提煉過程，有些知識(shí)和腳本也不是原創(chuàng)，文章很多地方也是融入了自己的一些想法和見解的，不足和膚淺之處肯定也非常多，拋磚引玉，也希望大家提意見和建議、補(bǔ)充，指正其中的不足之處。Stay Hungry Stay Foolish！

?

在SQL Server數(shù)據(jù)庫中，有可能存在重復(fù)的索引（Duplicate Indexes），這個(gè)不僅影響性能（INSERT、UPDATE、DELETE時(shí)帶來額外的IO開銷，當(dāng)數(shù)據(jù)庫維護(hù)，索引重組時(shí)也會(huì)帶來額外的開銷），而且占用空間。數(shù)據(jù)庫存在重復(fù)索引（Duplicate Indexes）的原因是多方面的，很多時(shí)候、很多事情不是你所能完全掌控的，除非你所管理的數(shù)據(jù)庫非常規(guī)范，權(quán)限控制、腳本發(fā)布非常嚴(yán)格、流程化。暫且不說這些，那么怎么在數(shù)據(jù)庫巡檢過程找出這些重復(fù)的索引（Duplicate Indexes）呢？ 下面分享一個(gè)我在Premier Proactive Services中發(fā)現(xiàn)一個(gè)的腳本（做了一些修改和調(diào)整）。

?

我們以AdventureWorks2014數(shù)據(jù)庫為例，如下所示，表[Person].[Address]下有4個(gè)索引，如下所示

?

?

假設(shè)某個(gè)二愣子在這個(gè)表的字段StateProvinceID上創(chuàng)建了下面重復(fù)索引，IX_Address_N1 與IX_Address_StateProvinceID是一個(gè)重復(fù)索引。

? CREATE?INDEX IX_Address_N1 ON [Person].[Address](StateProvinceID);

?

那么我們執(zhí)行下面腳本就能找到這個(gè)重復(fù)的索引，如下所示

;WITH??? IndexColumns ????????? AS ( SELECT?DISTINCT ????????????????????? SCHEMA_NAME(o.schema_id)???? AS SchemaName??? , ????????????????????? OBJECT_NAME(o.object_id)???? AS TableName???? , ????????????????????? i.name?????????????????????? AS IndexName???? , ????????????????????? o.object_id????????????????? AS [Object_ID]?? , ????????????????????? i.index_id?????????????????? AS Index_ID????? , ????????????????????? i.type_desc???????????????? AS IndexType????? , ????????????????????? ( SELECT??? CASE key_ordinal ??????????????????????????????????? WHEN 0 THEN?NULL ??????????????????????????????????? ELSE?'[' + COL_NAME(k.object_id, ??????????????????????????????????????????????????????? column_id) + '] ' ???????????????????????????????????????? + CASE?WHEN is_descending_key = 1 ??????????????????????????????????????????????? THEN?'Desc' ??????????????????????????????????????????????? ELSE?'Asc' ?????????????????????????????????????????? END ????????????????????????????????? END?AS [data()] ??????????????????????? FROM????? sys.index_columns? k WITH(NOLOCK) ??????????????????????? WHERE???? k.object_id = i.object_id ????????????????????????????????? AND k.index_id = i.index_id ??????????????????????? ORDER?BY? key_ordinal , ????????????????????????????????? column_id ????????????????????? FOR ??????????????????????? XML PATH('') ????????????????????? ) AS IndexColumns , ??????????????????????? CASE?WHEN i.index_id = 1 ???????????????????????????? THEN ( SELECT? '[' + name + ']'?AS [data()] ??????????????????????????????????? FROM??? sys.columns (NOLOCK) AS c ??????????????????????????????????? WHERE?? c.object_id = i.object_id ??????????????????????????????????????????? AND c.column_id NOT?IN ( ??????????????????????????????????????????? SELECT? column_id ??????????????????????????????????????????? FROM??? sys.index_columns (NOLOCK) ??????????????????????????????????????????????????? AS kk ??????????????????????????????????????????? WHERE?? kk.object_id = i.object_id ??????????????????????????????????????????????????? AND kk.index_id = i.index_id ) ??????????????????????????????????? ORDER?BY column_id ????????????????????????????????? FOR ??????????????????????????????????? XML PATH('') ????????????????????????????????? ) ???????????????????????????? ELSE ( SELECT? '[' + COL_NAME(k.object_id, ?????????????????????????????????????????????????????????? column_id) + ']'?AS [data()] ??????????????????????????????????? FROM??? sys.index_columns k WITH(NOLOCK) ??????????????????????????????????? WHERE?? k.object_id = i.object_id ??????????????????????????????????????????? AND k.index_id = i.index_id ??????????????????????????????????????????? AND is_included_column = 1 ??????????????????????????????????????????? AND k.column_id NOT?IN ( ??????????????????????????????????????????? SELECT? column_id ??????????????????????????????????????????? FROM??? sys.index_columns kk ??????????????????????????????????????????? WHERE?? k.object_id = kk.object_id ??????????????????????????????????????????????????? AND kk.index_id = 1 ) ??????????????????????????????????? ORDER?BY key_ordinal , ??????????????????????????????????????????? column_id ????????????????????????????????? FOR ??????????????????????????????????? XML PATH('') ????????????????????????????????? ) ??????????????????????? END?AS IndexInclude ?????????????? FROM???? sys.indexes? i WITH(NOLOCK) ??????????????????????? INNER?JOIN sys.objects o WITH(NOLOCK) ON i.object_id = o.object_id ??????????????????????? INNER?JOIN sys.index_columns ic? WITH(NOLOCK ) ON ic.object_id = i.object_id ????????????????????????????????????????????????????????????? AND ic.index_id = i.index_id ??????????????????????? INNER?JOIN sys.columns c WITH(NOLOCK) ON c.object_id = ic.object_id ????????????????????????????????????????????????????????????? AND c.column_id = ic.column_id ?????????????? WHERE??? o.type = 'U' ??????????????????????? AND i.index_id <> 0? -- 0 = 堆 ??????????????????????? AND i.type <> 3???????? -- 3 = XML? ??????????????????????? AND i.type <> 5???????? -- 5 = 聚集列存儲(chǔ)索引(SQL 2014~ SQL 2016) ??????????????????????? AND i.type <> 6???????? -- 6 = 非聚集列存儲(chǔ)索引(SQL 2014~ SQL 2016) ??????????????????????? AND i.type <> 7???????? -- 7 = 非聚集哈希索引(SQL 2014~ SQL 2016) ?????????????? GROUP?BY o.schema_id , ??????????????????????? o.object_id , ??????????????????????? i.object_id , ??????????????????????? i.name , ??????????????????????? i.index_id , ??????????????????????? i.type_desc ???????????? ), ??????? DuplicatesTable ????????? AS ( SELECT?? ic1.SchemaName??? , ??????????????????????? ic1.TableName???? , ??????????????????????? ic1.IndexName???? , ??????????????????????? ic1.[Object_ID]?? , ??????????????????????? ic2.IndexName AS DuplicateIndexName , ??????????????????????? ic1.IndexType?? , ??????????????????????? CASE?WHEN ic1.index_id = 1 ???????????????????????????? THEN ic1.IndexColumns + ' (Clustered)' ???????????????????????????? WHEN ic1.IndexInclude = ''?THEN ic1.IndexColumns ???????????????????????????? WHEN ic1.IndexInclude IS?NULL?THEN ic1.IndexColumns ???????????????????????????? ELSE ic1.IndexColumns + ' INCLUDE ' + ic1.IndexInclude ??????????????????????? END?AS IndexCols , ??????????????????????? ic1.index_id ?????????????? FROM???? IndexColumns ic1 ??????????????????????? JOIN IndexColumns ic2 ON ic1.object_id = ic2.object_id ???????????????????????????????????????????????? AND ic1.index_id < ic2.index_id ???????????????????????????????????????????????? AND ic1.IndexColumns = ic2.IndexColumns ???????????????????????????????????????????????? AND ( ISNULL(ic1.IndexInclude, '') = ISNULL(ic2.IndexInclude, ????????????????????????????????????????????????????????????? '') ?????????????????????????????????????????????????????? OR ic1.index_id = 1 ???????????????????????????????????????????????????? ) ???????????? ) ??? SELECT? SchemaName , ??????????? TableName , ??????????? IndexName , ??????????? DuplicateIndexName , ??????????? IndexType, ??????????? IndexCols , ??????????? Index_ID , ????????? Object_ID , ????????? 0 AS IsXML ??? FROM??? DuplicatesTable dt ??? ORDER?BY 1 , 2 ,3

?

?

注意，關(guān)于重復(fù)索引（Duplicate Indexes）表示存在的索引除了名字不一樣外， 索引所在字段以及索引字段順序都是一樣的。An index is considered to be a duplicate if it references the same column and ordinal position as another index in the same database。 這個(gè)腳本是找出一模一樣的索引，如果你創(chuàng)建下面索引，索引字段一樣，但是有包含列字段不一樣，那么這個(gè)腳本會(huì)將這個(gè)索引視為不一樣的索引。有興趣可以自己試試。

?

CREATE INDEX IX_Address_N2 ON [Person].[Address](StateProvinceID) INCLUDE (City);

?

另外關(guān)于XML索引的重復(fù)索引，可以使用下面腳本檢查。

--Use the below T-SQL script to generate the complete list of duplicate XML indexes in a given database: ? ;WITH??? XMLTable ????????? AS ( SELECT?? OBJECT_NAME(x.object_id) AS TableName , ??????????????????????? SCHEMA_NAME(o.schema_id) AS SchemaName , ??????????????????????? x.object_id , ??????????????????????? x.name , ??????????????????????? x.index_id , ??????????????????????? x.using_xml_index_id , ??????????????????????? x.secondary_type , ??????????????????????? CONVERT(NVARCHAR(MAX), x.secondary_type_desc) AS secondary_type_desc , ??????????????????????? ic.column_id ?????????????? FROM???? sys.xml_indexes x ( NOLOCK ) ??????????????????????? JOIN sys.objects o ( NOLOCK ) ON x.object_id = o.object_id ??????????????????????? JOIN sys.index_columns (NOLOCK) ic ON x.object_id = ic.object_id ????????????????????????????????????????????????????????????? AND x.index_id = ic.index_id ???????????? ), ??????? DuplicatesXMLTable ????????? AS ( SELECT?? x1.SchemaName , ??????????????????????? x1.TableName , ??????????????????????? x1.name AS IndexName , ??????????????????????? x2.name AS DuplicateIndexName , ??????????????????????? x1.secondary_type_desc AS IndexType , ??????????????????????? x1.index_id , ??????????????????????? x1.object_id , ??????????????????????? ROW_NUMBER() OVER ( ORDER?BY x1.SchemaName, x1.TableName, x1.name, x2.name ) AS seq1 , ??????????????????????? ROW_NUMBER() OVER ( ORDER?BY x1.SchemaName DESC, x1.TableName DESC, x1.name DESC, x2.name DESC ) AS seq2 , ??????????????????????? NULL?AS inc ?????????????? FROM???? XMLTable x1 ??????????????????????? JOIN XMLTable x2 ON x1.object_id = x2.object_id ??????????????????????????????????????????? AND x1.index_id < x2.index_id ??????????????????????????????????????????? AND x1.using_xml_index_id = x2.using_xml_index_id ??????????????????????????????????????????? AND x1.secondary_type = x2.secondary_type ???????????? ) ??? SELECT? SchemaName , ??????????? TableName , ??????????? IndexName , ??????????? DuplicateIndexName , ??????????? IndexType? , ??????????? Index_ID , ??????????? [Object_ID] , ??????????? 1 AS IsXML ??? FROM??? DuplicatesXMLTable dtxml ??? ORDER?BY 1 , ???????????? 2 , ???????????? 3;

?

在每個(gè)庫跑一次這個(gè)腳本，就能將所有的重復(fù)的索引（Duplicate Indexes）全部找出，但是當(dāng)手頭服務(wù)器、數(shù)據(jù)庫特別多時(shí)，這個(gè)工作也是一個(gè)體力活，可以將這個(gè)常規(guī)工作自動(dòng)化，避免重復(fù)勞動(dòng)，我將這個(gè)集成在MyDBA工具里面，只需要點(diǎn)擊一下鼠標(biāo)，就可以幫助我自動(dòng)處理這些工作。

總結(jié)

以上是生活随笔為你收集整理的MS SQL巡检系列mdash;mdash;检查重复索引的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。