SQL Server 重建索引|索引重组|索引的碎片检查 （MSSQL个人笔记之数据库优化之路 六<SQL2005以上>）
SQL Server 重建索引|索引重组|索引的碎片检查 （SQL2005以上） /******************************************************************************** *主题：SQL Server 重建索引|索引重组|索引的碎片检查 （SQL2005以上） *说明：本文是个人学习的一些笔记和个人愚见 * 有很多地方你可能觉得有异议，欢迎一起讨论 *作者：Stephenzhou(阿蒙) *日期: 2012.08.24 *Mail:szstephenzhou@163.com *另外：转载请著名出处。 **********************************************************************************/
什么是索引碎片呢？
由于表上有过度地插入、修改和删除操作，索引页被分成多块就形成了索引碎片，如果索引碎片严重，那扫描索引的时间就会变长，甚至导致索引不可用，因此数据检索操作就慢下来了。
检查索引碎片
SELECT OBJECT_NAME(dt.object_id) , si.name , dt.avg_fragmentation_in_percent, dt.avg_page_space_used_in_percent FROM (SELECT object_id , index_id , avg_fragmentation_in_percent, avg_page_space_used_in_percent FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'DETAILED') WHERE index_id <> 0 ) AS dt --does not return information about heaps INNER JOIN sys.indexes si ON si.object_id = dt.object_id AND si.index_id = dt.index_id
执行结果如下：
/* avg_fragmentation_in_percent avg_page_space_used_in_percent-------------------------------------------------------- ---------- ---------------------------- ------------------------------consume_vewt01 pk_Aount 0 0tb_Security PK__tb_Sec 0 0tb1 PK__tb1__3 0 1.54435384235236tb1 PK__tb1__3 50 70.5831480108723tb1 PK__tb1__3 0 0.296515937731653tb1 PK__tb1__3 0 1.54435384235236tb1 PK__tb1__3 0 1.54435384235236consume_vewt02 pk_Aount2 0 3.26167531504818consume_vewt03 pk_Aount3 0 0.76599950580677consume_vewt04 pk_Aount4 0 0sysarticles c1sysartic 0 30.5782060785767sysarticles c1sysartic 0 16.3330862367186sysarticlecolumns idx_sysart 0 25.3274030145787sysschemaarticles c1sysschem 0 0syspublications uc1syspubl 0 2.82925623918952syspublications unc2syspub 0 0.382999752903385syspublications nc3syspubl 0 0.0741289844329133syssubscriptions unc1syssub 0 6.46157647640227sysarticleupdates unc1sysart 0 0MSpub_identity_range unc1MSpub_ 0 0systranschemas uncsystran 0 0MSpeer_lsns uci_MSpeer 0 0MSpeer_lsns PK__MSpeer 0 0MSpeer_originatorid_history uci_MSpeer 0 0MSpeer_conflictdetectionconfigrequest PK__MSpeer 0 0MSpeer_conflictdetectionconfigresponse uci_MSpeer 0 0consume pk_cludere 27.9693855911781 53.1379169755374consume pk_cludere 0 48.0127625401532consume pk_cludere 0 48.0319372374599consume pk_cludere 0 53.1233012107734consume pk_cludere 0 5.52260934025204consume IX_Amount 0.159355006666088 99.0977637756363consume IX_Amount 3.27783558792924 96.9405238448233consume IX_Amount 96.6666666666667 94.1677044724487consume IX_Amount 0 44.0820360761058sysreplservers PK__sysrep 0 0.506548060291574consumeRange in_idex 0.01 99.9302693353101consumeRange in_idex 0 99.1549789967877consumeRange in_idex 0 24.2031134173462consumeRange in_idex 0 99.931739560168consumeRange in_idex 0 99.120484309365consumeRange in_idex 0 24.2031134173462consumeRange in_idex 0.01 99.931739560168consumeRange in_idex 0 99.120484309365consumeRange in_idex 0 24.2031134173462consumeRange in_idex 0 0consumeRange <Name of M 0.0888356334187257 98.7876575240919consumeRange <Name of M 1.33928571428571 98.7559179639239consumeRange <Name of M 100 51.1737089201878consumeRange <Name of M 0 0.889547813194959consumeRange <Name of M 0.0826651235843598 98.7873857178157consumeRange <Name of M 0 99.1660859896219consumeRange <Name of M 0 50.9451445515196consumeRange <Name of M 0 0.889547813194959consumeRange <Name of M 0.0929982640324047 98.7873857178157consumeRange <Name of M 1.34529147982063 99.1660859896219consumeRange <Name of M 100 50.9451445515196consumeRange <Name of M 0 0.889547813194959consumeRange <Name of M 0 0consume_Shopid_Range IX_Amount 0 0consume_Shopid_Range IX_Amount 0 0.296515937731653consume_Shopid_Range IX_Amount 0 99.8732023721275consume_Shopid_Range IX_Amount 0 96.9152829256239consume_Shopid_Range IX_Amount 0 10.6992834198171consume_Shopid_Range IX_Amount 0 99.8691252779837consume_Shopid_Range IX_Amount 0 95.3051643192488consume_Shopid_Range IX_Amount 0 7.23993081294786(67 行受影响) */
内部碎片和外部碎片
为了有效的利用内存，使内存产生更少的碎片 所以要对内存分页 。内存以页单位使用。因为在使用分页装载的过程中经常检查使用的页数也产生的碎片称内部碎片。
为了共享要分段 在段的切换过程中形成的碎片称外部碎片。
什么时候该索引重组
*检查 Externalfragmentation 部分
o 当avg_fragmentation_in_percent 的值介于 10 到 15 之间
*检查 Internalfragmentation 部分
o 当avg_page_space_used_in_percent 的值介于 60 到 75 之间
什么时候该索引重建
*检查 Externalfragmentation 部分
o 当avg_fragmentation_in_percent 的值大于 15
*检查 Internalfragmentation 部分
o 当avg_page_space_used_in_percent 的值小于 60
好了，根据上面的依据做个动态的判断来生成相应的语句哪些索引需要被重建或重组
SELECT 'ALTER INDEX [' + ix.name + '] ON [' + s.name + '].[' + t.name + '] ' + CASE WHEN ps.avg_fragmentation_in_percent > 15 THEN 'REBUILD' ELSE 'REORGANIZE' END + CASE WHEN pc.partition_count > 1 THEN ' PARTITION = ' + CAST(ps.partition_number AS nvarchar(MAX)) ELSE '' END, avg_fragmentation_in_percent FROM sys.indexes AS ix INNER JOIN sys.tables t ON t.object_id = ix.object_id INNER JOIN sys.schemas s ON t.schema_id = s.schema_id INNER JOIN (SELECT object_id , index_id , avg_fragmentation_in_percent, partition_number FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) ) ps ON t.object_id = ps.object_id AND ix.index_id = ps.index_id INNER JOIN (SELECT object_id, index_id , COUNT(DISTINCT partition_number) AS partition_count FROM sys.partitions GROUP BY object_id, index_id ) pc ON t.object_id = pc.object_id AND ix.index_id = pc.index_id WHERE ps.avg_fragmentation_in_percent > 10 AND ix.name IS NOT NULL
执行结果如下：
/* avg_fragmentation_in_percent---------------------------------------------------------------------------------------------------------------- ----------------------------ALTER INDEX [PK__tb1__3213E83F33139D18] ON [dbo].[tb1] REBUILD PARTITION = 2 50ALTER INDEX [pk_cludered_id_date] ON [dbo].[consume] REBUILD 27.9693855911781(2 行受影响)*/
*作者：Stephenzhou(阿蒙)
*日期: 2012.08.24
*Mail:szstephenzhou@163.com
*另外：转载请著名出处。
*博客地址：http://blog.csdn.net/szstephenzhou