Professional SQL Server™ 2005 Administration

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 ...