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

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

1. SELECT?OBJECT_NAME(dt.object_id)??????,???
2. ????
3. ????????si.name????????????????????????,226); color:inherit; line-height:18px"> ????????dt.avg_fragmentation_in_percent,???
4. ????????dt.avg_page_space_used_in_percent???
5. FROM??
6. ????????(SELECT?object_id????????????????????,226); color:inherit; line-height:18px"> ???????????????index_id????????????????????,226); color:inherit; line-height:18px"> ???????????????avg_fragmentation_in_percent,226); color:inherit; line-height:18px"> ???????????????avg_page_space_used_in_percent???
7. ????????FROM????sys.dm_db_index_physical_stats?(DB_ID(),?NULL,?'DETAILED')???
8. ????????WHERE???index_id?<>?0???
9. ????????)?AS?dt?--does?not?return?information?about?heaps???
10. ????????INNER?JOIN?sys.indexes?si???
11. ????????ON?????si.object_id?=?dt.object_id???
12. ???????????AND?si.index_id??=?dt.index_id ??
13. --------------------------------------------------------------------------------------------

14. 内部碎片和外部碎片

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

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

    ?

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

    
    

15. 建立动态视图。

SELECT?'ALTER?INDEX?['?+?ix.name?+?']?ON?['?+?s.name?+?'].['?+?t.name?+?']?'?+???
1. ????
2. ???????CASE??
3. ??????????????WHEN?ps.avg_fragmentation_in_percent?>?15???
4. ??????????????THEN?'REBUILD'??
5. ??????????????ELSE?'REORGANIZE'??
6. ????????END?+???
7. ???????? ??????????????WHEN?pc.partition_count?>?1???
8. ??????????????THEN?'?PARTITION?=?'?+?CAST(ps.partition_number?AS?nvarchar(MAX))???
9. ??????????????ELSE?''??
10. ????????END,???
11. ????????avg_fragmentation_in_percent???
12. FROM????sys.indexes?AS?ix???
13. JOIN?sys.tables?t???
14. ????????ON?????t.object_id?=?ix.object_id???
15. JOIN?sys.schemas?s???
16. ????????ON?????t.schema_id?=?s.schema_id???
17. JOIN??
18. ??????????????(SELECT?object_id???????????????????,226); color:inherit; line-height:18px"> ??????????????????????index_id????????????????????,???
19. ???????????????????????avg_fragmentation_in_percent,226); color:inherit; line-height:18px"> ??????????????????????partition_number???
20. ??????????????FROM??sys.dm_db_index_physical_stats?(DB_ID(),128); background-color:inherit">NULL)???
21. ??????????????)?ps???
22. ????????ON?????t.object_id?=?ps.object_id???
23. AND?ix.index_id?=?ps.index_id???
24. ??????????????(SELECT??object_id,226); color:inherit; line-height:18px"> ???????????????????????index_id?,226); color:inherit; line-height:18px"> ???????????????????????COUNT(DISTINCT?partition_number)?AS?partition_count???
25. ??????????????FROM?????sys.partitions???
26. ??????????????GROUP?BY?object_id,226); color:inherit; line-height:18px"> ???????????????????????index_id???
27. ??????????????)?pc???
28. ????????ON?????t.object_id??????????????=?pc.object_id???
29. AND?ix.index_id??????????????=?pc.index_id???
30. WHERE???ps.avg_fragmentation_in_percent?>?10???
31. ????AND?ix.name?IS?NOT?NULL??
32. 对查询出的建果进行重建
33. /*?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????avg_fragmentation_in_percent??
    1. ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------?----------------------------??
    2. ALTER?INDEX?[PK__tb1__3213E83F33139D18]?ON?[dbo].[tb1]?REBUILD?PARTITION?=?2?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????50??
    3. ALTER?INDEX?[pk_cludered_id_date]?ON?[dbo].[consume]?REBUILD?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????27.9693855911781??
    4. ??
    5. (2?行受影响)??
    6. */

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!