It's not all that uncommon to start a new job, or inherit a project, and find out that index maintenance hasn't exactly been a priority. One of the first things you want to do is look at fragmentation.
This query will pull back all the indexes for My_Database ordered by the average fragmentation in percent. (If you're not familiar with sys.dm_db_index_physical_stats, take a look at DMVs in Books Online.)
DECLARE @dbid TINYINT
SELECT @dbid = DB_ID ('My_Database')
SELECT OBJECT_NAME(object_id) [IndexName]
,*
FROM sys.dm_db_index_physical_stats (@dbid, NULL, NULL, NULL, NULL)
ORDER BY page_count desc, avg_fragmentation_in_percent DESC
You'll notice that I ordered by page count first, instead of fragmentation first. I'm simply more interested in high fragmentation on big indexes than I am in indexes that only have four pages. I may have 87% fragmentation on an 8 page index, but that means quite a lot less than 34% fragmentation on a 3,000 page index.
Note: I highly recommend saving this resultset somewhere, for comparison with the post-reindex readings. Management love before-and-after numbers.
So from here, let's build our reindex script. We have several options here. We could drop and recreate indexes, recreate with DROP_EXISTING, or use REBUILD or REORGANIZE. Our considerations include:
-
Today we're looking at a system that hasn't had index maintenance in months (if ever), so we want to completely rebuild the physical indexes.
-
The particular DB I'm looking at isn't very big – the largest table is something like 300,000 rows, and the whole database is smaller than 15Gb – and doesn't have excessive indexes (average of two or three per table).
-
AND I have the down time after hours to run the rebuild scripts without worrying about blocking.
So, I've decided to rebuild all the indexes on all tables that have at least one highly fragmented index. In other words, if Table1 had 3 indexes, and one of those is 60% fragmented, I'll rebuild all the indexes for Table1. Here's a bit of code that will accomplish that:
— Script index rebuilds
USE My_Database
GO
DECLARE @dbid TINYINT
SELECT @dbid = DB_ID ('My_Database')
SELECT 'ALTER INDEX ALL ON [' + OBJECT_NAME(object_id) + '] REBUILD WITH (FILLFACTOR = 90)'
FROM sys.dm_db_index_physical_stats (@dbid, NULL, NULL, NULL, NULL)
WHERE avg_fragmentation_in_percent > 24
ORDER BY avg_fragmentation_in_percent DESC
This will give us a list of ALTER INDEX statements for the tables that have at least one index fragmented above 24%. That's not a magical cutoff point, it's just a good starting place. Depending on your situation, you might choose to rebuild all your indexes, or not as many.
I also set the fill factor for each index to 90%. Again, that's just a starting place. Some of these tables aren't modified often, if at all, so I'll change those to 0%. Some of these tables are large (respectively), modified often, and highly fragmented. I might change some of those fill factors to 85, or even 80%.
So now all you have to do is run your script in your test environment, and then schedule a time for prod. After your rebuilds run, remember to run that first query again and save the results for your before-and-after numbers.
Hey, we just solved a huge part of our performance problem in five minutes! Another day, we'll tackle the next biggest parts: unnecessary cursors and 20-way joins.