Answered by:

New Cached Plan - but why?

Question

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.

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.

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.

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):

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:

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):

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:

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.