General Question on sp_recompile

For couple of scenarios, not many a times but few we have seen doing sp_recompile on a stored proc improving the performance.

Being a DBA i understand few of caveats involved when doing sp_recompile and why it would have worked in improving with performance.

There has been a constant debate within our management to provide Application/DEV DBA's the access to do sp recompile when they see performance issues like above. Just so that less of DBA involvement is there and APP devs can run on their during perf issues

I am very hesitant as i think it can bring some unwanted behaviors where it can sometime even worsen performance of other procs.

I am looking for general guidance and experience from your experts why this can be a bad idea under possible different circumstances or when it can be ideal. Your inputs might help me design and may be explain the approach in different way.

FYI- Above is being thought not across just one app but across few other as automation when seeing issues like parameter sniffing.

One thing that gets lost about sp_recompile is that it works on objects beyond stored procedures. From the docs:

The qualified or unqualified name of a stored procedure, trigger,
table, view, or user-defined function in the current database

If anyone gets a weird idea in their head, they could end up impacting more than just one procedure:

If object is the name of a table or view, all the stored procedures,
triggers, or user-defined functions that reference the table or view
will be recompiled the next time that they are run.

If developers already have the ability to use other flavors of recompile hints, it may be better to let them use those when appropriate, but also educate them on the difference in scope of what gets recompiled, and how they handle parameters:

For instances running at least SQL Server 2008 build 2746 (Service Pack 1 with Cumulative Update 5), using OPTION (RECOMPILE) has another significant advantage over WITH RECOMPILE: Only OPTION (RECOMPILE) enables the Parameter Embedding Optimization.

In general, WITH RECOMPILE should be avoided -- it's a very heavy handed approach. I'm in favor of targeting recompile hints either to temporarily solve a problem, or when the other potential solutions to a problem are unattractive.

StackExchange Database Administrators Q#283602, answer score: 5