The topic for this month’s T-SQL Tuesday #200 hosted by the legendary one-and-only Brent Ozar (Blog) is “When I look at a query, I know it’s bad if I see ___”. Here we go!

Indexes…Nonclustered…and lots of them

When I get paged, Slacked, emailed, or otherwise contacted to look at someone’s bad query and I see tons of NCI I know I’m in for a rough day. It usually doesn’t start that way though. First there must be a vague and general assertion by the user or their manager that the database server is unhealthy and impacting their service, module, sproc, or spid.

After I take a few deep breaths and reflect on having my cage rattled I put on my game face and begin to examine the SQL instance. In most cases, not long after I conduct my due diligence and can successfully rule out the flux capacitor…I mean the SQL instance then we dive down into the database layer and so forth until it’s time to have the awkward admission that the performance problem is really just kinda you dear user.

The Problems of Too Many NCI

I wish I could say I was exaggerating when I say that I’ve investigated query performance involving 20+ NCI on a single table on more than a few occasions. “Why not?” you may think – the more NCI the better, right? Well think on this:

• Writes pile up – every DML statement that executes against the table (inserts, updates, deletes) now has to also update the relevant NCIs – remember they are another separate internal data structure in SQL Server and represent a copy of the data.
• Disk space is used – this is especially a problem when dealing with VLDBs (TB+ size DBs). If you’re operating in the magical cloud computing of Azure / AWS / GCP then you’ll also be faced with the reality that, it turns out, storage is not cheap. In fact, it’s kinda expensive for “premium” disks.
• Memory / buffer pool get filled faster and may spill over to tempdb – taking a logical operation and making it more of a physical one (memory vs hard disk).
• Query plan casino – will you get a good query plan? The house always wins and you’re a guest at the SQL Server casino where cardinality estimators, stale statistics, and tons of NCI complicate the matter of generating a good query plan.
  • Teams can’t really steer this either. There’s an INDEX hint, but it’s brittle and a last resort per Microsoft’s own docs. Worse, every extra index is another access path the optimizer has to cost during compile, and enough expensive compiles at once can queue up on RESOURCE_SEMAPHORE_QUERY_COMPILE — a full-on compile storm that looks like the server’s dying even though CPU and memory are fine.
• Maintenance windows get longer. Index rebuilds and reorgs, statistics updates, AG syncs, log shipping — all of it scales with the number and size of indexes you’re carrying.
• Corruption – CHECKDB and CHECKTABLE will take longer.
  • Also, and this is more subtle, building an index doesn’t always mean reading straight from the base table. Microsoft’s CREATE INDEX docs note the optimizer might scan another existing index instead of doing a table scan, when that’s cheaper. So a wide covering index can end up as the source for building a sibling index.
  • If a value in that source index is wrong, the new index just copies the bad value forward — nothing got fixed, you made a second copy. The more covering indexes share the same included column, the more independent copies of that bad value exist. Consistency checks e.g. CHECKDB and CHECKTABLE will likely flag each one separately.

SQL Server 2025, for example, supports 999 NCI on a table. Really?!

That’s more over-engineering than an Omega Planet Ocean – impressive but way past the point of ever needing it and more a flex “just because we can”. Allowing 999 NCI is more laughable than a diving watch tested at 600 meters (almost 2,000 feet!).

Special Aside about Data Corruption and NCIs

I’ve dealt with this firsthand: a table with multiple corrupt nonclustered indexes turning up in the same CHECKTABLE run. My approach is to collect all the index definitions first, drop every corrupt NCI, then recreate them one at a time — not one drop/recreate cycle per index. If you rebuild your corrupt indexes one at a time instead, the ones you haven’t gotten to yet are still sitting there, still corrupt, and still eligible to be picked as the source for the index you’re currently rebuilding. Drop them all first, and that risk disappears. Ask me how I know this…

How Does It Happen?

Indeed – how does this happen? In my experience it’s usually through some index optimization tooling. After all, would a human really go through and add all those indexes? Maybe it’s someone looking at sys.dm_db_missing_index_details, Database Engine Tuning Advisor, a 3rd party COTS, or a homegrown solution.

Like a toll road, once you apply a toll to a road no matter what was said that tax is never coming off. Same thing with a tool that suggests missing indexes and applies them. Cleanup typically isn’t a feature.

Crazy Things I’ve Heard Which I Can Neither Confirm or Deny

Did you know that developers can get passionate about their design choices of nonclustered indexes on their tables? Let’s play a drinking game – if you’ve heard any of these anecdotes then…well…you know what to do.

• “If we drop an index we created, it might impact another team who uses it and we don’t know about it” — so nobody ever drops anything, forever.
• “Can we just signal the DB to stop creating indexes during an incident?” — as if indexes spawn on their own like rabbits.
• “I ran the Database Tuning Advisor and just applied every recommendation it gave me.”
• “This query’s slow — let’s just add an index for every column in the WHERE clause, separately.”
• “Indexes are free, right? It’s not like they take up space or anything.”
• “I added an index but it didn’t help, so I added three more just in case one of them works.”
• “The execution plan had a green ‘missing index’ suggestion, so I add those. Always. Every time. Never checked what already existed.”
• “This index isn’t being used, but let’s just leave it — just in case.”
• “Can we just rebuild all the indexes? That fixes slow queries, right?” — regardless of what’s actually wrong.

What Do?

This is a problem best dealt with during the index creation stage rather than the index maintenance phase. Whoever owns the tables / indexes should be responsible for their creation. One thing they could do is to start with sys.dm_db_index_usage_stats to see what’s really being used.

Example: if the server has been running for 30 days and the user_seeks and user_scans from the DMV are zero then it’s safe to say the index isn’t being used and is a candidate for some sweeper service to come through and garbage collect it, i.e. drop the index.

NOTE: be careful here because if an index is dropped and recreated, say because it was corrupt, then the DMV will show it as zero until activity hits it.

Another thing is to come up with a plan for dropping unused or unnecessary (however you or the business defines it) indexes. A simple check of how long the SQL Server service has been running and the index usage stats can determine a candidate NCI to drop. Ex. if the database server has been up and running for 7 / 30 / 60 days AND the

FWIW – a close runner up for me on this topic was misused CTEs – especially at scale but that’s for another post and/or rant.

Thanks for reading!

---

You Might Also Like