SQL Server 重建索引 |
您所在的位置：网站首页 › sql2000重建索引 › SQL Server 重建索引 |
SQL Server 重建索引
|
SQL Server 重建索引|索引重组|索引的碎片检查 （SQL2005以上）
/********************************************************************************
*主题：SQL Server 重建索引|索引重组|索引的碎片检查 （SQL2005以上）
*说明：本文是个人学习的一些笔记和个人愚见
* 有很多地方你可能觉得有异议，欢迎一起讨论
*作者：Stephenzhou(阿蒙)
*日期: 2012.08.24
*Mail:[email protected]
*另外：转载请著名出处。
**********************************************************************************/
什么是索引碎片呢？ 由于表上有过度地插入、修改和删除操作，索引页被分成多块就形成了索引碎片，如果索引碎片严重，那扫描索引的时间就会变长，甚至导致索引不可用，因此数据检索操作就慢下来了。
检查索引碎片 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 |
【本文地址】
今日新闻 |
推荐新闻 |