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.)
12 Comments. Leave new
As another option, SQL Server Data Tools has a schema compare option, along with the ability to script and/or directly deploy those changes.
Yes, that’s the first option, “If you want all indexes back” – I’ll add a mention to SSDT in there too. Thanks!
Yup, Nathan beat me to it!
In my case it’s I’m using it in Visio – same thing I’m sure. VERY pleased to see they finally got it right – I’d given up on the MS tools for a long time! reasonably fast and good options, etc.
Generate Script + Human Review + Run script. And also DropObjectsNotInSource=False + Handcrafted DROPs in PostDeployscript.
Too many bad surprises with direct deploy from any kind of SQL generator tools. They are awesome but don’t give them the key to your DB.
Use a tool like Flyway to do this.
Doent have to be enterprise, There are various flavors
It’s considered to be slower but I 100% agree with you, Fred.
A good example of this is that someone wanted to create a sister table powered by trigger and a bunch of other stuff to support it because they thought it would be faster.
After doing the “DBA Review” on the code and tested to give the Developer the benefit of the doubt, I replaced it with a single persisted compute column that does NOT contain a UDF call.
There’s just no substitute for knowledge and code reviews.
“Change your deployment tooling to only make constructive changes, not destructive. If an index already exists, leave it in place.”
A “great” example of this destroyer is Microsoft Dynamics AX: when deploying new code with database sync, it will wipe all current indexes…
Well, the process itself is incorrect as they have been working on a new piece of code and/or resolve bugs based on a different version of the database, if then, there was even some performance improvements to deploying… well, you get the picture…
So the best action would be rollback the change, test the new changes against the “real” database, adjust the process and deploy.
long ago, asking the UK Automobile Association (“AA”) for directions from a particular A to B gave the interesting/unexpected “..but to see more sights, start from C instead”. If there is drift between as-was, as-is and to-be it behoves the DBA (sorry “CI/CD DevOps practitioner”) to be aware of such nuances before pressing the big-red-button (Prez Biden please note). Don’t be fooled by all the razzamataz about AI, but remember ex-Prez Reagan’s paraphrase of the Russian proverb “Trust, but Verify” [https://en.wikipedia.org/wiki/Trust,_but_verify]
I often pull production schema into dev to see what people have been doing behind my back.
Ideally I would lock it down. But this is a comprise they will accept.
Autotuning on Azure SQL is good in many ways, but it can sometimes paper over database design cracks rather too much.
If you have a table where the system contains a lot of data that is stored as JSON in nvarchar(max) (lets not debate that), and your queries read that column then it has a habit of creating an include with that column in.
Very quickly your space usage goes up.
Autotuning is treated as a panacea, and almost as a ‘workaround’ for poor DB design.