Narcis Miclaus

5 min read

SQL Server, from the ground up Lesson 35 / 40

Maintenance plans vs Ola Hallengren

Why every shop eventually throws away the GUI Maintenance Plan and runs Ola's free scripts instead. The exact install + config for a sensible weekly schedule.

SQL Server ships with a Maintenance Plan Wizard. It’s a GUI, it’s built-in, it’s tempting. It’s also widely regarded as harmful.

This lesson is about why the community universally recommends Ola Hallengren’s maintenance scripts instead, what the scripts do, how to install them, and the sensible default schedule you can copy-paste.

Ola has given his work away free for 17 years, and just about every production SQL Server I’ve ever worked on eventually ended up running his scripts. This is the one lesson where I tell you to go to a third-party website and trust them over Microsoft’s built-in tool.

Why the built-in plan is bad

The maintenance plan wizard’s defaults:

  • Rebuild every index, regardless of fragmentation.
  • Update statistics on every table, regardless of change rate.
  • Use sampling defaults that are fine for small tables, wrong for big ones.
  • The Shrink Database task (enabled by default in older versions) re-fragments everything.
  • No selective logic; it does every task on every database equally.

Net result: a long-running, CPU-heavy job that does a lot of unnecessary work, and occasionally does harmful work. On a medium production server, the nightly maintenance plan can run for four hours doing things that take ten minutes of targeted effort.

You can tune the maintenance plan wizard, but the tuning knobs are limited and the resulting plan is still a XML-heavy blob that’s hard to read and version-control.

What Ola’s scripts do

Three main scripts:

  1. DatabaseBackup — smart backups. Handles FULL, DIFF, LOG. Supports backup to disk, network share, Azure Blob, S3. Compressed, checksummed, verified.
  2. IndexOptimize — smart index and statistics maintenance. Only rebuilds fragmented indexes, only updates stats that need it, with sensible defaults for thresholds.
  3. DatabaseIntegrityCheck — runs DBCC CHECKDB on all databases.

Plus some supporting procedures (CommandExecute, CommandLog).

All three are stored procedures. You schedule them via Agent. They take parameters, log their own history, and play nicely with anything you throw at them.

Installing

# Download MaintenanceSolution.sql from ola.hallengren.com
# Run it against master in SSMS

Running MaintenanceSolution.sql creates:

  • A table dbo.CommandLog for logging.
  • Stored procedures dbo.CommandExecute, dbo.DatabaseBackup, dbo.IndexOptimize, dbo.DatabaseIntegrityCheck.
  • Optionally (configurable), SQL Agent jobs for each.

Set the @CreateJobs = 'Y' flag before running and you get the jobs out of the box. Tweak the schedules later.

The canonical schedule

Here’s what Runehold runs:

Daily

00:00  Full backup of master
00:05  Full backup of msdb
00:10  Full backup of model
00:30  Full backup of Runehold
02:00  IndexOptimize (Runehold only, with @UpdateStatistics = 'ALL')

Every 30 minutes, 06:00–22:00

Transaction log backup of Runehold

Weekly (Sunday)

01:00  DatabaseIntegrityCheck on all databases

Monthly

03:00 first Sunday: full CHECKDB with EXTENDED_LOGICAL_CHECKS (data purity).

That’s it. Six Agent jobs cover the entire maintenance story.

IndexOptimize parameters, the important ones

EXEC master.dbo.IndexOptimize
    @Databases         = 'USER_DATABASES',
    @FragmentationLow  = NULL,          -- skip low fragmentation
    @FragmentationMedium = 'INDEX_REORGANIZE',
    @FragmentationHigh   = 'INDEX_REBUILD_ONLINE',
    @FragmentationLevel1 = 5,
    @FragmentationLevel2 = 30,
    @UpdateStatistics    = 'ALL',
    @OnlyModifiedStatistics = 'Y',
    @LogToTable          = 'Y';
  • @FragmentationLow = NULL — skip anything under 5%. Saves a ton of work.
  • @FragmentationMedium = 'INDEX_REORGANIZE' — reorganize between 5-30%.
  • @FragmentationHigh = 'INDEX_REBUILD_ONLINE' — rebuild above 30%, online (Enterprise) or offline (Standard).
  • @UpdateStatistics = 'ALL' — update stats on indexes and columns.
  • @OnlyModifiedStatistics = 'Y' — skip stats where no rows have changed.
  • @LogToTable = 'Y' — log outcomes to dbo.CommandLog so you can query what happened.

On Runehold this runs in about 35 minutes weekly. The built-in maintenance plan took four hours for a worse result.

DatabaseBackup parameters

EXEC master.dbo.DatabaseBackup
    @Databases         = 'USER_DATABASES',
    @Directory         = 'D:\Backups',
    @BackupType        = 'FULL',
    @Verify            = 'Y',
    @Compress          = 'Y',
    @CheckSum          = 'Y',
    @CleanupTime       = 336,             -- keep backups for 14 days (14 * 24)
    @LogToTable        = 'Y';

Log backups call the same proc with @BackupType = 'LOG'. DIFF with @BackupType = 'DIFF'.

Backup to Azure Blob:

EXEC master.dbo.DatabaseBackup
    @Databases  = 'USER_DATABASES',
    @URL        = 'https://runehold.blob.core.windows.net/backups',
    @Credential = 'AzureStorageCredential',
    @BackupType = 'FULL',
    @Compress   = 'Y',
    @CheckSum   = 'Y';

One line change; everything else the same.

DatabaseIntegrityCheck

EXEC master.dbo.DatabaseIntegrityCheck
    @Databases = 'ALL_DATABASES',
    @CheckCommands = 'CHECKDB',
    @LogToTable = 'Y';

DBCC CHECKDB is slow on big databases — on TB-sized databases, it can take hours. Run it weekly during a maintenance window. Breaking it into filegroup-level checks or running on a restored copy is an option for very large systems.

Log outcomes to a table

Because Ola’s scripts log to dbo.CommandLog, you have a complete audit trail:

SELECT TOP (100) *
FROM master.dbo.CommandLog
ORDER BY StartTime DESC;

Every command, every database, every duration, every error. Perfect for post-mortems and compliance.

For compliance-heavy shops (and Runehold under DORA, if applicable): point a monitoring alert at CommandLog.ErrorMessage IS NOT NULL so any maintenance failure gets noticed immediately.

Other tools worth knowing

  • Brent Ozar’s First Responder Kit (lessons 37-40) — diagnostic, not maintenance. Different focus.
  • dbatools — a PowerShell module that complements Ola’s scripts. Useful for things Ola doesn’t cover (instance-level configuration, copy databases between servers, etc.).
  • SQL Server Managed Backup to Azure — automates backups to Azure Blob without Ola’s scripts. Simpler but less flexible.

For a no-nonsense production setup: Ola’s scripts + dbatools + SSMS Query Store UI. That trio handles 95% of the DBA toolkit.

Run this on your own machine

-- 1. Download MaintenanceSolution.sql from https://ola.hallengren.com
-- 2. Open it in SSMS.
-- 3. Before running, set the @CreateJobs parameter to 'Y'.
-- 4. Execute against the master database.
-- 5. Check that the jobs were created:

SELECT name, enabled FROM msdb.dbo.sysjobs
WHERE name LIKE '%IndexOptimize%'
   OR name LIKE '%DatabaseBackup%'
   OR name LIKE '%DatabaseIntegrityCheck%';

-- 6. Try running IndexOptimize manually (small DB first!)
EXEC master.dbo.IndexOptimize
    @Databases = 'Runehold',
    @FragmentationLow  = NULL,
    @FragmentationMedium = 'INDEX_REORGANIZE',
    @FragmentationHigh = 'INDEX_REBUILD_ONLINE',
    @LogToTable = 'Y';

-- 7. Check what it did
SELECT TOP (20) * FROM master.dbo.CommandLog ORDER BY StartTime DESC;

Once you’ve run it once and verified the CommandLog output, set up the Agent jobs to run on schedule and largely forget about maintenance. That’s the goal. Boring is good.

Next lesson: DMVs every DBA should know by heart. The queries you run on a cold server when someone hands you root and says “figure out what’s wrong.”

Search