Day-to-Day Maintenance Plan - Part 1: Automatic Index Defragmentation

It is a mistake to consider the database as a kind of reference unit, because, over time, various undesirable situations can manifest themselves - performance degradation, malfunctions and so on.



To minimize the likelihood of such situations occurring, maintenance plans are created from tasks that guarantee stability and optimal database performance.

')

Among these tasks are the following:



1. Defragmentation indices

2. Update statistics

3. Backup



Consider the automation of each of these tasks in order.



So, the first point ...



In addition to the fragmentation of the file system and the log file, fragmentation within the data files has a tangible impact on database performance:



1. Fragmentation within individual index pages



After insertion, update, and deletion of entries, empty spaces inevitably appear on the pages. Nothing wrong with that, because this situation is quite normal, if not for one thing ...



The length of the string is very important. For example, if a line has a size that takes up more than half a page, the free half of this page will not be used. As a result, as the number of rows increases, there will be an increase in unused space in the database.



It is worth fighting with this kind of fragmentation at the design stage of the scheme, i.e., to choose such types of data that would fit compactly on the pages.



2. Fragmentation within index structures



The main reason for the emergence of this type of fragmentation is page splitting. For example, according to the structure of the primary key, a new line needs to be inserted into a specific index page, but there is not enough space on the page to contain the inserted data.



In this case, a new page is created, to which about half of the entries from the old page will move. A new page is often not physically contiguous with the old one and, therefore, is marked as fragmented by the system.



In any case, fragmentation leads to an increase in the number of pages to store the same amount of information. This automatically leads to an increase in the size of the database and an increase in unused space.



When executing queries that call fragmented indexes, more IO operations are required. In addition, fragmentation imposes additional costs on the memory of the server itself, which has to store extra pages in the cache.



To combat index fragmentation, SQL Server has the following commands in its arsenal: ALTER INDEX REBUILD / REORGANIZE .



Rebuilding the index involves removing the old and creating a new instance of the index. This operation eliminates fragmentation, restores disk space by compacting the page, while reserving free space on the page, which can be specified with the FILLFACTOR option. It is important to note that the index restructuring operation is very costly.



Therefore, in the case where fragmentation is minor, it is preferable to reorganize the existing index. This operation requires less system resources than re-creating the index and consists in reorganizing leaf-level pages. In addition, reorganization compresses index pages if possible.



The degree of fragmentation of an index can be found in the dynamic system view sys.dm_db_index_physical_stats :

SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) WHERE avg_fragmentation_in_percent > 0 

In this query, the last parameter specifies a mode, from the value of which it is possible to quickly, but not quite accurately determine the level of index fragmentation ( LIMITED / NULL modes). Therefore, it is recommended to set the SAMPLED / DETAILED modes .



We know where to get the list of fragmented indexes. Now it is necessary for each of them to generate the corresponding ALTER INDEX command. Traditionally, the cursor is used for this:

 DECLARE @SQL NVARCHAR(MAX) DECLARE cur CURSOR LOCAL READ_ONLY FORWARD_ONLY FOR SELECT ' ALTER INDEX [' + i.name + N'] ON [' + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + '] ' + CASE WHEN s.avg_fragmentation_in_percent > 30 THEN 'REBUILD WITH (SORT_IN_TEMPDB = ON)' ELSE 'REORGANIZE' END + ';' FROM ( SELECT s.[object_id] , s.index_id , avg_fragmentation_in_percent = MAX(s.avg_fragmentation_in_percent) FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) s WHERE s.page_count > 128 -- > 1 MB AND s.index_id > 0 -- <> HEAP AND s.avg_fragmentation_in_percent > 5 GROUP BY s.[object_id], s.index_id ) s JOIN sys.indexes i WITH(NOLOCK) ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id JOIN sys.objects o WITH(NOLOCK) ON o.[object_id] = s.[object_id] OPEN cur FETCH NEXT FROM cur INTO @SQL WHILE @@FETCH_STATUS = 0 BEGIN EXEC sys.sp_executesql @SQL FETCH NEXT FROM cur INTO @SQL END CLOSE cur DEALLOCATE cur 

In order to speed up the process of rebuilding the index, it is recommended to additionally specify the SORT_IN_TEMPDB option. You also need to separately mention the option ONLINE - it slows down the re-creation of the index. But sometimes it is useful. For example, reading from a clustered index is very expensive. We created a covering index and solved the performance problem. Next, we do a REBUILD nonclustered index. At this point, we will have to again refer to the cluster index - which reduces performance.



SORT_IN_TEMPDB allows you to rebuild indexes in the tempdb database, which is especially useful for large indexes in case of low memory, or the option is ignored. In addition, if the tempdb database is located on another disk, this will significantly reduce the time to create an index. ONLINE allows you to recreate an index without blocking requests to the object for which this index is being created.



As practice has shown, defragmenting indexes with a low degree of fragmentation or with a small number of pages does not bring any noticeable improvements to improve performance when working with them.



In addition, the above query can be rewritten without using the cursor:

 DECLARE @IsDetailedScan BIT = 0 , @IsOnline BIT = 0 DECLARE @SQL NVARCHAR(MAX) SELECT @SQL = ( SELECT ' ALTER INDEX [' + i.name + N'] ON [' + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + '] ' + CASE WHEN s.avg_fragmentation_in_percent > 30 THEN 'REBUILD WITH (SORT_IN_TEMPDB = ON' -- Enterprise, Developer + CASE WHEN SERVERPROPERTY('EditionID') IN (1804890536, -2117995310) AND @IsOnline = 1 THEN ', ONLINE = ON' ELSE '' END + ')' ELSE 'REORGANIZE' END + '; ' FROM ( SELECT s.[object_id] , s.index_id , avg_fragmentation_in_percent = MAX(s.avg_fragmentation_in_percent) FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, CASE WHEN @IsDetailedScan = 1 THEN 'DETAILED' ELSE 'LIMITED' END) s WHERE s.page_count > 128 -- > 1 MB AND s.index_id > 0 -- <> HEAP AND s.avg_fragmentation_in_percent > 5 GROUP BY s.[object_id], s.index_id ) s JOIN sys.indexes i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id JOIN sys.objects o ON o.[object_id] = s.[object_id] FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') PRINT @SQL EXEC sys.sp_executesql @SQL 

As a result, both queries will, when executed, generate queries to defragment the problematic indexes:

 ALTER INDEX [IX_TransactionHistory_ProductID] ON [Production].[TransactionHistory] REORGANIZE; ALTER INDEX [IX_TransactionHistory_ReferenceOrderID_ReferenceOrderLineID] ON [Production].[TransactionHistory] REBUILD WITH (SORT_IN_TEMPDB = ON, ONLINE = ON); ALTER INDEX [IX_TransactionHistoryArchive_ProductID] ON [Production].[TransactionHistoryArchive] REORGANIZE; 

Actually, this is the first part of creating a maintenance plan for the database. In the next part we will write a request to automatically update the statistics .



If you want to share this article with an English-speaking audience:

SQL Server Typical Maintenance Plans: Automated Index Defragmentation



UPDATE 2016-04-22: added the ability to defragment individual sections and fixed some bugs

 USE ... DECLARE @PageCount INT = 128 , @RebuildPercent INT = 30 , @ReorganizePercent INT = 10 , @IsOnlineRebuild BIT = 0 , @IsVersion2012Plus BIT = CASE WHEN CAST(SERVERPROPERTY('productversion') AS CHAR(2)) NOT IN ('8.', '9.', '10') THEN 1 ELSE 0 END , @IsEntEdition BIT = CASE WHEN SERVERPROPERTY('EditionID') IN (1804890536, -2117995310) THEN 1 ELSE 0 END , @SQL NVARCHAR(MAX) SELECT @SQL = ( SELECT ' ALTER INDEX ' + QUOTENAME(i.name) + ' ON ' + QUOTENAME(s2.name) + '.' + QUOTENAME(o.name) + ' ' + CASE WHEN s.avg_fragmentation_in_percent >= @RebuildPercent THEN 'REBUILD' ELSE 'REORGANIZE' END + ' PARTITION = ' + CASE WHEN ds.[type] != 'PS' THEN 'ALL' ELSE CAST(s.partition_number AS NVARCHAR(10)) END + ' WITH (' + CASE WHEN s.avg_fragmentation_in_percent >= @RebuildPercent THEN 'SORT_IN_TEMPDB = ON' + CASE WHEN @IsEntEdition = 1 AND @IsOnlineRebuild = 1 AND ISNULL(lob.is_lob_legacy, 0) = 0 AND ( ISNULL(lob.is_lob, 0) = 0 OR (lob.is_lob = 1 AND @IsVersion2012Plus = 1) ) THEN ', ONLINE = ON' ELSE '' END ELSE 'LOB_COMPACTION = ON' END + ')' FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) s JOIN sys.indexes i ON i.[object_id] = s.[object_id] AND i.index_id = s.index_id LEFT JOIN ( SELECT c.[object_id] , index_id = ISNULL(i.index_id, 1) , is_lob_legacy = MAX(CASE WHEN c.system_type_id IN (34, 35, 99) THEN 1 END) , is_lob = MAX(CASE WHEN c.max_length = -1 THEN 1 END) FROM sys.columns c LEFT JOIN sys.index_columns i ON c.[object_id] = i.[object_id] AND c.column_id = i.column_id AND i.index_id > 0 WHERE c.system_type_id IN (34, 35, 99) OR c.max_length = -1 GROUP BY c.[object_id], i.index_id ) lob ON lob.[object_id] = i.[object_id] AND lob.index_id = i.index_id JOIN sys.objects o ON o.[object_id] = i.[object_id] JOIN sys.schemas s2 ON o.[schema_id] = s2.[schema_id] JOIN sys.data_spaces ds ON i.data_space_id = ds.data_space_id WHERE i.[type] IN (1, 2) AND i.is_disabled = 0 AND i.is_hypothetical = 0 AND s.index_level = 0 AND s.page_count > @PageCount AND s.alloc_unit_type_desc = 'IN_ROW_DATA' AND o.[type] IN ('U', 'V') AND s.avg_fragmentation_in_percent > @ReorganizePercent FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') PRINT @SQL --EXEC sys.sp_executesql @SQL