DBCC dbreindex reconstruction index improves SQL server performance

Majority SQL Server The table needs to be indexed to speed up data access. If no index is available, SQL Server You need to scan the table to read every record in the table to find the requested 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, while non-cluster indexes increase the data index by maintaining the data pointer in the table.

1.Index Architecture

Why should we constantly maintain table indexes? First, we will briefly introduce the index architecture.SQL ServerIn hard disk8 KBThe 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 available. The data page stores the data information written by the user. The index page stores the data value list (keyword) used to retrieve the column 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 the pointer address of the data. Insert data into a table with a cluster index. When the data page reaches100%When there is no space on the page to insert a new record, a page appears,SQL ServerMove about half of the data from the full page to the blank page to generate two half full pages. In this way, there is a large amount of data space. The cluster index is a two-way linked list. The address of the previous and next pages and the address for moving data after pages are saved in the header of each page. Because the new page may be anywhere in the database file, therefore, the page link does not necessarily point to the next physical page of the disk. The link may point to another area, which forms a block, which slows down the system speed. 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 useDBCC showcontigTo determine whether to reconstruct the index of the table.

2.DBCC showcontigUsage

The following is an example.DBCC showcontigAndDBCC redbindex. ToApplicationProgramMediumOfEmployeeDataTableAs An Example, InSQL ServerOfQuery AnalyzerEnter 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:

InformationDescription

Pages scannedLong pages in a table or index

Extents scannedNumber of Long-zone pages in a table or index

Extent switches DBCCNumber of times a page is traversed from one region to another

AVG. pages per extentPage number in the relevant area

Scan density [best count: actual count]

Best countIs the number of ideal region changes for continuous connections,Actual countIs the number of changes in the actual region,Scan DensityIs100%Indicates that no chunks exist.

Logical scan fragmentationPercentage of out-of-order pages scanned for index pages

Extent scan fragmentationNumber of areas that are not actually adjacent to or that contain all links on the Link

AVG. bytes free per pageAverage 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 countIs23WhileActual countIs25This indicatesOrdersYou need to reconstruct the index of a table that has parts. The following code usesDBCC dbreindexTo reconstruct the Cluster Index of the table.

3. DBCC dbreindexUsage

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.IfDatabaseOrOwnerMust use single quotes(')Set the entireDatabase. Owner. table_name. If you only specifyTable_name.

Index_name

Is the index name to be rebuilt.The index name must comply with the rules of the identifier.If not specifiedIndex_nameOr 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.FillfactorReplace 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. IfFillfactorIs0,DBCC dbreindexThe specified start point will be used during index creation.Fillfactor.

InQuery AnalyzerEnter the following command:

DBCC dbreindex ('database _ name. DBO. Employee ', '', 90)

And then useDBCC showcontigView 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 resultsScan denityIs100%.

******

Original article: how to improveSQL ServerPerformance

Http://www.csdn.com.cn/database/1142.htm