I am facing a potential parameter sniffing issue with an In Line function in sql server 2008.
Basically Given the below three queries (same function):
DECLARE @SQL NVARCHAR(1000) SELECT @SQL = N'SELECT * FROM tfn_ArticleHistory (''2019-05-07'',''2019-05-07'',77,929)' EXEC sp_executesql @SQL
I get the optimal plan with index Seeks.
SELECT * FROM SELECT * FROM tfn_ArticleHistory ('2019-05-07','2019-05-07',77,929)
I get the same optimal plan with index Seeks.
But the problem becomes with the third query that causes index scans:
DECLARE @StartDate DATETIME ='2017-05-07',@EndDate DATETIME ='2019-05-07',@CompanyINT =77,@ArticledINT = 929 SELECT * FROM report.SELECT * FROM tfn_ArticleHistory (@StartDate,@EndDate,@Company,@Articled)
Find attached the first two plans for first two queries:
Plans for Query 1 and 2:
Plan for Query 3:
My question is what could be the best approach to fix this, What I have in mind is to:
1.- Try to troubleshoot Index scan of the bad plan by creating a covering index for the output column in the operator and update statistcs and see what plan the optimizer choses.
- Use OPTION (RECOMPILIE) within the function, I tried this one already, and I get the optimal for query 3 as same as query 1 and 2.
If anyone else has additions to this, will be highly appreciated.