Sort index fragments to improve SQL Server speed

Database Table A has 100,000 records, and the query speed is normal. However, after importing one thousand data records, the problem occurs. When the selected data is between the original 100,000 records, the speed is still quite fast; but when the selected data is between the one thousand data records, the speed becomes very slow.

Based on experience, this is an index fragmentation problem. Check the index fragmentation dbcc showcontig (table) and obtain the following results:

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 scanned pages ..................................... 3127

-Scan the number of extended disk areas ......: 403

-Extended disk partition switch count ......................: 1615

-Average number of pages on each extended disk... 7.8

-Scan density [optimal value: actual value] ......: 24.20% [391: 1616]

-Logically scan fragments...

-Extended disk scan fragments...: 38.46%

-Average number of available bytes on each page ......: 2073.2

-Average page density (complete) ................: 74.39%

DBCC execution is complete. If DBCC outputs an error message, contact the system administrator.

From the above we can see that the scanning fragmentation of the logical scan fragmentation and the extended disk area is very large, and it is really necessary to process the index fragmentation.

There are two solutions: one is to use dbcc indexdefrag to sort out index fragmentation, and the other is to use dbcc dbreindex to reconstruct the index. Both have their own advantages and disadvantages. The original statement for calling Microsoft is as follows:

The dbcc indexdefrag command is an online operation, so the index is only available when the command is running. In addition, the operation can be interrupted without losing the completed work. The disadvantage of this method is that there is no clustered index removal/re-creation operation in reorganizing data.

Re-create a clustered index will re-organize the data. The result is that the data page is filled up. You can use the FILLFACTOR option to configure the fill level. The disadvantage of this method is that the index is offline during the deletion/re-creation period and the operation belongs to the atomic level. If the index creation is interrupted, the index will not be re-created.

That is to say, to achieve good results, you still need to re-build the index, so you have to re-build the index.

Dbcc dbreindex (table, index name, fill factor)

The first parameter can be the table name or table ID.

The second parameter, if it is '', affects all indexes of the table.

The third parameter is the fill factor, that is, the data fill degree on the index page. If it is 100, it means that every index page is filled up. In this case, select is the most efficient, but when you want to insert an index later, you have to move all the subsequent pages, which is very inefficient. If the value is 0, the previous fill factor value is used.

Dbcc dbreindex (A, '', 100)

Retest the query speed.

In general, the fill factor can use the default value.

Edit recommendations]