4

After clearing the plan cache on SQL Server, how do I regenerate execution plans for stored procedures without executing the stored procedures themselves?

When I updated statistics this morning, one of my stored procedures was running really sluggishly. Per some google-fu, I found that clearing the plan cache can fix the issue - and it did. However, if I have to do this again, I'd like to avoid the performance hit from regenerating the plans while a user is waiting for a response.

Version/edition of SQL Server is 2016 Enterprise.

  • 3
    Devil's advocate: What would be the point of clearing cache just to add the plans back in? What are you really trying to solve. – Sean Gallardy Jan 4 '17 at 22:25
  • For whatever reason - when I updated statistics this morning, one of my stored procedures was running really sluggish. Per some google fu, I found that clearing the plan cache can fix the issue - and it did. But if I have to do this again, I'd like to avoid the performance hit from regenerating the plans when a user is waiting for a response. – Tony Shepherd Jan 4 '17 at 22:33
  • 7
    You shouldn't clear the entire procedure cache to troubleshoot the performance of a single stored procedure. Please show us where you read that advice so we can proactively try to correct it. Then, show us the stored procedure you're trying to troubleshoot, and the actual execution plan you get when running it manually. – Aaron Bertrand Jan 4 '17 at 23:20
  • @TonyShepherd - As Aaron Bertrand asked, where did you find the advice that led you to clear the entire procedure cache for troubleshooting purposes? Do you still have that link or could you find it? – SQL_Underworld Jan 5 '17 at 19:16
3

After clearing the plan cache on SQL Server, how do I regenerate execution plans for stored procedures without executing the stored procedures themselves?

You don't / can't. Execution plans are generated when the object is executed. If you want a plan in the plan cache, then the object must be executed. BUT, assuming that there is at least one input parameter to the stored procedure, you would need to use a value that represents probably the majority case, else you could be forcing a non-ideal / "bad" plan into the cache.

if I have to do this again, I'd like to avoid the performance hit from regenerating the plans while a user is waiting for a response.

You are better off taking the performance hit because you can't guarantee that the plan will even still be in the cache by the time the first person uses it. Plans can be dropped if SQL Server decides that it needs more memory for other operations. So if it does get dropped before the first person uses it, you saved them nothing, and now caused the server to generate the plan twice.

Besides, it is only a hit the first time. You are aware of this as you are trying to use it to your advantage in "pre"-caching it. But this also means that it will only be the first user that first time that gets a slight performance hit. After that, as long as the plan isn't dropped, others wouldn't be experiencing the performance hit anyway.

Save yourself a lot of wasted time / effort and don't worry about pre-caching. Worry more about performance problems with the queries in the stored procedures that would affect all users each time and spend your time wisely improving those trouble spots.

4

The only way the plan cache can be repopulated is by running the relevant T-SQL from stored procs or ad-hoc T-SQL.

If you have several stored procs that need to be "primed" at the start of the day, you could use SQL Server Agent to run them with predefined parameters prior to your business coming "on line".

2

I believe you are (were) looking for sp_recompile. As stated in the msdn link, sp_recompile "causes stored procedures, triggers, and user-defined functions to be recompiled the next time that they are run. It does this by dropping the existing plan from the procedure cache forcing a new plan to be created the next time that the procedure or trigger is run."

Brent Ozar mentions a big downside to using this system stored procedure in production, however, so caveat emptor.

  • 2
    I believe the OP is asking how to get the plan back into the cache, which only happens when you run the stored proc. – Max Vernon Jan 4 '17 at 23:40
  • Perhaps. I can't speak for OP. He DID, however, specifically ask for a way to regenerate the execution plans without running the stored procs. Yes, I know they need execute in order to regenerate the plan but sp_recompile fits his criteria since the plan would have been rebuilt upon the next scheduled run. There's a lot of context about this situation that we simply do not have from OP. – SQL_Underworld Jan 5 '17 at 19:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.