Index Fragmentation And Broken Demos

Egg Meet Face

The other night I was presenting for a user group, and I had a demo break on me.

Not the first time, not the last time. But this was weird because I had rehearsed things that morning.

I skipped over it at the time, but afterwards I started thinking about what had happened, and walking back through other stuff I had done that day.

Turns out, I had fragmented my indexes, and that broke a trivial plan demo.

Just Too Trivial

The working demo looks like this.

I run these queries. The top one receives a trivial plan, and doesn’t have a missing index request.

The bottom one gets full optimization, and one shows up.

Picture perfect.

Snap Your Shot

How I had broken this demo was by playing with Snapshot Isolation.

At some point earlier in the day, I had done something like this:

When you use optimistic isolation levels, a 14 byte pointer gets added to every row to keep track of its version.

If I had run the update without it, it wouldn’t have been a big deal. But, you know.

I’m not that lucky. Or smart.

See, after updating every row in the table, my table got uh… bigger.

Looter in a riot

Now, if I rebuild the table with snapshot still on, the problem goes away.

The problem is that I didn’t do that before or after my little experiment.

With a heavily fragmented index, both queries not only get fully optimized, but also go parallel.

Faster/Stronger

They’re both a bit faster. They both use a little more resources.

Why? Because SQL Server looked at the size of the table and decided it would be expensive to scan this big chonker.

Egg Wipes

Moral of the story: Index fragmentation makes your queries better.

Don’t @ me.

4 thoughts on “Index Fragmentation And Broken Demos”

  1. Why does the 1=1 get full optimization? Is it forcing a recompile or something? Does sql like easy math? Love the columns. Thanks for writing

  2. I have to ask the question given your previous writing on fragmentation doesn’t matter – if it could make it better, could it also make it worse?

Leave a Reply

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