Hi,
I had a SQL agent job STUCK at a certain step and that step was using a SP PROC. Now, there was no blocking however high CPU due to some other queries, which I have noted. Now, when I recompiled the SP PROC( using USE DBNAME; GO EXECUTE SPPROC WITH RECOMPILE GO) which was being used by the same step, the Job moved to the next step and completed. Now, I don't fully understand why however are there any drawbacks of recompiling SP which is in use? is it possible, it might impact inserts if that SP for example say doing inserts, and recompiled while being used. Does recompile only updates the statistics or something else. Please share your thoughts.
Regards!
October 10, 2020 at 10:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
Recompile forces SQL to recreate the query plan, even if one is already available in memory.
That process may require updating stats if one of the tables in the query has had enough modifications to cause SQL to need to update the stats. The stats update may be immediate or delayed, depending on other settings you made in SQL.
There should be no harm from a recompile, other than a slight use of resources as SQL creates the plan. In general, if recompile helps a run, use it, use it freely. It's better to recompile when not needed than not to recompile when needed.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 11, 2020 at 5:19 pm
Further to what Scott has stated - the recompile will occur on the *next* use of the procedure/table and will not affect the currently running process. It will not affect any currently running processes.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply