SQL Server re-indexing Rebuild Index

Index reconstruction aims to reduce data fragmentation. Data fragmentation can cause SQL Server to read unnecessary data and reduce SQL Server performance. Re-indexing also updates column statistics. If the Column Used for the query lacks or misses statistical information, the optimizer in SQL Server may choose a query plan that is less efficient than expected.

 

If you recreate a clustered index on a table, the non-clustered index on the table is also updated.

 

 

To update the index, you can use the Maintenance Wizard (for more information, see the http://msdn.microsoft.com/en-us/library/ms180074.aspx), or on the SQL Server Agent) run the following custom code to update the indexes of all tables in a database:

USE DatabaseName -- Enter the name of the database you want to reindex

 

DECLARE @ TableName varchar (255)

 

DECLARE TableCursor CURSOR

SELECT table_name FROM information_schema.tables

WHERE table_type = 'base table'

 

OPEN TableCursor

 

Fetch next from TableCursor INTO @ TableName

WHILE @ FETCH_STATUS = 0

BEGIN

Dbcc dbreindex (@ TableName, '', 90)

Fetch next from TableCursor INTO @ TableName

END

 

CLOSE TableCursor

 

DEALLOCATE TableCursor

 

You can modify the DBREINDEX parameters as needed.

It should be noted that the share lock will be temporarily applied to the table when the non-clustered index is rebuilt, and the user cannot perform operations other than SELECT. The exclusive lock will be temporarily applied to the table when the clustered index is rebuilt, access by any user is not allowed. Therefore, a plan should be prepared to prevent possible access problems.

 

REBUILD has a fill factor parameter. If fill factor is set to 100%, each index page is completely full. If fill factor is set to 50%, each index page is half full. For fill factor 100%, each time a new record is inserted or updated, the current page may be generated by page because there is no space available. Excessive paging reduces the performance of SQL Server. The following is an example:

Assume that you have created a new index using the default fill factor on a table. When SQL Server creates an index, it places the index on a consecutive physical page to optimize data read and I/O access. However, when the INSERT, UPDATE, DELETE, and other operations of a table increase and change, the page is displayed. SQL Server allocates new pages elsewhere on the disk, resulting in the inconsistency between the new page and the original physical page, with Random I/O added, access to the index page slows down.

 

What is the proper fill factor value? This depends on the read/write ratio of the table:

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

High update table (write exceeds read): 50%-70% fill factor

Center: 80%-90% fill factor

 

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

If you cannot determine what fill factor is set, you must first determine the disk read/write ratio by using the following two counters:

Physical Disk Object: % Disk Read Time and Physical Disk Object: % Write Time. Another useful counter is SQL Server Access Methods: Pages Splits/Sec. This counter measures the number of pages per second in SQL Server. If the value is too high, you need to reduce the fill factor to prevent new pages.

 

If you want to confirm the fragmentation of your index due to paging, you can run the dbcc showcontig command. If you want to view a specific table and a specific index, run the following code:

The most important parameter in the result set is Scan Density. The closer it is to 100%, the better. If the Scan Density is less than 75%, you may need to recreate the index in the table.

-- Script to identify table fragmentation

 

-- Declare variables

DECLARE

@ ID int,

@ IndexID int,

@ IndexName varchar (128)

 

-- Set the table and index to be examined

SELECT @ IndexName = 'index _ name' -- enter name of index

SET @ ID = OBJECT_ID ('table _ name') -- enter name of table

 

-- Get the Index Values

SELECT @ IndexID = IndID

FROM sysindexes

WHERE id = @ id and name = @ IndexName

 

-- Display the fragmentation

Dbcc showcontig (@ id, @ IndexID)

 

Rebuilding indexes for Data Pages smaller than 100 does not significantly improve performance. This is because the physical hardware cache, SQL Server cache, and SQL Server pre-read mechanism hide the negative effects of fragmentation. However, for a very large table, re-indexing will greatly benefit it because it involves a large number of disk I/O operations.