Parameter Sniffing and Recompile

Transact-SQL
1

I am going through some code in an application that calls stored procedures on SQL Server (2014). In the C# code, it executes the procedure and passes in WITH RECOMPILE.

The procedures consist of a single SQL statement, some conditionals, and then some dynamic SQL executed with EXEC...

The procedures have NO parameters.

I know using recompile is useful when less than optimal plans are used but targeting specific SQL statements is preferred rather than the whole procedure - in general. The fact that there are no parameters at all is a concern and caching a plan for that is what a cached plan is for in the first place.

I did some tests with statistics time and compared the two calls using recompile and not using it. Using it adds to the parse and compile time - obviously. Not a lot when seen as an individual procedure call but when done possibly hundreds of times it does add up.

I was going to request that the use of recompile is removed from the client code because I don't see any need for it but wanted to make sure I have not missed anything.

All of my searches returned results for when procedures are called with parameters and that doesn't apply in this case.

Any feedback?

Thanks in advance for your time!

2

I agree with you that there does not seem to be any reason for invoking the :with recompile" option. It sounds like this could just be a case of someone trying here because they had heard that it helped in a different situation. My vote, FWIW, make the change.

3

That is what I was leaning toward but I wanted to get feedback before acting or, as Paul Randall so wisely states, not make knee jerk reactions.

Thanks for the feedback.

4

Without seeing the actual code it's very difficult to be sure, but, in general, it does not sound as if that proc would benefit from being recompiled.

However, it's very possible the dynamic sql being generated should have an explicit RECOMPILE requested specified within it. But can't be sure of that either without seeing the type of code begin dynamically executed.