RECOMPILE function

Question

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

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