Clear the cache while Stored Procedure session is running

Mario2286 441 Reputation points
2021-01-04T15:12:37.18+00:00

What happen if a SP A is running slowly with 5 different session ID (executed 5 times) and that point of a time, I clear the cache of that SP A or I execute sp_recompile for that SP A. Is it all the 5 session ID which is running SP A will start again with different plan or it's still executing using the same execution plan which previously it used before clear the cache ?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,961 questions
0 comments No comments
{count} votes

Accepted answer
  1. Shashank Singh 6,251 Reputation points
    2021-01-04T18:48:51.313+00:00

    Is it all the 5 session ID which is running SP A will start again with different plan or it's still executing using the same execution plan which previously it used before clear the cache

    Clearing a cache, in your case to be precise procedure cache, will not affect already running queries. When you execute a query it is compiled and plans are generated and the most cost effective plan is selected and stored, post this query is run by sql server based on the plan. Clearing the cache removes the plan but will not affect already running queries. But, a new SP execution request will now first create new plan and then run it.

    1 person found this answer helpful.
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Tom Phillips 17,736 Reputation points
    2021-01-04T17:09:39.157+00:00

    Doing anything to the plan cache does not affect already running stored procs or queries.

    1 person found this answer helpful.
    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.