SQL Server 重建索引|索引重组|索引的碎片检查 （MSSQL个人笔记之数据库优化之路 六）
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 0
tb_Security PK__tb_Sec 0 0
tb1 PK__tb1__3 0 1.54435384235236
tb1 PK__tb1__3 50 70.5831480108723
tb1 PK__tb1__3 0 0.296515937731653
tb1 PK__tb1__3 0 1.54435384235236
tb1 PK__tb1__3 0 1.54435384235236
consume_vewt02 pk_Aount2 0 3.26167531504818
consume_vewt03 pk_Aount3 0 0.76599950580677
consume_vewt04 pk_Aount4 0 0
sysarticles c1sysartic 0 30.5782060785767
sysarticles c1sysartic 0 16.3330862367186
sysarticlecolumns idx_sysart 0 25.3274030145787
sysschemaarticles c1sysschem 0 0
syspublications uc1syspubl 0 2.82925623918952
syspublications unc2syspub 0 0.382999752903385
syspublications nc3syspubl 0 0.0741289844329133
syssubscriptions unc1syssub 0 6.46157647640227
sysarticleupdates unc1sysart 0 0
MSpub_identity_range unc1MSpub_ 0 0
systranschemas uncsystran 0 0
MSpeer_lsns uci_MSpeer 0 0
MSpeer_lsns PK__MSpeer 0 0
MSpeer_originatorid_history uci_MSpeer 0 0
MSpeer_conflictdetectionconfigrequest PK__MSpeer 0 0
MSpeer_conflictdetectionconfigresponse uci_MSpeer 0 0
consume pk_cludere 27.9693855911781 53.1379169755374
consume pk_cludere 0 48.0127625401532
consume pk_cludere 0 48.0319372374599
consume pk_cludere 0 53.1233012107734
consume pk_cludere 0 5.52260934025204
consume IX_Amount 0.159355006666088 99.0977637756363
consume IX_Amount 3.27783558792924 96.9405238448233
consume IX_Amount 96.6666666666667 94.1677044724487
consume IX_Amount 0 44.0820360761058
sysreplservers PK__sysrep 0 0.506548060291574
consumeRange in_idex 0.01 99.9302693353101
consumeRange in_idex 0 99.1549789967877
consumeRange in_idex 0 24.2031134173462
consumeRange in_idex 0 99.931739560168
consumeRange in_idex 0 99.120484309365
consumeRange in_idex 0 24.2031134173462
consumeRange in_idex 0.01 99.931739560168
consumeRange in_idex 0 99.120484309365
consumeRange in_idex 0 24.2031134173462
consumeRange in_idex 0 0
consumeRange <Name of M 0.0888356334187257 98.7876575240919
consumeRange <Name of M 1.33928571428571 98.7559179639239
consumeRange <Name of M 100 51.1737089201878
consumeRange <Name of M 0 0.889547813194959
consumeRange <Name of M 0.0826651235843598 98.7873857178157
consumeRange <Name of M 0 99.1660859896219
consumeRange <Name of M 0 50.9451445515196
consumeRange <Name of M 0 0.889547813194959
consumeRange <Name of M 0.0929982640324047 98.7873857178157
consumeRange <Name of M 1.34529147982063 99.1660859896219
consumeRange <Name of M 100 50.9451445515196
consumeRange <Name of M 0 0.889547813194959
consumeRange <Name of M 0 0
consume_Shopid_Range IX_Amount 0 0
consume_Shopid_Range IX_Amount 0 0.296515937731653
consume_Shopid_Range IX_Amount 0 99.8732023721275
consume_Shopid_Range IX_Amount 0 96.9152829256239
consume_Shopid_Range IX_Amount 0 10.6992834198171
consume_Shopid_Range IX_Amount 0 99.8691252779837
consume_Shopid_Range IX_Amount 0 95.3051643192488
consume_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 50 ALTER INDEX [pk_cludered_id_date] ON [dbo].[consume] REBUILD 27.9693855911781 (2 行受影响) */
*作者：Stephenzhou(阿蒙)
*日期: 2012.08.24
*Mail:szstephenzhou@163.com
*另外：转载请著名出处。
*博客地址：http://blog.****.net/szstephenzhou