Foliotek Developer Blog

SQL Server Optimization Script with Index Rebuilding to Reduce Fragmentation

Overview

I recently completed the SQL Server 2008 Essential Training on Lynda.com and learned about the Standard Reports feature in SQL Server Management Studio. ?From looking at these reports, I observed that several of our indexes on various databases had become quite fragmented and were spread out over sometimes thousands of (memory) pages.

I searched for some information online and was directed to a query that would identify indexes that were 30% or more fragmented and rebuild them all. ?I noticed, though, that some indexes on very small tables weren’t seeing any improvement, so I added another parameter to filter out indexes with a small amount of pages.

The results were pretty significant. ?One database size was actually reduced by over 30%, since the fragmented indexes were taking up so much memory and were so inefficiently stored.

SQL Server Standard Reports

If you haven’t seen the SQL Standard Reports before, do the following:

  • Right click on your database in SQL Server Management Studio
  • Mouse over “Reports” and then “Standard Reports”
  • Select a report.

The two reports I found most useful were “Index Physical Statistics” *and *“Disk Usage by Top Tables”. ?On the “Index Physical Statistics” report, you will see recommendations for each index that will say 1) nothing, 2) reorganize, or 3) rebuild. ?The reorganize is apparently quicker and, from what I saw, more geared towards slightly fragmented indexes. ?The rebuild is for more highly fragmented ones. ?Of particular note on this report is the number of pages the indexes are taking up. When it numbers in the thousands with a high percentage of fragmentation, it really needs to be rebuilt and will probably yield some significant improvements in performance and memory requirements.

On the “Disk Usage by Top Tables” report you can see which tables in your database are taking up the most space, which as the most records, and how much memory is being allocated towards storage for indexes. ?**From what I could tell, the storage for the primary index seems to be included in the Data (KB) column rather than the Indexes (KB) column.

*NOTE: *Before you start rebuilding any indexes, I would recommend that you do the following for comparison purposes:

  • Save these reports (or export them), so you can tell how significantly the rebuild improved your database by reducing fragmentation and database size.
  • Write a fairly comp

Also, if your indexes are highly fragmented and you don’t know when they were last rebuilt, it may be better to begin with higher values for the fragmentation and page count, so you don’t make your database inaccessible for too long. ?You might start at 95% and 1000 pages and then just walk down bit by bit.

Index Rebuild Script

So, here’s the script I ran to identify and rebuild fragmented indexes with a high page count (you can adjust the fragmentation percentage and page count as desired). I definitely borrowed most of this from somewhere else, but now I can’t find where that was. My apologies for that.

 BEGIN TRY  
 BEGIN TRAN

– Ensure a USE statement has been executed first.

SET NOCOUNT ON;

DECLARE @objectid int;  
 DECLARE @indexid int;  
 DECLARE @partitioncount bigint;  
 DECLARE @schemaname nvarchar(130);  
 DECLARE @objectname nvarchar(130);  
 DECLARE @indexname nvarchar(130);  
 DECLARE @partitionnum bigint;  
 DECLARE @partitions bigint;  
 DECLARE @frag float;  
 DECLARE @pagecount int;  
 DECLARE @command nvarchar(4000);

DECLARE @page_count_minimum smallint  
 SET @page_count_minimum = 50

DECLARE @fragmentation_minimum float  
 SET @fragmentation_minimum = 30.0

– Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function  
 – and convert object and index IDs to names.

SELECT object_id AS objectid,  
 index_id AS indexid,  
 partition_number AS partitionnum,  
 avg_fragmentation_in_percent AS frag,  
 page_count AS page_count  
 INTO #work_to_do  
 FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, ‘LIMITED’)  
 WHERE avg_fragmentation_in_percent > @fragmentation_minimum  
 AND index_id > 0  
 AND page_count > @page_count_minimum;

– Declare the cursor for the list of partitions to be processed.  
 DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;

– Open the cursor.  
 OPEN partitions;

– Loop through the partitions.  
 WHILE (1=1)  
 BEGIN;  
 FETCH NEXT  
 FROM partitions  
 INTO @objectid, @indexid, @partitionnum, @frag, @pagecount;

IF @@FETCH_STATUS

SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)  
 FROM sys.objects AS o  
 JOIN sys.schemas as s ON s.schema_id = o.schema_id  
 WHERE o.object_id = @objectid;

SELECT @indexname = QUOTENAME(name)  
 FROM sys.indexes  
 WHERE object_id = @objectid AND index_id = @indexid;

SELECT @partitioncount = count (*)  
 FROM sys.partitions  
 WHERE object_id = @objectid AND index_id = @indexid;

SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';

IF @partitioncount > 1  
 SET @command = @command + N’ PARTITION=’ + CAST(@partitionnum AS nvarchar(10));

EXEC (@command);

PRINT N’Rebuilding index ‘ + @indexname + ‘ on table ‘ + @objectname;  
 PRINT N’ Fragmentation: ‘ + CAST(@frag AS varchar(15));  
 PRINT N’ Page Count: ‘ + CAST(@pagecount AS varchar(15));  
 PRINT N’ ‘;  
 END;

– Close and deallocate the cursor.  
 CLOSE partitions;  
 DEALLOCATE partitions;

– Drop the temporary table.  
 DROP TABLE #work_to_do;  
 COMMIT TRAN

END TRY  
 BEGIN CATCH  
 ROLLBACK TRAN  
 PRINT ‘ERROR ENCOUNTERED’  
 END CATCH

SQL Server Agent Job

In order to prevent my indexes from ever getting too fragmented again, I set up a job under SQL Server Agent that runs once a month. ?The frequency that is best for your database will depend on its size and how fast it grows.

Final Comparison

After you’ve set all this up, don’t forget to run the Standard Reports again and compare them with the reports you ran before you did anything. ?You can also rerun the query ?you wrote and see if you can detect any improvement.

OK. ?That’s it. ?Good luck.

For more tips os optimizing SQL Server database queries, see A Beginner’s Guide to SQL Server Database Optimization

Script to identify indexes that should be rebuilt.

P.S. To simply identify indexes that should be rebuilt, run just a portion of the script:

– This query identifies indexes on a database that need to be rebuilt.  
 –  
 – The recommendation is to rebuilt queries whose fragmentation exceeds 30.0%  
 – and who page count exceeds 50 pages. These parameters may be adjusted below.

DECLARE @page_count_minimum smallint  
 DECLARE @fragmentation_minimum float

SET @page_count_minimum = 50  
 SET @fragmentation_minimum = 30

SELECT  
 sys.objects.name AS Table_Name,  
 sys.indexes.name AS Index_Name,  
 avg_fragmentation_in_percent AS frag,  
 page_count AS page_count,  
 sys.dm_db_index_physical_stats.object_id AS objectid,  
 sys.dm_db_index_physical_stats.index_id AS indexid,  
 partition_number AS partitionnum  
 FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, ‘LIMITED’)  
 inner join sys.objects  
 on sys.objects.object_id = sys.dm_db_index_physical_stats.object_id  
 inner join sys.indexes  
 on sys.indexes.index_id = sys.dm_db_index_physical_stats.index_id  
 and sys.indexes.object_id = sys.dm_db_index_physical_stats.object_id  
 WHERE avg_fragmentation_in_percent > @fragmentation_minimum  
 AND sys.dm_db_index_physical_stats.index_id > 0  
 AND page_count > @page_count_minimum  
 ORDER BY page_count DESC

For more tips on managing a SQL Server Database, see Managing a SQL Server Database: Tips for Beginners