Chapter 5. Performance Monitoring

Monitoring server performance is an essential part of database administration, and Microsoft SQL Server has several tools that you can use to perform this task. The Microsoft Windows Server 2003 Performance tools, System Monitor, and Performance Logs and Alerts provide counters for SQL Server 2005 that allow you to track server resources and activities. SQL Server Profiler enables you to trace and analyze SQL Server events. Other tools and resources include stored procedures, distributed management views (DMVs), and the SQL Server log.

You need to plan your SQL Server performance monitoring strategy carefully. We saw in Chapter 1, “Troubleshooting Database and Server Performance,” that you can monitor performance counters and use SQL traces and DMVs to troubleshoot server, instance, and database performance. However, your monitoring strategy should accomplish more than troubleshooting. It should enable you to track trends, discover what resources are coming under more pressure through time, and solve problems before they occur. By tracking user activity and baselining performance at typical, busy, and quiet periods, you should be able to identify normal operation and distinguish between an unusual activity peak and persistent and permanent pressure on resources.