DBCC dbreindex Rebuilding indexes improves SQL Server performance

Most SQL Server tables require an index to increase the speed of data access, and if there is no index, SQL Server will be able to scan every record in the table to find the requested data. Indexes can be divided into clustered and non-clustered indexes, and the cluster index improves data access speed by re-scheduling data in tables, while non-clustered indexes improve data indexing by maintaining data pointers in tables.

1. Architecture of the index

Why keep the index of the table constantly maintained? First, briefly describe the architecture of the index. SQL Server stores data in a database file with 8KB pages in the hard disk. By default, these pages and the data they contain are non-organized. In order for chaos to become orderly, an index will be generated. After the index is built, the index page and data page are available, and the data page holds the data information written by the user. The index page holds the list of data values (keywords) used to retrieve the column and the address pointer of the record in the index table for that value. Indexes are clustered and non-clustered, and the clustered index essentially sorts the data in the table, as if it were the index directory of the dictionary. A non-clustered index does not sort data, it only holds the pointer address of the data. Inserting data into a table with a clustered index, when the data page reaches 100%, because the page does not have space to insert a new record, paging occurs, and SQL Server moves about half of the data from the full page to the empty page, resulting in a two-half full page. This will have a lot of data space. A clustered index is a doubly linked list that stores the previous page, the next page address, and the address of the data movement after paging, because the new page may be anywhere in the database file, so the link to the page does not necessarily point to the next physical page of the disk, and the link may point to another area, which forms a block. This slows down the speed of the system. For tables with clustered and non-clustered indexes, the keywords for non-clustered indexes point to the cluster index instead of to the data page itself.

To overcome the negative impact of data chunking, it is time-consuming to refactor the table's index, so it can only be done when needed. DBCC SHOWCONTIG can be used to determine whether the index of the table needs to be refactored.

2. DBCC showcontig usage

The following are examples of how DBCC SHOWCONTIG and DBCC REDBINDEX are used. Take the Employee data table in the application as an example, in SQL Server Query Analyzer input 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: ' Employee ' (1195151303); Index id:1, Database id:53

TABLE level scan performed.

-Pages scanned ..............: 179........

-extents scanned .......: 24.............

-Extent switches .......: 24.............

-Avg. Pages per Extent ...............: 7.5

-Scan Density [best count:actual Count] ....: 92% [23:25]

-Logical Scan fragmentation.......: 0.56%.

-Extent Scan fragmentation........: 12.5%.

-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 know whether the table's indexes need to be refactored. The meanings of each line are described as follows:

Information description

Pages scanned long pages in a table or index

Extents scanned the number of long extents in a table or index

Extent switches the number of times that DBCC traverses a page from one region to another

Avg. Pages per Extent number of pages in the relevant area

Scan density[best count:actual Count]

Best count is the number of ideal region changes for consecutive links, Actual count is the number of actual region changes, and Scan density 100% indicates no chunking.

Logical Scan Fragmentation scans the percentage of pages in an index page

Extent Scan Fragmentation The number of regions that are not physically contiguous and contain all linked pages in the link

Avg. Bytes Free bytes per page scan

Avg. page Density (full) Average page density, which means that the page has more than

From the execution results of the above command can be seen, the best count is 23 and actual count is 25 this indicates that the Orders table has a chunked need to refactor the table index. The cluster index of the table is refactored below through DBCC DBREINDEX.

3 . DBCC dbreindex usage

Rebuilds one or more indexes of a table in the specified database.

Grammar

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. Database, owner, and table names must conform to the rules for identifiers. For more information, see Using Identifiers. If the database or owner portion is provided, the entire database.owner.table_name must be enclosed in single quotation marks ('). If you specify only table_name, you do not need single quotes.

Index_name

is the name of the index to rebuild. Index names must conform to the rules for identifiers. If index_name is not specified or specified as ', rebuild all indexes on the table.

FillFactor

is the percentage of space on each index page that will be used to store data when the index is created. FILLFACTOR replaces the starting fill factor as the new default value for the index or any other rebuilt nonclustered index (because the clustered index has been rebuilt). If FILLFACTOR is 0,DBCC dbreindex The specified start fillfactor will be used when the index is created.

Also enter the command in Query Analyzer:

DBCC DBREINDEX (' database_name.dbo.Employee ', ', 90)

Then use DBCC SHOWCONTIG to view the results of the refactoring index:

DBCC showcontig scanning ' Employee ' table ...

Table: ' Employee ' (1195151303); Index id:1, Database id:53

TABLE level scan performed.

-Pages scanned ..............: 178........

-extents scanned .......: 23.............

-Extent switches .......: 22.............

-Avg. Pages per Extent ...............: 7.7

-Scan Density [best count:actual Count] ....: 100% [23:23]

-Logical Scan fragmentation.......: 0%.

-Extent Scan fragmentation........: 0%.

-AVG. Bytes free per Page ............: 509.5

-Avg. Page Density (Full) ............: 93.7%

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Through the results we can see that scan denity is 100%.

******

The original link: How to improve the performance of SQL Server

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

Unknown

database table A has 100,000 records, query speed could have been, but after importing 1000 data, the problem arises. When the selected data between the original 100,000 records, the speed is still very fast, but when the selected data between the 1000 data, the speed becomes very slow.

With experience, this is an index fragmentation issue. Check index fragmentation DBCC SHOWCONTIG (table) to get the following result:

DBCC Showcontig is scanning the ' A ' table ...
Table: ' A ' (884198200); index id:1, Database id:13
A TABLE-level scan has been performed.
-Number of pages .............: 3127......... £ º
-Scan the number of extents ...........: 403........ £ º
-The number of expansion panel switches ............: 1615...... £ º
-The average number of pages on each extent .......: 7.8.....
-Scan density [best value: Actual value] .....: 24.20%[391:1616......
-Logical scan fragments .............: 68.02%........ £ º
-Extended area Scan fragment ........: 38.46%.......... £ º
-The average number of bytes available on each page ........: 2073.2....
-Avg. page density (full) ............: 74.39%....
DBCC execution is complete. If DBCC outputs an error message, contact your system administrator.

As we can see, both the logical scan fragment and the extents scan fragment are very large, and you really need to process the index fragmentation.

There are generally two ways to solve this, one is to use DBCC INDEXDEFRAG to organize index fragmentation, and the other is to use DBCC DBREINDEX to rebuild the index. Each has its advantages and disadvantages. The following words are called Microsoft:
The DBCC indexdefrag command is an online operation, so the index is available only when the command is running. You can also interrupt the operation without losing the completed work. The disadvantage of this approach is that the drop/re-create operation without a clustered index is valid in terms of data re-organization.

Re-creating the clustered index will reorganize the data, with the result that the data page fills up. The fill level can be configured using the FILLFACTOR option. The disadvantage of this approach is that the index is offline in the drop/recreate cycle, and the operation is atomic level. If the index creation is interrupted, the index is not recreated.

In other words, to achieve good results, you still have to rebuild the index, so you decide to rebuild the index.
DBCC dbreindex (table, index name, fill factor)
The first parameter, either a table name or a table ID.
The second parameter, if "," represents all indexes that affect the table.
The third parameter, the fill factor, is the extent to which the index page's data is populated. If it is 100, it means that every index page fills up and the select is the most efficient, but when you want to insert The index later, you have to move all the pages behind it, which is inefficient. If 0, indicates that the previous fill factor value is used.

DBCC Dbreindex (A, "', 100)
Re-test the query speed, fast.

DBCC Showcontig is the fragmentation information that displays data and indexes for the specified table.

The explanations are as follows:

Page scanned-Scan pages: If you know the approximate size of a row and the number of rows in a table or index, you can estimate how many pages are in the index. Look at the number of pages scanned, if it is significantly higher than the number of pages you estimate, there is internal fragmentation.

Extents scanned-scan extents: Divide the number of scanned pages by 8, rounding to the next highest value. The value should match the number of scan extents returned by DBCC SHOWCONTIG. If DBCC SHOWCONTIG returns a high number, it indicates that there is an external fragment. The severity of the fragment depends on how much higher the value you just showed than the estimated value.

Extent switches-Expansion Switch Number: This number should be equal to the number of scan extents minus 1. High, the external fragments are indicated.

Avg. pages per extent-average pages on each extents: this is the number of scanned pages divided by the number of extents scanned, typically 8. Less than 8 indicates an external fragment.

Scan Density [best count:actual count]-scanning density [optimal value: Actual value]:DBCC Showcontig returns the most useful percentage. This is the ratio of the best and actual values of the extents. The percentage should be as close to 100% as possible. Low, it means that there are external fragments.

Logical Scan fragmentation-Logical Scan Fragmentation: Percentage of unordered pages. The percentage should be between 0% and 10%, and high will indicate an external fragment.

Extent Scan fragmentation-Extents scanning fragment: the percentage of the unordered extents in the scan index leaf level page. The percentage should be 0%, and high will indicate an external fragment.

Avg. Bytes free per page-The average number of bytes available on each page: the average number of bytes available on the scanned page. The higher is the internal fragment, but you should consider fill factor (fill factor) before you use this number to determine if there are internal fragments.

Avg. page Density (full)-average page density (complete): the inverse of the average percentage of bytes available on each page. A low percentage indicates internal fragmentation.

DBCC dbreindex Rebuilding indexes improves SQL Server performance