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

2 thoughts on “Ola Hallengren’s Maintenance Solution

  1. When I choose online rebuild, offline rebuild when the online rebuild fails trying to rebuild a clustered index with a table that contains a text datatype(which it should fail) the offline rebuild is NOT executed. Wondering if Ola’s process is completely tested and works.

    • Did you define the maintenance routine using T-SQL job steps?

      If so, then the maintenance routine will stop on the first error. SQL Server Agent is designed that way, but it’s not always the behavior you want.

      Ola’s recommendation is to use sqlcmd in CmdExec steps instead.

      From the ‘Job Execution’ section of the FAQ:

      Do the stored procedures need to be executed in a CmdExec job step with sqlcmd? Or can I use a T-SQL job step?

      The stored procedures must be executed in a CmdExec job step with sqlcmd and the -b option, if error handling and logging are to work as designed. The problem with the T-SQL job step is that it stops executing after the first error.

      You could use the MaintenanceSolution.sql script to create the jobs.

      Just to check that I understand your situation, here’s how I interpreted your comment.

      You have a table with a clustered index and a column of type text.

      You have a maintenance routine containing two steps. The first step performs an online rebuild of the clustered index and the second step performs an offline rebuild.

      As you expected, the online rebuild fails because SQL Server doesn’t support online index operations on tables that use the deprecated text data type.

      If you defined the maintenance routine using T-SQL steps, the second step (offline rebuild) will never start because the first step (online rebuild) always fails.

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