(Question moved from SO)
I have a table (dummy data) with clustered index contains 2 columns :
Now I run those two queries :
declare @productid int =1 , @priceid int = 1 SELECT productid, t.priceID FROM Transactions AS t WHERE (productID = @productid OR @productid IS NULL) AND (priceid = @priceid OR @priceid IS NULL) SELECT productid, t.priceID FROM Transactions AS t WHERE (productID = @productid) AND (priceid = @priceid)
The actual execution plan for both queries is :
As you can see , the first one is using SCAN while the second one is using SEEK.
However - adding
OPTION (RECOMPILE) to the first query , made the execution plan also to use SEEK :
Friends at DBA chat told me that:
In your query, @productid=1, which means that (productID=@productID OR @productID IS NULL) can be simplified to (productID=@productID). The former requires a scan to work with any value of @productID, the latter could use a seek. So, when you use RECOMPILE, SQL Server will look at what value you actually have in @productID and make the best plan for it. With a non-null value in @productID, a seek is best. If the value of @productID is unknown, the plan has to suit any possible value in @productID, which would require a scan. Be warned: OPTION (RECOMPILE) will force a recompile of the plan every time you run it, which will add a few milliseconds to every execution. Though this is only a problem if the query runs very frequently.
If @productID is null, to what value would you seek? Answer: there is nothing to seek to. All values qualify.
I understand that
OPTION (RECOMPILE) forces SQL Server to see what actual values the parameters has , and see if it can SEEK with it.
But now I lose the benefit of ahead-compilation.
IMHO - SCAN will only occur if a param is null .
That's fine - let SQL SERVER create an execution plan for SCAN.
BUT if SQL Server sees that I run this query many many times with values:
1,1 , then why doesn't it create ANOTHER execution plan and use SEEK for that ?
AFAIK - SQL creates execution plan for the most hit queries.
Why doesn't SQL SERVER save an execution plan for :
@productid int =1 , @priceid int = 1
( I run it many many times with those values)
- Is it possible to force SQL to keep that execution plan(which uses SEEK) - for future invocation ?