Estimated vs Actual Number of Rows

Question

I have 5 tables that contain data split by quarter each table has a similar structure to the following:

ID_Column bigint PK,
EventType int,
DateStamp datetime PK
CustomerId int,
ARG_1 nvarchar(32),
ARG_2 nvarchar(32)

There is also a CHECK constraint on each table to ensure that the DateStamp is within the specified quarter. I have then created a View across these tables which I am querying against.

I need to execute several queries which aggregate the date by week, month, 3 months and 12 months. When I look at the Query Plan for my query it is querying all tables regardless of the filter on the DateStamp column.

I have a controller Stored Proc which in turns calls a Processing stored procedure. The processing stored procedure accepts the start and date as parameters, assigns them to local variables within the procedure and runs the query.

The query is similar to the following:

Select v.ARG_1,
       t.SomeColumn,
       Count(Distinct(v.Customer_Id))

From  dbo.My_View v
Join  dbo.AnotherTable t on v.ARG_2 = v.PKColumn
Where v.DateStamp >= @StartDate
And   v.DateStamp <= @EndDate
And   v.EventType = 1
And   t.SomeColumn = 'A_VALUE'
GROUP BY v.ARG_1, t.SomeColummn

I have also noticed that the estimated row count is wildly inaccurate (Estimated rows: 13 million vs Actual rows : 170 million). This confuses me as the statistics are up to date and I have rebuilt the indexes.

The performance of the query is acceptable for my purposes but I am wondering what could be the reason for these oddities?

EDIT: I am running SQL Server 2012 Standard Edition.

The anonymized XML Query plan can be found here

Answer 1

Up to date statistics does not necessarily imply perfect estimation. If you try to represent a range of 170 million values with 200 samples, even a small skew can throw you off by a factor of 10.

Here is a hint: time series are almost always accessed by time. Very likely DateStamp should be clustered key. For more detailes, post the exact DDL and actual execution plan (XML, not picture).

Answer 2

Barry, Check out the statistics histograms on the indexes being used. This great book (free from RedGate) has a section on histograms: http://www.red-gate.com/products/sql-development/sql-prompt/entrypage/sql-query-optimizer-ebook2

You could have skewed data that is messing up the estimates. This has happened to me in the past: Poor query performance, though in my case it ended up being a parameter sniffing issue.

If you check the histogram and it doesn't look skewed, you will need to analyze the execution plan XML and see if you find cases where ParameterCompiledValue is not equal to ParameterRuntimeValue. The author of the book above has an article on this: http://www.benjaminnevarez.com/2010/06/the-parameter-sniffing-problem/

As was mentioned above as well, we would really need to see the full query and execution plan (and possibly more) to determine the issue. If you are willing to share all this information this question could end up being helpful to other people having similar issues.