Database Maintenance (OLTP)---a Technical Reference Guide for Designing Mission-Critical OLTP 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

This section provides some best practices and references for further information.

• 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.

  • Note that index maintenance operations could impact log file size.

  • Consider the impact of index maintenance on disaster recovery, such as log shipping and database mirroring.

• 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.

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.

• Statistics are updated at an index or table level, not at the partition level. The trigger on auto-updatestats is (500 + 20 percent * table size), so as table size grows, this trigger is called less frequently. Deciding when to update statistics (updates can occur asynchronously if necessary) and selecting a sampling size to use is important, particularly on systems that require stability, in addition to high performance. Also, statistics updates can sometimes negatively impact the system (for example, causing a wait on recompile locks).

• Monitor data and log file size to understand their characteristics. The goal is to shrink and grow files only during scheduled times to minimize impact on performance; it is best to proactively manage growth. In general, shrinking files should be avoided as unnecessary shrinking of files can lead to file system fragmentation.

• Consider how maintenance may affect the performance or uptime of the application. Evaluate and/or establish maintenance windows during which 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.

• Try to understand transaction scopes. For example, understand whether there are features, such as database mirroring or transactional replication, that require the log records to be retained in the transaction log.

• 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 Databaseshttp://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 Rebuildhttp://sqlcat.com/whitepapers/archive/2007/11/20/tempdb-capacity-planning-and-concurrency-considerations-for-index-create-and-rebuild.aspx

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

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