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.

  • Did you have a look at data in the periods? For instance, if you have few rows in the first one and millions in the second one the difference isn't surprising. – Denis Rubashkin Jun 21 '18 at 14:32
  • Out put row counts are similar(<20) for both data ranges. – user9516827 Jun 21 '18 at 14:34
  • 1
    But what do the estimates say? That is what will drive the plan that is chosen. If it's not getting cached then rather than being reused the estimates might be driving a completely different plan. Is the date column the leading column in an index? When were stats last updated? It could be that the newer data is outside the histogram (look up "ascending key problem") and so SQL Server has to make an approximation guess that doesn't reflect your actual data distribution. – Aaron Bertrand Jun 21 '18 at 14:37
  • Run your slow query with including actual execution plan and look at parameters (compiled and runtime). Are they the same? (I guess no). Do you work in the separate test environment? If so, run DBCC FREEPROCCACHE before looking for your queries in sys.dm_exec_cached_plans – Denis Rubashkin Jun 21 '18 at 14:49
  • @AaronBertrand : Stats are updated i checked that.i have to check for estimates. – user9516827 Jun 21 '18 at 14:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.