Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Updating Statistics


Updating Statistics

Author
Message
Dana Medley
Dana Medley
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2316 Visits: 1691
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!
hakan.winther
hakan.winther
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2702 Visits: 601
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
MCITPBigGrinatabase Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10765 Visits: 12019
Nice question, I had forgotten that sp_updatestats existed so I got it wrong. Maybe now I'll remember ;-)

Tom

pchirags
pchirags
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2170 Visits: 613
nice explanation steve.
thanks.
Hany Helmy
Hany Helmy
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2528 Visits: 1108
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
Hany Helmy
Hany Helmy
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2528 Visits: 1108
TomThomson (3/14/2014)
Nice question, I had forgotten that sp_updatestats existed so I got it wrong. Maybe now I'll remember ;-)

How did I miss that?! the database level.Hehe
ClaireM
ClaireM
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 253
Oh dear. Would have gone for the correct answer if I hadn't paused and asked myself what this question really wanted. My interpretation was that you can't update statistics on multiple tables using just one statement which left only the database level.
I'll avoid any questions in the future that make me wonder what they are asking for.
julienchappel 38298
julienchappel 38298
SSC Veteran
SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)

Group: General Forum Members
Points: 246 Visits: 230
The database leve and sp_udatestats is the correct answer. Please read carefully your own question.
kapfundestanley
kapfundestanley
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1016 Visits: 1224
Easy one for me.thanks

“When I hear somebody sigh, ‘Life is hard,’ I am always tempted to ask, ‘Compared to what?’” - Sydney Harris
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search