This question already has an answer here:

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):

First Query:

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.

Second Query:

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:

Third Query:

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:

enter image description here

Plan for Query 3:

enter image description here

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.

  1. 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.

marked as duplicate by Erik Darling sql-server May 16 at 11:39

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.