SQL Server improves speed by organizing index fragmentation and rebuilding indexes

In SQL Server database operations, we can implement queries by index when there are more records in the database. However, when the index fragment is too much, it can seriously affect the speed of the query. There are two ways to solve this: one is to defragment the index , and the other is to rebuild the index . This article mainly introduces this process, then let us come together to understand it.

Check index fragmentation DBCC SHOWCONTIG (table) to get the following result:

2. DBCC Showcontig is scanning the ' A ' table ...
6. Table: ' A ' (884198200); index id:1, Database id:13
10. A TABLE-level scan has been performed.
14. -Number of pages .............: 3127......... £ º
18. -Scan the number of extents ...........: 403........ £ º
22. -The number of expansion panel switches ............: 1615...... £ º
26. -The average number of pages on each extent .......: 7.8.....
30. -Scan density [best value: Actual value] .....: 24.20%[391:1616......
34. -Logical scan fragments .............: 68.02%........ £ º
38. -Extended area Scan fragment ........: 38.46%.......... £ º
42. -The average number of bytes available on each page ........: 2073.2....
46. -Avg. page density (full) ............: 74.39%....
50. DBCC execution is complete.

As we can see, both the logical scan fragment and the extents scan fragment are very large, and sure enough, the index fragmentation needs to be processed.

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 speed, found that the speed is very fast.

The knowledge about SQL Server database to improve indexing speed by organizing index fragmentation and rebuilding indexes is introduced here, and I hope this presentation will be of some benefit to you.

SQL Server improves speed by organizing index fragmentation and rebuilding indexes