Setting Up MS SQL Server Maintenance Plans (Automated Maintenance)

Imagine: after a year of operation without maintenance, your SQL Server turns into a "sluggish" system. Report execution times that used to take milliseconds grow to tens of seconds.

Index fragmentation exceeds 50%, statistics become outdated — the optimizer chooses bad plans. The transaction log grows to tens of gigabytes, and integrity checks have never been performed.

We solve these problems comprehensively: we set up automated maintenance that sustains performance and reliability without your involvement. Our engineers have implemented similar solutions on 100+ servers, including high-load OLTP systems.

Four key processes — index rebuilds, statistics updates, integrity checks, and backup rotation — must run on a schedule. Without them, the database degrades: fragmentation above 30% reduces query speed by up to 40%, and outdated statistics can increase execution time tenfold. T-SQL scripts perform these tasks twice as fast as standard graphical plans.

What tasks does the maintenance routine solve?

• Index reorganization and rebuild — fragmentation > 30% leads to extra I/O operations and reduced performance. The script automatically chooses between reorganize and rebuild.
• Statistics update — without up-to-date statistics, the optimizer chooses poor plans, and query execution time can increase tenfold. We update only modified objects.
• Integrity check (DBCC CHECKDB) — detects page corruption before it becomes a problem. We run it once a week, at night.
• Backups with rotation — full, differential, transaction log with deletion of old files. Retention period — from 2 to 30 days depending on criticality. Typical RPO — 15 minutes.

Which maintenance method to choose: GUI or T-SQL?

Maintenance Plans from the SSMS graphical interface are a quick start, but fragile: breaking a plan with one clumsy click is easy, and versioning it in git is impossible. For a serious environment, we use SQL Agent Jobs on T-SQL. This gives full control, logging, and the ability to recover.

Why did Ola Hallengren become the standard?

The de facto standard for SQL Server maintenance is Ola Hallengren. It is downloaded from GitHub, installed with a single script, and creates the stored procedures DatabaseBackup, DatabaseIntegrityCheck, IndexOptimize.

The solution has been tested on thousands of servers, including in high-load systems. Our engineers have been using it since its release — not a single failure in production. T-SQL scripts reduce maintenance time by up to 30% compared to graphical plans.

-- Index maintenance: reorganize at 10-30% fragmentation, rebuild at >30%
EXECUTE dbo.IndexOptimize
    @Databases = 'USER_DATABASES',
    @FragmentationLow = NULL,
    @FragmentationMedium = 'INDEX_REORGANIZE',
    @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
    @FragmentationLevel1 = 10,
    @FragmentationLevel2 = 30,
    @MinNumberOfPages = 1000,
    @UpdateStatistics = 'ALL',
    @OnlyModifiedStatistics = 'Y',
    @LogToTable = 'Y';

-- Integrity check (run once a week, at night)
EXECUTE dbo.DatabaseIntegrityCheck
    @Databases = 'USER_DATABASES',
    @CheckCommands = 'CHECKDB',
    @LogToTable = 'Y';

How we do it: step-by-step process

1. Analysis — we check fragmentation, statistics, backups, and log size. We assess SLA and recovery point.
2. Schedule design — we define maintenance windows, task frequency, and backup retention periods. We take Always On into account.
3. Implementation — we deploy Ola Hallengren and configure the parameters for your environment. We create Agent Jobs with failure alerts.
4. Testing — we run it in a test environment, check logs and performance. We make sure the backups are restorable.
5. Deployment and monitoring — we move it to production and set up notifications in Telegram or email. We give you access to logs and reports.

Recommended settings for different workloads

What is included in the work

• Setup of a complete set of Maintenance Plans turnkey in one day.
• Documentation describing each job, schedules, and parameters.
• Training for your team: how to check logs, restore backups, and change schedules.
• Post-implementation support: one month of incident assistance.

Common mistakes when configuring it yourself

• Rebuilding indexes too often — causes excessive load. Use fragmentation thresholds and a minimum page count (1000).
• No integrity checks — you only find out about corruption during a restore. A monthly DBCC CHECKDB reduces the risk.
• Improper log rotation — the transaction log can fill up the disk. Set up automatic truncation after backup.

Approximate timelines: from one day for a standard setup to three for a complex infrastructure. Cost calculated after audit. Get a consultation with an engineer — we'll help you set up maintenance and sleep soundly. Order the setup, and your SQL Server will run reliably.