Ola Hallengren’s Maintenance Solution

A colleague asked me today to help him solve a maintenance issue that is affecting performance in the data warehouse.

Our server’s indexes are heavily fragmented and our statistics are months out of date in some places. Slow data retrieval makes it more difficult for the BI team to meet its operational KPIs because it takes longer to process data.

Well-organized indexes and fresh statistics are very important for quick data retrieval.

Without regular maintenance of these objects, SQL Server still works, but it eventually retrieves data very slowly as it rummages around the messy data. Think of trying to find a book in a library when all the books are scattered on the floor.

So what’s a busy DBA to do? Leverage the work of others: use Ola Hallengren’s Maintenance Solution!

Intelligent Index Maintenance

The SQL Server Maintenance Solution lets you intelligently rebuild or reorganize only the indexes that are fragmented. In the IndexOptimize procedure, you can define a preferred index maintenance operation for each fragmentation group. Take a look at this code:

EXECUTE dbo.IndexOptimize @Databases = ‘USER_DATABASES’,

@FragmentationLow = NULL,

@FragmentationMedium = ‘INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,

@FragmentationHigh = ‘INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,

@FragmentationLevel1 = 5,

@FragmentationLevel2 = 30

In this example, indexes that have a high fragmentation level will be rebuilt, online if possible. Indexes that have a medium fragmentation level will be reorganized. Indexes that have a low fragmentation level will remain untouched.

Update Statistics

The IndexOptimize procedure can also be used to update statistics. You can choose to update all statistics, statistics on indexes only, or statistics on columns only. If an index is rebuilt, the statistics is not being updated. You can also choose to update the statistics only if any rows have been modified since the most recent statistics update.

EXECUTE dbo.IndexOptimize @Databases = ‘USER_DATABASES’,

@FragmentationLow = NULL,

@FragmentationMedium = ‘INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,

@FragmentationHigh = ‘INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,

@FragmentationLevel1 = 5,

@FragmentationLevel2 = 30,

@UpdateStatistics = ‘ALL’,

@OnlyModifiedStatistics = ‘Y’

Ola Hallengren is a SQL Server DBA working for a Scandinavian bank. He gave a talk about his maintenance solution at SQLBits a couple of years ago.

I’m keen to try it out. If it’s good enough for all these companies that you’ve possibly heard of, then it’s good enough for me.

Plus, it’s free!

Advertisements