Cardinality Estimator – What’s new in SQL Server 2014

  • Alessandro Mortola

    Say Hey Kid

    Points: 675

    Comments posted to this topic are about the item Cardinality Estimator – What’s new in SQL Server 2014

  • spaghettidba

    SSC Guru

    Points: 105661

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

  • Alessandro Mortola

    Say Hey Kid

    Points: 675

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

  • mister.magoo

    SSC-Forever

    Points: 47068

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

    MM



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

  • 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]

  • Alan Burstein

    SSC Guru

    Points: 61026

    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!

    -- Alan Burstein


    Helpful links:Best practices for getting help on SQLServerCentral -- Jeff ModenHow to Post Performance Problems -- Gail ShawNasty fast set-based string manipulation functions:For splitting strings try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL Server 2012+)To split strings based on patterns try PatternSplitCMNeed to clean or transform a string? try NGrams, PatExclude8K, PatReplace8K, DigitsOnlyEE, or Translate8KI 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

    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: 27727

    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: 1279

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

  • Alessandro Mortola

    Say Hey Kid

    Points: 675

    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: 513

    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 - SQL Server
    [MCP] [MCITP]

  • Viewing 10 posts - 1 through 10 (of 10 total)

    You must be logged in to reply to this topic. Login to reply