Automatic update of outdated statistics

  • Uwe Ricken

    Hall of Fame

    Points: 3098

    Comments posted to this topic are about the item Automatic update of outdated statistics

    Microsoft Certified Master: SQL Server 2008
    MVP - Data Platform (2013 - ...)
    my blog: http://www.sqlmaster.de (german only!)

  • HappyGeek

    SSCoach

    Points: 18670

    Very good question, got it wrong as so far everyone has, but a little confused over the answer:

    The statistics for the non unique index will be updated because it is outdated,

    The statistics for the non unique index will not be updated

    The explanation seems to make it clear by suggesting the statistics for the unique index will not be updated but am I missing something?

    ...

  • SADSAC

    SSCommitted

    Points: 1953

    Good question, Uwe, thanks.

    enjoyed puzzling it out.

    good explanation, too

    but the answer appears to be a contradiction of terms:

    The statistics for the non unique index will be updated because it is outdated,

    The statistics for the non unique index will not be updated

  • Uwe Ricken

    Hall of Fame

    Points: 3098

    Hi Folks,

    There is a mistake in the correct answers. I have informed Steve to fix it.

    Correct answers must be:

    - the NON UNIQUE Index stats will be updaten

    - the UNIQUE Index stats will NOT be updated

    I am so sorry for the inconvenience, Uwe

    I promise to check the answers more precise the next time!!!!!

    I've blogged about this topic (GERMAN) here:

    http://www.db-berater.de/2016/02/auto_update_statistics-wird-nicht-immer-ausgefhrt/

    If you are not familiar with the german language, do not hesitate to visit this article.

    The demo and depiction is self-explanatory.

    All the best to you, Uwe

    Microsoft Certified Master: SQL Server 2008
    MVP - Data Platform (2013 - ...)
    my blog: http://www.sqlmaster.de (german only!)

  • Stuart Davies

    SSCoach

    Points: 18874

    Uwe Ricken (2/23/2016)


    Hi Folks,

    There is a mistake in the correct answers. I have informed Steve to fix it.

    Correct answers must be:

    - the NON UNIQUE Index stats will be updaten

    - the UNIQUE Index stats will NOT be updated

    I am so sorry for the inconvenience, Uwe

    I promise to check the answers more precise the next time!!!!!

    I've blogged about this topic (GERMAN) here:

    http://www.db-berater.de/2016/02/auto_update_statistics-wird-nicht-immer-ausgefhrt/

    If you are not familiar with the german language, do not hesitate to visit this article.

    The demo and depiction is self-explanatory.

    All the best to you, Uwe

    OK thanks - I was a bit confused too.

    -------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a questionThere are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan I would never join a club that would allow me as a member - Groucho Marx

  • Igor Micev

    SSC-Dedicated

    Points: 33109

    Great QotD like a mini article.

    I only knew that the unique index won't be updated, so a bit lucky.

    Thanks.

    Igor Micev,
    My blog: www.igormicev.com

  • Kenny Jozi

    SSCrazy

    Points: 2004

    Great Question .

    SQL 2000/2005/2008/2012 DBA - MCTS/MCITP

  • milos.minarik

    SSC Enthusiast

    Points: 137

    Great question. However, I have to point out that the example behaves differently on SQL 2014 (e.g. the traceflag 9204 have been replaced by 2363). Seems I have some reading to do regarding the changes in SQL 2014 🙂

  • KitKaterina

    Ten Centuries

    Points: 1012

    Just ran this on SQL 2014 and BOTH statistics were updated.....

  • ako58

    Hall of Fame

    Points: 3263

    modification_counter

    bigint

    Total number of modifications for the leading statistics column (the column on which the histogram is built) since the last time statistics were updated.

  • milos.minarik

    SSC Enthusiast

    Points: 137

    In MSSQL 2014, you can set the compatibility level to 110, then it will work as expected in the example.

    ALTER DATABASE [db_name] SET COMPATIBILITY_LEVEL = 110

    GO

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 716562

    The answers have been updated, and I'm awarding back points to this time.

    Steve

  • HappyGeek

    SSCoach

    Points: 18670

    Uwe Ricken (2/23/2016)


    Hi Folks,

    There is a mistake in the correct answers. I have informed Steve to fix it.

    Correct answers must be:

    - the NON UNIQUE Index stats will be updaten

    - the UNIQUE Index stats will NOT be updated

    I am so sorry for the inconvenience, Uwe

    I promise to check the answers more precise the next time!!!!!

    I've blogged about this topic (GERMAN) here:

    http://www.db-berater.de/2016/02/auto_update_statistics-wird-nicht-immer-ausgefhrt/

    If you are not familiar with the german language, do not hesitate to visit this article.

    The demo and depiction is self-explanatory.

    All the best to you, Uwe

    Thanks for the clarification, did actually test this on SQL 2014 Dev Ed and both stats updated, so got even more confused but god question to ask and issue well raised!

    ...

  • Ed Wagner

    SSC Guru

    Points: 286960

    Like Igor said, great mini-article for a QOTD. I got the one right answer and was lucky on the other. I learned something new from it. Thanks, Uwe.

  • TomThomson

    SSC Guru

    Points: 104772

    Well, I got it wrong. But my answer was just a guess, based on how I expect things to work, not a real answer based on actual measurement.

    But my sql server 2014 instance also got it wrong when I used it to check this, because I expect things to work they way I think they will; so I'm not too worried that my guess was wrong.

    And if I remember rightly flag 9204 stopped working anyway, so should it be in QotD without the version of SQL Server being explicitly stated?

    And I believe flag 9292 is not documented, so shouldn't be relied on - so maybe the explanation or the question ought include a warning on that.

    Tom

Viewing 15 posts - 1 through 15 (of 19 total)

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