I got an interesting request for consulting, and I’m going to paraphrase it:
We were using Azure SQL DB with automatic index tuning enabled for months. Things were going great, but… we just deployed a new version of our code. Our deployment tool made the database schema match our source control, which… dropped the indexes Azure had created. How do we get them back?
First, restore a pre-deployment backup somewhere else. The indexes will still be present in that other database. Don’t overwrite the production database – I need to mention that in case SQL Server users are reading this to solve a similar problem.
Then, decide whether you want all of the old indexes, or only some.
If you want all indexes back, use a tool like sp_BlitzIndex @Mode = 2 to script out all of the index definitions, or Redgate Schema Compare or SQL Server Data Tools to compare the prod & backup databases. This lets you quickly put all of the indexes back, in the shortest amount of time, with the least amount of thought. However, it’ll also include indexes that may no longer be useful to you with your new version of app code.
If you only want some of the indexes back, keep that backup around – but wait to apply indexes until one of two things happens:
- A query shows up on your top-10 most resource intensive queries from tools like sp_BlitzCache or Query Store, or
- A user complains about a query
In either case, go into Query Store and try to find the prior versions of that query plan. Look to see which indexes it used to use, and then pull those index definitions from your pre-deployment backup. (Query Store won’t include the index definitions.)
If you can’t find prior plans in Query Store, you’ll need to review the tables used in the query, then review the indexes on those tables in the pre-deployment backup. Pluck the ones out that you think will be most useful, and then apply those in production.
After the emergency’s over, it’s time to think about how you’re going to prevent this problem the next time you do a deployment.
Change your deployment tooling to only make constructive changes, not destructive. If an index already exists, leave it in place. Frankly, I think this is the smart long-term thing to do regardless of how your deployments are done, or where your database is hosted. This same problem has existed for ISVs hosting on-premises applications for decades because customers will apply their own indexes just like Azure does.
Or, source control Azure’s changes as they happen. You can log Azure’s telemetry somewhere (or poll it on demand) and then build a process to apply those changes to a dev database, and open pull requests with Azure’s changes. That’s an easy sentence for me to write, but a challenging process to build. (I’ve certainly never bothered.)