Before I give you the link to the next research paper in this week’s series, I wanna give you a few questions to think about:

• If you had to build something to automatically add indexes to SQL Server databases, where would you start?
• What data would you use to make your decisions?
• How would you test your decisions without implementing them in production?
• How would you apply your decisions?
• What’s the worst that could happen if your decisions were wrong?
• How would you find out if your decisions were wrong?
• What might your users do that would break your hard work?

Now, think about the same points – but for automatically dropping indexes. What data might you base your decision on? What’s the worst that could happen? How would you find out if the worst had happened, and could you correct the situation?

Seriously, print out this blog post or copy/paste those questions into an email, send it to yourself, and just think about it when you’re standing in a line or riding the train. Don’t cheat by reading the white paper first – spend some serious spare time thinking about how you’d answer those questions.

This paper covers those answers.

After you’ve spent a day or two thinking about the answers, read this: (PDF) Automatically Indexing Millions of Databases in Microsoft Azure SQL Database by Sudipto Das, Miroslav Grbic, Igor Ilic, Isidora Jovandic, Andrija Jovanovic, Vivek R. Narasayya, Miodrag Radulovic, Maja Stikic, Gaoxiang Xu, and Surajit Chaudhuri.

This easy-to-read 12-page paper is wonderfully candid about how Azure’s auto-indexing does what it does, the challenges it faces, how it’s succeeded, and times when it’s failed.

I don’t want to give away any spoilers here, but trust me when I say that if you do a lot of index tuning – especially graduates of my Mastering Index Tuning class – you’re going to recognize the challenges in this work, and you’re going to identify a lot with how Clippy runs into problems. Indexing is seriously complex, and there’s no magic answer. Azure SQL DB faces the same problems you do, and it’s making a really cool set of first steps to improve the situation – but with tools you might not expect.

I’ll talk spoilers in the comments as folks write in – feel free to leave spoilers in the comments, and avoid the comments before you’ve taken a day or two to think about your answers, and then read the white paper. This thought exercise really is worth the time. (I say this because I’ve thought a lot about these problems over the years!)