6

I have this stored procedure..when i told the developers that it is not recommended to use with Recompile option they replied "This was because this SP can be called with a number of different params and we wanted the optimizer to grab a new plan with each call (not ideal, but it was an attempt to get it to run more reliably faster each time vs. using an old cached plan)"

Is what they saying correct ? Is there any way they can do that with no recompile

   create PROCEDURE [dbo].[VERIFIER_QUEUE]
   @cas_name varchar(20) = NULL,
   @instance_name varchar(50) = NULL,
   @verifier_id int = NULL,
   @applicant_type VARCHAR(20) = NULL

    WITH RECOMPILE     
    AS

   BEGIN 
SET NOCOUNT ON

DECLARE @cas_name_x varchar(20)
DECLARE @instance_name_x varchar(50)
DECLARE @verifier_id_x int
DECLARE @InstanceId INT
...............
  • I Would recomend local variables. But I will wait some better answers. I use it with the programers where I work, and works great. – Racer SQL Jul 30 '15 at 17:34
  • 8
    You want OPTION (RECOMPILE) on the statement(s) with issues, not WITH RECOMPILE on the procedure. I wouldn't recommend the local variables "trick" - it just makes the code messier, better to use OPTIMIZE FOR UNKNOWN on modern versions. For a whole lot more on this topic, see this great post by Paul White. – Aaron Bertrand Jul 30 '15 at 17:36
  • 4
    Also, if many parameters are optional (so the query has things like WHERE col = @param or @param IS NULL), check out the kitchen sink - sometimes dynamic SQL can be a much more effective solution. – Aaron Bertrand Jul 30 '15 at 17:38
  • I will go for dynamic tsql, that saved us on couple of huge sp we have – Yaroslav Jul 30 '15 at 18:50
  • 1
    The devs are coding around a phenomenon called "parameter sniffing." There are many posts on this. – Michael Green Jul 30 '15 at 20:30
6

Michael Green is right: the devs are trying to thwart parameter sniffing, which happens when SQL Server compiles a plan that is great for one set of parameter values, but horrible for others.

You'll want to use OPTION (RECOMPILE) on the statement(s) with issues, not WITH RECOMPILE on the procedure. And I wouldn't recommend the local variables "trick" - it just makes the code messier; better to use OPTIMIZE FOR UNKNOWN on modern versions if that's the method that works best in your scenario. (For a whole lot more on this topic, see this great post by Paul White.)

Also, if many parameters are optional (so the query has things like WHERE col = @param or @param IS NULL), check out the kitchen sink - sometimes dynamic SQL can be a much more effective solution. You didn't show the rest of your code, only that you were already using the local variables trick, but it will essentially look like this:

DECLARE @sql NVARCHAR(MAX) = N'SELECT ... FROM ... WHERE 1 = 1';

IF @cas_name IS NOT NULL
  SET @sql += N' AND cas_name = @cas_name';

IF @instance_name IS NOT NULL
  SET @sql += N' AND instance_name = @instance_name';

IF @verified_id IS NOT NULL
  SET @sql += N' AND verifier_id = @verifier_id';

...

SET @sql = @sql + N' OPTION (RECOMPILE);';
PRINT @sql;

EXEC sys.sp_executesql @sql,
  N'@cas_name VARCHAR(20), @instance_name VARCHAR(50), @verifier_id INT, ...',
  @cas_name, @instance_name, @verifier_id, ...;

This approach of only adding clauses for parameters that are actually supplied protects you from caching plans based on different sets of parameters (for example, if I supply @FirstName on first execution, the seek plan on that column that gets cached isn't going to help when I ask for @LastName LIKE N'%s%'). The OPTION (RECOMPILE); at the end protects you from plans that can vary greatly based on the values of the same parameters from execution to execution (for example, WHERE name LIKE N'%s%' should yield a different plan shape than WHERE name LIKE N'Q%').

This typically works best with the server setting optimize for ad hoc workloads, which you can read about here and here. Essentially what this does is prevents your plan cache from filling up with all these slight plan variations, unless they are used more than once. (Yes, with OPTION (RECOMPILE), the point is moot; however, the server setting can't hurt for the rest of your ad hoc query workload, and I've never come across a downside to having it on.)

This is pretty safe from SQL injection, since you don't have to worry about concatenating user input into SQL strings (all parameters are strongly typed), but it can't hurt to read these topics on dynamic SQL:

  • Not sure what's wrong with with recompile. MSDN refers to it in the context of parameter sniffing: msdn.microsoft.com/en-us/library/ms190439.aspx It's not fine-grained or elegant but it gets the job done. – Andomar Jul 31 '15 at 14:21
  • 2
    @Andomar There's also nothing wrong with replacing my whole car when I get a flat tire. Recompiling the whole procedure for one problem statement is inelegant, wasteful, and loses certain optimizations (see Paul White's post.) Just because Microsoft's documentation correlates the two does not make it the best idea. – Aaron Bertrand Jul 31 '15 at 14:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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