Any better monitoring for locks and blocking in SQL2005?

19 Jan 2006 12:50 PM

James

Are the facilities for monitoring locks and blocking in SQL2005 better than
2000?
I found that in SQL2000 Enterprise Manager by the time you waited for
"current activity" to refresh, the information could have been and gone.
I'd be interested to hear if you've been using it sucessfully to do this
sort of thing.

19 Jan 2006 8:08 PM

Sue Hoegemeier

If you want something similar to Current Activity, Activity
Monitor in Management Studio is an improvement over the
Current Activity node in Enterprise Manager. Opens in a
separate window, you have a few different view to chose
from, you can set a refresh interval.
2005 also has server level reports such as Blocking
Transactions and Top Transactions by Locks.
But just like Current Activity, you are seeing a snapshot
and you need to refresh. You have more options in 2000 to
understand the blocking, locking if you use queries to
monitor activity and it's somewhat the same in 2005.
However, there are improvements to the monitoring and
details you can get related to locking, blocking. In 2005,
you have a lot more details exposed on the waits. You can
also set up a blocked process threshold and use this with
the Blocked Process Report event class in Profiler, SQL
trace. You can also get detailed historical information
related to locking and blocking from the DMV
Sys.dm_db_index_operational_stats.
The following article has a lot of information on you can
use to monitor blocking, locking issues in 2005:
Troubleshooting Performance Problems in SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx

-Sue

On Thu, 19 Jan 2006 04:50:02 -0800, "James"
<Ja***@discussions.microsoft.com> wrote:

Show quote

>Are the facilities for monitoring locks and blocking in SQL2005 better than
>2000?
>I found that in SQL2000 Enterprise Manager by the time you waited for
>"current activity" to refresh, the information could have been and gone.
>I'd be interested to hear if you've been using it sucessfully to do this
>sort of thing.

20 Jan 2006 9:23 AM

Michael Hotek

Night and day, across the board. SQL Server 2000 exposed very limited
information to be able to monitor. SQL Server 2005 has layer after layer
after layer of diagnostics. From DBCC commans through DMVs/DMFs, there
aren't many areas of the engine you can't get at.

--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.

Show quote

"James" <Ja***@discussions.microsoft.com> wrote in message
news:98B66E56-8895-4040-BA63-056144C882C6@microsoft.com...
> Are the facilities for monitoring locks and blocking in SQL2005 better
> than
> 2000?
> I found that in SQL2000 Enterprise Manager by the time you waited for
> "current activity" to refresh, the information could have been and gone.
> I'd be interested to hear if you've been using it sucessfully to do this
> sort of thing.