I’ve been working on writing a new Microsoft white paper since January covering the main changes made in the SQL Server 2014 Cardinality Estimator, and happy to announce that it was just published:
Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator
A big thanks to the contributor and reviewer team – including Yi Fang (Microsoft), Shep Sheppard (Microsoft), Mike Weiner (Microsoft), Paul White (SQL Kiwi Limited), Barbara Kess (Microsoft), Jimmy May (Microsoft), Sanjay Mishra (Microsoft), Vassilis Papadimos (Microsoft) and Jack Li (Microsoft). And thanks to Kathy MacDonald (Microsoft) for managing the whole process!
2 Responses to Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator
Hi Joe – thanks for this great CE white paper. I just wanted to check a couple of things:
In the section under ‘New CE Troubleshooting Methods’, ‘Changing the Database Compatibility Level’ it states ‘You enable the new CE by reverting to a database compatibility level below 120.’ I’m assuming the ‘enable’ should be ‘disable’.
And also in the section mentioning ‘Table Variables’ it states ‘Unless a statement-level recompilation occurs, table variables assume a fixed, single-row cardinality estimate for filter and row predicates’. Should that very last bit read ‘filter and join predicates’ ?
Great reference when we’re switching over to SQL 2014. Thanks again.
Thanks for the feedback!
And you are correct – someone emailed me regarding the “enable” / “disable” correction and I’ve submitted it to MSFT (might take until May for that change to go in, but thank you for letting me know).
And the second correction is one that someone hasn’t caught – so good catch! That should indeed read “filter and join predicates”.
Thanks again for letting me know!
Best Regards,
Joe