With OPTION RECOMPILE vs without OPTION RECOMPILE

Question

I am lost why execution plan is different if I run query with option recompile to compare to same query (with clean proc cache) without option recompile. The result is one row. The script is:

Create two tables:

CREATE TABLE H (id INT PRIMARY KEY CLUSTERED IDENTITY(1,1), header CHAR(100))
CREATE TABLE D (id INT PRIMARY KEY CLUSTERED IDENTITY(1,1), idH INT, detail CHAR(100))
CREATE INDEX IX_idH ON dbo.D(idH)

Fulfill:

DECLARE @i int = 1
WHILE @i <= 10
BEGIN
    INSERT INTO H (header) VALUES ('nonononono')
    SET @i = @i + 1
    PRINT @i
END
SELECT * FROM H
GO

DECLARE @i int = 1
DECLARE @ii int = 1
WHILE @i <= 7
BEGIN
    WHILE @ii <= POWER (10,(@i - 1))
    BEGIN
        INSERT INTO D (idH, detail) VALUES (@i, 'nonononono')
        SET @ii = @ii + 1
    END
    SET @i = @i + 1
    SET @ii = 1
END

The statement:

DBCC FREESESSIONCACHE

DECLARE @hid INT = 1

SELECT *
FROM H h
JOIN D d ON d.idH = h.id
WHERE h.id = @hid
OPTION (RECOMPILE)

With OPTION (RECOMPILE) it uses the key lookup for the D table, without it uses scan for D table. I do not understand why. MS SQL version 13.0.4411.0. And simple (7 rows) and actual statistics.

Answer 1

With OPTION(RECOMPILE) optimiser knows the value of the @hid variable and essentially generates the plan, as if you wrote:

SELECT *
FROM H h
JOIN D d ON d.idH = h.id
WHERE h.id = 1

The generated plan has to be valid for this specific value of the parameter. It does not have to be valid for any other value of the parameter. Also, optimiser knows statistics of the table and usually can make a better decision. If there is only one value in the table that is equal to 1, most likely it will choose a seek. If there are a lot of values in the table that are equal to 1, it would choose a scan.

Without OPTION(RECOMPILE) optimiser has to generate a plan that is valid (produces correct results) for any possible value of the parameter.

As you have observed, this may lead to different plans.

I would recommend to look at both actual execution plans in the free SQL Sentry Plan Explorer tool. Look at details of each operator in the plan and you should see what is going on. You can do it in SSMS as well, but Plan Explorer is much nicer.