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.
Want to advertise here and reach my savvy readers?
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.