SQL ConstantCare® Now Gives Index Advice, Too.

When I was a DBA, I got so sick and tired of monitoring tools and scripts yelling at me to make a million different changes – most of which didn’t really have any measurable impact on performance or reliability. Sure, I want things to go fast, but I only have so many hours in the day – and are users really going to notice if, say, I turn Lock Pages in Memory on? Especially when I’m not having any memory pressure to begin with?

So when we built SQL ConstantCare®, we only wanted to give you advice that users would actually notice, or that would save your job.

We wanted to focus on really big bang for the buck.

Indexes are big bang for the buck, but they’re hard to get right.

Especially when SQL Server’s missing index recommendations suck so much. So now, we’re going to make life easier for you: starting now, SQL ConstantCare® watches the missing index advice coming out of SQL Server, tracks it over time, and then recommends the most important index changes that can make a real performance difference.

We’re still dealing with data coming from SQL Server’s missing index DMVs, though, so we’re starting really paranoid:

We’re only focusing on small tables: under 100M rows and under 10GB in size. Again, starting on the paranoid side. As we build up expertise, we’ll also examine your SQL Server edition to see whether we should do the index build online, and based on your TempDB metrics & size, whether the SORT_IN_TEMPDB option should be used.

We make sure the same index recommendation shows up across multiple days. We want to avoid the problem where someone runs a few analysis queries on just one day, and then never does that work again. Going even further, we make sure that the query involved is running at least 100x per day. (Could less-frequent queries benefit from indexes? Absolutely – but we’re starting paranoid.)

We make sure the index isn’t a duplicate or narrower subset. If you’ve already got an index on DisplayName, LastAccessDate, then SQL Server may still ask for an index on DisplayName alone. We ignore that recommendation because while it might make queries marginally faster, it would make your delete/update/insert workloads slower, and we’re just not fans of slower.

We make sure the table already has a clustered rowstore index. If you’ve set up a table as a heap, we’re not going to recommend that you index it yet – although as we gain more confidence in our recommendations, we’ll start recommending clustered indexes for tables where it looks like someone just made an innocent mistake, like a heap with a nonclustered primary key. We’re also not recommending indexes yet for columnstore tables – even though your workload might actually need one.

We focus on tables with <5 nonclustered indexes. We don’t want to put you in a position where you’ve added so many indexes that your delete/update/insert performance suffers terribly. (If you’ve been through my Mastering Index Tuning class, you’ll know why.) We’re not automatically de-duping indexes (yet) to get you down to a lower number of indexes, but that’ll be in the works this year as well.

If the index recommendation has >5 columns, we remove the includes. We don’t wanna double the size of your table, so we’re starting with index recommendations with the 5 & 5 Guideline from Mastering Index Tuning. There can absolutely be cases where wider indexes make sense – but we’re not going to automate those yet.

We don’t include big (>200 byte) columns in includes. SQL Server has a nasty habit of throwing everything and the kitchen sink into your index, but if you want this Big Data™, you’re gonna have to do a key lookup to get it.

We limit the index changes per day. We only take the top 10 missing index recommendations on the server overall, and no more than 1 per table. We’re trying to limit the blast radius if a well-meaning admin just takes everything we say verbatim and hits execute. After all, when you add indexes, you tend to have wide-ranging changes in execution plans, and suddenly SQL Server will come up with better ways to do all kinds of queries.

Yeah, we’re being pretty paranoid.

When Richie started building the code for this, he joked that it was really hard to get the recommendation to actually happen! I’d put so many exclusions in the spec that it was really hard to build a workload to actually trigger it! And sure enough, since the recommendation went live a week ago, only a handful of index recommendations gone out. We’re definitely starting on the shy side. Over the coming weeks, we’ll watch how the recommendations go, and gradually ease up on the restrictions to get more indexing goodness out there in the wild.

The resulting index recommendations are in your prescription T-SQL file in your SQL ConstantCare® emails. Just like many of our recommendations these days, they’re ready to run – just copy ’em into SSMS or ADS, review ’em to make sure you’re comfortable, and hit execute.

To get ’em, sign up for SQL ConstantCare®.