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