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.
/*Nothing for you*/ SELECT * FROM dbo.Users AS u WHERE u.Reputation = 2; /*Missing index requests*/ SELECT * FROM dbo.Users AS u WHERE u.Reputation = 2 AND 1 = (SELECT 1);
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:
ALTER DATABASE StackOverflow2013 SET ALLOW_SNAPSHOT_ISOLATION ON; BEGIN TRAN UPDATE dbo.Users SET Reputation += 1 ROLLBACK
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.
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.
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.
Moral of the story: Index fragmentation makes your queries better.
Don’t @ me.