Performance Tuning
Silent Demo: The Plan Cache
Shhh, and pay close attention. In 76 seconds, I’ll teach you something intriguing about the way SQL Server caches execution plans:
Free, 3× a week
Get my new posts by email
Three posts a week, plus a Monday roundup of the best database news from around the web.
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.