Optimizer gets the wrong number of estimated rows even with updated stats [closed]

I have a procedure running in SQL Server 2008 that contains a query similar to the one below;

SELECT 
/* various columns from table 1 and table 2 */
FROM dbo.tbl_1 AS a
INNER JOIN dbo.tbl_2 AS b ON a.AccountNumber = b.AccountNumber
WHERE a.RecordActive = 1 AND b.RecordActive = 1;

tbl_1 and tbl_2 are both partitioned views that equally contain 3 yearly base tables (2014-2016), partitioned by month and unioned in the view. The tables are large and 2016’s data contains approximately 34,000,000 records for the first 3 months alone.

The query normally takes 10 minutes to run, when the right plan is chosen. However, recently it has been taken well over an hour.

When I check the estimated plan, it uses an index seek using a filtered index WHERE RecordActive = 1 for the table containing 2016’s data. The estimated rows is 18,000, but when the query executes the actual number of rows is closer to 2,000,000 – a clustered index scan probably being a better choice.

This is the case despite updating the statistics for the filtered index with a full scan. As the predicate on the tables for column RecordActive is a BIT datatype yielding either 1 or 0, the histogram for the filtered statistic only contains one entry for the value 1, which shows the number of rows equaling that value is 2,000,0000.

I have turned on OPTION(RECOMPILE, QUERYTRACEON 3604, QUERYTRACEON 9204) and I can see that the filtered stats have been considered. So why then does it get the estimate so badly wrong?

Please help, as I am running out of ideas and would like to know what is really happening under the hood!

P.S. If pictures would help explain the problem better please let me know, this is my first post on the site so please excuse my bad etiquette.

Have a look at this DBCC UPDATEUSAGE command:

DBCC UPDATEUSAGE(N'db_name',N'dbo.table_name') WITH COUNT_ROWS