none
New Cached Plan - but why?

    Question

  • Hi,

    I have an application which calls a stored procedure, which untill recently only had one cahced exection plan stored.  This has a hits_exec_context off over 500,000 (it is called alot), so I know it's been there a long time and it performs well (within a few seconds).

    Recently the appliction started to take over 30 seconds to run, yet calling the proc from SSMS produced the results straight away.

    Having read this article.... http://rahmanagoro.wordpress.com/slow-query-performance-from-application-but-fast-from-management-studio-what-is-happening/

    I
    could see I now had two cached plans, the difference between them been the set options (releating to ARITH_ABORT).  I ran the proc in SSMS using both ARITH_ABORT ON and ARITH_ABORT OFF and both results returned instantly. This proved to me that both cached plans are working fine.

    However the application still ranslow, so I cleared down the newly created cached plan using DBCC FREEPROCCACHE (Plan_handel) and then the application started to perform within seconds.

    This leads me to believe that for some reason a new cached plan was created which the application started to use, which lead to the poor performace.

    My questions are:

    Why would a new plan have been created when the "old" one had been used for so long?

    Why would the application appear to start and use the "new" cached plan and perform slowly, when if I use that plan via SSMS is runs fine?  Does SQL use the plan differently if been called from SSMS as oppose to an application?

    The issue is now solved by removing the new plan, yet I am affriad it may start to happen again.

    Thanks,

    Chris

    Tuesday, August 07, 2012 9:41 AM

Answers

  • I have an article on my web site,
    http://www.sommarskog.se/query-plan-mysteries.html
    that discusses the same problem as the blog post you referred to, but my article goes into more depth. I will not give you the answer to why you got a second plan, but it does list a couple of possibly reasons. Since you have cleared the cache, you may never be able to tell. My article also includes a couple of queries that you can use to diagnose this sort of problem.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, August 07, 2012 10:29 AM
  • Multiple levels of caching

    It is important to understand that cache matches at multiple "levels" happen independently of one another. Here is an example. Suppose that Batch 1 (not a stored procedure) contains the following statement (among others):

    EXEC dbo.procA;
    

    Batch 2 (also, not a stored procedure) does not text-match with Batch 1, but contains the exact "EXEC dbo.procA;" referring to the same stored procedure. In this case, query plans for Batch 1 and Batch 2 do not match. Nevertheless, whenever "EXEC dbo.procA;" is executed in one of the two batches, a possibility for query plan reuse (and execution context reuse, explained later in this paper) for procA exists if the other batch has executed prior to the current batch, and if the query plan for procA still exists in the plan cache.

    Each separate execution of procA gets its own execution context. That execution context is either freshly generated (if all of the existing execution contexts are in use) or reused (if an unused execution context is available). The same type of reuse may happen even if dynamic SQL is executed using EXEC, or if an auto-parameterized statement is executed inside Batch 1 and Batch 2. In short, the following three types of batches start their own "levels" in which cache matches can happen irrespective of whether a cache match happened at any of the containing levels:

    • Stored procedure executions such as "EXEC dbo.stored_proc_name;"
    • Dynamic SQL executions such as "EXEC query_string;"
    • Auto-parameterized queries

    Read more: Plan Caching in SQL Server 2008


    • Edited by irusul Tuesday, August 07, 2012 10:08 AM
    • Marked as answer by ChrisSQL2000000 Tuesday, August 07, 2012 11:07 AM
    Tuesday, August 07, 2012 10:08 AM

All replies

  • Multiple levels of caching

    It is important to understand that cache matches at multiple "levels" happen independently of one another. Here is an example. Suppose that Batch 1 (not a stored procedure) contains the following statement (among others):

    EXEC dbo.procA;
    

    Batch 2 (also, not a stored procedure) does not text-match with Batch 1, but contains the exact "EXEC dbo.procA;" referring to the same stored procedure. In this case, query plans for Batch 1 and Batch 2 do not match. Nevertheless, whenever "EXEC dbo.procA;" is executed in one of the two batches, a possibility for query plan reuse (and execution context reuse, explained later in this paper) for procA exists if the other batch has executed prior to the current batch, and if the query plan for procA still exists in the plan cache.

    Each separate execution of procA gets its own execution context. That execution context is either freshly generated (if all of the existing execution contexts are in use) or reused (if an unused execution context is available). The same type of reuse may happen even if dynamic SQL is executed using EXEC, or if an auto-parameterized statement is executed inside Batch 1 and Batch 2. In short, the following three types of batches start their own "levels" in which cache matches can happen irrespective of whether a cache match happened at any of the containing levels:

    • Stored procedure executions such as "EXEC dbo.stored_proc_name;"
    • Dynamic SQL executions such as "EXEC query_string;"
    • Auto-parameterized queries

    Read more: Plan Caching in SQL Server 2008


    • Edited by irusul Tuesday, August 07, 2012 10:08 AM
    • Marked as answer by ChrisSQL2000000 Tuesday, August 07, 2012 11:07 AM
    Tuesday, August 07, 2012 10:08 AM
  • I have an article on my web site,
    http://www.sommarskog.se/query-plan-mysteries.html
    that discusses the same problem as the blog post you referred to, but my article goes into more depth. I will not give you the answer to why you got a second plan, but it does list a couple of possibly reasons. Since you have cleared the cache, you may never be able to tell. My article also includes a couple of queries that you can use to diagnose this sort of problem.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, August 07, 2012 10:29 AM
  • Thanks for the help :)
    Tuesday, August 07, 2012 11:07 AM