InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 18 Jun 2013 17:27:03 GMT20http://www.sqlservercentral.com/Forums/Topic1426522-391-1.aspxThanks 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[b] check the time for only REBUILD them regardless [/b]they have #frag<30%ThanksThu, 21 Mar 2013 15:33:09 GMTinHouseDBAhttp://www.sqlservercentral.com/Forums/Topic1426522-391-1.aspxOne 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: [url]http://blogs.msdn.com/b/saponsqlserver/archive/2011/09/07/changes-to-automatic-update-statistics-in-sql-server-traceflag-2371.aspx[/url]Thu, 21 Mar 2013 09:23:55 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic1426522-391-1.aspxThanks 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 youTue, 19 Mar 2013 18:04:03 GMTinHouseDBAhttp://www.sqlservercentral.com/Forums/Topic1426522-391-1.aspx[quote][b]inHouseDBA (3/12/2013)[/b][hr]Gurus, Any more suggestions?[/quote]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 suiteTue, 19 Mar 2013 07:50:47 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic1426522-391-1.aspx[quote][b]inHouseDBA (3/18/2013)[/b][hr]Anyone else?[/quote]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.Mon, 18 Mar 2013 18:06:45 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1426522-391-1.aspxAnyone else?Mon, 18 Mar 2013 16:30:48 GMTinHouseDBAhttp://www.sqlservercentral.com/Forums/Topic1426522-391-1.aspxGurus, Any more suggestions?Tue, 12 Mar 2013 12:08:36 GMTinHouseDBAhttp://www.sqlservercentral.com/Forums/Topic1426522-391-1.aspxThanks 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 stepWed, 06 Mar 2013 19:30:34 GMTinHouseDBAhttp://www.sqlservercentral.com/Forums/Topic1426522-391-1.aspxI 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):-DWed, 06 Mar 2013 05:39:22 GMTSQLChargerhttp://www.sqlservercentral.com/Forums/Topic1426522-391-1.aspxThank 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...Tue, 05 Mar 2013 17:19:37 GMTinHouseDBAhttp://www.sqlservercentral.com/Forums/Topic1426522-391-1.aspxHi,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,IgorMiTue, 05 Mar 2013 02:31:00 GMTIgorMihttp://www.sqlservercentral.com/Forums/Topic1426522-391-1.aspxUsing SQL Server 2008R2As 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_updatestatsCurrently I am performing the following steps during weekend maintenance:1. ALTER Database MyDBSET RECOVERY Simple,AUTO_CREATE_STATISTICS OFF,AUTO_UPDATE_STATISTICS OFF2. 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:[b]sp_updatestats[/b]Since sp_updatestats updates statistics by using the default sampling rate, it possibly can deteriorate all my indexes after running reindex routine.[b]3A.[/b] 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_StatisticsSo here is my logic for performing routine in [b]3A[/b]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_updatestats4. ALTER Database MyDBSET RECOVERY Full,AUTO_CREATE_STATISTICS ON,AUTO_UPDATE_STATISTICS ONPlease let me know if you have any comments, suggestions, recommendations on [b]step 3A[/b].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.Mon, 04 Mar 2013 16:51:27 GMTinHouseDBA