SQL Server Rebuild Index

The index is rebuilt to reduce data fragmentation. Data fragmentation can cause SQL Server to make unnecessary data reads and reduce SQL Server performance. Rebuilding the index also updates column statistics, and if the columns used by the query are missing or missing statistics, this could cause the optimizer inside the SQL Server to choose a query plan that is less efficient than expected.

If you rebuild a clustered index on a table, the nonclustered indexes on that table are also updated.

To update the index, you can use the maintenance Wizard (you can refer to http://msdn.microsoft.com/en-us/library/ms180074.aspx) or the SQL The Server Agent (agent) runs the following custom code to update indexes on all tables in a database:

 UseDatabaseName--Enter The name of the database want to Reindex DECLARE @TableName varchar(255)  DECLARETablecursorCURSOR  for SELECTtable_name fromInformation_schema.tablesWHERETable_type= 'base Table'  OPENTablecursorFETCH NEXT  fromTablecursor into @TableName  while @ @FETCH_STATUS = 0 BEGIN DBCCDbreindex (@TableName,' ', -) FETCH NEXT  fromTablecursor into @TableName END  CLOSETablecursordeallocateTablecursor

You can modify the parameters of the Dbreindex according to your needs.

It is important to note that when you rebuild a nonclustered index, the table is temporarily coupled with a shared lock, which is not allowed for operations other than select, and when the clustered index is rebuilt, the table temporarily adds an exclusive lock and does not allow any user access. A plan is therefore needed to prevent possible access problems.

Rebuild has a fill factor parameter, and if the fill factor is set to 100%, this means that each index page is fully full, and if fill factor is set to 50% it means that each index page is half full. For fill factor 100%, each time a record is newly inserted or updated, a paging condition may occur because there is no space available for the current page. Excessive paging can degrade SQL Server performance. Here is a specific example:

Suppose you set up a new index on a table that uses the default fill factor. When SQL Server creates an index, it places the index on a contiguous physical page so that the data is read sequentially and I/O access is optimized. However, when the table is changed due to an increase in operations such as Insert,update,delete, the paging occurs, and SQL Server allocates new pages elsewhere on the disk, causing the new pages to be disjoint from the original physical pages, increasing the random I/O, and accessing the index pages slowly.

So what should be the appropriate value for fill factor? This depends on the read/write ratio of the table:

Low Update table (read/write ratio: 100:1): 100% fill factor

High Update table (write over Read): 50%-70% fill factor

Center: 80%-90% Fill Factor

Too low fill factor will increase the number of pages, which will also cause more pages to be moved to the cache, and the data that is useful in the cache is reduced. The default fill factor is 0 (that is, 100% fill factor), which is often not a good choice, especially for clustered indexes.

If you cannot determine what fill factor is set, you first need to determine the read/write ratio of the disk. The method is to use the following two counters:

Physical Disk object:% Disk Read time and Physical Disk object:% Write time. Another potentially useful counter is the SQL Server Access methods:pages splits/sec. This counter measures the number of paging per second in SQL Server. If the value is too high, you need to lower the fill factor to prevent new paging.

You can run the DBCC SHOWCONTIG command if you want to confirm how fragmented your index is due to pagination. If you look at a specific table and a specific index, you can run the following code:

The most important parameter in the result set is Scan Density, which is as close as 100% as possible. If scan density is less than 75%, you may need to rebuild the indexes in the table.

--Script to identify table fragmentation --Declare VariablesDECLARE @ID int, @IndexID int, @IndexName varchar( -)  --Set the table and index to is examinedSELECT @IndexName = 'index_name' --Enter name of indexSET @ID = object_id('table_name')--Enter name of table --Get the Index ValuesSELECT @IndexID =indid fromsysindexesWHEREId= @ID  andName= @IndexName  --Display The fragmentationDBCCShowcontig (@id,@IndexID)

For less than 100 data pages, rebuilding the index does not have a noticeable performance improvement. This is because of the physical hardware cache, the SQL Server cache, and the SQL Server read-ahead mechanism that hides the negative effects of fragmentation. For very large tables, however, rebuilding the index can be beneficial because it involves a lot of disk I/O operations.

SQL Server Rebuild Index