SP_UpdateStats

Question

SP_UpdateStats

Divine Flame

SSCoach

Points: 15943
More actions
January 12, 2012 at 8:00 am

#251280

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 point:cool:.
Gail/Grant please help to clear this.
Sujeet Singh

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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 SSCoach Points: 15943 More actions · Answer 2

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

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 SSC Guru Points: 398890 More actions · Answer 5

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

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Divine Flame SSCoach Points: 15943 More actions · Answer 6

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

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 SSC Guru Points: 398890 More actions · Answer 8

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

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Divine Flame SSCoach Points: 15943 More actions · Answer 9

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

kapil_kk SSC-Insane Points: 21316 More actions · Answer 10

excelent articel..

thanks Grant 🙂

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/