Should we do index maintenance on an Azure SQL Managed Instance or Azure SQL Database?

on July 17, 2023

Have you ever received advice that was technically correct, but it was too hard to understand?

I think of this as “accidental bad advice,” because it can lead to confusion and bad outcomes. There’s a LOT of accidental bad advice out there on index maintenance for SQL Server and cloud versions like Azure SQL, even in the official documentation.

In this post I’m answering a common index maintenance question, and we’re going to keep it simple.

Do we need index maintenance if we have fast storage?

Last week I received a question about whether index maintenance was needed for an Azure SQL Managed instance or Azure SQL Database, given that the documentation says:

For most types of storage used in Azure SQL Database and Azure SQL Managed Instance, there is no difference in performance between sequential I/O and random I/O. This reduces the impact of index fragmentation on query performance.

Source: Optimize index maintenance to improve query performance and reduce resource consumption

Essentially, this is saying that often Azure SQL uses SSD for physical storage. It implies that therefore index maintenance isn’t very useful. A reader would be easily tempted to use this sentence to justify not setting up the maintenance at all.

Yes, we need index maintenance, but tune it to do the minimum.

Even if you have very fast storage, if you don’t ever do index maintenance then you can end up with problems such as a lot of empty space in an index.

This is no big deal on small indexes (think less than 300MB to keep it simple) but can slow down performance on larger indexes.

In consulting it isn’t uncommon for me to find 20+ GB of empty space in larger databases when index maintenance hadn’t been implemented.
Think of this space as both disk space and space in memory. That means you can fit less in memory!
Memory is especially expensive in Azure SQL Managed Instance and Azure SQL Database. It’s kinda shocking how little you get for how much you pay, really.

But if you overdo index maintenance, then you are wasting CPU resources and can slow down concurrent activities.

So it’s a good general rule to have index maintenance, but to tune the maintenance to only do the minimum.

At this point, someone might say: “But Kendra, the documentation essentially says that after the quote!”

Sure, the official guidance says this. It says this so verbosely and in such excruciating detail that I must force myself to read the full page. Would I understand it if I didn’t have years of foundational knowledge on the topic? Probably not.

How do we make index maintenance do the minimum?

Like loads of folks, I’m a big fan of Ola Hallengren’s free SQL Server Index and Statistics Maintenance solution.

This tool should work for Azure SQL Managed Instance like it does for a SQL Server hosted on a VM, container, or server. It should also work on Amazon RDS.

For Azure SQL Database, “You need to download the objects as separate scripts” and come up with your own way to automate execution for them. (Source). One way to do this is via Azure Data Factory: Daniel Taylor explains how to set it up in this video.

Azure SQL supports online index operations for both Managed Instance and Database. Assuming your database may be queried during maintenance, here’s a sample command:

EXECUTE dbo.IndexOptimize
    @Databases = 'USER_DATABASES',
    @MinNumberOfPages = 40000,
    @FragmentationLow = NULL,
    @FragmentationMedium = 'INDEX_REORGANIZE, INDEX_REBUILD_ONLINE',
    @FragmentationHigh = 'INDEX_REBUILD_ONLINE, INDEX_REORGANIZE',
    @FragmentationLevel1 = 50,
    @FragmentationLevel2 = 80,
    @LogToTable = 'Y';

Let’s walk through these parameters at a high level. For more details, check Ola’s docs (which are great).

Databases – this will loop through all user databases. You can specify an individual database, use wildcards, and do much more.
MinNumberOfPages – this defaults to 1000 pages, or 7.85MB. That’s REAL small to me on modern storage. To skip smaller indexes, I suggest raising this up to 40000 pages, which is ~313MB.
FragmentationLevel1 and FragmentationLevel2 – I agree with Brent Ozar’s suggested limits of 50% and 80% as general starter levels.