Cardinality Estimator – What’s new in SQL Server 2014

Question

Post reply

Cardinality Estimator – What’s new in SQL Server 2014

Alessandro Mortola

SSC Eights!

Points: 904
More actions
April 8, 2015 at 10:10 pm

#306728

Comments posted to this topic are about the item Cardinality Estimator – What’s new in SQL Server 2014
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

Alessandro, thank you for your thorough and accurate article. I look forward to reading more from you.

-- Gianluca Sartori

Alessandro Mortola SSC Eights! Points: 904 More actions · Answer 2

Thanks Gianluca, I hope to meet you at SqlSaturday in Torino on 23th May

mister.magoo SSC-Forever Points: 47174 More actions · Answer 3

Excellent article. Well written, with good examples. Thanks.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Alan Burstein SSC Guru Points: 61141 More actions · Answer 4

Excellent article. 5 stars from me. I have not seen an article that discusses the new 2014 Cardinality Estimator in such detail. Well done sir!

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

robert.diley SSC Eights! Points: 860 More actions · Answer 5

A very fine article. I have had little success finding any detail on the algorithms used in optimization. The information provided was most welcome as would be any additional detail. Five stars!

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 6

A nice article indeed, Alessandro!

It hit all the major points and required a significantly smaller investment of time and coffee than the white paper at https://msdn.microsoft.com/en-us/library/dn673537.aspx, which coincidentally enough I just read yesterday.

It's a good white paper, and has information not included here (you would hope so, since it's 43 pages long) but this article is a much nicer introduction to some of the key changes.

Cheers!

John N Hick Ten Centuries Points: 1320 More actions · Answer 7

An impressive article - worth the 5 stars I gave it but, where did you find out about the S1 formula?

Alessandro Mortola SSC Eights! Points: 904 More actions · Answer 8

Hi John,

excuse me if I have misunderstood your question.

Actually S1 is not a formula, but it is the selectivity of the first predicate and it is defined as “Number of Selected Records” divided by “Number of all records of the table”.

If you mean the formula used by the old CE or the Exponential back-off formula you can dig deeper reading the above mentioned white paper.

Alessandro Alpi Mr or Mrs. 500 Points: 515 More actions · Answer 9

Thank you Alessandro for this awesome article.

Now I'm stucked with SQL Server 2012 CE, but I'll use the new CE in few months.

Then I will contact you 😉

Looking forward to meet you in Torino too!

ciao

Alessandro Alpi
CTO Engage IT Services S.r.l.
DBA | Team Leader
Microsoft MVP - Data platform
[MCP] [MCITP]