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.
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount on 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.
- What Do Missing Index Requests Really Mean In SQL Server?
- Using Views To Reduce Memory Grants In SQL Server
- Getting The Top Value Per Group With Multiple Conditions In SQL Server: Row Number vs. Cross Apply With MAX
- Getting The Top Value Per Group In SQL Server: Row Number vs. Cross Apply Performance