SQL Server 重建索引|索引重组|索引的碎片检查

什么是索引碎片呢?

由于表上有过度地插入、修改和删除操作,索引页被分成多块就形成了索引碎片,如果索引碎片严重,那扫描索引的时间就会变长,甚至导致索引不可用,因此数据检索操作就慢下来了。

 

 

检查索引碎片

 

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 行受影响)
*/

 
 

©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页