How Unused Indexes Hurt: Buffer Pool

Lost In The Woods

When you find unused indexes, whether using Some Script From The Internetâ„¢, sp_BlitzIndex, or Database Telepathy, the first thing most people think of is “wasted space”.

Sure, okay, yeah. That’s valid. They’re in backups, restores, they get hit by CHECKDB. You probably rebuild them if there’s a whisper of fragmentation.

But it’s not the end of the story.

Not by a long shot.

Today we’re going to look at how redundant indexes can clog the buffer pool up.

Holla Back

If you want to see the definitions for the views I’m using, head to this post and scroll down.

Heck, stick around and watch the video too.

LIKE AND SUBSCRIBE.

Now, sp_BlitzIndex has two warnings to catch these “bad” indexes:

  • Unused Indexes With High Writes
  • NC Indexes With High Write:Read Ratio

Unused are just what they sound like: they’re not helping queries read data at all. Of course, if you’ve rebooted recently, or rebuilt indexes on buggy versions of SQL Server, you might get this warning on indexes that will get used. I can’t fix that, but I can tell you it’s your job to keep an eye on usage over time.

Indexes with a high write to read ratio are also pretty self-explanatory. They’re sometimes used, but they’re written to a whole lot more. Again, you should keep an eye on this over time, and try to understand both how important they might be to your workload, or how much they might be hurting your workload.

I’m not going to set up a fake workload to generate those warnings, but I am going to create some overlapping indexes that might be good candidates for you to de-clutter.

Index Entrance

The Votes table is pretty narrow, but it’s also pretty big — 53 million rows or so as of Stack 2013.

Here are my indexes:

First, I’m gonna make sure there’s nothing in memory:

Don’t run that in production. It’s stupid if you run that in production.

Now when I go to look at what’s in memory, nothing will be there:

I’m probably not going to show you the results of an empty query set. It’s not too illustrative.

I am going to show you the index sizes on disk:

Size Mutters

And I am going to show you this update:

After The Update

This is when things get more interesting for the memory query.

Life Of A Moran

We’re updating the column BountyAmount, which is present in all of the indexes I created. This is almost certainly an anti-pattern, but it’s good to illustrate the problem.

Pieces of every index end up in memory. That’s because all data needs to end up in memory before SQL Server will work with it.

It doesn’t need the entirety of any of these indexes in memory — we’re lucky enough to have indexes to help us find the 10k or so rows we’re updating. I’m also lucky enough to have 64GB of memory dedicated to this instance, which can easily hold the full database.

But still, if you’re not lucky enough to be able to fit your whole database in memory, wasting space in the buffer pool for unused (AND OH GODD PROBABLY FRAGMENTED) indexes just to write to them is a pretty bad idea.

After all, it’s not just the buffer pool that needs memory.

You also need memory for memory grants (shocking huh?), and other caches and activities (like the plan cache, and compressed backups).

Cleaning up those low-utilization indexes can help you make better use of the memory that you have.

Thanks for reading!

Leave a Reply

Your email address will not be published. Required fields are marked *