Index reconstruction improves query efficiency

SQL Server rebuild index can improve query speed

As the amount of data in the table grows, many stored data is not properly stored across pages (the smallest unit stored in SQL Server is pages, and pages cannot be divided ), it will produce a lot of index fragments. At this time, indexes need to be rebuilt to improve query performance.

 

SQL Server 2005 uses the 8 KB page on the hard disk to store data in the database file memory. By default, these pages and their data are unorganized. In order to change chaos to order, an index must be generated. After an index is generated, the index page and data page are divided into: data page is used to save the data information written by the user; index page is used to store the data value list (keyword) used to retrieve columns) and the address pointer of the record where the value is located in the index table. Indexes are divided into cluster indexes and non-cluster indexes. In essence, a cluster index sorts data in a table, just like a dictionary index directory. Non-clustered indexes do not sort data. They only store data addresses. Insert data to a table with a cluster index. When the data page reaches 100%, a page is displayed because there is no space to insert new records, SQL Server moves about half of the data from the full page to the empty page to generate two 1/2 full pages. In this way, there is a large amount of empty data space. The cluster index is a two-way linked list. The header of each page stores the address for removing data from the previous page, the next page, and the pages. Because the new page may be anywhere in the database file, the page link does not necessarily point to the next physical page of the disk. The link may point to another region, which forms a chunk and slows down the system. For tables with cluster indexes and non-cluster indexes, the keywords of non-cluster indexes point to the Cluster Index, rather than to the data page itself.

To overcome the negative impact of data blocks, you need to reconstruct the index of the table, which is time-consuming and can only be performed as needed. You can use DBCC showcontig to determine whether to reconstruct the index of the table.

DBCC showcontig (table_name)

Returned results:

DBCC showcontig is scanning the 'table _ name' table... table: 'table _ name' (128211707); index ID: 1, Database ID: 5 the table-level scan has been performed. -Number of scanned pages ................................: 564273-Number of scan zones ..............................: 70543-Number of Area switches ..............................: 70543-average page number for each partition ........................: 8.0-scan density [optimal count: actual count] ......: 99.99% [70535: 70544]-logical scan fragmentation ..................: 0.01%-scan fragments ..................: 2.52%-average number of available bytes per page ........................: 684.6-average page density (full ).....................: 91.54% DBCC execution is complete. If DBCC outputs an error message, contact the system administrator.

 

The optimal count is 70535, and the actual count is 70544. This indicates that the table has blocks and the index needs to be reconstructed. If the scan density is 100%, No blocks are displayed.

 

 

How to view the index usage:
Select index_type_desc, alloc_unit_type_desc, avg_fragmentation_in_percent, fragment_count, avg_fragment_size_in_pages, page_count, record_count, avg_page_space_used_in_percent
From SYS. dm_db_index_physical_stats (db_id ('db _ name'), object_id ('table _ name), null, null, 'sampled ')

 

The preceding statement queries the index usage of the table table_name of the database db_name.

 

If the avg_fragment_size_in_pages value of the column in the query result exceeds 40%, the index must be rebuilt.

ReconstructionIndex:

Alter Index name on table name rebuild

 

 

Index reconstruction improves query efficiency