SQL Server 2008 DBA Routine Maintenance Tasks: Daily, Weekly, Monthly/Quarterly

SQL Server 2008 DBA Daily Routine Maintenance Tasks
Maintenance tasks requiring close and regular attention are commonly checked each day. DBAs who take on these tasks daily ensure system reliability, availability, performance, and security. Some of the daily routine maintenance tasks include the following:
■ Check that all required SQL Server services are running.
■ Check Daily Backup logs for success, warnings, or failures.
■ Check the Windows Event logs for errors.
■ Check the SQL Server logs for security concerns such as invalid logins.
■ Conduct full or differential backups.
■ Conduct Transaction Log backups on databases configured with the Full or Bulk-Logged recovery model.
■ Verify that SQL Server jobs did not fail.
■ Check that adequate disk space exists for all database files and transaction logs.
■ At least monitor processor, memory, or disk counters for bottlenecks.

SQL Server 2008 DBA Weekly Routine Maintenance Tasks
Maintenance procedures that require slightly less attention than daily checking are categorized in a weekly routine. The following list details these weekly tasks:
■ Conduct full or differential backups.
■ Review Maintenance Plan reports.
■ Check database integrity.
■ Shrink the database if needed.
■ Compact clustered and nonclustered tables and views by reorganizing indexes.
■ Reorganize data on the data and index pages by rebuilding indexes.
■ Update statistics on all user and system tables.
■ Delete historical data created by backups, restores, SQL Server agent, and maintenance plan operations.
■ Manually grow database or transaction log files if needed. Adjust automatic growth values if needed.
■ Remove files left over from executing maintenance plans.

SQL Server 2008 DBA Monthly or Quarterly Maintenance Tasks
Some maintenance task are managed more infrequently, such as on a monthly or quarterly basis. Do not interpret these tasks as unimportant because they don’t require daily maintenance. These tasks also require maintenance to ensure the health of their environment, but on a less regular basis because they are more self-sufficient and self-sustaining. Although the following tasks may appear mundane or simple, they should not be overlooked during maintenance.
■ Conduct a restore of the backups in a test environment.
■ Archive historical data if needed.
■ Analyze collected performance statistics and compare them to baselines.
■ Review and update maintenance documentation.
■ Review and install SQL Server patches and service packs (if available).
■ Test failover if running a cluster, database mirroring, or log shipping.
■ Validate that the backup and restore process adheres to the Service Level Agreement defined.
■ Update SQL Server build guides.
■ Update SQL Server disaster recovery documentation.
■ Update maintenance plan checklists.
■ Change Administrator passwords.
■ Change SQL Server service account passwords.

From book "Microsoft SQL Server 2008 Management and Administration".

Best Practices on Managing SQL Server 2008 Indexes

Best Practices on Managing SQL Server 2008 Indexes

■ Managing and optimizing indexes is an ongoing process because performance can suffer both with the lack of indexes and poorly implemented and managed indexes.
■ Create clustered indexes on columns that are frequently used and are lean data types. For example, the clustered index could be an identity column. However, it is not uncommon to create the clustered index on a column used in WHERE, ORDER BY, and GROUP BY clauses and in joins.
■ Nonclustered indexes are dependent on the clustered index. Be careful when disabling a clustered index because the nonclustered indexes are also automatically disabled.
■ A table can have only one clustered index. Take care to ensure that the key length of the clustered index doesn’t exceed 900 bytes.
■ Use nonclustered indexes to cover frequent or costly queries that are not covered by the clustered index. As many as 999 nonclustered indexes can be created on a table.
■ Take care to ensure the key length of the nonclustered index doesn’t exceed 900 bytes. Add columns as nonkey include columns to place additional data into the index.
■ In order to alleviate performance degradation caused by fragmentation, maintain indexes by either updating or rebuilding indexes.
■ If the tempdb is stored in a different filegroup or on a different set of physical disks, use the option to sort the index in the tempdb for a performance improvement.
■ Leverage the Enterprise Edition’s online indexing feature to eliminate downtime when removing fragmentation from indexes.
■ When deleting all indexes on a table, remember to remove the clustered index last. If the clustered index is removed first, any nonclustered indexes are unnecessarily maintained as part of the removal process.
■ Spatial indexes should be created to increase the response time of spatial queries.
■ Leverage Filtered indexes to improve performance and reduce maintenance costs on columns that have nulls.

From book "Microsoft SQL Server 2008 Management and Administration".