Professional Microsoft® SQL Server® 2008 Administration

Chapter 13. Monitoring Your SQL Server

Effective monitoring of your SQL Server is the first step to an easy life as a DBA. Having good monitoring in place enables you to move from reactively dealing with events to proactively diagnosing problems and fixing them before your users are even aware there is a problem.

Many of the SQL Server administration guides available explain how to use Reliability and Performance Monitor (PerfMon), how to capture a Profiler trace using either SQL profiler or SQL Trace, or how to run a few dynamic management views (DMVs) to find blocking, or long-running, queries.

This chapter will teach you how to proactively monitor your SQL Server system so that you can prevent or react to events before the server gets to the point where users begin calling.

Here's a quick example. I recently took over an existing system after the DBA moved to a different team. This system ran well, but there was something that needed fixing every other day — transaction logs filling, tempdb out of space, not enough locks, filegroups filling up. Nothing major, just a slow steady trickle of problems that needed fixing. This is the DBA's death of 1,000 cuts. Your time is sucked away doing these essential maintenance tasks until you get to the point where you don't have time to do anything else.

After a few weeks of this, I managed to put some new monitoring in place and was able to make several proactive changes to resolve issues before anything broke. These changes weren't rocket ...