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.
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