MCITP Administrator: Microsoft SQL Server 2005 Optimization and Maintenance (Exam 70-444) Study Guide

As always, we’ll start by covering the various issues you need to consider when deciding which tools to use for your performance monitoring solution.

Several factors can guide you in selecting a tool for performance monitoring besides the type of information you want to track. The following are the various considerations you should take into account when deciding on the appropriate tool to use:

• Addressing a real-time requirement   As database solutions are becoming ever more important to companies as line-of-business applications or other mission-critical systems, there is potentially the need for some real-time performance monitoring solution. The database administrator (DBA) might need to respond to certain thresholds or events in real time to ensure that any service-level agreement (SLA) is met.

• Minimizing performance overhead   Using a tool to measure performance will result in performance overhead. How much overhead depends on the server’s power, the quantity of information collected, and the method of monitoring.

• Addressing trend analysis requirements   Another factor that can influence your decision is the capability of trend analysis. SQL Server Profiler and System Monitor are the perfect candidates for this purpose, though you can also use the DMVs and functions with a small additional effort. Don’t forget, however, that DMVs typically represent a snapshot of your system over a period of time, although some are cumulative. We covered the various DMVs supported by SQL Server 2005 in both Chapter 1, “Optimizing the Performance of Databases and Database Servers” and Chapter 2, “Optimizing the Performance of Queries.”

  The Performance Overhead of Monitoring Tools

  It can be important to factor in the overhead of the performance monitoring tools you will be using. Use the following as a rough guideline.

  System Monitor run locally can add up to 18–20 percent performance overhead if you monitor a low-end system and add all the performance counters you can imagine. To avoid that performance penalty, you can reduce the number of counters, increase the sampling interval, and monitor from a remote computer. Remote monitoring will generate another type of overhead that you should be aware of; namely, it will increase the amount of network traffic generated. However, an additional network adapter will solve this problem.

  SQL Server Profiler can, in certain circumstances, consume up to 30 percent of your server’s resources. How can you prevent this? Reduce the number of events, store the resulted trace in a file (instead of a table), use the SQL Trace stored procedures instead of the graphical interface, and so on.

  The new DMVs can also be a source of overhead for your server in special situations such as monitoring memory allocation per object or monitoring locking. You can use SQL Server 2005 Books Online (and of course common sense) to decide how to use them.

  Of course, these “metrics” depend on the hardware and operational environment, so they are rough guidelines. Captain Barbossa said it best: “The code is more what you’d call guidelines than actual rules.”

• Addressing automatic monitoring requirements   The need for an automatic tool will often require that you use Performance Logs and Alerts instead of System Monitor or use SQL Trace instead of SQL Server Profiler. Although you can use a range of third-party tools, the SQL Server development team has provided an impressive array of functionality, so it’s more a matter of learning how to use the tools.

• Considering additional factors   Several other factors can help you decide what tool should be used, including the presence of a graphical interface, the capability of generating alerts, the ability to replay captured events, and the option of being able to embed the tool within a custom application.

Performance Monitoring Tools

We already discussed a number of utilities or tools that come with SQL Server 2005 in Chapter 1 and Chapter 2. However, not all of these tools are appropriate for performance monitoring a database solution.

The following tools are appropriate in a performance monitoring context:

• Performance Logs and Alerts

• SQL Server Profiler

• SQL Trace

• System Monitor

This chapter will cover these in more detail as appropriate. You will also examine how you can potentially use triggers and event notifications to supplement your database performance monitoring strategy.