I think I may know the answer based on my research, but am looking for confirmation on how/why the engine compiles the plan the way it does with
Parameters being passed in: @ID int ,@OtherID INT
SELECT b.Column1
,b.Column2
,b.Column3
,b.Column4
,b.Column5
,c.Column1
,b.Column1
,e.Column1
FROM Table1 AS b
inner join Table2 AS t
on b.ID = t.ID
left join [LINKED SERVER].[DB].dbo.Table3 as c
on b.ID = c.ID
left join Table4 AS e
on b.ID= e.ID
where (b.ID = @ID or @ID= 0)
And b.ID = @OtherID
And b.ID IS NOT NULL
and e.ID = 1
Now I have determined that the cause of the index scan is because of this line: where (b.ID = @ID or @ID= 0)
. More specifically, @ID = 0. To clarify even further, 0 for that ID field does not exist as a value in the underlying table, it was simply something a developer did to allow a user to pull back all of the results by passing in 0 to the parameter and then checking to see if that parameter is 0 so as a result more rows are pulled back (typically, you would just return 1-3 results).
Now, what is extremely odd, is that if I add OPTION RECOMPILE
, the engine is able to create a much better plan at the cost of overhead (compilation time) of course:
What I would like to know is how is this possible. From what I have read online, by using OPTION RECOMPILE
, the engine will literally replace the value with the actual value passed into the parameter and it can very easily see that @ID 1234 does not equal 0. However, if you don't use OPTION RECOMPILE
the engine will take the total # of records, which is 120,000, and divide it by the total number of distinct possibilities, 107,000. This comes out to about 1.1 estimated rows being returned and I confirmed this by looking at the estimated properties of the plan that has the index scan, but why would the engine continue to index scan if the estimation is correct? I even updated stats just to be sure.