The Tale of a Database with No Indexes
Not long ago I met a database with no indexes.
But How? But Why? I must know!
This database was born to serve the new analytics and reporting department of a budding company. The analytics team made quick work of amassing dozens of reporting tables and processes. The database wept quietly in a corner for attention, but its cries fell on deaf ears.
As a bright eyed, bushy tailed developer in a new playground, I thought I’d do some query tuning. I quickly noticed a theme— table scans and missing indexes in every execution plan. Upon confirming my suspicions that nary an index was present, I walked in to the office of the analytics director much like the Kool-Aid man that still haunts our collective dreams. Something must be done! But who should be responsible?
Hey Nerds! Guess Who’s Got Two Thumbs, Speaks Limited French, and Hasn’t Cried Once Today? This Moi!
Then I got to thinking… I’m a developer now, but the dark side of the force is calling my name. If I can convince the DBAs to grant me permissions, I can take ownership of designing and tuning indexes for the reporting team, drive measurable improvements, and gain valuable experience along the way. Ultimately, the stakes were low enough that I was handed the keys to the castle.
You know nothing, John Snow!
Without a human resource to lean on for guidance, I relied heavily on good 'ole Google to help plan my attack. I put Pinal Dave’s sys.dm_exec_query_stats query to work, returning the most expensive queries hitting the database.
My strategy was to zero in on those queries for tuning opportunities and then apply appropriate indexes. I took a holistic approach, and tried to cover multiple expensive queries where possible. I cross checked the results with Brent Ozar Unlimited’s sp_BlitzCache to leave no stone unturned, and came back with a few more tweaks to work on.
When I was comfortable that I had covered enough ground from that angle, I decided to have a look at the sys.dm_db_missing_index DMVs. I was immediately overwhelmed by the plethora of missing indexes screaming at me, and began looking for a way to prioritize. I came across Bart Duncan’s query that assigns an improvement measure to the missing indexes, which was exactly what I needed.
This is where I had the most fun! There were tables with several dozen missing indexes— many with high improvement measures. I split the Duncan result set by tables, and began looking for patterns. I decided to limit the number of indexes on each table to a maximum of 5 to start out. Within that framework, my goal was to design indexes to cover the high improvement opportunities, while taking as many smaller improvement measure indexes in to account as possible.
Do You Even Index, Bro?
I knew that I would need a way to evaluate my hits and misses over time. Without access to fancy third party tools or the Database Engine Tuning Advisor, I decided to use Brent Ozar Unlimited’s sp_BlitzIndex to gain insight. I also took a look at sys.dm_db_index_usage_stats to get an idea of what tables were still getting hammered with a high proportion of scans, and how often the new indexes were being used, if at all.
And so it goes— the tale of index design, tuning, and maintenance is still being written. But at least she’s no longer the database with no indexes.