Authored by: Anonymous on Wednesday, December 26 2012 @ 07:10 am UTC

Colin,Thank you for clearly dearsntomting your point with T-SQL. It's always nice to visually affirm what you already understand theoretically However, I'd like to add a few caveats, which are worth considering when you are talking about maintaining the health of very large tables (1-4 B rows) or a database with dozens of large tables:1. Rebuilding (even with the Online option) can be very expensive overall if you have a very large (over 1TB) OLTP database that is often busy processing lot of transactions. Multiple times we noticed significant CPU spikes (taking 20-50% more) and some blocking in our production environment. Yes, it can happen even on a dedicated SQL 2005 server with 16 procs and 64 GB RAM. But you got options: use Reorganize instead. Although this method is not as effective a defrag tool and may possibly take longer to finish (depending on the amount of DML activity), you can at the least not worry about disrupting users.2. Also, when you are rebuilding indexes consider using the MAXDOP and SORT_IN_TEMPDB options to control the load on the server. I usually assign half the procs for index rebuilding but if you have a long slow period you might want to use all the CPUs and get done with that much faster.3. Another important consideration, which seems to be one of the less understood options is the STATISTICS_NORECOMPUTE flag. Part of the problem seems to be Microsoft's wording itself and how it interconnects with DB options. However, this is an important option deserving special attention. But first a short detour: From what I gather most DBAs leave the AUTO_UPDATE_STATISTICS database option to the default setting, which is ON but in our experience we learned that this is not very desirable. (Hold back your knives dissenters, I will tell you why it might be better to turn it off in just a bit!). In our heavily transactional databases we noticed that the query plans chosen by the SQL engine are not always consistent or the best possible. On some of our frequently called procs the logical reads were varying a lot in PRD servers compared to DEV servers. But after reviewing the query plan in PRD we decided to tweak the procs using the query hint FORCE ORDER, which preserves the join order during query optimization. We noticed significant performance gains with this change. By using this hint we are asking the query optimizer to take it easy and not mess with the join order specified in the proc. In other words, behind the scenes the query optimizer is no longer issuing update stats on the fly when it judges the existing stats to be out of date. So, what's my point? Turning auto stats off ain't as bad as it sounds because the optimizer's decision is well, not always the most intelligent. However, bear in mind that turning it OFF means some additional diligence on the DBA's part because now you got to take care of updating stats. You will need a stats update job running few times a week and particularly soon after your index defrag job or after major DML activity. And that's where STATISTICS_NORECOMPUTE comes into picture! If you choose to keep AUTO_UPDATE_STATISTICS turned ON but don't want query optimizer altering the query plan then set STATISTICS_NORECOMPUTE to ON. By setting STATISTICS_NORECOMPUTE to ON you are basically marking these tables to be excluded from the auto update stats at the DB level. This is OK because your scheduled update stats job will take care of it during off hours. 4. If you are Reorganizing your indexes (instead of Rebuilding) make sure you follow it up with either sp_updatestats or UPDATE STATISTICS because Reorganize unlike Rebuild doesn't automatically update your index statistics. Take note that Alter Index will never update column stats.5. Finally, a note on updating stats. If you are using the highly customizable UPDATE STATISTICS method instead of sp_updatestats, which offers limited functionality you'd want to use the NORECOMPUTE option to ensure that you are not resetting auto update stats on these tables. (Same idea as STATISTICS_NORECOMPUTE option in ALTER INDEX.)

[ Reply to This | Parent | # ]