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»»

SP_UpdateStats Expand / Collapse
Author
Message
Posted Thursday, January 12, 2012 8:00 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 8:34 AM
Points: 1,307, Visits: 2,476
Hey Guys,

I read this nice article from Grant on Simple Talk:

http://www.simple-talk.com/sql/database-administration/grant-fritcheys-sql-server-howlers/

In this article, Grant says that executing sp_updatestats will update the statistics again for those indexes also which have been Rebuild.

Earlier, I believed (I think I read somewhere, don't remember where ) that statistics is updated only for those that need it & sp_updatestats doesn't update it for the recently rebuilded indexes.

I am not suspecting on Grant, just want to make my assumptions clear on this point.

Gail/Grant please help to clear this.



Sujeet Singh
Post #1234842
Posted Thursday, January 12, 2012 8:06 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:41 AM
Points: 42,442, Visits: 35,496
sp_update stats has threshold of rows that must have been changed since the last stats update for it to update them again. As Grant says in his article

then, you run sp_updatestats… Which will go through the whole table, determining if any data has changed and the statistics need to be updated. “Any data” reads, one row. So if even a single row has been modified since the last time the statistics were updated


Yes, that threshold is a single row.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1234849
Posted Thursday, January 12, 2012 8:15 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 8:34 AM
Points: 1,307, Visits: 2,476
GilaMonster (1/12/2012)
sp_update stats has threshold of rows that must have been changed since the last stats update for it to update them again. As Grant says in his article

then, you run sp_updatestats… Which will go through the whole table, determining if any data has changed and the statistics need to be updated. “Any data” reads, one row. So if even a single row has been modified since the last time the statistics were updated


Yes, that threshold is a single row.


Thanks Gail, so it makes sense to run the maintenance in this order:

1. sp_updatestats
2. ALTER INDEX...REBUILD/REORGANIZE

Thanks again



Sujeet Singh
Post #1234864
Posted Thursday, January 12, 2012 8:17 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:41 AM
Points: 42,442, Visits: 35,496
p.s. The maintenance plan 'Update Statistics' task doesn't use sp_updatestats. It runs an explicit UPDATE STATISTICS against every table, so no checks at all for whether or not even a single row has changed.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1234865
Posted Thursday, January 12, 2012 8:17 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:41 AM
Points: 42,442, Visits: 35,496
Divine Flame (1/12/2012)
Thanks Gail, so it makes sense to run the maintenance in this order:

1. sp_updatestats
2. ALTER INDEX...REBUILD/REORGANIZE

Thanks again


No, it doesn't. Why update statistics that are about to be updated?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1234866
Posted Thursday, January 12, 2012 11:13 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 12:12 PM
Points: 15,517, Visits: 27,896
I wouldn't trust anything that guys says. He's a notorious crank.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1235132
Posted Thursday, January 12, 2012 4:23 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 8:34 AM
Points: 1,307, Visits: 2,476
Grant Fritchey (1/12/2012)
I wouldn't trust anything that guys says. He's a notorious crank.


lol...

No, it doesn't. Why update statistics that are about to be updated?


So how should I go about updating the statistics Gail , which order to folllow? Is there any guideline ?



Sujeet Singh
Post #1235302
Posted Thursday, January 12, 2012 4:26 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:41 AM
Points: 42,442, Visits: 35,496
How about only updating ones that haven't just been updated (by the index rebuild)? Yes, it's more work than blanket updating everything (maintenance plan) or updating with sampled if one row has changed (as sp_update stats does), but no free lunches here.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1235304
Posted Thursday, January 12, 2012 4:28 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 12:12 PM
Points: 15,517, Visits: 27,896
If you're going to target the updates, I'd rebuild indexes that need it, then defrag those that need it, then update stats for those that were not rebuilt. But that requires you to take direct control. If you were using a Maintenance Plan, I'd reverse it and update stats first, then rebuild, then defrag. While you'll be updating some stats twice, at least you won't be stepping on clean updates.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1235305
Posted Thursday, January 12, 2012 4:31 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 8:34 AM
Points: 1,307, Visits: 2,476
GilaMonster (1/12/2012)
How about only updating ones that haven't just been updated (by the index rebuild)? Yes, it's more work than blanket updating everything (maintenance plan) or updating with sampled if one row has changed (as sp_update stats does), but no free lunches here.


OK, I got it. Thanks a lot Grant for writing the article so that I got the better idea about sp_updatestas & thanks Gail for making it simple & clear .



Sujeet Singh
Post #1235306
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse