Updating Statistics

Question

Updating Statistics

Steve Jones - SSC Editor

SSC Guru

Points: 736254
More actions
March 13, 2014 at 8:38 pm

#74765

Comments posted to this topic are about the item Updating Statistics
March 14, 2014 at 3:33 am

This was removed by the editor as SPAM

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

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

free_mascot One Orange Chip Points: 27169 More actions · Answer 1

Enjoyed the question, thanks.

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

Richard Warr SSCertifiable Points: 6966 More actions · Answer 2

Stewart "Arturius" Campbell (3/14/2014)
Interesting question, thanks Steve.
Misread the question, thought it meant the highest level that a single command can be used...
ah well...

Same here :doze:

balde SSCommitted Points: 1594 More actions · Answer 3

UPDATE STATISTICS + sp_updatestats = 2 statements!

🙂

Ed Wagner SSC Guru Points: 287026 More actions · Answer 4

Good question, thanks. They may be different statements, but a single statement can accomplish any of them.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Umer Akram SSC-Insane Points: 22209 More actions · Answer 5

Umer Akram

SSC-Insane

Points: 22209

March 14, 2014 at 6:32 am

#1697113

nice question. thanks for sharing

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 6

Richard Warr (3/14/2014)
Stewart "Arturius" Campbell (3/14/2014)
Interesting question, thanks Steve.
Misread the question, thought it meant the highest level that a single command can be used...
ah well...
Same here :doze:

+1

Didn't get the intent of the question, so I choose database level.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

OCTom SSChampion Points: 11785 More actions · Answer 7

Koen Verbeeck (3/14/2014)
Richard Warr (3/14/2014)
Stewart "Arturius" Campbell (3/14/2014)
Interesting question, thanks Steve.
Misread the question, thought it meant the highest level that a single command can be used...
ah well...
Same here :doze:
+1
Didn't get the intent of the question, so I choose database level.

Yup. Same here. :ermm:

sestell1 SSChampion Points: 10230 More actions · Answer 8

OCTom (3/14/2014)
Koen Verbeeck (3/14/2014)
Richard Warr (3/14/2014)
Stewart "Arturius" Campbell (3/14/2014)
Interesting question, thanks Steve.
Misread the question, thought it meant the highest level that a single command can be used...
ah well...
Same here :doze:
+1
Didn't get the intent of the question, so I choose database level.
Yup. Same here. :ermm:

Arrrgggh.... me too. :crazy:

I have to stop answering these first thing in the morning. :ermm:

Dana Medley SSCertifiable Points: 6764 More actions · Answer 9

sestell1 (3/14/2014)
OCTom (3/14/2014)
Koen Verbeeck (3/14/2014)
Richard Warr (3/14/2014)
Stewart "Arturius" Campbell (3/14/2014)
Interesting question, thanks Steve.
Misread the question, thought it meant the highest level that a single command can be used...
ah well...
Same here :doze:
+1
Didn't get the intent of the question, so I choose database level.
Yup. Same here. :ermm:
Arrrgggh.... me too. :crazy:
I have to stop answering these first thing in the morning. :ermm:

Almost happened to me and then I second guessed myself. Thanks for the question Steve.

Everything is awesome!

SQLWinther SSCertifiable Points: 6294 More actions · Answer 10

Well, it depends:

sp_updatestats updates only the statistics that require updating based on the rowmodctr information in the sys.sysindexes catalog view, thus avoiding unnecessary updates of statistics on unchanged rows.

That is, if the rowmodctr is too low, the statistics is not updated.

Anyway, if you experience problem with statistics, you should try to add the traceflags in SQL Server 2008R2 (sp2) or later:

2371, Changes the threashold for auto update stats for large tables

2389, Changes the behaviour of known incrementing columns like IDENTITY

2390, Changes the behaviour of columns with incrementing values like a datetime column with incrementing values.

(Or wait 'til SQL server 2014)

/Håkan Winther
MCITP:Database Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform

TomThomson SSC Guru Points: 104773 More actions · Answer 11

Nice question, I had forgotten that sp_updatestats existed so I got it wrong. Maybe now I'll remember 😉

Tom

PChiragS SSCarpal Tunnel Points: 4965 More actions · Answer 12

PChiragS

SSCarpal Tunnel

Points: 4965

March 14, 2014 at 2:21 pm

#1697311

nice explanation steve.

thanks.

Hany Helmy SSChampion Points: 13921 More actions · Answer 13

sestell1 (3/14/2014)
OCTom (3/14/2014)
Koen Verbeeck (3/14/2014)
Richard Warr (3/14/2014)
Stewart "Arturius" Campbell (3/14/2014)
Interesting question, thanks Steve.
Misread the question, thought it meant the highest level that a single command can be used...
ah well...
Same here :doze:
+1
Didn't get the intent of the question, so I choose database level.
Yup. Same here. :ermm:
Arrrgggh.... me too. :crazy:
I have to stop answering these first thing in the morning. :ermm:

- 1 :w00t:

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant