SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SP_UpdateStats


SP_UpdateStats

Author
Message
Divine Flame
Divine Flame
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: 2279 Visits: 2816
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 :-P) 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 pointCool.

Gail/Grant please help to clear this.


Sujeet Singh
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86490 Visits: 45240
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, MVP, M.Sc (Comp Sci)
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


Divine Flame
Divine Flame
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: 2279 Visits: 2816
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86490 Visits: 45240
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, MVP, M.Sc (Comp Sci)
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


GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86490 Visits: 45240
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, MVP, M.Sc (Comp Sci)
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


Grant Fritchey
Grant Fritchey
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39275 Visits: 32617
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 Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Divine Flame
Divine Flame
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: 2279 Visits: 2816
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86490 Visits: 45240
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, MVP, M.Sc (Comp Sci)
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


Grant Fritchey
Grant Fritchey
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39275 Visits: 32617
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 Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Divine Flame
Divine Flame
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: 2279 Visits: 2816
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
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