In this article I demonstrated how I go about detecting SQL Server problems in the form of excessive locking, and blocking. While this is a good start for the DBA detective, there is much more ground to cover. I mentioned CPU and I/O in this article only peripherally, as it relates to problem code. In my book, I continue on the path of analyzing performance issues, but extend this topic to explain how to make sure you get notified immediately of performance, and other, issues.

After all, if you do not know about the problem, you can't fix it. I would much rather be notified of a potential issue from a system that is monitoring such events than from an irate application user, or from the Help Desk. Granted, you will be hard pressed to totally escape emails from users and that is OK, generally they are understanding. It is their bosses that are not. If you can find and fix, or even just report, an issue before anyone else, it appears that you are ahead of the game. And you are … you are a DBA after all.

• System tables versus DMVs

• Tracking down database performance issues

• Using sp_who2

• Who is running the query?

• DBCC: What is the query doing?

• Killing the offending query

• Using sp_lock

• Automating discovery of problems