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


Update statistics


Update statistics

Author
Message
Kishore-132325
Kishore-132325
Right there with Babe
Right there with Babe (750 reputation)Right there with Babe (750 reputation)Right there with Babe (750 reputation)Right there with Babe (750 reputation)Right there with Babe (750 reputation)Right there with Babe (750 reputation)Right there with Babe (750 reputation)Right there with Babe (750 reputation)

Group: General Forum Members
Points: 750 Visits: 112
hello - i have bi-monthly maintenance plan that re-builds all the indexes of my databases where fragementation > 5%. my indexes are in good shape with this frequency.
However, with the re-build of indexes, my statistics only for those indexes are updated when the indexes are rebuilt. Also, i have auto-stats and create stats on [ default options ].

But my other statistics are not getting updated.

Is is a good idea to run sp_updatestats for all my user dbs after the rebuild index job completes.
This will make sure all my other stats that need a update will also be updated and since my re-indexing job will update stats for those indexes, sp_updatestats will not touch them since they have just been rebuilt by the rebuild index job.

So, basically, 2 steps:
1. Run rebuild index job for all Dbs for frag > 5%
2. Run sp_updatestats for all dbs.

please suggest on the approach,.

Thanks
K
GilaMonster
GilaMonster
SSC Guru
SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)

Group: General Forum Members
Points: 215716 Visits: 46270
Don't use sp_updatestats, it does sampled updates (unless you tell it otherwise). If you are doing a maint plan to manage stats, run the stats updates with fullscan.

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


Kishore-132325
Kishore-132325
Right there with Babe
Right there with Babe (750 reputation)Right there with Babe (750 reputation)Right there with Babe (750 reputation)Right there with Babe (750 reputation)Right there with Babe (750 reputation)Right there with Babe (750 reputation)Right there with Babe (750 reputation)Right there with Babe (750 reputation)

Group: General Forum Members
Points: 750 Visits: 112
but UPDATE STATISTICS with FULL SCAN will scan for the entire table everytime and unnecessary update stats even if its not needed. i ran a samle update stats with full scan on few of my dbs and its taken a very long time [ 8-9 hours ] to complete, also its very resource intensive.
whereas when I run sp_updatestats, it works intelligently and just updates stats on those stats where its required, though it does for sample updates, but it coves more than 60% of my statistics.

Thoughts ?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)

Group: General Forum Members
Points: 215716 Visits: 46270
Kishore-132325 (1/7/2013)
but UPDATE STATISTICS with FULL SCAN will scan for the entire table everytime


Yup, that's what you need for accurate stats. You can run full scans on more important tables and sampled on less if you like. Not hard to add into a script if you know your tables well

and unnecessary update stats even if its not needed.


Then you can just do a check against sysindexes (yes, the deprecated one) for rowmodctr > 0 and only update those. That's all that sp_updatestats does.

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 Guru
SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)

Group: General Forum Members
Points: 95227 Visits: 33013
Kishore-132325 (1/7/2013)
but UPDATE STATISTICS with FULL SCAN will scan for the entire table everytime and unnecessary update stats even if its not needed. i ran a samle update stats with full scan on few of my dbs and its taken a very long time [ 8-9 hours ] to complete, also its very resource intensive.
whereas when I run sp_updatestats, it works intelligently and just updates stats on those stats where its required, though it does for sample updates, but it coves more than 60% of my statistics.

Thoughts ?


Just remember, the level of requirement for the sp_updatestats is just a single row being modified. It's not that sophisticated a piece of code. You could follow Gail's advice and use the same approach.

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