对表的CUD操作导致出现不连续行级块出现,也就是传说中的碎片,这时需要对索引重建,也就是把分配的空块干掉。
- 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
- --------------------------------------------------------------------------------------------
-
内部碎片和外部碎片
为了有效的利用内存,使内存产生更少的碎片 所以要对内存分页 。内存以页单位使用。因为在使用分页装载的过程中经常检查使用的页数也产生的碎片称内部碎片。
为了共享要分段 在段的切换过程中形成的碎片称外部碎片。
什么时候该索引重组
*检查 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 行受影响)
- */