I’m not worried about my backups, I’m worried about my indexes. That’s not entirely true, I’m always concerned/interested/maybe even anxious about my backups, I’m a DBA, they are always on my mind. My backups are running just fine. I’ve checked them. I’ve verified them. I’ve shipped them offsite, tucked them in and kissed them good night. My indexes are another story. A while back I was testing out Ola’s IndexOptimize procedure. It wasn’t finishing and I wasn’t sure why, but other things were pressing and I had a solution that was working. I put them aside and said I’ll get back to them later. Well, it’s later.
I pulled down the latest versions from Ola’s site.
- IndexOptimize.sql: Stored procedure to rebuild and reorganize indexes and update statistics
- CommandExecute.sql: Stored procedure to execute and log commands
- CommandLog.sql: Table to log commands
Setting them up on my dev server I tested against a few small databases, playing around with the different parameters and making sure I had an inkling of what was going on. Then I moved on to one of my problem databases, 133+ thousand tables and over 300 thousand indexes. I used the following to figure out how many of those 300k were fragmented. >= 10% is a good starting point.
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS physstats
INNER JOIN sys.indexes AS ind ON physstats.OBJECT_ID = ind.OBJECT_ID AND physstats.index_id = ind.index_id
physstats.avg_fragmentation_in_percent >= 10
and physstats.page_count > 100
ORDER BY physstats.avg_fragmentation_in_percent DESC
Alright! Only 41 indexes with fragmentation, less about 0.01%. The Rebuild Index Task in a standard Maintenance Plan has been doing its job. Why mess with a good thing? Well, just because something is working doesn’t mean it can’t work better.
F5, here we go.
EXEC [IndexOptimize] @Databases = 'xxxxxx', @PageCountLevel = 100, @LogToTable = 'Y'
Guess what the default index fragmentation starting point is. It’s not 10. About 18 minutes and 556 ALTER_INDEX commands later I have my indexes defraged. Let’s look at the results of my earlier query, this time with physstats.avg_fragmentation_in_percent >= 5. Now there are only 186 indexes here with a max fragmentation of under 15%.
I don’t have any reasons why I this procedure wasn’t working for me earlier, with fewer indexes, but I’m making progress.