[Distinct] DBCC dbreindex reconstruction index improves SQL server performance

From:Http://blog.sina.com.cn/s/blog_4d4290b20100bg27.html

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, 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 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 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 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 and a 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 and 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 use DBCC showcontig to determine whether to reconstruct the index of the table.   2. DBCC showcontigUsage The following example describes how to use DBCC showcontig and DBCC redbindex. By applicationProgramAs an example, run the following command in SQL Server Query Analyzer: 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                 Number of times that DBCC traverses a page 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 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  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   From the execution result of the above command, we can see that the best count is 23 and the actual count is 25, which indicates that the orders table has a partition and needs to be restructured. The following uses DBCC dbreindex to reconstruct the table's cluster index.

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 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

Is the index name to be rebuilt. The index name must comply with the rules of the identifier. If index_name or ''is not specified, all indexes of the table are rebuilt.

 

Fillfactor

Is the percentage of space on each index page to store data when an index is created. Fillfactor replaces the starting fill factor as the new default value for the index or any other rebuilt non-clustered index (because the clustered index has been rebuilt. If fillfactor is 0, DBCC dbreindex uses the specified starting fillfactor when creating an index.

 

Similarly, enter the command in query Analyzer:

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

Then, you can use DBCC showcontig to 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 that scan denity is 100%.

 

 

Exam:

Http://blog.sina.com.cn/s/blog_4d4290b20100bg1o.html