Have you looked at the Activity Monitor screen within the Managment Studio?

I feel this is somewhat surprise features included from February CTP (or was it there before?). This is more useful to the DBAs to have a look at resource activity and locking/blocking. There is no doubt that Ken Henderson's insight is filled up within this feature and this feature of  activity monitor has three pages, process info, locks by process & locks by object.

So you might be wondering how this could be help for DBA, the monitor will let you to diagnose (quickly) the performance data with little overhead. A quick tool on DBA perspective in similar to the Windows Resource Monitor with the details that inlcudes active sessions, waits, file I/O and not to mention about long running queries. Not only this the 3 pages such as process info page will contain all the information about connections, locks by process will give you sort of locking information (by connection) and locks by objects will give a sorting list of locks by object name. Also you can even use the Filter option to select list of required information to be displayed without waiting to page to load for complete information, I say very handy when you have a SQL Server instance with more than 400 user connections.

So what its for, a handy tool for  troubleshooting database locking issues, and to terminate a deadlocked or otherwise unresponsive process. By default the permission to run this feature is to have 'view server state' permission on the SQL instance and this can be used against SQL Server 2005 version and upcoming SQL versions too. As in monitoring to the previous versions such as (SQL 2000 version) the user must have SELECT permissions on sysprocesses & syslocks tables within master database, by default this is granted to PUBLIC database role and to terminate the session the user must be part of SYSADMIN and PROCESSADMIN fixed database roles.

So what are you waiting for, just test these features using February CTP version.