SQL fragmentation affects database performance and is mainly divided into internal fragments and external fragments. The detection method is to use the sys.dm_db_index_physical_stats function. The processing method selects reorganization or reconstruction according to the fragmentation rate. It is recommended to maintain it regularly to avoid performance degradation.

The database runs for a long time and the query speed becomes slower. Many times, it is not because the hardware is not strong enough, but because SQL fragmentation is at work. Especially for users using SQL Server, index fragmentation is a common problem that affects performance. Simply put, if there are too many index fragments, it will be more difficult for the query optimizer to find data, and the efficiency will naturally decrease.

To solve this problem, the key is to identify the fragments first and then deal with them according to the situation.

What is SQL fragmentation?

SQL fragmentation mainly refers to the discontinuity of physical storage in database indexes, and is divided into two types:

• Internal Fragmentation : There is too much free space in the page, resulting in wasted storage.
• External Fragmentation : The physical and logical order of index pages on disk are inconsistent, which affects reading efficiency.

To give a simple example, when you organize your bookshelf, the originally neatly arranged books are constantly drawn out and inserted into new books, and finally become messy and find books slowly.

How to detect index fragmentation?

SQL Server provides a system function sys.dm_db_index_physical_stats , which can be used to view index fragmentation.

You can run a query like this:

 SELECT 
    OBJECT_NAME(object_id) AS TableName,
    index_id,
    avg_fragmentation_in_percent,
    avg_page_space_used_in_percent
FROM 
    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED')
WHERE 
    avg_fragmentation_in_percent > 10
    AND index_id > 0;

This query lists all indexes with fragmentation rates exceeding 10%. This threshold can be adjusted according to actual conditions, for example, for systems with high performance requirements, it can be set to 5%.

How to deal with fragmentation? Rebuild or reorganization?

There are two main ways to deal with fragmentation: Rebuild and Reorganize

• Rebuild : will completely rebuild the index structure, free up unused space, suitable for situations where fragmentation rate is high (for example > 30%)
• Reorganize : organize existing pages, compress free space, suitable for medium fragmentation rate (such as 10% - 30%)

Their comparison:

• Rebuilding locks tables may affect running queries
• Reorganization is milder, but the effect is relatively limited
• Rebuild can be used online in supported versions with ONLINE = ON parameter.

It is usually recommended to automatically select the processing method according to the fragmentation rate, such as:

• avg_fragmentation_in_percent
• 10% - 30%: Reorganization

• 30%: Rebuild

Maintenance strategy recommendations

In order to prevent fragmentation problems from occurring repeatedly, it is recommended to establish a regular maintenance plan:

• Check index fragmentation once a week
• Schedule rebuild or reorganization tasks according to the thresholds above
• For large tables that are frequently updated, you can consider maintaining them more frequently
• Use SQL Agent Jobs or third-party maintenance scripts, such as those of Ola Hallengren, to automate

In addition, pay attention to some points during maintenance:

• Avoid peak business execution
• Monitor execution time and resource usage
• Record logs to facilitate subsequent analysis and adjustment of strategies

Basically that's it. SQL fragmentation analysis and processing are not complicated, but are easily overlooked. Regular maintenance not only improves performance, but also extends the life of the database.

The above is the detailed content of SQL Fragmentation Analysis and Resolution. For more information, please follow other related articles on the PHP Chinese website!