Devart Blog

SQL Server Typical Maintenance Plans – Part 1: Automated Index Defragmentation

Posted by on January 27th, 2014

It’s incorrect to consider a database as a stable unit, since various kinds of undesirable situations can appear over time — database performance degradation, malfunctions, etc.

To minimize occurrence of such situations, maintenance plans are created, that guarantee stability and optimal performance of any databases.

Among maintenance plan tasks, the following can be pointed out:

  1. Index defragmentation
  2. Statistics update
  3. Backup and checking the logical and physical integrity of all the objects in database

Let’s consider automation of each task.

So, the first point…

In addition to file system and log file fragmentation, sizable impact on database performance is provided by fragmentation within data-files:

1. Fragmentation inside separate index pages

After record insert, update and delete operations, empty spaces inevitably appear on pages. There is nothing wrong with that, since the situation is regular enough, but there is a slight hitch…

A very important role is played by the string length. For example, if a string is longer than a half of a page, the free half of the page won’t be used. As a result, when increasing the number of strings, the unused space in the database will be increasing.

This kind of fragmentation should be struggled on the stage of scheme design, i.e., choose such data types, that would take compact place on pages.

2. Fragmentation inside index structures

The main cause of this type of fragmentation are page split operations. For example, according to the structure of the primary key, a new row should be inserted on a specific index page, but this page has not enough space to place the inserted data.

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

Anyway, fragmentation leads to growth of number of pages to hold the same amount of information. This automatically increases the database size and unused space.

When executing queries, in which fragmented indexes are accessed, 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 prevent fragmentation, SQL Server provides commands to reorganize and rebuild indexes.

Rebuilding an index involves deletion of the old one and creation of a new instance of the index, in which data on pages is neatly compressed and organized as continuously as possible. It is important to note that the index rebuilding operation is rather costly.

So, in case, when fragmentation is insignificant, it is preferably to reorganize the existing index. This operation requires less system resources than re-indexing, and it is reorganization of leaf-level pages. In addition, reorganization, when possible, compresses index pages.

The index fragmentation level can be learned from a 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 the given query, the last parameter sets the mode, which value provides possibility to quickly, but not quite exactly, define the index fragmentation level (LIMITED/NULL modes). Therefore it’s recommended to set SAMPLED/DETAILED modes.

We know where to get the list of fragmented indexes from. Now, we need to generate a corresponding ALTER INDEX statement for each of them. Traditionally, a 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'
				-- Enterprise, Developer
				+ CASE WHEN SERVERPROPERTY('EditionID') IN (1804890536, -2117995310)
						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, 'DETAILED') 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

To speed up the process of rebuilding index, it is recommended to additionally specify the SORT_IN_TEMPDB. Additionally the ONLINE option slows down index rebuilding. However, it may be useful in some cases. For instance, reading from the cluster index may take a while. We have created a covering index and have resolved the performance problem. Further we rebuild a non-clustered index, and we need to apply to the clustered index, which impacts the performance.

The first one allows to rebuild indexes in the tempdb database, which can be especially useful for large indexes in an out of memory case, in other cases — the option is ignored. In addition, if the tempdb database is located on another disk — this will significantly decrease index creation time.
The second option allows to re-create the index without blocking queries to an object, for which this index is created.

As practice showed, defragmentation of indexes with a low level of fragmentation or with a small number of pages does not bring any major improvements that contribute to enhance performance when working with them.

In addition, the above query can be rewritten without the use of a cursor:

DECLARE @IsDetailedScan BIT
SELECT @IsDetailedScan = 1

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)
						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 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]
	FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')
	OPTION (OPTIMIZE FOR (@IsDetailedScan = 1))

--PRINT @SQL
EXEC sys.sp_executesql @SQL

As a result, both queries, when executing, will generate queries for defragmentation of problem 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;
...

The provided queries execution time can be compared using the profiler embedded to dbForge Studio for SQL Server:

SQL Profiler

As it can be seen, when using a cursor, more time is required for query execution.

Actually, the first part of creation of a maintenance plan for the database is finished.

In the next part we will write a query for statistics auto update.

Leave a Reply