Monitoring Your SQL Server
WHAT’S IN THIS CHAPTER
- Monitor SQL Server Behavior with Dynamic Management
- Monitor SQL Server Error Log, and the Windows Event Logs
- A Quick Look at the Management Data Warehouse, UMDW, and Utility Control Point
- Monitoring SQL with the SCOM Management Pack, SQL Server Best Practices Analyzer, and System Center Advisor
Implementing good monitoring 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. This chapter teaches 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. Say you recently took over an existing system after the DBA moved to a different team. This system’s applications ran well, but there was something that needed fixing every other day — transaction logs filling, tempdb out of space, not enough locks, and 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, you manage to put some new monitoring in place and make several proactive changes to resolve issues before anything breaks. These changes aren’t rocket science; they are simple things such as ...