About performance optimization and troubleshooting (15)-index maintenance-index fragmentation Identification

Indexing is already a problem that we often mention during performance optimization. Many indexing concepts and resolutions are part of our site's advanced index series.ArticleWe will not repeat it here because we have made a comprehensive introduction.

We all know that for indexes, we will face two problems:

1. Have you created an appropriate index?

2. How to maintain and diagnose existing Indexes

For the first question, this is really not just a few words to be clear, because this first requires a very clear understanding of the index knowledge, in addition, you need to know how to use the data in the created index table (such as the read/write frequency ). We will conduct in-depth analysis on the index in the "pay-as-you-go" section, and hope you will pay attention to it.

For the second problem, we must first understand that after the index is created, it is not once and for all, but requires constant maintenance.DatabaseThe data in is changed, so the index can be unsuitable or need to be processed, such as restructuring.

The following articles will discuss the following issues:

• How to find index fragmentation

• · Use fill factor

• · How to Use rebuild to Improve Index Efficiency

• · How to Use reorganize to Improve Index Efficiency

• · How to find missing Indexes

• How to find useless Indexes

• · How to locate high-cost index Maintenance

• · How to Use index view to improve performance

• · How to Use indexes on computing columns to improve performance

 

 

Fragment type description

Internal Fragment

Speaking of fragmentation, the topic here is actually very broad. Here we will mainly discuss the fragmentation of indexes. As for how fragments are generated, we will not go too far. For details, please refer to this article: http://www.agilesharp.com/showtopic-162.aspx. For indexes, fragments are divided into two types: External fragments and internal fragments. Here we will briefly introduce these two types of images:

 

 

We can see that many pages in the index structure are not filled, which may be intentionally specific, for example, we can specify the page fill factor during index reconstruction and reorganization, but in many cases, we do not realize this. Because we often think that the index page is filled, but with the addition, deletion, and modification of data, the index page contains the above internal fragments.

External fragments

The so-called external fragmentation is actually related to the underlying physical storage of the index structure. The index structure we see above is actually a logical view of the index, because the pages in the actual index structure are stored in physical storage as follows:

 

 

That is to say, the index data is stored in a bucket. However, this bucket also stores data for other structures, such as tables and other indexes. Therefore, for an index, for example, A, the address stored on the page it contains may not be consecutive, as shown in, the two storage locations marked with red inside are other objects. However, when the database reads data from the storage system, it reads the continuous space every time, instead of the skip reading. As shown above, it needs to read all the data of index, the data in the two marked red spaces in the bucket above will also be read, which means that the data we should not have read is read. In fact, we can fully understand this point based on the disk fragments we know at ordinary times.

 

 

Next let's take a look at how to find the fragments:

 

Search for index fragments

 

In fact, in the search process, we mainly rely on the dynamic management function SYS. dm_db_index_physical_stats.

 

Internal Fragment

 

Next, we will look up the internal fragment query. In fact, the principle is very simple, mainly to see the percentage of the page space used, and then add some experience values to filter, as shown below:

 

Exec Sp_configure ' Show advanced options ' , 1
Go
Reconfigure With Override
Go
Declare @ Defafilfillfactor Int
Declare @ Fillfactor Table (Name Varchar ( 100 ), Minimum Int , Maximum Int , Config_value Int , Run_value Int )
Insert Into @ Fillfactor Exec Sp_configure ' Fill Factor (%) '
Select @ Defafilfillfactor = Case When Run_value = 0 Then 100 Else Run_value End From @ Fillfactor

Select
Db_name () As Dbname,
Quotename (S. Name) As Cchemaname,
Quotename (O. Name) As Tablename,
I. Name As Indexname,
Stats. index_type_desc As Indextype,
Stats. page_count As [ Pagecount ] ,
Stats. partition_number As Partitionnumber,
Case When I. fill_factor > 0 Then I. fill_factor Else @ Defafilfillfactor End As [ Fill Factor ] ,
Stats. avg_page_space_used_in_percent,
Case When Stats. index_level = 0 Then ' Leaf level ' Else ' Nonleaf level ' End As Indexlevel
From
SYS. dm_db_index_physical_stats ( Db_id (), Null , Null , Null , ' Detailed ' ) As Stats,
SYS. Objects As O,
SYS. Schemas As S,
SYS. Indexes As IWhere
O. Object_id = Stats. Object_id And S. schema_id = O. schema_id And I. Object_id = Stats. Object_id And I. index_id = Stats. index_idand stats. avg_page_space_used_in_percent <= 85 And Stats. page_count > = 10 And Stats. index_id > 0 Order By Stats. avg_page_space_used_in_percent ASC , Stats. page_count Desc

 

I often add a lot of filters to the WHERE clause, because as I said before, it doesn't make much sense to read the original data. In the preceding query, we are only interested in indexes with at least 10 pages and page space usage less than 85%. The key fields in the preceding query are avg_page_space_used_in_percent and pagecount.

Generally, avg_page_space_used_in_percent is low because:

Due to page separation and deletion records: In this case, we must reconstruct or restructure the index. If the fragment is not in the leaf level, the reconstruction needs to reduce the fragment.

Because of the fill factor settings:Internal fragmentation. This is because an incorrect index setting of the fill factor value in the fill factor setting may cause internal fragmentation. We must re-create the index and select a new fill factor value.

Because of the record size: Some data records may split pages. For example, if the size of a record is 3000 bytes, an index page can accommodate only two records. The third record cannot be installed on a page, and the remaining available space on the page is less than 3000 bytes. In this case, each page will have 2060 bytes of blank space. To get rid of the shard size record, we may need to redesign the table or create a vertical partition table.

 

 

External fragments

 

exec sp_configure 'show advanced options ', 1
go
reconfigure with override
G
odeclare @ defaultfillfactor int
declare @ fillfactor table (name varchar (100), minimum int, maximum int, config_value int, run_value INT)
insert into @ fillfactor exec sp_configure 'fill factor (%) '
select @ defaultfillfactor = case when run_value = 0 Then 100 else run_value end from @ fillfactor

Select
Db_name () as dbname,
Quotename (S. Name) as cchemaname,
Quotename (O. Name) as tablename,
I. Name as indexname,
Stats. index_type_desc as indextype,
Stats. page_count as [pagecount],
Stats. partition_number as partitionnumber,
Case when I. fill_factor> 0 then I. fill_factor else @ defaultfillfactor end as [fill factor],
Stats. avg_fragmentation_in_percent, stats. fragment_count,
Case when stats. index_level = 0 then 'leaf level' else 'nonleaf level' end as indexlevel
From
SYS. dm_db_index_physical_stats (db_id (), null, 'limited') As stats,
SYS. Objects as O,
SYS. schemas as S,
SYS. indexes as iWhere
O. object_id = stats. object_id and S. schema_id = O. schema_id and I. object_id = stats. object_id and I. index_id = stats. index_idand stats. avg_fragmentation_in_percent> = 20 and stats. page_count & gt; = 1000 order by stats. avg_fragmentation_in_percent DESC, stats. page_count DESC

 

 

Generally, the reasons for high avg_fragmentation_in_percent are as follows:
 

The SQL Server storage engine allocates pages for a table or index in a hybrid zone until the data size of the table or index reaches 8 pages. once the number of pages reaches 8, the storage engine begins to allocate a unified data block (extent) for their subsequent data storage, and then place the data in the page of the data block. If there are many small data tables in the database, their pages will be placed in the hybrid block and added. There are 7 pages of a certain data table, these seven pages may be placed in different blocks. In other words, these pages may be completely discontinuous in the storage, which will lead to large fragments.
 

Other common reasons are the page splitting of DML operations, and the newly allocated pages and previous pages are not in the continuous storage space.

 

The following suggestions are provided for maintenance:

• The fragmentation rate is between 20% and 40%, and index fragmentation is reorganized.

• If the fragmentation rate is above 40%, consider re-Indexing

• If the number of pages in the index structure is less than 1000, it cannot be maintained temporarily.

• If the number of pages in an index structure exceeds 50 thousand, and the fragmentation rate is between 10% and 20%, reorganization is also considered.