I’m Probably Not Supposed To Show You Microsoft’s Auto-Indexing Tool.

This needs to just stay between you and me, okay?

Microsoft’s been working on automatically indexing SQL Server databases. No, not that Azure SQL DB feature, but one that actually works on-premises, going back through all supported versions of SQL Server. This is the brain child of Conor Cunningham and Jianjun Chen – yes, those guys, legends of the SQL Server engine, who’ve been hard at work on this for over two decades.

And it doesn’t even need AI. Just plain ol’ T-SQL.

It’s super simple: the installation file, AutoIndex.sql, creates an Agent job and a series of stored procedures including auto_create_index and auto_drop_index. They watch the index usage and missing index DMVs, log their findings to tables, and trend the data over time in order to figure out which indexes to create, and which ones to prune back because they’re only slowing you down.

And it’s even free!

I don’t think the launch page is supposed to be live right now, but here it is on learn.microsoft.com. I have a hunch that when this blog post goes live, they’re gonna take that announcement down, so here’s the Wayback Machine archive of it, and here’s the zip download.

Go ahead, read that announcement, and then come back here.

Okay, you’re back – and you probably figured out by the date on the Microsoft page that it’s actually from 2006. <sigh> Yeah, that tool actually went live over twenty years ago, didn’t get updated, and then eventually got pulled. That’s kind of a bummer, eh?

It was visionary, back from the giddy optimistic days of SQL Server 2005 when the dynamic management views (DMVs) had just gone live. The world seemed full of hope and dreams, like the database might actually become self-tuning. We had the data! The pieces were all there! We coulda been a contender! Wait, actually, we’re still contenders. Anyhoo.

I asked a couple of LLMs to review the code, and they pointed out some amusing stuff, but I love this note from Claude:

The comment in auto_drop_index — “in the future, we will add a special bit inside metadata to distinguish” — is exactly what shipped as sys.indexes.auto_created in Azure SQL Database a decade later. The recommendations table with counts, history, and execute/disable is recognizably the ancestor of sys.dm_db_tuning_recommendations and automatic tuning’s create/drop-index with verification and revert. The prototype’s missing pieces — checking existing indexes, verifying impact after creation, rolling back regressions — are precisely what the production feature added. Good bones, and a nice artifact of how far the idea had to travel.

My first instinct was, “No, that couldn’t have been a decade later – it had to be longer than that” – but indeed, it dropped around 2017.

It’s wild to think that it’s been a decade since Azure SQL DB got automatic index tuning, two decades since Conor and Jianjun’s initial blog post, and we still don’t have anything like it for SQL Server. I gotta imagine that some enterprising person out there is using AI right now to build something like it, and when they release it and get it working, they’ll be as well-known for their script as Ola Hallengren is synonymous with index maintenance.

The pieces are all there. We’re just waiting for someone to put ’em together. Could be you!