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.