General Question on sp_recompile

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.

From my observation, a lot of inexperienced developers inevitably seem to stumble on sp_recompile, OPTION (RECOMPILE), DBCC FREEPROCCACHE, etc and unfortunately abuse them after they find it magically temporarily fixed the issue they were facing in the moment, so your feelings to be hesitant are justified.

I will say this from the opposite perspective though, depending on the environment, its not the end of the world when in panic mode to force recompilation of an individual query plan when done carefully on a specific procedure or query. Carefully meaning the developer should only do it to the object of which the problematic query plan belongs to, and not spam the "recompilation button" to nuke everything, and the developer should concretely identify that the performance issue they are facing is due to a bad execution plan (such as due to parameter sniffing ) before doing so. Therefore there should be some upfront training and maybe selectivity on which non-DBA developers are allowed to "push the magic button". I've seen cases where inexperienced developers scheduled jobs to run DBCC FREEPROCCACHE hourly unbeknownst to them they were causing a lot more performance problems by nuking the cached query plans instead of solving their issues.

By laying down some guidelines as above, you minimize the chances for introducing more problems than your solving (e.g. causing an even worse execution plan to be cached).

I would also recommend that forcing the developer to maintain a running list of the procedures / queries where these type of performance issues occur, so that an actual fix can be applied by a qualified developer / DBA when possible.