SQL Server 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.

	/*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);
SQL Server Query Plan
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:

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.

SQL Server Query Results
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.

SQL Server Query Results
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.

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.



4 thoughts on “SQL Server 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

    1. No, no recompilation. It’s just one of those things that makes SQL Server think extra hard.

      Like me when I need to count by 45.

      Thanks!

  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?

Comments are closed.