Estimated Number of Rows = 1 in Query Plan operator

Last Post 19 Feb 2014 11:31 AM by gunneyk. 1 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages Resolved
rerichards
New Member
New Member

--
19 Nov 2013 08:07 PM
I am running an Adhoc query in SSMS on SQL 2008 R2 that looks like this:

SELECT L.ListName, COUNT(*) AS ListCount, MAX(LD.LogTime) AS MaxLogTime
FROM dbo.LogData LD WITH(NOLOCK)
JOIN dbo.Lists L WITH(NOLOCK)
ON LD.ListID = L.ListID
WHERE L.ListName in ('CHGY','TLMK')
and LD.LogTime > dateadd(mi, -5, getdate())
GROUP BY L.ListName

There is an INDEX SEEK taking place on an index in dbo.LogData with the Predicate being LD.LogTime > dateadd(mi, -5, getdate()). The Actual Number of Rows is 75,691 and the Estimated Number of Rows = 1. This is fed into a NESTED LOOP, likely because of the Estimated Number of Rows = 1. The plan is thus, a disaster. It takes about 30 seconds to complete.


However, if I change the predicate to use a variable in place of the function, like the following, the Actual Number of Rows (are once again) 75,691 and the Estimated Number of Rows = 68,264. The same index is used (an INDEX SEEK) with the predicate being LD.LogTime > @Date, which flows into a HASH MATCH join. This version of the Adhoc query takes about 2 seconds to complete.

DECLARE @Date datetime
SELECT @Date = dateadd(mi, -5, getdate())

SELECT L.ListName, COUNT(*) AS ListCount, MAX(LD.LogTime) AS MaxLogTime
FROM dbo.LogData LD WITH(NOLOCK)
JOIN dbo.Lists L WITH(NOLOCK)
ON LD.ListID = L.ListID
WHERE L.ListName in ('CHGY','TLMK')
and LD.LogTime > @Date
GROUP BY L.ListName

Can somebody explain to me why the first query that uses the DATEADD function leads to Estimated Number of Rows = 1?
gunneyk
New Member
New Member

--
19 Feb 2014 11:31 AM
I know this is pretty old thread but just for kicks the optimizer won't use (or can't get) the actual value of a function that is used in a WHERE clause so it takes a guess. Why it chose 1 in this case I don't really know but with a guess you take your chances. If you use a parameter the value is known to the optimizer at the time it makes the plan and uses the stats instead of guessing. Under some conditions variables can also be sniffed to get a value to compare against the statistics and in this case that is what it looks like happened.
You are not authorized to post a reply.

Acceptable Use Policy