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


sp_updatestats and the default sampling rate


sp_updatestats and the default sampling rate

Author
Message
inHouseDBA
inHouseDBA
SSC Veteran
SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)

Group: General Forum Members
Points: 212 Visits: 191
Using SQL Server 2008R2
As my Production database approaching 500GB and potentially can grow up to 1TB in 2 years, issue with sp_updatestats (after re-indexing part of maint step) using the default sampling rate (which potentially can skew the performance) bothers me.
It has been a discussion earlier at that link: http://www.sqlservercentral.com/Forums/Topic1310877-146-2.aspx,
but I still confused about using sp_updatestats

Currently I am performing the following steps during weekend maintenance:

1. ALTER Database MyDB
SET RECOVERY Simple,
AUTO_CREATE_STATISTICS OFF,
AUTO_UPDATE_STATISTICS OFF

2. My index maintenance routine based on the following criteria:
Currently I Reindex Clustered and Non-Clustered Indexes when avg_page_space_used_in_percent < 75 and avg_fragmentation_in_percent > 10 and page_count > 500.
Of those selected, if Fragmentation <=30, than I reorganize those Indexes. If Fragmentation > 30, than I rebuild those Indexes.
So at the end of the Reindex Maint I have Non-Clustered and Clustered Indexes either Rebuilt or Reorganized.

3. Currently I am running the Update Statistics on a whole database after previous reindex step:
sp_updatestats

Since sp_updatestats updates statistics by using the default sampling rate, it possibly can deteriorate all my indexes after running reindex routine.

3A. So I was thinking about “… update statistics for all tables which are defragged and don't run update statistics for all tables which are REBUILD”
http://sqlserverpedia.com/wiki/Updating_Statistics

So here is my logic for performing routine in 3A
Select indexes which were "REORGANIZE"d (no statistics update) during reindex maintenance along with other indexes, where statistics were either not updated for the last, say, few weeks and run the following:
UPDATE STATISTICS Table_Name (IndexName) WITH FULLSCAN, NORECOMPUTE against indexes selected above.

By running above I will be able to update statistics without running sp_updatestats


4. ALTER Database MyDB
SET RECOVERY Full,
AUTO_CREATE_STATISTICS ON,
AUTO_UPDATE_STATISTICS ON

Please let me know if you have any comments, suggestions, recommendations on step 3A.

It has been a suggestion earlier to run:
USE dbname;
EXEC sys.sp_MSforeachtable
@command1 = N'UPDATE STATISTICS ? WITH FULLSCAN, COLUMNS;';

But unfortunately it takes way to long on my Production Database, given the time frame I have for the weekend maintenance.
Igor Micev
Igor Micev
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10296 Visits: 5157
Hi,

sp_updatestats makes the statistics update on sampling max 20% of the data. For big tables AUTO_UPDATE_STATISTICS *does not* function well. In that case you'll need to enable trace flag 2371, but it will withdraw more recompilation of queries (you should experiment with this trace flag, the public thinking is positive on it). Additionally your database is big, and this flag could probably be of asset for your database.

Rebuild automatically means update of the stats with FULLSCAN, so you don't need any kind of update stats for the rebuilt indexes.
After REORGANIZE, it is better (if you have time,resources) to perform UPDATE with FULLSCAN. But if you do REORGANIZE on big indexes and then update their stats, then you'd better check the time for only REBUILD them regardless they have #frag<30%. You could make your algorithm here more flexible.

Regards,
IgorMi

Igor Micev,
My blog: www.igormicev.com
inHouseDBA
inHouseDBA
SSC Veteran
SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)

Group: General Forum Members
Points: 212 Visits: 191
Thank you,
Yes - I am planning to select indexes which were "REORGANIZE"d (no statistics update) during reindex maintenance along with other indexes, where statistics were not updated for the last 2 weeks and run the following:

UPDATE STATISTICS Table_Name (IndexName) WITH FULLSCAN, NORECOMPUTE instead of running sp_updatestats on entire Database...
SQLCharger
SQLCharger
Mr or Mrs. 500
Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)

Group: General Forum Members
Points: 554 Visits: 1445
I would consider running sp_updatestats right before index rebuilds or custom stats update jobs.

It is fast, catches bits that rebuilds don't catch and any full-scan stats updates will overwrite the sampled stats from sp_updatestats.

Best of both worlds (kinda):-D

Cheers,

JohnA

MCM: SQL2008
inHouseDBA
inHouseDBA
SSC Veteran
SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)

Group: General Forum Members
Points: 212 Visits: 191
Thanks for your reply.
I am running reindex (reorg and rebuild) in parallel threads (in multiple jobs) simultaneously, so I cannot run sp_updatestats before reindex step
inHouseDBA
inHouseDBA
SSC Veteran
SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)

Group: General Forum Members
Points: 212 Visits: 191
Gurus, Any more suggestions?
inHouseDBA
inHouseDBA
SSC Veteran
SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)

Group: General Forum Members
Points: 212 Visits: 191
Anyone else?
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214204 Visits: 41979
inHouseDBA (3/18/2013)
Anyone else?


Yes. You didn't mention what you're doing to reestablish the log chain after you break it by setting the DB to SIMPLE recovery mode. I'm just trying to make sure that you're ok there.

Shifting gears back to the original problem, which edition of SQL Server are you running? If you're running the Enterprise edition, you might be able to avoid rebuilding or even defragging the indexes on certain large parts of the table by using table partitioning. That will give you more time to do the full scans on the parts of the large tables that have actually changed.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
TheSQLGuru
TheSQLGuru
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32113 Visits: 8672
inHouseDBA (3/12/2013)
Gurus, Any more suggestions?


1) My suggestion is that you get a professional on board for a few days to completely review and improve your entire maintenance process. Managing a TB sized database properly is almost certainly not optimal if you don't have some serious training and experience doing it already. Get a mentor to a) get things done right and b) teach you how/why it is so. Win-Win.

2) if you aren't using it, I HIGHLY recommend ola.hallengren.com's AWESOME, FREE and FULLY DOCUMENTED maintenance suite

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
inHouseDBA
inHouseDBA
SSC Veteran
SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)

Group: General Forum Members
Points: 212 Visits: 191
Thanks Jeff,
I have over 20 steps in the Sunday Maint Plan.
Among them:
Backup before Maint, put DB in Simple Recovery, Disable auto update statistics, dbcc checkdb, Reindex, Update Staisticsts, put DB in a Full Recovery, Post maint backup, etc.

Unfortunately we have SQL Server 2008R2 Standard, so we cannot use partitioning as of now. So it is maybe another year before we will start looking into partitioning.
My Reindex routine is pretty robust, and I need to enhance my Statistics step now.

Can you please comment on the following part:
After Reindexing Step is complete, I am selecting indexes which were only "REORGANIZE"d (no statistics update) during Reindex Maintenance along with other indexes, where statistics were not updated for the last 2 weeks and I am running the following:

UPDATE STATISTICS Table_Name (IndexName) WITH FULLSCAN, NORECOMPUTE on the individual Indexes instead of running sp_updatestats on the entire Database.
Does it make sense?
Once Statistics step is completed I turn on automatic statistics update on the entire Database (SET AUTO_UPDATE_STATISTICS ON)

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