1.查找索引碎片大于50%
SELECT OBJECT_NAME(IND.OBJECT_ID) AS TABLENAME
,IND.NAME AS INDEXNAME
,INDEXSTATS.INDEX_TYPE_DESC AS INDEXTYPE
,INDEXSTATS.AVG_FRAGMENTATION_IN_PERCENT
FROM SYS.DM_DB_INDEX_PHYSICAL_STATS(DB_ID()
,NULL
,NULL
,NULL
,NULL) INDEXSTATS
INNER JOIN SYS.INDEXES IND ON IND.OBJECT_ID = INDEXSTATS.OBJECT_ID
AND IND.INDEX_ID = INDEXSTATS.INDEX_ID
WHERE INDEXSTATS.AVG_FRAGMENTATION_IN_PERCENT > 50
ORDER BY INDEXSTATS.AVG_FRAGMENTATION_IN_PERCENT DESC
2.DBCC SHOWCONTIG是显示指定的表的数据和索引的碎片信息。
DBCC SHOWCONTIG(表名);
查出之后的详细说明
解释如下:
Page Scanned-扫描页数:如果你知道行的近似尺寸和表或索引里的行数,那么你可以估计出索引里的页数。看看扫描页数,如果明显比你估计的页数要高,说明存在内部碎片。
Extents Scanned-扫描扩展盘区数:用扫描页数除以8,四舍五入到下一个最高值。该值应该和DBCC SHOWCONTIG返回的扫描扩展盘区数一致。如果DBCC SHOWCONTIG返回的数高,说明存在外部碎片。碎片的严重程度依赖于刚才显示的值比估计值高多少。
Extent Switches-扩展盘区开关数:该数应该等于扫描扩展盘区数减1。高了则说明有外部碎片。
Avg. Pages per Extent-每个扩展盘区上的平均页数:该数是扫描页数除以扫描扩展盘区数,一般是8。小于8说明有外部碎片。
Scan Density [Best Count:Actual Count]-扫描密度[最佳值:实际值]:DBCC SHOWCONTIG返回最有用的一个百分比。这是扩展盘区的最佳值和实际值的比率。该百分比应该尽可能靠近100%。低了则说明有外部碎片。
Logical Scan Fragmentation-逻辑扫描碎片:无序页的百分比。该百分比应该在0%到10%之间,高了则说明有外部碎片。
Extent Scan Fragmentation-扩展盘区扫描碎片:无序扩展盘区在扫描索引叶级页中所占的百分比。该百分比应该是0%,高了则说明有外部碎片。
Avg. Bytes Free per Page-每页上的平均可用字节数:所扫描的页上的平均可用字节数。越高说明有内部碎片,不过在你用这个数字决定是否有内部碎片之前,应该考虑fill factor(填充因子)。
Avg. Page Density (full)-平均页密度(完整):每页上的平均可用字节数的百分比的相反数。低的百分比说明有内部碎片
3.DBCC DBREINDEX 和 DBCC INDEXDEFRAG 命令常用来整理索引碎片。
DBCC DBREINDEX 和 DBCC INDEXDEFRAG 的区别(简单整理)
DBCC DBREINDEX
①用于在指定的表上重建一个或多个索引。
②DBCC DBREINDEX是离线操作方式。当该操作运行时,涉及到的表就无法被用户访问。
③DBCC DBREINDEX可以比DBCC INDEXDEFRAG执行得更快。
DBCC INDEXDEFRAG
①用于对指定的索引进行重建。
②DBCC INDEXDEFRAG是在线操作的;因此在整理索引碎片时,仍然可以访问被整理到的表和索引。
③当数据库完全恢复模式下,DBCC INDEXDEFRAG写入事务日志的数据量远远小于DBCC DBREINDEX。
使用说明:
DBCC DBREINDEX(table_name,index_name,fillfactor);
参数
'database.owner.table_name'
是要重建其指定的索引的表名。数据库、所有者和表名必须符合标识符的规则。有关更多信息,请参见使用标识符。如果提供 database 或 owner 部分,则必须使用单引号 (') 将整个 database.owner.table_name 括起来。如果只指定 table_name,则不需要单引号。
index_name
是要重建的索引名。索引名必须符合标识符的规则。如果未指定 index_name 或指定为 ' ',就要对表的所有索引进行重建。
fillfactor
是创建索引时每个索引页上要用于存储数据的空间百分比。fillfactor 替换起始填充因子以作为索引或任何其它重建的非聚集索引(因为已重建聚集索引)的新默认值。如果 fillfactor 为 0,DBCC DBREINDEX 在创建索引时将使用指定的起始 fillfactor。填充因子,即索引页的数据填充程度。如果是100,表示每一个索引页都全部填满,此时select效率最高,但以后要插入索引时,就得移动后面的所有页,效率很低。如果是0,表示使用先前的填充因子值。
DBCC INDEXDEFRAG(database_name, table_name, index_name)
database_name | database_id | 0
包含要进行碎片整理的索引的数据库。 如果指定 0,则使用当前数据库。
table_name | table_id | view_name | view_id
包含要进行碎片整理的索引的表或视图。
index_name | index_id
要进行碎片整理的索引的名称或 ID。 如果未指定,该语句将针对指定表或视图的所有索引进行碎片整理。
partition_number | 0
要进行碎片整理的索引的分区号。 如果未指定或指定 0,该语句将对指定索引的所有分区进行碎片整理。
实例:
DBCC DBREINDEX(TABLE,'',100);实际测试使用这个就可以加快检索速度。
DBCC INDEXDEFRAG(DB, TABLE, INDEX) 这个没有实际测试。
上面的SQL执行之后速度应该会变快,如果没有请确认是否需要追加索引。
下面的文章连接也有一些技术详细的说明:
https://www.cnblogs.com/bluedy1229/p/3227167.html
https://blog.csdn.net/leamonjxl/article/details/7086923
如果资源学习了,可以点赞支持!