SQL Server fragmentation collection and maintenance

Fragmentation occurs throughout the entire process of making data modifications (INSERT, UPDATE, and DELETE statements) to the table and to the indexes defined in the table. Because these modifications are usually not evenly distributed across the table and index rows, the fill per page changes over time. For queries that scan some or all of the indexes on a table, this fragmentation causes additional page reads. Thus delaying the parallel scanning of the data.

You can learn about the fragmentation of the index by using the sys.dm_db_index_physical_stats function, which returns the size and fragmentation information for the data and indexes of the specified table or view. for indexes, a row is returned for each level of the B-tree in each partition. For a heap, the In_row_data allocation unit for each partition is returned with its corresponding row. For large object (LOB) data, the Lob_data allocation unit for each partition returns its corresponding row.

through sys.dm_db_index_physical_stats function gets more fragments than Ten the table and index information , The code is as follows:

SET Nocounton;

[Email protected] INT;

[Email protected]=db_id ()

selectobject_id, index_id, partition_number,avg_fragmentation_in_percent

Into#cc

Fromsys.dm_db_index_physical_stats (@dbid, NULL, NULL, NULL, ' LIMITED ')

Whereavg_fragmentation_in_percent > 10.0 and index_id > 0

selectt2.name,t2.schema_id,t1.* to #dd from #cc t1,sys.objects T2

wheret1.object_id=t2.object_id

T2.name

Selectt2.name+ '. ' +t1.name as TableName, t1.* into #ee

FROM#DD T1,sys.schemas T2

wheret1.schema_id=t2.schema_id

Select T1.tablename,t2.nameasindexname,t1.avg_fragmentation_in_percent,

T1.partition_number from #ee t1,sys.indexes T2

WHERET1.INDEX_ID=T2.INDEX_ID and t1.object_id=t2.object_id

T1.name

with the above code, The fragment level of the index or heap is displayed in the Avg_fragmentation_in_percent column, which represents the area fragmentation of the heap for the heap. For an index, this value represents the logical fragmentation of the index. For best performance, the value of avg_fragmentation_in_percent should be as close to 0 as possible. However, values from within the 0到10% range are acceptable. beyond this range, consider maintenance to improve SQL performance:

There are three ways to reduce fragmentation:

1. Delete and recreate the clustered index. Re-creating the clustered index will redistribute the data so that the data page fills up. The fill level can be configured using the FILLFACTOR option in the Create INDEX.

    

2. fragments between 10% and 30%, reorganize the index,

as : ALTER indexindexname on tablename reorganizewith (online=on)

3. Use the ALTER index rebuild online or offline to rebuild the index.

such as: ALTER indexindexname on tablename REBUILD with (FillFactor = all, online= on)

Note: 1. because the partition table does not support online reconstruction, only the partition with index fragmentation is rebuilt on the partitioned table, and the non-partitioned table is rebuilt on-line, and the fill factor is set to zero . partition_number>1 as a partitioned table

This article is from "What is our favorite" blog, please be sure to keep this source http://pxizhi.blog.51cto.com/5283742/1602447

SQL Server fragmentation collection and maintenance