Giving Ola’s scripts another go

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.

SELECT ind.name
, physstats.avg_fragmentation_in_percent
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
WHERE
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.

 
RebuildIndexTask
 

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s