0

We had a production level event over the weekend where execution of a function suddenly went from MS to nearly a minute.

Unfortunately, SQL wasn't able to execute the ALTER Function and it kept 'spinning' due to the high volume of queries calling the function and the CPU hit critical 100% and the database became unusable/instable.

Ultimately the fastest resolution was to reboot the server.

However, is there a better/faster way to force the RECOMPILE that wouldn't hang?

I've found a number of articles on RECOMPILE but nothing along the lines of the hanging and being blocked.

Attempts to KILL the individual queries resulted in whack-a-mole and couldn't clear them out fast enough.

Thanks

New contributor
MikeL is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
1

...Ultimately the fastest resolution was to reboot the server. ...Attempts to KILL the individual queries resulted in whack-a-mole and couldn't clear them out fast enough.

There is no need to reboot server or kill sessions manually when you want to have an exclusive access to database, you can use ALTER DATABASE ... SET SINGLE_USER WITH ROLLBACK IMMEDIATE and than issue your ALTER FUNCTION.

To remove individual plan from cache you can use DBCC FREEPROCCACHE(plan_handle) with the plan_handle you are interested in, see Example A. Clearing a query plan from the plan cache here DBCC FREEPROCCACHE (Transact-SQL)

  • In a perfect world I agree that would allow the ALTER FUNCTION/DBCC FREEPROCCACHE to execute. Unfortunately, I suspectnot all of our apps are self-aware enough to reconnect once the SET MULTI_USER is executed. I will give it a try if there is a next time. Thanks – MikeL yesterday

Your Answer

MikeL is a new contributor. Be nice, and check out our Code of Conduct.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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