February 23, 2016 at 10:20 pm
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!)
February 23, 2016 at 10:42 pm
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?
...
February 23, 2016 at 10:46 pm
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
February 23, 2016 at 11:30 pm
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!)
February 24, 2016 at 12:38 am
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 question
There 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
February 24, 2016 at 12:58 am
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
February 24, 2016 at 1:08 am
Great Question .
SQL 2000/2005/2008/2012 DBA - MCTS/MCITP
February 24, 2016 at 3:02 am
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 🙂
February 24, 2016 at 4:38 am
Just ran this on SQL 2014 and BOTH statistics were updated.....
February 24, 2016 at 6:52 am
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.
February 24, 2016 at 6:59 am
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
February 24, 2016 at 8:44 am
The answers have been updated, and I'm awarding back points to this time.
Steve
February 24, 2016 at 11:17 am
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!
...
February 25, 2016 at 6:45 am
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.
February 26, 2016 at 10:35 am
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 18 total)
You must be logged in to reply to this topic. Login to reply