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?

M. Jacobson
  • 154
  • 1
  • 8

0 Answers0