Monitoring the SQL Server Platform

The first step in monitoring the platform is to identify the components to monitor. For example, if you are using SQL Server Profiler to trace a server, you can define the trace to collect data about specific events. You can also exclude events that do not apply to your situation.

Next, select Metrics for Monitored Components. After identifying the components to monitor, determine the metrics for components you monitor. For example, after selecting the events to include in a trace, you can choose to include only specific data about the events. Limiting the trace to data that is relevant to the trace minimizes the system resources required to perform the tracing.

Next you will run the monitoring tool that you have configured to gather data. For example, after a trace is defined, you can run the trace to gather data about events raised in the server.

Finally you will analyze the data. After the trace has finished, analyze the data to see if you have achieved your monitoring goal. If you have not, modify the components or metrics that you used to monitor the server.

The following outlines the process for capturing event data and putting it to use.

Apply Filters to Limit the Event Data Collected   Limiting the event data allows the system to focus on the events pertinent to the monitoring scenario. For example, if you want to monitor slow queries, you can use a filter to monitor only those queries issued by the application that take more than 30 seconds to run against a particular database.

Monitor (Capture) Events   As soon as it is enabled, active monitoring captures data from the specified application, instance of SQL Server, or operating system. For example, when disk activity is monitored using System Monitor, monitoring captures event data such as disk reads and writes and displays it to the screen.

Save Captured Event Data   Saving captured event data lets you analyze it later or even replay it using SQL Server Profiler. Captured event data is saved to a file that can be loaded back into the tool that originally created it for analysis. SQL Server Profiler permits event data to be saved to a SQL Server table. Saving captured event data is important when you are creating a performance baseline. The performance baseline data is saved and used when comparing recently captured event data to determine whether performance is optimal. For more information, see the section on SQL Server Profiler later in this chapter.

Create Trace Templates That Contain the Settings Specified to Capture the Events   Trace templates include specifications about the events themselves, event data, and filters that are used to capture data. These templates can be used to monitor a specific set of events later without redefining the events, event data, and filters. For example, if you want to frequently monitor the number of deadlocks and the users involved in those deadlocks, you can create a template defining those events, event data, and event filters; save the template; and reapply the filter the next time that you want to monitor deadlocks. SQL Server Profiler uses trace templates for this purpose.

Analyze Captured Event Data   To be analyzed, the captured, saved event data is loaded into the application that captured the data. For example, a captured trace from SQL Server Profiler can be reloaded into SQL Server Profiler for viewing and analysis. Analyzing event data involves determining what is occurring and why. This information lets you make changes that can improve performance, such as adding more memory, changing indexes, correcting coding problems with Transact-SQL statements or stored procedures, and so on, depending on the type of analysis performed. For example, you can use the Database Engine Tuning Advisor to analyze a captured trace from SQL Server Profiler and make index recommendations based on the results.

Replay Captured Event Data   Event replay lets you establish a test copy of the database environment from which the data was captured and repeat the captured events as they occurred originally on the real system. This capability is only available in SQL Server Profiler. You can replay them at the same speed as they originally occurred, as fast as possible (to stress the system), or more likely, one step at a time (to analyze the system after each event has occurred). By analyzing the exact events in a test environment, you can prevent harm to the production system.