索引碎片：1,当索引包含的页中的逻辑顺序与数据文件中的物理排序不匹配时，就存在碎片，碎片非常多的索引会降低查询的性能，导致应用程序响应缓慢，可通过重新组织索引或重新生成索引来修复索引碎片。

2,当索引页不在逻辑顺序上时就会产生外部碎片。索引创建时，索引键按照逻辑顺 序放在一组索引页上。当新数据插入索引时，新的键可能放在存在的键之间。为 了让新的键按照正确的顺序插入， 可能会创建新的索引页来存储需要移动的那些 存在的键。这些新的索引页通常物理上不会和那些被移动的键原来所在的页相 邻。创建新页的过程会引起索引页偏离逻辑顺序。

 3,在有特定搜索或者返回无序结果集的查询的情况下， 偏离顺序的索引页不会引起 问题。 对于返回有序结果集的查询， 搜索那些无序的索引页需要进行额外的处理。造成性能低下。
即：在初建时索引是有序的，但不断的update,insert,delete操作使得索引页变得局部无序，但要求返回有序的结果集时，不得不浪费额外时间进行排序。而索引维护的过程就是将排序归为有序的过程

 索引状况检查:dm_db_index_physical_stats
 当avg_fragmentation_in_percent碎片率 <5%时，是正常级别
 当avg_fragmentation_in_percent碎片率 >=5% AND <=30%时，是轻度级别，需要重新组织索引
 当avg_fragmentation_in_percent碎片率 >30%时，是严重级别，需要重建索引
 alert index 索引名称 ON 架构名称.表名 reorganize/rebulid partition = 分区号
 sys.objects:存储表、视图、存储过程

查看：
SELECT *
FROM SYS.dm_db_index_physical_stats(DB_ID(), object_id('bmdcustomer'),NULL,NULL,'LIMITED') DDIPS

1，索引状态检查与维护
SELECT s.name schema_name,o.name OBJECT_name,ix.name INDEX_name,DDIPS.avg_fragmentation_in_percent 碎片率,
	CASE WHEN avg_fragmentation_in_percent > 30 THEN '严重碎片级别，索引需要重建'
	     WHEN avg_fragmentation_in_percent >= 5 AND avg_fragmentation_in_percent < 30 THEN '轻度碎片级别，索引需要重新组织'
		 ELSE '正常状态' END 提示
	FROM SYS.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,'LIMITED') DDIPS
	JOIN SYS.objects o ON DDIPS.[object_id] = O.[object_id] --得到表名
	JOIN SYS.schemas s ON S.[schema_id] = O.[schema_id] --得到表的架构名
	JOIN SYS.indexes IX ON DDIPS.[object_id] = IX.[object_id] AND DDIPS.index_id = IX.index_id --得到索引名称
WHERE ddips.[object_id] = object_id('bdcustomerallocate') AND s.[schema_id] = SCHEMA_ID('dbo')  

/*
* 1,重新组织索引：alter index IX_bdCustomerAllocate ON dbo.bdCustomerAllocate reorganize
* 2,重建索引：alter index PK_bdCustomerAllocate ON dbo.bdCustomerAllocate rebulid
* */

2，索引状态批量维护
/*索引维护:检查数据库中所有索引，修复有问题的索引
* alert index 索引名称 ON 架构名称.表名 reorganize/rebulid partition = 分区号
* sys.partitions:数据库中所有表和索引的每个分区在表中各对应一行
* sys.objects:存储表、视图、存储过程
*
* */
DECLARE cur_dx CURSOR FOR

WITH PT AS(
SELECT p.[object_id],p.index_id, COUNT(1) partition_count
FROM SYS.partitions p
GROUP BY p.[object_id],p.index_id),

DIX AS(
	SELECT DDIPS.[object_id],DDIPS.index_id,DDIPS.partition_number,DDIPS.avg_fragmentation_in_percent,
	o.name OBJECT_name,s.name schema_name,ix.name INDEX_name,pt.partition_count,
	CASE WHEN avg_fragmentation_in_percent > 30 THEN '严重碎片级别，索引需要重建'
	     WHEN avg_fragmentation_in_percent >= 5 AND avg_fragmentation_in_percent < 30 THEN '轻度碎片级别，索引需要重新组织' END warning

	FROM SYS.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,'LIMITED') DDIPS
	JOIN SYS.objects o ON DDIPS.[object_id] = O.[object_id] --得到表名
	JOIN SYS.schemas s ON S.[schema_id] = O.[schema_id] --得到表的架构名
	JOIN SYS.indexes IX ON DDIPS.[object_id] = IX.[object_id] AND DDIPS.index_id = IX.index_id --得到索引名称
	JOIN PT ON DDIPS.[object_id] = PT.object_id AND DDIPS.index_id = pt.index_id --得到分区号
	WHERE ddips.avg_fragmentation_in_percent > 5 AND DDIPS.index_id > 0
	AND ddips.[object_id]=OBJECT_ID('bdcustomerallocate')
),
DIXSQL AS(
	SELECT ' ALTER INDEX ' + QUOTENAME(INDEX_NAME)
	+ ' ON ' + QUOTENAME(schema_name) + '.' + QUOTENAME(OBJECT_NAME)
	+ CASE WHEN avg_fragmentation_in_percent < 30 THEN ' REORGANIZE '
	  ELSE ' REBUILD ' END
	+ CASE WHEN partition_count > 1
	THEN ' PARTITION =  ' + CONVERT(NVARCHAR(20),partition_number)
	ELSE '' END  SQL
	FROM DIX
)
SELECT * FROM DIXSQL

DECLARE @sql NVARCHAR(max)
OPEN cur_dx
FETCH cur_dx INTO @sql
WHILE @@FETCH_STATUS = 0
BEGIN
	EXEC sp_executesql @sql
	RAISERROR('Executed:%s',10,1,@sql) WITH NOWAIT
	FETCH cur_dx INTO @sql
END
CLOSE cur_dx
DEALLOCATE cur_dx

--ALTER INDEX [PK_bdCustomerAllocate] ON [dbo].[bdCustomerAllocate]   REBUILD 

【版权声明】
本站部分内容来源于互联网，本站不拥有所有权，不承担相关法律责任。如果发现本站有侵权的内容，欢迎发送邮件至masing@13sy.com 举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。