I am trying to fix a performance problem with one of our reporting queries on SQL Server 2008 R2.
I have included the part of the query that is causing low estimate. This part is further joined with other tables. Since the estimate for this one is so low, further joins end up being nested loop and causing the query to run forever.
select n.Transactionid from nath n WHERE StatusId = 3 and Date IS NOT NULL and NOT EXISTS (SELECT 1 FROM nath WHERE Transactionid= n.Transactionid AND StatusId = 3 AND HistoryId < n.HistoryId)
The estimate for the hash match is only 1.17 but in reality there are 550K records coming out. Statistics have been updated with full scan.
I ran the exact same query on one of our SQL Server 2014 instances and it produced better results; the estimate was 557K on the hash match operator. I then tried trace flag 9481 to force the old cardinality estimator on 2014 and the estimates were back to 1. So I think the issue is something to do with old CE estimating self joins.
I tried trace flag 4199 on SQL Server 2008 R2 but that did not help.
Actual execution plan
I didn't want the actual tables names to be visible, so I created similar tables with fewer columns and different table and column names. The estimates are slightly off than mentioned above but the bigger problem still persists.
SQL Server 2014 with TF 9481
(I don't have a SQL Server 2008 R2 test environment):
SQL Server 2014
Please let me know if there is anyway to fix this wrong estimate.
The issue can be simulated with the below script:
create table nat ( c1 int identity(1,1) primary key,c2 int) declare @a int=1 declare @b int =1 while @a<10000 begin set @b=1 while @b<=5 begin insert into nat select @a set @b=@b+1 end set @a=@a+1 end select * from nat a where not exists (select 1 from nat b where b.c2=a.c2 and b.c1<a.c1) OPTION(QUERYTRACEON 9481); -- estimated no of rows from hash match 1 select * from nat a where not exists (select 1 from nat b where b.c2=a.c2 and b.c1<a.c1) -- estimated no of rows from hash match 49995
I have done some testing with the above query on SQL Server 2012 and I am not able to force the new cardinality estimator behaviour with trace flag 4199.
Current testing results:
- SQL 2014 - High estimates on Hash match operator
- SQL 2014 with TF 9481 - Low(1) estimate
- SQL 2012 - Low(1) estimate
- SQL 2012 with TF 4199 - Still low estimates
How is it that I am able to replicate old cardinality behavior on 2014, but not able to get new CE estimates on 2012?
Is it that the change is not part of trace flag 4199 and only came about in 2014?
NOT EXISTS to a left join seems to produce a better estimate.