Shhh, and pay close attention. In 76 seconds, I’ll teach you something intriguing about the way SQL Server caches execution plans:
Hi! I’m Brent Ozar.
I make Microsoft SQL Server go faster. I love teaching, travel, cars, and laughing. I’m based out of Las Vegas. He/him. I teach SQL Server training classes, or if you haven’t got time for the pain, I’m available for consulting too.
Get Free SQL Stuff
"*" indicates required fields
14 Comments. Leave new
Hello Brent,
Using your example, when I use the same query, I get two different execution plans. I thought SQL Server would not use a different execution plan, when the query is identical to the previous run?
DBCC FREEPROCCACHE;
GO
SELECT COUNT(*) FROM dbo.Users;
GO
SELECT COUNT(*) FROM dbo.Users;
GO
sp_BlitzCache @HideSummary=1;
James – unfortunately, I can’t really troubleshoot that remotely.
The hash value of the Query is changing therefore it cannot match it to a cached plan
Richard – correct. What surprises most folks is that spacing, casing, and comments also change the query hash.
….
?
!!!!!
Well crap.
I knew most of this, but what really surprised me is that even with the comment being after the semicolon it still generated a separate plan. I guess that means plans are generated per-batch instead of per-statement? I guess that makes sense, it’s just not what I expected.
John – yep, isn’t that neat? But if you put the comment BEFORE the semicolon, depending on placement, you may actually see it in the text.
Yeah, neat is one word for it.
I actually started considering using this on purpose to get around throwing a RECOMPILE hint on a query. I think I need a vacation.
Well said Brent
Matt – hahaha, thanks!
Hi Brent – At the end I presume you are using the American hand sign for two to say that there are 2 items in the plan cache – rather than the British two fingered version to express your opinion of the situation 🙂
Steve – hahaha, correct.
Yeah, remember what happened to the guy in “Inglorious Basterds” when he used the wrong hand signal for ‘three’. Eeek.