Chapter 13: Monitoring Your SQL Server

The goal of monitoring databases is to assess how your server is performing. SQL Server 2005 provides a comprehensive set of tools for monitoring events in SQL Server. Both SQL Server and the Windows operating system provide tools to view the current condition of the database system and to track performance as the system parameters change. When you optimize and tune the performance of SQL Server, you're trying to minimize the response time for database queries, and maximize SQL Server throughput. In this chapter, we will concentrate on out-of-the-box tools for monitoring SQL Profiler, Performance Monitor (also called System Monitor), Dynamic Management Views (DMVs), and Dynamic Management Functions (DMFs).

Why Should You Monitor?

SQL Server provides service in a dynamic environment. There are many variables in this environment: The types of access that the users require change, data in the application changes, new requirements crop up in your applications, and more. With growing frequency, SQL Server database systems are being chosen as the preferred backend database solution for large and complex business-critical systems. As a responsible DBA, it's your job to know the health of your SQL Server so that you can take proactive actions. This will help you spot potential problems not yet visible to the end-user. For example, if a certain query has slowed down from 50 ms to 250 ms, then the end-user will not be alarmed, but you should be. As a system administrator, you must identify performance goals and factors that affect the performance of Microsoft SQL Server.

Determining Your Monitoring Goals

Before you start monitoring you must first clearly identify your reasons for monitoring. Some of these reasons are as follows:

• Identify performance changes over time.

• Audit user activity.

• Establish a baseline for a performance.

• Diagnose a specific performance problem.

Choosing the Appropriate Monitoring Tools

Once you define your monitoring goals you should select the appropriate tools for monitoring. The following list introduces you to the basic monitoring tools. The rest of the chapter discusses these tools in detail.

• SQL Profiler: All database engine process events are tracked by this tool, which has a graphical user interface. You can also store the data into a SQL table or file for later analysis, and you can replay the captured event on SQL Server step by step to see exactly what happened. It can monitor a SQL server instance locally or remotely. The Profiler works well for trend analysis, and is the only tool that can replay captured events. You can also use the Profiler within a custom application, by using the Profiler system stored procedures.

• Performance Monitor: Performance Monitor tracks resource use on Microsoft Windows Server and Windows operating systems. It can monitor a SQL Server instance, locally or remotely, on Windows OS (4.0 or later). The Performance Monitor is useful in trend analysis, and it can generate alerts, but it's not useful for ad-hoc monitoring.

  The key difference between Profiler and Performance Monitor is that Performance Monitor monitors resources (CPU, memory, disk I/O, etc.) related to server processes, whereas Profiler monitors database engine events.

• Activity Monitor in SQL Server Management Studio: This tool graphically displays the following information:

  • Processes running on an instance of SQL Server

  • Locks

  • User activity

  • Blocked processes

  Activity Monitor works best for ad-hoc monitoring, but not very well for trend analysis.

  Note

  To open Activity Monitor in SQL Server Management Studio, connect to the server with Object Explorer, expand Management, and then double-click Activity Monitor.

• Dynamic Management Views: Dynamic Management Views and Functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance. This is one of the best tools added to SQL Server 2005 for ad-hoc monitoring. Of course, you have to save the result from the DMVs for later analysis.

• Transact-SQL: Some system stored procedures provide useful information for SQL Server monitoring, such as sp_who, sp_who2, sp_lock, and several others. These stored procedures are best for ad-hoc monitoring, not trend analysis.

Evaluating Performance

For many reasons, it is very difficult and sometimes impossible to emulate the production environment in a development or system test for complex systems. Of course, "money matters" is one of the reasons, but other reasons include the load on the system at different times of the day, the numbers of users, differing application use scenarios, and everyday data changes. It is also difficult to have the same hardware in all the environments. Typically, your production system has the best hardware, so application performance changes a lot from development to deployment. Once your application is deployed in production, it is very important that you establish a performance baseline. We don't mean that you shouldn't establish a baseline in other environments. In fact, you should be thinking about application performance right from the requirements gathering and database design stages. Ongoing evaluation of database system performance helps you minimize response time and maximize throughput to yield optimal performance.

To determine whether your SQL Server system is performing optimally, take performance measurements at a regular interval over time, even when no problem occurs, to establish a server performance baseline. At a minimum, use baseline performance to determine the following:

• Peak and off-peak hours of operation

• Query or batch response time

After you establish the baseline, compare the statistics to current performance. Look for numbers far above or below your baseline, as these warrant further investigation.