ms sqlserver 索引（在MSSQL 2008上试通过，2005需自行测试）

对表的CUD操作导致出现不连续行级块出现，也就是传说中的碎片，这时需要对索引重建，也就是把分配的空块干掉。

1. SELECT OBJECT_NAME(dt.object_id)      ,   
2.     
3.         si.name                        ,   
4.     
5.         dt.avg_fragmentation_in_percent,   
6.     
7.         dt.avg_page_space_used_in_percent   
8.     
9. FROM  
10.     
11.         (SELECT object_id                    ,   
12.     
13.                index_id                    ,   
14.     
15.                avg_fragmentation_in_percent,   
16.     
17.                avg_page_space_used_in_percent   
18.     
19.         FROM    sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'DETAILED')   
20.     
21.         WHERE   index_id <> 0   
22.     
23.         ) AS dt --does not return information about heaps   
24.     
25.         INNER JOIN sys.indexes si   
26.     
27.         ON     si.object_id = dt.object_id   
28.     
29.            AND si.index_id  = dt.index_id   
30. --------------------------------------------------------------------------------------------

31. 内部碎片和外部碎片

        为了有效的利用内存，使内存产生更少的碎片 所以要对内存分页 。内存以页单位使用。因为在使用分页装载的过程中经常检查使用的页数也产生的碎片称内部碎片。

    为了共享要分段 在段的切换过程中形成的碎片称外部碎片。

     

    什么时候该索引重组
    
        *检查 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

    
    

32. 建立动态视图。

1. SELECT 'ALTER INDEX [' + ix.name + '] ON [' + s.name + '].[' + t.name + '] ' +   
2.     
3.        CASE  
4.     
5.               WHEN ps.avg_fragmentation_in_percent > 15   
6.     
7.               THEN 'REBUILD'  
8.     
9.               ELSE 'REORGANIZE'  
10.     
11.         END +   
12.     
13.         CASE  
14.     
15.               WHEN pc.partition_count > 1   
16.     
17.               THEN ' PARTITION = ' + CAST(ps.partition_number AS nvarchar(MAX))   
18.     
19.               ELSE ''  
20.     
21.         END,   
22.     
23.         avg_fragmentation_in_percent   
24.     
25. FROM    sys.indexes AS ix   
26.     
27.         INNER JOIN sys.tables t   
28.     
29.         ON     t.object_id = ix.object_id   
30.     
31.         INNER JOIN sys.schemas s   
32.     
33.         ON     t.schema_id = s.schema_id   
34.     
35.         INNER JOIN  
36.     
37.               (SELECT object_id                   ,   
38.     
39.                       index_id                    ,   
40.     
41.                        avg_fragmentation_in_percent,   
42.     
43.                       partition_number   
44.     
45.               FROM  sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL)   
46.     
47.               ) ps   
48.     
49.         ON     t.object_id = ps.object_id   
50.     
51.            AND ix.index_id = ps.index_id   
52.     
53.         INNER JOIN  
54.     
55.               (SELECT  object_id,   
56.     
57.                        index_id ,   
58.     
59.                        COUNT(DISTINCT partition_number) AS partition_count   
60.     
61.               FROM     sys.partitions   
62.     
63.               GROUP BY object_id,   
64.     
65.                        index_id   
66.     
67.               ) pc   
68.     
69.         ON     t.object_id              = pc.object_id   
70.     
71.            AND ix.index_id              = pc.index_id   
72.     
73. WHERE   ps.avg_fragmentation_in_percent > 10   
74.     
75.     AND ix.name IS NOT NULL  
76. 对查询出的建果进行重建
77. 1. /*                                                                                                                                                                                                                                                                 avg_fragmentation_in_percent  
    2. ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------  
    3. ALTER INDEX [PK__tb1__3213E83F33139D18] ON [dbo].[tb1] REBUILD PARTITION = 2                                                                                                                                                                                     50  
    4. ALTER INDEX [pk_cludered_id_date] ON [dbo].[consume] REBUILD                                                                                                                                                                                                     27.9693855911781  
    5.   
    6. (2 行受影响)  
    7.   
    8. */