Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Update statistics


Update statistics

Author
Message
Kishore-132325
Kishore-132325
SSC Veteran
SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)

Group: General Forum Members
Points: 290 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-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47435 Visits: 44405
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
SSC Veteran
SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)

Group: General Forum Members
Points: 290 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-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47435 Visits: 44405
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
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17653 Visits: 32273
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