Chapter 18: Monitoring SQL Server

Overview

A number of monitoring tools come standard with the operating system platform for SQL Server, namely Windows Server 2003 and later. Despite the services of these operating systems, SQL Server comes equipped with its own set of tools for monitoring events in the DBMS.

At any point in the lifetime of a SQL Server system, no matter the application and the data needs, there will be a need to monitor and report on what’s going on inside SQL Server. The type of monitoring and performance tools you will use depend on what you are looking for. If the server is not responding well, then you will need to use a tool tailored to performance analysis and monitoring. On the other hand, you may have applications that are reporting errors, or users who report losing connections, data, or worse. In the latter case you will need to use tools that can trace connections to the server and can monitor what is coming across the client connections, what may be returned, and what code is being executed at any given time.

Table 18–1 lists the choice of monitoring tools you have at your disposal. You will also monitor your server reactively, to troubleshoot problems that have appeared on the system, or proactively, to ensure the server is performing to required operations and performance metrics. Proactive analysis means taking periodic snapshots of performance and comparing the results against established baselines. If you see that performance is degrading to the point that you are seeing signs of bottlenecks, or spikes in processor use or disk use against your base line, then it’s time to react and look further into the causes. If you do nothing, your server will be harder to fix when something awful happens.

Table 18–1: Options for Monitoring SQL Server *Using SQL Server Profiler Stored Procedures

Event or Activity	SQL Server Profiler	System Monitor	Activity Monitor	T-SQL	Error logs
Trend analysis	Yes	Yes
Replaying captured events	Yes
Ad hoc monitoring	Yes		Yes	Yes	Yes
General alerts		Yes
Graphical interface	Yes	Yes	Yes		Yes
Using within a custom application	Yes*			Yes

To monitor any component of SQL Server effectively, follow these steps:

• Determine your monitoring goals.

• Select the appropriate tool.

• Identify components to monitor.

• Select metrics for those components.

• Monitor the server.

• Analyze the data.

These steps are discussed in turn in the text that follows.

• Determine Your Monitoring Goals

To monitor SQL Server effectively, you should clearly identify your reason for monitoring. Reasons can include the following:

• Establish a baseline for performance.

• Identify performance changes over time.

• Diagnose specific performance problems.

• Identify components or processes to optimize.

• Compare the effect of different client applications on performance.

• Audit user activity.

• Test a server under different loads.

• Test database architecture.

• Test maintenance schedules.

• Test backup and restore plans.

• Determining when to modify your hardware configuration.

• Select the Appropriate Tool

Windows provides the following tools for monitoring applications that are running on a server:

• System Monitor: With this tool you can collect and view real-time data about activities such as memory, disk, and processor usage.

• Performance logs and alerts.

• Task Manager.

We will discuss these tools later in this chapter. SQL Server provides the following tools for monitoring components of SQL Server:

• SQL Trace

• SQL Server Profiler

• SQL Server Management Studio Activity Monitor

• SQL Server Management Studio Graphical Showplan

• Stored procedures

• Database Console Commands (DBCC)

• Built-in functions

• Trace flags