In this article, we will learn how to identify and resolve Index Fragmentation in SQL Server. Index fragmentation identification and index maintenance are important parts of the database maintenance task. Microsoft SQL Server keeps updating the index statistics with the Insert, Update or Delete activity over the table. The index fragmentation is the index performance value in percentage, which can be fetched by SQL Server DMV. According to the index performance value, users can take the indexes in maintenance by revising the fragmentation percentage with the help of Rebuild or Reorganize operation.
在本文中，我们将学习如何在SQL Server中识别和解决索引碎片。 索引碎片识别和索引维护是数据库维护任务的重要部分。 Microsoft SQL Server通过对表进行“插入”，“更新”或“删除”活动来不断更新索引统计信息。 索引碎片是索引性能值（以百分比为单位），可以由SQL Server DMV获取。 根据索引性能值，用户可以借助Rebuild或Reorganize操作修改碎片百分比来维护索引。
为什么索引碎片百分比会变化？ (Why the Index Fragmentation percentage varies?)
Index Fragmentation percentage varies when the logical page orders don’t coordinate with the physical page order in the page allocation of an index. With the data modification in the table, information can be resized on the data page. The page was top full before the update operation on the table. However, free space could be found on the data page with an update operation on the table. Users can observe the disturbing page order with the massive delete operation on the table. Along with the update and delete operations, the data page won’t be a top-full or empty page. Therefore, non-utilized free space raises the order mismatch between logical page and physical page with increasing the fragmentation, and that can cause worst query performance and consumes more server resources as well.
当逻辑页面顺序与索引的页面分配中的物理页面顺序不协调时，索引碎片百分比会有所不同。 通过修改表中的数据，可以在数据页面上调整信息的大小。 在对表进行更新操作之前，该页面已满。 但是，可以通过在表上执行更新操作在数据页上找到可用空间。 用户可以通过对表进行大规模删除操作来观察令人不安的页面顺序。 连同更新和删除操作一样，数据页面将不会是最高页面或空白页面。 因此，未利用的可用空间随着碎片的增加而增加了逻辑页面和物理页面之间的顺序不匹配，这可能导致最差的查询性能并消耗更多的服务器资源。
More essential to spell out that the index fragmentation affects query performance only with the page scan. In such cases, it raises the chances of the poor performance of another SQL requests as well, because query with the high fragmented index over the table takes more time to execute and consumes more resources such as Cache, CPU, and IO. Therefore, the rest of the SQL requests find it difficult to finish the operation with the inconsistent server resources. Even blocking can occur by the Update and Delete operation because the optimizer won’t gather the information of the index fragmentation while generating the execution plan for the query.
更重要的是要说明索引碎片只会影响页面扫描的查询性能。 在这种情况下，这也增加了另一个SQL请求的性能差的可能性，因为在表上具有高碎片索引的查询会花费更多的时间执行并消耗更多的资源，例如缓存，CPU和IO。 因此，其余SQL请求发现很难在服务器资源不一致的情况下完成操作。 更新和删除操作甚至可能发生阻塞，因为优化器在生成查询的执行计划时不会收集索引碎片的信息。
There can be a number of indexes created on a single table with the combination of various columns, and each index can have a different fragmentation percentage. Now, before making it appropriate or taking an index in maintenance, users have to find that threshold value from the database. The below T-SQL statement is an efficient way to find it with object details.
在单个表上可以创建具有多个列组合的多个索引，并且每个索引可以具有不同的碎片百分比。 现在，在使其适合使用或进行维护索引之前，用户必须从数据库中找到该阈值。 下面的T-SQL语句是使用对象详细信息查找它的有效方法。
使用T-SQL语句查找索引碎片状态 (Find Index Fragmentation status using the T-SQL statement)
SELECT S.name as 'Schema',
T.name as 'Table',
I.name as 'Index',
DDIPS.avg_fragmentation_in_percent,
DDIPS.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS DDIPS
INNER JOIN sys.tables T on T.object_id = DDIPS.object_id
INNER JOIN sys.schemas S on T.schema_id = S.schema_id
INNER JOIN sys.indexes I ON I.object_id = DDIPS.object_id
AND DDIPS.index_id = I.index_id
WHERE DDIPS.database_id = DB_ID()
and I.name is not null
AND DDIPS.avg_fragmentation_in_percent > 0
ORDER BY DDIPS.avg_fragmentation_in_percent desc
Here, we can see that the maximum average fragmentation percentage is noticeable as 99%, which must be engaged with an action to reduce the fragmentation with the choices of either REBUILD or REORGANIZE. REBUILD or REORGANIZE is the index maintenance command that can be executed with the ALTER INDEX statement. Users can perform this command using SSMS as well.
在这里，我们可以看到最大平均碎片百分比为99％，这必须通过选择REBUILD或REORGANIZE来采取行动来减少碎片。 REBUILD或REORGANIZE是可以与ALTER INDEX语句一起执行的索引维护命令。 用户也可以使用SSMS执行此命令。
使用SQL Server Management Studio（SSMS）重建和重新组织索引 (Rebuild and Reorganize Index using SQL Server Management Studio (SSMS))
Find and expand the table in Object Explorer >> Open Indexes >> Right-click on the target index >> Rebuild or Reorganize.
在对象资源管理器>>打开索引>>右键单击目标索引>>重建或重新组织，找到并展开表格。
As visible in the above image, REBUILD and REORGANIZE are the two available choices to play out the trim operation over the page. Ideally, this operation ought to be done in the off-peak time to avoid its impact on other transactions and users. Microsoft SQL Server Enterprise Edition supports index online and offline features with index REBUILD.
如上图所示，REBUILD和REORGANIZE是在页面上进行修剪操作的两个可用选项。 理想情况下，此操作应在非高峰时间进行，以避免对其他交易和用户造成影响。 Microsoft SQL Server企业版支持带有索引REBUILD的索引联机和脱机功能。
重建指数 (REBUILD INDEX)
INDEX REBUILD always drops the index and reproduce it with new index pages. This activity can be run in parallel using an online option (Enterprise Edition) with the ALTER INDEX command, which does not affect the running requests and tasks of a similar table.
INDEX REBUILD始终会删除索引，并使用新的索引页对其进行重现。 可以使用带有ALTER INDEX命令的联机选项（企业版）并行运行此活动，这不会影响相似表的运行请求和任务。
REBUILD Index can be set online or offline using the below SQL commands:
可以使用以下SQL命令在线或离线设置REBUILD Index：
--Basic Rebuild Command
ALTER INDEX Index_Name ON Table_Name REBUILD
--REBUILD Index with ONLINE OPTION
ALTER INDEX Index_Name ON Table_Name REBUILD WITH(ONLINE=ON) | WITH(ONLINE=ON)
If a user performs the REBUILD INDEX offline, then the object resource (Table) of the index won’t be accessible till the end of REBUILD process completion. It affects numerous other transactions as well, which are associated with this object. Rebuild index operation recreates the index. Therefore, it generates new statistics and appends the log records of the index in the database transaction log file too.
如果用户脱机执行REBUILD INDEX，则在REBUILD进程完成之前，将无法访问索引的对象资源（表）。 它也会影响与此对象关联的许多其他事务。 重建索引操作将重新创建索引。 因此，它会生成新的统计信息，并将索引的日志记录也附加到数据库事务日志文件中。
For example, before rebuilding the index, let’s take the current allotment of pages for the index of the AdventureWorks database, Sales.OrderTracking table and IX_OrderTracking_CarrierTrackingNumber named index.
例如，在重建索引之前，让我们获取AdventureWorks数据库的索引，Sales.OrderTracking表和名为IX_OrderTracking_CarrierTrackingNumber的索引的当前页面分配。
SELECT OBJECT_NAME(IX.object_id) as db_name, si.name, extent_page_id, allocated_page_page_id, previous_page_page_id, next_page_page_id
FROM sys.dm_db_database_page_allocations(DB_ID('AdventureWorks'), OBJECT_ID('Sales.OrderTracking'),NULL, NULL, 'DETAILED') IX
INNER JOIN sys.indexes si on IX.object_id = si.object_id AND IX.index_id = si.index_id
WHERE si.name = 'IX_OrderTracking_CarrierTrackingNumber'
ORDER BY allocated_page_page_id
Here, 1961 pages exist in the database file for this index, and the first 5 pages are the 861, 862, 1627, 1628, and 1904 in order of the page number. Now, let’s rebuild the index using SSMS.
此处，数据库文件中有1961个页面用于该索引，并且按页面编号的顺序，前5个页面是861、862、1627、1628和1904。 现在，让我们使用SSMS重建索引。
Index REBUILD operation is completed successfully and take page allocation references for the same index with the help of the same T-SQL query again.
索引REBUILD操作成功完成，并借助相同的T-SQL查询再次获取同一索引的页面分配引用。
SELECT OBJECT_NAME(IX.object_id) as db_name, si.name, extent_page_id, allocated_page_page_id,
previous_page_page_id, next_page_page_id
FROM sys.dm_db_database_page_allocations(DB_ID('AdventureWorks'), OBJECT_ID('Sales.OrderTracking'),NULL, NULL, 'DETAILED') IX
INNER JOIN sys.indexes si on IX.object_id = si.object_id AND IX.index_id = si.index_id
WHERE si.name = 'IX_OrderTracking_CarrierTrackingNumber'
ORDER BY allocated_page_page_id
After rebuilding the index, the refreshed page count is 1457, which was 1961 before. If you check the first 5 allocated pages of the same index, it has been changed with the new page references. It presumes that the index is dropped and made once again. We should check the refreshed fragmentation percentage for the same index, and as can be seen below, it’s 0.1% now.
重建索引后，刷新的页数为1457，这是1961年之前的。 如果您检查同一索引的前5个分配页面，则它已被新页面引用更改。 假定该索引已删除并再次创建。 我们应该检查相同索引的刷新碎片百分比，如下所示，现在是0.1％。
REBUILD clustered index over the table affects other indexes of the table as well because the REBUILD clustered index rebuilds the non-clustered index of the table as well. Perform rebuild operation on all indexes of the table or database together; a user can use DBCC DBREINDEX() command.
表上的REBUILD聚集索引也会影响表的其他索引，因为REBUILD聚集索引也会重建表的非聚集索引。 一起对表或数据库的所有索引执行重建操作； 用户可以使用DBCC DBREINDEX（）命令。
DBCC DBREINDEX ('DatabaseName', 'TableName');
重组索引 (REORGANIZED INDEX)
The REORGANIZE INDEX command reorders the index page by expelling the free or unused space on the page. Ideally, index pages are reordered physically in the data file. REORGANIZE does not drop and create the index but simply restructure the information on the page. REORGANIZE does not have any offline choice, and REORGANIZE does not affect the statistics compared to the REBUILD option. REORGANIZE performs online always.
REORGANIZE INDEX命令通过排除页面上的可用或未使用空间来对索引页面进行重新排序。 理想情况下，索引页将在数据文件中进行物理重新排序。 REORGANIZE不会删除并创建索引，而只是重新组织页面上的信息。 REORGANIZE没有任何脱机选择，并且与REBUILD选项相比，REORGANIZE不会影响统计信息。 REORGANIZE始终在线执行。
For example, before performing the REORGANIZE over the index, let’s take the fragmentation reading for the database ‘AdventureWorks’, table ‘Sales.OrderTracking’ and Index named ‘IX_OrderTracking_SalesOrderID’.
例如，在对索引执行REORGANIZE之前，让我们对数据库'AdventureWorks'，表'Sales.OrderTracking'和名为'IX_OrderTracking_SalesOrderID'的索引进行碎片读取。
Here, the index fragmentation percentage is 98.39 before REORGANIZE. The below list in the image is the allocation pages to the index.
在这里，索引碎片百分比为98.39（重新组织之前）。 图像中的以下列表是索引的分配页面。
SELECT OBJECT_NAME(IX.object_id) as db_name, si.name, extent_page_id, allocated_page_page_id,
previous_page_page_id, next_page_page_id
FROM sys.dm_db_database_page_allocations(DB_ID('AdventureWorks'), OBJECT_ID('Sales.OrderTracking'),NULL, NULL, 'DETAILED') IX
INNER JOIN sys.indexes si on IX.object_id = si.object_id AND IX.index_id = si.index_id
WHERE si.name = 'IX_OrderTracking_CarrierTrackingNumber'
ORDER BY allocated_page_page_id
Here, a total of 459 pages are listed in the above image, and the first five pages are 1065, 1068, 1069, 1944, and 1945. Now, let’s perform the REORGANIZE command on the index using the below T-SQL statement and look at the page allocation again.
这里，上图列出了总共459页，前五页是1065、1068、1069、1944和1945。现在，让我们使用下面的T-SQL语句对索引执行REORGANIZE命令在页面分配再次。
ALTER INDEX IX_OrderTracking_SalesOrderID ON Sales.OrderTracking REORGANIZE
Here, the total page count is decreased to 331, which was 459 before. Furthermore, we do not see new pages in the first five pages list, which implies data is just restructured – not refilled again. Even you could see new pages as well, it happens in the situation when the big index is heavily fragmented, and reshuffling over the data use a new page.
在这里，总页数减少到331，之前为459。 此外，我们在前五页列表中看不到新页，这意味着数据只是在进行重组-不再重新填充。 甚至您也可以看到新页面，这种情况发生在大索引严重分散且重新整理数据时使用新页面的情况。
To perform the REORGANIZE index operation on all indexes of the table or database together, the user can use the DBCC INDEXDEFRAG() command:
要一起对表或数据库的所有索引执行REORGANIZE索引操作，用户可以使用DBCC INDEXDEFRAG（）命令：
DBCC INDEXDEFRAG('DatabaseName', 'TableName');
As seen, there is a substantial difference between the Index REBUILD and REORGANIZE. Here the users have a choice to choose one of the alternatives as per the index fragmentation percentage. We can understand that there are no documented standards; however, the database administrator follows the standard equation as per the requirement of the Index size and type of information.
可以看出，REBUILD和REORGANIZE索引之间存在很大差异。 在这里，用户可以选择根据索引碎片百分比选择其他方法之一。 我们可以理解，没有文件化的标准。 但是，数据库管理员按照索引大小和信息类型的要求遵循标准方程式。
Usual determination of the use of the equation :
通常确定使用该方程式：
- When the Fragmentation percentage is between 15-30: REORGANIZE 当碎片百分比在15到30之间时：重新整理
- When the Fragmentation is greater than 30: REBUILD 当碎片大于30：重新构建
REBUILD option is more useful with the ONLINE option when the database is not available to take index maintenance in off-peak hours.
当数据库无法在非高峰时间进行索引维护时，REBUILD选项与ONLINE选项一起使用会更加有用。
结论 (Conclusion)
Index Fragmentation is an internal fragmentation in the data file. Core parameters of quick performance of your database are the Database Architecture, Database Design, and Query writing. A Good index design with maintenance always boosts the query performance in the database engine.
索引碎片是数据文件中的内部碎片。 数据库快速性能的核心参数是数据库体系结构，数据库设计和查询编写。 具有维护功能的良好索引设计始终可以提高数据库引擎中的查询性能。
翻译自: https://www.sqlshack.com/how-to-identify-and-resolve-sql-server-index-fragmentation/
扫一扫
1652
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
