SQL Server2005 Index Fragmentation analysis and workaround

SQL Server2005 Index Fragmentation analysis and workaround

The author of this article (Zheng), please respect the author's copyright when reading this article.

Summary: SQL Server, in order to react to updates to the data, needs to maintain indexes on the tables, and these indexes can become fragmented. Depending on the characteristics of the workload, these fragments can affect the corresponding operating performance. This article helps you decide whether you need to defragment to improve performance information. SQL serve provides some commands to defragment the index. Compare two of these commands here: DBCC DBREINDEX and DBCC INDEXDEFRAG.

Keywords: SQL Server; index fragmentation; database optimization Undoubtedly, it is advantageous to add indexes to tables, and most of the work you do is to maintain indexes, which can be fragmented during data changes, so some maintenance is necessary. Fragmentation can be a source of performance problems for your queries.

How do I determine if an index is fragmented?

SQL Server provides a database command: DBCC showcontig to determine whether a specified table or index is fragmented. Here's an example:

Perform DBCC SHOWCONTIG on the ' t_exam ' table, with the following results:

---scan pages .........: 20229........ £ º The number of .....

---Scan the extent of extents ............: 2543....... £ º

---the number of extents on the switch ......: 15328..... £ º......

---the average number of pages on each extent .....: 8.0..... £ º

---Scan Density (best value: Actual value) .....: 16.50%(2529:15329) (if less than 100, there is fragmentation.). 16.5% Description There are many fragments)

---logical scan fragments ....: 46.23% (if 0 is the best) ......... (If it is a good one)..

---extents scan fragments ........: 45.1%...... £ º-----

---the average number of bytes available on each page .......: 3240.1.....

---Avg. page density (full) ....: 59.97% (if 100% is the best), ........ ().

The results show that both the logical scan fragment and the extents scan fragment are very large and need to be processed for index fragmentation.

The DBCC DBREINDEX and DBCC INDEXDEFRAG commands are commonly used to defragment index fragments.

It is important to note that a very low fragmentation level (less than 5%) should not be addressed by these commands, since the proceeds from deleting such a small amount of fragmentation are always much lower than the cost of re-organizing or rebuilding the index.

1 DBCC Dbreindex

DBCC Dbreindex is used to physically reconstruct one or more indexes on a specified table. DBCC Dbreindex is a way to operate offline. When the operation is run, the tables involved cannot be accessed by the user.

DBCC Dbreindex dynamically rebuilds the index. It is not necessary to know what the table structure involved in rebuilding is, whether to use information such as primary key or uniqueness constraint, and automatically manage it when rebuilding. DBCC Dbreindex completely rebuilds the index, which is the process of removing fragmentation, reclaiming disk space by setting compressed pages with the specified or existing fill factor, and reordering index rows in successive pages (assigning new pages as needed). This can improve disk performance by reducing the number of page reads required to obtain the requested data. From the inside, DBCC Dbreindex and manually use T-SQL statements to run the delete and re-create the index very similar.

The following two points are a superior place for DBCC DBREINDEX than DBCC INDEXDEFRAG:

DBCC Dbreindex automatically rebuilds statistics during the rebuilding of the index, which will significantly improve the performance of the work.

DBCC Dbreindex can run in multiprocessor environments, leveraging the benefits of multiprocessor, which can be very fast when rebuilding large and fragmented indexes.

All the work of DBCC Dbreindex is a single, atomic transaction. You must complete creating a new index and replace the old index, and then the old index page is freed. There is enough free space in the data file to complete the rebuild. If there is not enough space available, DBCC Dbreindex either cannot rebuild the index, or it produces logical fragments greater than 0. The required free space depends on the situation, depending on the number of indexes to be created in the transaction.

2 DBCC Indexdefrag

DBCC Indexdefrag is used to rebuild the specified index. Similar to DBCC DBREINDEX, there is no need to take into account the table's infrastructure; however, DBCC Indexdefrag cannot rebuild all indexes with a single statement. The DBCC INDEXDEFRAG must be run once for each index that you want to defragment.

Both DBCC DBREINDEX and DBCC INDEXDEFRAG can effectively defragment index fragments and restore page density near the page density specified by the initial fill factor. Based on these results, you need to decide when to apply which sort of collation.

If you allow for a period of time to be reconstructed from the lead, DBCC DBREINDEX is generally faster than DBCC INDEXDEFRAG. DBCC Dbreindex can take advantage of the parallel performance of multiprocessor systems. DBCC Indexdefrag is used in situations where the production environment is less intrusive and has little impact on performance. Tests show that even though several DBCC INDEXDEFRAG work in parallel, the impact on performance degradation never exceeds 10%. However, this also makes DBCC INDEXDEFRAG a long time to complete for larger indexes. Also, the length of the working hours depends on the access work that was running on the server at that time.

3 Conclusion

For different types of work, index defragmentation has a very different effect. Some apps can get a lot of performance gains from defragmentation. Understanding application characteristics, system performance, and the fragmentation statistics provided by SQL Server are key to correctly determining when to defragment. SQL Server provides some commands to complete index defragmentation. In SQL Server 2005, DBCC DBREINDEX and DBCC INDEXDEFRAG have been used as two steps in the maintenance plan: Rebuilding indexes and re-organizing indexes facilitates database maintenance for database management. This article can help us decide when and how to defragment the index so that performance is best improved.

Source: http://blog.csdn.net/wxzyq/article/details/6821802