The DBA as Detective: Troubleshooting Locking and Blocking

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

[@more@]

http://www.simple-talk.com/sql/database-administration/the-dba-as-detective-troubleshooting-locking-and-blocking/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/66009/viewspace-1057266/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/66009/viewspace-1057266/