sp_updatestats and the default sampling rate

Question

sp_updatestats and the default sampling rate

inHouseDBA

Default port

Points: 1403
More actions
March 4, 2013 at 4:51 pm

#274864

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.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 1

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 Default port Points: 1403 More actions · Answer 2

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 SSCommitted Points: 1958 More actions · Answer 3

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 Default port Points: 1403 More actions · Answer 4

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 Default port Points: 1403 More actions · Answer 5

inHouseDBA

Default port

Points: 1403

March 12, 2013 at 12:08 pm

#1596211

Gurus, Any more suggestions?

inHouseDBA Default port Points: 1403 More actions · Answer 6

inHouseDBA

Default port

Points: 1403

March 18, 2013 at 4:30 pm

#1598215

Anyone else?

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 7

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 8

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 Default port Points: 1403 More actions · Answer 9

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

Steve Jones - SSC Editor SSC Guru Points: 736160 More actions · Answer 10

One more thing to keep in mind, you may have statistics on columns that are not in indexes. Make sure those are being updated. Your plan could potentially skip these statistics.

I agree with Igor's post. Here's some info on the trace flag: http://blogs.msdn.com/b/saponsqlserver/archive/2011/09/07/changes-to-automatic-update-statistics-in-sql-server-traceflag-2371.aspx

inHouseDBA Default port Points: 1403 More actions · Answer 11

Thanks Guys for your input,

Igor,

can you please elaborate on ...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%

Thanks