Software Vendor Mistakes With SQL Server: Avoiding Clustered Indexes

Come Clean

---

Without fail, I seem to have this conversation every week:

Why is this a heap?

The vendor doesn’t like clustered indexes.

Ah, okay, why?

I don’t know.

There’s a nonclustered primary key, though…

Isn’t that the default?

No, you have to go out of your way to choose that.

Wann Ist Es

---

Heaps aren’t always bad, but you have to be careful when you choose to use them, because the problems that sneak up with them are tricky to detect and annoying to fix. If you never run into them, great.

But if you do, try to keep an open mind. Clustered indexes work wonderfully for a great many people, and it’s unlikely that you fall far out of that category, especially if you have anything within a horseshoe or hand grenade from an OLTP workload.

When I look at client workloads  that have problems with heaps, the main things that I call out are either:

• Forwarded fetches that result in uneven I/O patterns
• Captive pages from tiny deletes that don’t remove empty pages

Rather than go on and on about this stuff, here are a couple videos where I discuss the downsides and upsides of heaps. Normally they’re part of my paid training, but I’m making them available here to you for free:

To find tables that might need clustered indexes, a good, free tool is sp_BlitzIndex. It’s part of an open source project that I’ve spent a lot of time on. It’ll warn you about heaps that have forwarded records in them, and that have been deleted from. From there, it’s up to you to figure out if the table is big and critical enough to warrant adding a clustered index to.

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.