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: Yesterday @ 8:38 AM
Points: 1,828, Visits: 1,362
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: Thursday, July 3, 2014 2:45 AM
Points: 2,531, Visits: 536
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
Post #1551247
Posted Friday, March 14, 2014 12:47 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 7:29 PM
Points: 8,562, Visits: 9,063
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, July 7, 2014 9:38 AM
Points: 1,780, Visits: 582
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: Yesterday @ 4:38 AM
Points: 1,139, Visits: 520
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: Yesterday @ 4:38 AM
Points: 1,139, Visits: 520
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 Monday, March 17, 2014 9:28 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, July 24, 2014 7:11 AM
Points: 2,382, Visits: 177
Thanks for the question
Post #1551798
Posted Wednesday, March 19, 2014 9:24 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 7:33 PM
Points: 14, Visits: 222
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
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 7:45 PM
Points: 23, Visits: 162
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: Monday, May 19, 2014 2:24 AM
Points: 952, Visits: 1,156
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