SQL Server Performance – Using Stored Procedure with Recompile

execution-planoptimizationperformancequery-performancesql server

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
...............

Best Answer

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), this is what I call "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:

I have videos about my solution to "the kitchen sink" here and here as well as a blog post about it.

Related Question