Rebuilding indexes improves SQL server performance

MajoritySQL ServerThe table needs to be indexed to speed up data access. If no index is available,SQL ServerYou must scan the entire table to read every record in the table to find the desired data. Indexes can be divided into cluster indexes and non-cluster indexes: Cluster indexes increase the data access speed by rearranging the data in the table; instead of the Cluster Index, the data access speed is improved by maintaining the data pointer in the table.

1. Index Architecture SQL Server 2005 In hard disk 8 KB The page stores 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 into a table with a cluster index. When the data page reaches 100% When there is no space on the page to insert a new record, a page appears, SQL Server Move 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.

2. DBCC showcontig Usage The following is an example. DBCC showcontig And DBCC redbindex . To ApplicationProgramMedium Of Employee Data Table As An Example , In SQL Server Of Query Analyzer Enter the following command:

Use database_name
Declare @ table_id int
Set @ table_id = object_id ('Employee ')
DBCC showcontig (@ table_id)

 

Output result:

DBCC showcontig scanning 'Employee' table...
Table: 'employe' (1195151303); index ID: 1, Database ID: 53
Table level scan completed MED.
-Pages scanned ......
-Extents scanned...
-Extent switches...
-Avg. pages per extent...
-Scan density [best count: actual count] ......: 92.00% []
-Logical scan fragmentation ......: 0.56%
-Extent scan fragmentation ..........: 12.50%
-Avg. bytes free per page ......: 552.3
-Avg. Page density (full) ......: 93.18%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

 

By analyzing these results, you can see whether the index of the table needs to be reconstructed. The meaning of each row is described as follows:

Information Description Pages scanned Long pages in a table or index Extents scanned Number of Long-zone pages in a table or index Extent switches DBCC Number of times a page is traversed from one region to another AVG. pages per extent Page number in the relevant area Scan density [best count: actual count] Best count Is the number of ideal region changes for continuous connections, Actual count Is the actual region change, Scan Density Is 100% Indicates that no chunks exist. Logical scan fragmentation Percentage of out-of-order pages scanned for index pages Extent scan fragmentation Number of areas that are not actually adjacent to or that contain all links on the Link AVG. bytes free per page Average number of free bytes on the scan page AVG. Page density (full) Average page density, indicating how many pages are full

 

We can see from the execution result of the above command, Best count Is 23 While Actual count Is 25. This indicates Orders The table has parts and the index needs to be reconstructed. The following code uses DBCC dbreindex To reconstruct the Cluster Index of the table. 3 . DBCC dbreindex Usage Rebuild one or more indexes of a table in the specified database. Syntax

DBCC dbreindex
(
['Database. Owner. table_name'
[, Index_name
[, Fillfactor]
]
]
)

 

Parameters 'Database. Owner. table_name' Is the name of the table whose specified index is to be rebuilt. The database, owner, and table names must comply with the identifier rules. For more information, see use identifiers. If Database Or Owner Must use single quotes (') Set the entire Database. Owner. table_name . If you only specify Table_name . Index_name Is the index name to be rebuilt. The index name must comply with the rules of the identifier. If not specified Index_name Or specify '' All indexes of the table must be rebuilt. Fillfactor Is the percentage of space on each index page to store data when an index is created. Fillfactor Replace the starting fill factor as the new default value for the index or any other re-built non-clustered index (because the clustered index has been rebuilt. If Fillfactor Is 0 , DBCC dbreindex The specified start point will be used during index creation. Fillfactor . In Query Analyzer Enter the following command: DBCC dbreindex ('database _ name. DBO. Employee ', '', 90) And then use DBCC showcontig View the results of the restructured index:

DBCC showcontig scanning 'Employee' table...
Table: 'employe' (1195151303); index ID: 1, Database ID: 53
Table level scan completed MED.
-Pages scanned ......
-Extents scanned...
-Extent switches...
-Avg. pages per extent...
-Scan density [best count: actual count] ......: 100.00% []
-Logical scan fragmentation ......: 0.00%
-Extent scan fragmentation ..........: 0.00%
-Avg. bytes free per page ......: 509.5
-Avg. Page density (full) ......: 93.70%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

 

We can see through the results Scan denity Is 100% .

Address: http://fly3118.blog.51cto.com/361030/83836