2

I am trying to gain a better understanding as to how SQL Server caches query plans.

For the following table(Populated with the values 1-500,000):

CREATE TABLE [dbo].[Test] ([ID] INT PRIMARY KEY);

And the following stored procedure:

CREATE PROCEDURE [dbo].[TestGet]
    @ID INT
AS
BEGIN;
    IF(@ID IS NOT NULL) BEGIN;
        SELECT [ID]
        FROM [dbo].[Test]
        WHERE [ID] = @ID;
    END;
    ELSE BEGIN;
        SELECT [ID]
        FROM [dbo].[Test];
    END;
END;

Making the following two calls:

EXEC [dbo].[TestGet] @ID = 1000;
EXEC [dbo].[TestGet] @ID = NULL;

Results in two different plans in the query plan cache:

Plan Cache

If the stored procedure were re-written as:

CREATE PROCEDURE [dbo].[TestGet]
    @ID INT
AS
BEGIN;
    SELECT [ID]
    FROM [dbo].[Test]
    WHERE [ID] = @ID OR @ID IS NULL;
END;

And the same two calls were made:

EXEC [dbo].[TestGet] @ID = 1000;
EXEC [dbo].[TestGet] @ID = NULL;

Only a single query_hash, and query_plan_hash are cached:

enter image description here

The calls to the stored procedure have the same shape in both cases. Why does one the first stored procedure and call generate two cached plans, with a different query_hash, and a different query_plan_hash? What criteria is SQL Server using to generate these hashes?

  • 2
    Because the entire batch is compiled regardless of the parameters passed in, and both queries need to be identifiable. See my answer here. – Erik Darling Dec 24 '18 at 18:14
  • sp_BlitzErik - Is that also to say the number of cached plans will potentially be equal to the number of statements contained inside a condition, and that a plan will only be cached once a statement within a condition is executed. Tangent question, for the pattern provided(@ID = scalar or @ID IS NULL) is there any benefit in separating the conditions into their own stored procedures, one for a scalar case and one for the NULL case? – M. Jacobson Dec 24 '18 at 18:27
  • @M.Jacobson If you have another question, please post it. Comments aren't a great place to go back and forth a bunch. – Erik Darling Dec 24 '18 at 18:37
  • @sp_BlitzErik - Thanks for the meta guidance. – M. Jacobson Dec 24 '18 at 18:39