Re-indexing to improve SQL server performance

Most SQL Server tables require indexes to speed up data access. If no indexes are available, SQL Server performs a table scan to read each record in the table to locate 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.

Most SQL Server tables require indexes to speed up data access. If no indexes are available, SQL Server performs a table scan to read each 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.

Most SQL Server tables requireIndexComeImproveData access speed, if notIndex, SQL Server needs to scan the table to read every record in the table to find the requested data.IndexCan be divided into clustersIndexNon-ClusterIndex, ClusterIndexBy rearranging the data in the tableImproveData access speed, rather than the ClusterIndexMaintain the data pointer in the table.ImproveDataIndex.

1.IndexArchitecture

Why constantly maintain tables?Index? First, let's briefly introduceIndexArchitecture. SQL Server 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 make chaos orderly, it is necessary to generateIndex. GenerateIndexAnd then there will beIndexPage and data page. The data page stores the data information written by the user.IndexPage stores the list of data values (keywords) andIndexThe address pointer of the record where the value is located in the table.IndexDivided into clustersIndexNon-ClusterIndex, ClusterIndexSort the data in a table in a dictionary.IndexDirectory. Non-ClusterIndexIf the data is not sorted, only the pointer address of the data is saved. To a clusterIndexWhen the data page reaches 100%, because there is no space on the page to insert a new record, a page is displayed, SQL Server moves about half of the data from the full page to the empty page to generate two and a half full pages. In this way, there is a large amount of data space. ClusterIndexA two-way linked list stores the address of the previous and next pages and the address of data movement after pages 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 and slows down the system speed. For ClustersIndexNon-ClusterIndexNon-ClusterIndexIs directed to the cluster.IndexInstead of pointing to the data page itself.

To overcome the negative impact of data blocks, you need to reconstructIndex, This is very time-consuming, so it can only be done as needed. You can use dbcc showcontig to determine whether to refactor the table.Index.

2. dbcc showcontig usage

The following example describes how to use dbcc showcontig and dbcc redbindex. Take the Employee data table in the application as an example. In SQL Server's 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 knowIndexWhether reconstruction is required. The meaning of each row is described as follows:

Description

Pages Scanned table orIndexLong pages in

Extents Scanned table orIndexNumber of Long-zone pages in

Number of times that Extent Switches DBCC traverses a page from one region to another

Avg. Pages per ExtentRelatedNumber of pages in the region

Scan Density [Best Count: Actual Count]

Best Count is the number of ideal region changes when the link is continuous, Actual Count is the number of Actual region changes, and Scan Density is 100%, indicating that there are no blocks.

Logical Scan Fragmentation ScanIndexPercentage of out-of-order pages in the page

Extent Scan Fragmentation number of areas that are not actually adjacent to or contain all link pages in the Link

Avg. Bytes Free per Page scan Page Average Free Bytes

Avg. Page Density (full) Average Page Density, indicating how full the Page is

From the execution result of the preceding command, we can see that the Best count is 23 and the Actual Count is 25, which indicates that the orders table needs to be reconstructed in parts.Index. The following uses dbcc dbreindex to reconstruct the table's clusters.Index.

3. dbcc dbreindex usage

ReconstructionOne or more tables in the databaseIndex.

Syntax

DBCC DBREINDEX

(['Database. owner. table_name'

[, Index_name

[, Fillfactor]

]

]

)

Parameters

'Database. owner. table_name'

YesReconstructionTheIndex. The database, owner, and table names must comply with the identifier rules. For more information, see use identifiers. If the database or owner part is provided, you must use single quotation marks (') to enclose the entire database. owner. table_name. If only table_name is specified, no single quotation marks are required.

Index_name

YesReconstructionOfIndexName.IndexThe name must comply with the rules of the identifier. If index_name or ''is not specified, allIndexProceedReconstruction.

Fillfactor

YesIndexEveryIndexPage. Replace the starting fill factor with fillfactorIndexOr any otherReconstructionNon-clusteringIndex(BecauseReconstructionAggregationIndex. If fillfactor is 0, dbcc dbreindex is createdIndexThe specified starting fillfactor is used.

Similarly, enter the command in Query Analyzer:

Dbcc dbreindex ('database _ name. dbo. Employee ', '', 90)

Then use dbcc showcontig to view the reconstructionIndexResult:

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 that Scan Denity is 100%.

Original article: HowImproveSQL SERVERPerformance"

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