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:

Previous Post
My How to Think Like the Engine Class is Now Free – and Open Source
Next Post
Why You Should Test Your Queries Against Bigger Data

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;

  • The hash value of the Query is changing therefore it cannot match it to a cached plan

  • Dmitriy Briskin
    October 31, 2016 11:05 am

    ….
    ?
    !!!!!

  • 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

  • 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 🙂

Menu
{"cart_token":"","hash":"","cart_data":""}