I am investigating the reason why a query is performing bad in my SQL server 2016.
- The query executes in 1 sec when i give the date range filter as 2017-06-21 and 2018-06-20.
- When i give the date range filter as 2016-06-21 and 2017-06-20 ,the query becomes slow and takes 8 sec to executes.
The query is getting generated from .net application(linq-sql and EFF) and i capture the query with SQL profiler and execute in SSMS for testing.Query is same with same parameters but with different parameter values(date range).
I checked the actual execution plan from SSMS and it looks slightly different.I tried to retrieve the query execution plan for the query using the below sql query
SELECT cp.objtype AS ObjectType, OBJECT_NAME(st.objectid,st.dbid) AS ObjectName, cp.usecounts AS ExecutionCount, st.TEXT AS QueryText, qp.query_plan AS QueryPlan FROM sys.dm_exec_cached_plans AS cp CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
and adding where clause to filter my query.But to my surprise,i couldn't find any query plan for the query i execute.
I know there will be suggestions that it is due to parameter sniffing.But sql server is not even caching my query,as far as i tested.It is very weird for me.
The query when i capture from profiler starts with
exec sp_executesql and i dunno how sql handles ad-hoc queries like this.
Any suggestions of explanation?
Additional Info: I took out the portion of the query where filtering takes place and ran with two date ranges.In the execution plan of faster query at the beginning itself,left most node(left most part of the execution plan),the filtering of date is taking place. But for the slower query,it is not happening,the filtering of date occurs somewhere in the middle part.
select * from Searchview as tb1 where (tb1.ReportDateTime >= '2016-06-21 00:00:00') and (tb1.ReportDateTime <= '2017-06-21 23:59:00') and ( (tb1.AgentID='1330056') ) select * from Searchview as tb1 where (tb1.ReportDateTime >= '2017-06-21 00:00:00') and (tb1.ReportDateTime <= '2018-06-21 23:59:00') and ( (tb1.AgentID='1330056') )
The above queries are reduced version of the actual query and here the parameters are same only difference is the dates. The execution plans are different though.I don't understand why the difference,as i have leading index on ReportDateTime and AgentID and statistics are uptodate.