Database Maintenance (DW)---a Technical Reference Guide for Designing Mission-Critical DW Solutions

There are a number of administrative tasks that a database administrator (DBA) usually has to complete as part of his or her daily, weekly, monthly, or quarterly task list to keep the application and database functioning at expected levels. At the Microsoft SQL Server database level, these tasks include:

• Developing a backup/restore strategy (as discussed in the Backup/Restore guide).

• Removing index fragmentation.

• Ensuring that statistics are up-to-date.

• Confirming that the database is structurally sound (by using Database Console Commands [DBCC]/database consistency checks).

• Managing the size of the data and log files.

At a more detailed level, these tasks may include security patch, update, and service pack management (as discussed in the Software Maintenance guide).

Best Practices

The following resources provide reference material and additional information.

• Statistics Updates are at an index or table level, not at the partition level. The trigger on auto-updatestats is (500 + 20% * table size) so as table size grows this can get called less frequently. Thinking about when to update stats (and if necessary you can do it asynchronously) and with what sampling size is important particularly on systems which are not only concerned with performance but stability. Also, sometimes stats updates can be too impactful and cause problems for the system (wait on recompile locks…). Considering the statistics sampling size is also important.

• Be sure to regularly review plans of long-running queries to ensure proper usage of indexes/stats.

• Make judicious use of indexes and covering indexes just to performance tune a handful of queries. There is a delicate balance between tuning for queries and ensuring data loads are not impacted by numerous non-clustered indexes.

• In almost all cases for data warehouse, the clustered index column and the partitioning column should be the same.

• If statistics are regenerated, be sure to issue a DBCC freeproccache to force query plan recalculation based on new statistics.

• Develop a strategy to reorganize/rebuild indexes only when necessary (usually at a table or index level). In the article sys.dm_db_index-physical-stats (Transact-SQL),1 example D, "Using sys.dm_db_index_physical_stats in a script to rebuild or reorganize indexes," may be helpful. It is important to find a time and frequency to do the reorganization/rebuild. There is a trade-off between the impact of maintaining/rebuilding the indexes and statistics, versus the impact of fragmentation and/or out-dated to current transaction throughput, determine whether it can be done online, and consider the impact of fill factor and of the defragmentation itself. Performing the reorganization/rebuild may have more of a negative impact than the fragmentation in the table.

• The article DBCC Checks and Terabyte-Scale Databases2 describes considerations for performing consistency checks on very large databases (VLDBs).

• The white paper TEMPDB Capacity Planning and Concurrency Considerations for Index and Create and Rebuild3 describes options for index create and rebuild operations that you can use to effectively meet the requirements of performance, concurrency, and resources.

• The ability to perform online index operations is a key reason for implementing SQL Server Enterprise or Datacenter editions, as many organizations today have limited maintenance time windows available. The article Online Indexing Options in SQL Server 20054 provides considerations and best practices.

• The TechNet Magazine article Top Tips for Effective Database Maintenance5 provides an overview of a few database maintenance tasks

• Monitor data and log file size to understand characteristics. Goal should be to not have to shrink files and grow files only if necessary during a scheduled time, as to not impact performance. Proactively managing growth is best.

Case Studies and References

SQL Server has been deployed by customers for many Tier-1 (and lower tier) applications.

• For backup architecture discussions, see the Sample Case Studies/Reference section in the Backup/Restore guide.

• Enhancements for database maintenance include the ability to perform online index rebuilds (in certain cases), rebuild in tempdb, asynchronous statistics updates, DBCC checks on file groups, backup compression, and more.

Questions and Considerations

This section provides questions and issues to consider when working with your customers.

• There are many strategies to use for performance tuning a data warehouse. Many involve non-clustered indexing, but often times simply recalculating stats can have an enormous impact.

• Consider how the maintenance may affect the performance or uptime of the data warehouse. Evaluate and/or establish maintenance windows where some tasks can be accomplished.

• Understand how data is changing in the objects and size of objects. This can be monitored through scripts and sometimes through perfmon. Understanding this can help establish expected sizes for data and log files that can be pre-allocated to certain sizes or re-allocated during a maintenance window. This can also help determine strategies for keeping indexes defragmented and statistics up-to-date.

• Understand frequency and volume of database loads. If incremental load sizes are significant, statistics can be invalid thereby generating bad query plans.

• Consider options to reload tables rather than recreating indexes.

• Many customers have enterprise-wide backup strategies/technologies already in existence to which you need to conform.

Appendix

Following are the full URLs for the hyperlinked text.

1 sys.dm_db_index-physical-stats (Transact-SQL) https://msdn.microsoft.com/en-us/library/ms188917.aspx

2 DBCC Checks and Terabyte-Scale Databases http://sqlcat.com/technicalnotes/archive/2009/08/13/dbcc-checks-and-terabyte-scale-databases.aspx

3 TEMPDB Capacity Planning and Concurrency Considerations for Index and Create and Rebuild http://sqlcat.com

4 Online Indexing Options in SQL Server 2005 https://technet.microsoft.com/en-us/library/cc966402.aspx

5 Top Tips for Effective Database Maintenance https://technet.microsoft.com/en-us/magazine/2008.08.database.aspx?pr=blog