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

sp_updatestats and the default sampling rate Expand / Collapse
Author
Message
Posted Monday, March 4, 2013 4:51 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, December 18, 2013 6:45 PM
Points: 17, Visits: 108
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.
Post #1426522
Posted Tuesday, March 5, 2013 2:31 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 4:35 AM
Points: 2,901, Visits: 2,927
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,
SQL Server developer at Seavus
www.seavus.com
Post #1426660
Posted Tuesday, March 5, 2013 5:19 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, December 18, 2013 6:45 PM
Points: 17, Visits: 108
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...
Post #1427090
Posted Wednesday, March 6, 2013 5:39 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 17, 2014 1:41 AM
Points: 170, Visits: 1,400
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)


Cheers,

JohnA

MCM: SQL2008
Post #1427326
Posted Wednesday, March 6, 2013 7:30 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, December 18, 2013 6:45 PM
Points: 17, Visits: 108
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
Post #1427721
Posted Tuesday, March 12, 2013 12:08 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, December 18, 2013 6:45 PM
Points: 17, Visits: 108
Gurus, Any more suggestions?
Post #1430031
Posted Monday, March 18, 2013 4:30 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, December 18, 2013 6:45 PM
Points: 17, Visits: 108
Anyone else?
Post #1432390
Posted Monday, March 18, 2013 6:06 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:22 AM
Points: 36,767, Visits: 31,223
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1432417
Posted Tuesday, March 19, 2013 7:50 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:19 AM
Points: 4,320, Visits: 6,113
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 at GMail
Post #1432655
Posted Tuesday, March 19, 2013 6:04 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, December 18, 2013 6:45 PM
Points: 17, Visits: 108
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
Post #1432950
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse