TSQL Execution Plan - Estimated Number of Rows = 1 - Poor Performing Query

Question

TSQL MSSQL 2008r2

I am trying to tune a poor performing query.

For 3 out of 4 of the largest tables (several millions of rows) being queried (index seeks), the plan has decided that the estimated number of rows = 1. I think this is where the issue is. The number of logical reads is crazy high which I think is as a result.

I don't know what is making the query engine assume this is the case. Statistics are updated and I'm not using any user defined functions. The query engine is not trying to "convert_implicit" any columns. So what do I need to look at, change to make this query more efficient.

The plan is here, https://www.brentozar.com/pastetheplan/?id=S18GOLFig

The variables are missing from Paste the Plan. Here they are:

DECLARE  @1stDay_StartExtract   DATE
        ,@LastDay_EndExtract    DATETIME
        ,@WhenProcessLastTime   DATETIME;

SET @1stDay_StartExtract= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-3, 0); 
SET @LastDay_EndExtract = DATEADD(SECOND,-1,DATEADD(MONTH, DATEDIFF(MONTH,0,GETDATE())-1,0)); 

DECLARE  @dtStartDate_LT DATETIME= DATEADD( DAY, -1,@1stDay_StartExtract )
        ,@dtEndDate_LT   DATETIME= DATEADD( DAY, 1, @LastDay_EndExtract );

Let me know if you need more information.

Thank you in advance.

Answer 1

I know of three main ways of addressing a query performance issue caused by a cardinality mis-estimate:

1. Giving the optimizer more information

The query optimizer generally works better if it has higher quality information to inform the model. Steps here can include updating statistics, creating new statistics, using the RECOMPILE hint to pass along the literal values or variables, or materializing key intermediate result sets to provide better cardinality estimates or indexing.

2. Rewriting your query to be more clear to the optimizer

This can include simplifying code to remove redundant filters or refactoring it to be more clear to the optimizer. The query looks complex and we don't have the view code so it's hard to say more. There are a few filters in the query that appear to be extremely complex. It wouldn't surprise me at all that the optimizer cannot do a good job with guessing how those filters will affect the results.

3. Taking advantage of SQL Server enhancements

Sometimes there are features that you can turn that will make SQL Server do a better job with your workload. If you aren't using trace flag 4199 you could test this query with it. Trace flag 4199 is a collection of query optimization fixes that Microsoft has done over the years. It is on my default in SQL Server 2016. Trace flag 2301 is a bit less straightforward. It makes some changes to the optimizer around join cardinality estimate and in a rough sense you can say that the optimizer works harder to find a better plan. It is riskier and not nearly as common as trace flag 4199. Might not be practical but worth mentioning that each new version of SQL Server makes changes to improve query performance. In SQL Server 2014 there is a new cardinality estimator model which works better for some workloads.

For your particular query, I also want to note that it's easy to misread the single row estimate that you're seeing. The estimated number of rows that you see on the inner side of the nested loop is the number of rows returned per iteration of the loop. Seeing one row estimated from a nested loop seek is common and often not a sign of a problem.

However, the cardinality estimate for the outer part of the query is a bit off (36269 actual rows versus 6976 rows). It's perfectly natural to see a high number of logical reads with a nested loop and to suspect that part of the query is slow and needs to be improved. I find it useful to try to think about what the query optimizer should do instead to get the data that it needs. Would a hash join be better? Merge join? A nested loop with a different index?

I don't have the full picture but the nested loop joins that you called out don't look that bad to me. I don't see any key lookups and one of the indexes is covering. One way to move forward is to materialize all of the results of the query up until that point. Gather statistics on the temp table. Then look at a query plan for the adjusted query and see how long it takes to run. If the query plan changes for the better then you have a useful clue on how to make it run faster. If it doesn't change then you can at least get a more precise measurement of what you think the slow part is. Good luck!

Answer 2

Combining all of those ORs and ANDs might make it less efficient when dealing with larger data sets. It is possible that if you break them up into separate queries that are UNIONed together that it could help improve the execution plan.

Try changing this:

   SELECT TOP 250 --Just for testing
     V.vNodeId
    ,V.VehicleKey
    ,V.BusGrpId
    ,V.BusinessGroupKey
    ,V.CreateDate 'StartDate'
    ,V.DeletedTime
   FROM
    dbo.vw_xED_Test_Month V
   WHERE
    (
     ( CreateDate <= @1stDay_StartExtract AND DeletedTime IS NULL ) 
     OR ( CreateDate BETWEEN @1stDay_StartExtract AND @LastDay_EndExtract AND DeletedTime IS NULL ) 
     OR ( CreateDate <= @1stDay_StartExtract AND COALESCE(DeletedTime,'2100-12-31') BETWEEN @1stDay_StartExtract AND @LastDay_EndExtract )
     OR ( CreateDate BETWEEN @1stDay_StartExtract AND @LastDay_EndExtract ) 
     OR ( @1stDay_StartExtract BETWEEN CreateDate AND COALESCE(DeletedTime,'2100-12-31') ) --MI

To This:

SELECT V.vNodeId ,V.VehicleKey ,V.BusGrpId ,V.BusinessGroupKey ,V.CreateDate 'StartDate' ,V.DeletedTime
   FROM
    dbo.vw_xED_Test_Month V
WHERE ( CreateDate <= @1stDay_StartExtract AND DeletedTime IS NULL ) 
UNION
SELECT V.vNodeId ,V.VehicleKey ,V.BusGrpId ,V.BusinessGroupKey ,V.CreateDate 'StartDate' ,V.DeletedTime
   FROM
    dbo.vw_xED_Test_Month V
WHERE ( CreateDate BETWEEN @1stDay_StartExtract AND @LastDay_EndExtract AND DeletedTime IS NULL ) 
UNION
SELECT V.vNodeId ,V.VehicleKey ,V.BusGrpId ,V.BusinessGroupKey ,V.CreateDate 'StartDate' ,V.DeletedTime
   FROM
    dbo.vw_xED_Test_Month V
WHERE ( CreateDate <= @1stDay_StartExtract AND COALESCE(DeletedTime,'2100-12-31') BETWEEN @1stDay_StartExtract AND @LastDay_EndExtract )
UNION
SELECT V.vNodeId ,V.VehicleKey ,V.BusGrpId ,V.BusinessGroupKey ,V.CreateDate 'StartDate' ,V.DeletedTime
   FROM
    dbo.vw_xED_Test_Month V
WHERE ( CreateDate BETWEEN @1stDay_StartExtract AND @LastDay_EndExtract ) 
UNION
SELECT V.vNodeId ,V.VehicleKey ,V.BusGrpId ,V.BusinessGroupKey ,V.CreateDate 'StartDate' ,V.DeletedTime
   FROM
    dbo.vw_xED_Test_Month V
WHERE ( @1stDay_StartExtract BETWEEN CreateDate AND COALESCE(DeletedTime,'2100-12-31') ) 

NOTE: UNION to remove duplicates instead of UNION ALL to include duplicates.

Here is some good info related to breaking down complex queries.

It is also possible that replacing most or all of those COALESCEs with ISNULLs could improve performance.

Here is some good info related to COALESCE and ISNULL.

A quote from that article:

The ISNULL function and the COALESCE expression have a similar purpose but can behave differently. Because ISNULL is a function, it is evaluated only once. As described above, the input values for the COALESCE expression can be evaluated multiple times.

It also seems like you could simplify some things such as combining these two conditions:

( CreateDate <= @1stDay_StartExtract AND DeletedTime IS NULL ) 
     OR ( CreateDate BETWEEN @1stDay_StartExtract AND @LastDay_EndExtract AND DeletedTime IS NULL ) 

Into this one condition:

CreateDate <= @LastDay_EndExtract AND DeletedTime IS NULL