14.5. Best practice considerations: monitoring and automation

A good DBA knows the tools at his or her disposal and selects the right tool for the job, with mundane and repetitive tasks automated to achieve more and reduce errors, thereby freeing up time for more rewarding and enjoyable tasks.

• Wherever possible, use server-side traces configured to write to local dedicated disks instead of client-side Profiler traces, particularly for high-volume and/or long-running traces. Use Profiler's Export menu to create server-side scripts once the required events, columns, and filters have been chosen.

• Use Profiler for specialized tasks such as deadlock and blocked process monitoring, rather than as a primary performance analysis and tuning tool. Dynamic management views are far more effective in this regard, particularly when used as part of a waits and queues analysis technique, covered in chapter 17.

• The RML utilities (ReadTrace, OStress) are ideal for database load testing and evaluating the performance impact of proposed changes. Such tools, however, cannot be used for load testing all aspects of an application infrastructure. Tools such as Visual Studio Team System: Test Load Agent and LoadRunner are ideal for this purpose.

• Recording a baseline of system performance under typical operating conditions and performance response times is crucial in understanding normal system behavior. Such understanding is invaluable when troubleshooting performance problems as it allows abnormal performance measurements to stand out, thereby narrowing the scope of a performance troubleshooting exercise.

• A performance baseline is typically created using Performance Monitor to record a number of key metrics over a period of time representing typical system usage. A one-second sampling interval should be used for this recording (unless the log size and/or performance impact become too great).

• Consider including application metrics in the baseline, for example, number of users and user response time. Such metrics enhance the value of the baseline by providing a richer end-to-end performance picture and enable projection of likely database performance under future application-load scenarios. Depending on the application, such metrics may be exposed as Performance Monitor counters in a manner similar to how SQL Server exposes its own internal metrics.

• Maintaining a load-test environment, ideally configured identically to production, allows the impact of proposed production changes to be measured before production implementation. It also permits benchmark testing, which is crucial in understanding a system's breaking point and essential for capacity-planning purposes.

• Regular performance baseline analysis allows emerging performance trends to be understood, for example, the growth in batches/sec over a number of months. When combined with benchmark testing to establish a known breaking point, such analysis enables performance projections as part of a capacity-planning exercise.

• Ensure counters such as user connections and logins/logouts per second are kept in the baseline. Most counter values tend to rise in unison with the number of connected users, so these are valuable counters for cross-referencing purposes.

• While maintenance plans are easy to create and use, beware of their limitations, for example, the inability to set threshold levels for index rebuild/reorganize. Although you could use the T-SQL task here, SQL Server Agent jobs provide more flexibility with the ability to create additional job step types such as Powershell.

• Consider the use of backup devices for more flexibility when scripting backup jobs for implementation as SQL Server Agent jobs. Rather than using script jobs containing hard-coded directory paths, the use of backup devices enables portability of backup scripts, with each environment's backup devices configured for the appropriate drive letters and directory paths.

• Ensure the appropriate alerts are enabled either using SQL Server Agent operators or through the use of MOM or something similar. At a minimum, alerts should be established for job failures, severity 16+ errors, deadlocks, and peak performance conditions.

• When using trace flags in a production environment, using the -T startup option is preferred over the DBCC TRACEON command. Having the trace flag invoked on startup ensures that all statements run with the same trace flag setting.

Additional links and information on the best practices covered in this chapter can be found online at http://www.sqlcrunch.com/automation-monitoring.

In the next chapter, we'll expand on our coverage of automation by focusing on a new feature introduced in SQL Server 2008 that automates the collection of performance and management data for storage and later analysis.