Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Updating Statistics Expand / Collapse
Author
Message
Posted Friday, March 14, 2014 7:32 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, November 21, 2014 1:23 PM
Points: 1,951, Visits: 1,473
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


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


Yup. Same here.



Arrrgggh.... me too.
I have to stop answering these first thing in the morning.

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




Everything is awesome!
Post #1551154
Posted Friday, March 14, 2014 10:35 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, November 12, 2014 2:56 AM
Points: 2,624, Visits: 581
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
Post #1551247
Posted Friday, March 14, 2014 12:47 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:25 PM
Points: 7,868, Visits: 9,607
Nice question, I had forgotten that sp_updatestats existed so I got it wrong. Maybe now I'll remember

Tom
Post #1551303
Posted Friday, March 14, 2014 2:21 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, September 15, 2014 11:49 AM
Points: 1,848, Visits: 587
nice explanation steve.
thanks.
Post #1551358
Posted Sunday, March 16, 2014 2:12 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 4:06 AM
Points: 1,392, Visits: 665
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


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


Yup. Same here.



Arrrgggh.... me too.
I have to stop answering these first thing in the morning.

- 1
Post #1551544
Posted Sunday, March 16, 2014 2:13 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 4:06 AM
Points: 1,392, Visits: 665
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.
Post #1551545
Posted Wednesday, March 19, 2014 9:24 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 2:24 PM
Points: 14, Visits: 226
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.
Post #1552676
Posted Thursday, March 27, 2014 7:33 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, November 9, 2014 5:47 PM
Points: 33, Visits: 165
The database leve and sp_udatestats is the correct answer. Please read carefully your own question.
Post #1555732
Posted Wednesday, April 9, 2014 2:36 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 3:41 AM
Points: 968, Visits: 1,172
Easy one for me.thanks

“When I hear somebody sigh, ‘Life is hard,’ I am always tempted to ask, ‘Compared to what?’” - Sydney Harris
Post #1559829
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse