SQL Server 2000 index Reconstruction Method

I. Necessity:

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.

When creating an index in a database, the index information used for the query is stored on the index page. Consecutive index pages are linked by pointers from one page to the next page. When changes to data affect the index, the information in the index may be dispersed in the database. Re-indexing can re-organize index data (for clustered indexes, including table data
)
To clear fragments. This improves disk performance by reducing the number of page reads required to obtain request data.

 

II,

 

When do I need to reconstruct the Index? How do I detect it?

 

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.

DBCC showcontig
(
'
Table Name
'
)
;

 

Take a test table as an example to output the results;

-Pages scanned .................................... 197214 -Extents scanned... 　　 -Extent switches DBCC ...... 　　 -Avg. pages per exten ..........: 8.0 　　 -Scan densitybest coun ......: 99.97% [24652: 24659] 　　 -Extent scan fragmentation ...... ................: 15.46% 　　 -Avg. bytes free per page ......: 374.6 　　 -Avg. Page density (full) ......: 95.37%

 

By analyzing these results, you can see whether the index of the table needs to be reconstructed. The meanings of each row are described below

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 densitybest count
Is the ideal area for continuous connections

[Best count: actual count]
Number of Domain changes,
Actual count
Is the number of changes in the actual region,
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
3
While
Actual count
Is
5
This indicates
Orders
You need to reconstruct the index of a table that has parts.

Iii. Index Reconstruction Method

<1>
Refactor a single table,

DBCC dbreindex
Rebuild one or more indexes of a table in the specified database.



Syntax

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

 

See syntax
SQL Server
Help.

 

For example, refactor a table and execute
DBCC dbreindex
(
'
Table Name
'
)

 

To
Northwind
Library
Orders
For example,

 
DBCC dbreindex ('northwind. DBO. Orders ')

 
DBCC showcontig ('northwind. DBO. Orders '),
The result is as follows,

DBCC showcontig scanning 'Orders 'table... 　　 Table: 'Orders '(21575115); index ID: 1 , Database ID: 6 　　 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 ..........: 33.33% 　　 -Avg. bytes free per page ......: 869.2 　　 -Avg. Page density (full) ......: 89.26%

We can see through the results
Scan denity
Is
100%
You do not need to reconstruct the table index if the table has no partitions.

 

<2>
Refactor all tables in a database,

Use
Database Name

Sp_msforeachtable 'dbcc dbreindex ("? ")'

Sp_msforeachtable
Execute a command for each table in the database, which is equivalent to a loop. After execution, indexes of all tables are reconstructed;

 

<3>
Overall Reconstruction
DBCC checkdb
;

DBCC checkdb
(
'Pubs', repair_rebuild
)

 

DBCC checkdb
It not only repairs indexes, but also checks the allocation and structural integrity of all objects in the specified database.

 

For each table in the database,
DBCC checkdb
Check it:

1.
The index and data page are correctly linked.

2.
Whether the index is arranged in the correct order.

3.
Whether the pointers are consistent.

4.
Whether the data on each page is reasonable.

5.
Whether the page offset is reasonable.

The command combination is as follows:

<1>
Set the database to single-user mode;

Sp_dboption

Database Name
, Single, true

<2>
Refactor the entire database;

 
DBCC checkdb ('
Database Name
', Repair_rebuild)

 

Note: To refactor a single table,

DBCC checktable (authors, repair_rebuild)

<3>
Set the database to multi-user mode;

Sp_dboption ZRB, single, false

 

Note:

DBCC checkdb
Is occupying a large amount
CPU
And disk operations. Each data page to be checked must first be read into the memory from the disk. In addition,
DBCC checkdb
Use
Tempdb
Sort.

If
DBCC checkdb
When the transaction is executed dynamically, the transaction log continues to grow because
DBCC
Command to block log truncation before reading the log.

We recommend that you run it when the server load is low.
DBCC checkdb
. If it runs during peak load
DBCC checkdb
, Then the transaction throughput performance and
DBCC checkdb
The completion time performance will be affected.

Iv. timed reconstruction,

If the database is frequently accessed, data blocks are very likely to occur. Therefore, you can use jobs to reconstruct indexes when the system is relatively idle.