Home Dashboard Directory Help
Search

SQL Server 2014 New Cardinality Estimator and Ascending Key Columns by Milos Radivojevic


Status: 

Active


4
0
Sign in
to vote
Type: Suggestion
ID: 870089
Opened: 5/9/2014 4:13:50 AM
Access Restriction: Public
0
Workaround(s)
view

Description

New CE handles key ascending columns differently than the old one. In case of the range predicate with boundaries defined by two literals it seems (I did not found a confimation, but based on my measurements) that new CE expects (by guessing) about 9% of newly added rows (rows added to the table after the last statistics update). What is definitely true is that the new CE completely ignores values of literals, which can have significant impact to the performance for common used query patterns.

For instance, if we have a parent table with 11M rows where the last stats update was after the 10M rows, the last 1M rows does not exist for the statistics. Therefore when this column is referenced in another table the old CE estimates 1 by default, but by using TF 2389 or 2390 we can have good estimations. Let's compare estimations done by both CEs for common used queries (last 100 or 1000 items or something related to last week, day or hour...)

SELECT * FROM ChildTable WHERE ParentKey BETWEEN 10999000 AND 11000000 ORDER BY C2,C1 OPTION (RECOMPILE, QUERYTRACEON 2390);
--Estimated Rows: 303, Actual Rows: 301, Memory Grant: 1 MB

SELECT * FROM ChildTable WHERE ParentKey BETWEEN 10999000 AND 11000000 ORDER BY C2,C1 OPTION (RECOMPILE, QUERYTRACEON 2312);
--Estimated Rows: 270.000, Actual Rows: 301, Memory Grant: 110 MB

DECLARE @I AS INT = 11000000;
SELECT * FROM ChildTable WHERE ParentKey BETWEEN 10999000 AND @I ORDER BY C2,C1 OPTION (QUERYTRACEON 2390);
--Estimated Rows: 900, Actual Rows: 301, Memory Grant: 1.6 MB

DECLARE @I AS INT = 11000000;
SELECT * FROM ChildTable WHERE ParentKey BETWEEN 10999000 AND @I ORDER BY C2,C1 OPTION (QUERYTRACEON 2312);
--Estimated Rows: 492.950, Actual Rows: 301, Memory Grant: 5 GB!

In this case the estimations made by the old CE and TF 2390 were completely OK, new CE simply ignores literals.

Under the new compatibility level 120 we can enforce the old CE behavior by using TF 9481, but the functionality provided by TF 2389 and 2390 is not available anymore. At least not under these flags.


Thank you.
Milos Radivojevic
Details
Sign in to post a comment.
Posted by Microsoft on 5/16/2014 at 1:56 PM
Hi Milos, thank you for your feedback and for reporting the behavior. We will investigate and update this connect item when we have more information.
Thanks, Pooja Harjani, Sr. Program Manager, SQL Server.
Sign in to post a workaround.