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

Update statistics Expand / Collapse
Author
Message
Posted Monday, January 7, 2013 8:08 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, January 28, 2013 7:46 AM
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
Post #1403634
Posted Monday, January 7, 2013 8:14 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 @ 10:25 AM
Points: 40,385, Visits: 36,827
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 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 #1403642
Posted Monday, January 7, 2013 9:45 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, January 28, 2013 7:46 AM
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 ?
Post #1403713
Posted Monday, January 7, 2013 10:31 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 @ 10:25 AM
Points: 40,385, Visits: 36,827
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 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 #1403744
Posted Wednesday, January 9, 2013 4:50 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:38 PM
Points: 13,999, Visits: 28,378
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
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 #1404683
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse