Dynamic Management Views Put to Work on Blocking

SQL Server, T-SQL
This is my first pass at a modern (2005/2008) blocking monitoring script. I think it's a decent blocking script to capture information about blocks as they are occurring. Filters can be applied and it wouldn't be hard at all to add on other information such as execution plans, plan hash, etc. SELECT  tl.request_session_id AS WaitingSessionID        ,wt.blocking_session_id AS BlockingSessionID        ,wt.resource_description        ,wt.wait_type        ,wt.wait_duration_ms        ,DB_NAME(tl.resource_database_id) AS DatabaseName        ,tl.resource_associated_entity_id AS WaitingAssociatedEntity        ,tl.resource_type AS WaitingResourceType        ,tl.request_type AS WaitingRequestType        ,wrt.[text] AS WaitingTSql        ,btl.request_type BlockingRequestType        ,brt.[text] AS BlockingTsql FROM    sys.dm_tran_locks tl         JOIN sys.dm_os_waiting_tasks wt         ON tl.lock_owner_address = wt.resource_address         JOIN sys.dm_exec_requests wr         ON wr.session_id = tl.request_session_id         CROSS APPLY sys.dm_exec_sql_text(wr.sql_handle) AS wrt         LEFT JOIN sys.dm_exec_requests br         ON br.session_id = wt.blocking_session_id         OUTER APPLY…
Read More

More Dynamic Management Views: sys.dm_tran_locks

SQL Server, T-SQL
I'm working on the chapter on blocking in the new book. Explaining blocking of course means explaining locks. Prior to 2005, to understand locks, you went to sp_lock. Not anymore. Now you can query sys.dm_tran_locks. It's so much more sophisticated than the old system procedure.  Best of all, the information within it is simply a view into the internal locking infrastructure, so you're not placing extra load or extra processing on the system to marshal this data. A simple query to get basic locking information would look like this:   SELECT tl.request_session_id             ,tl.resource_database_id             ,tl.resource_associated_entity_id             ,tl.resource_type             ,tl.resource_description             ,tl.request_mode             ,tl.request_status   FROM sys.dm_tran_locks tl That just outputs roughly the same information as sp_lock. Lots more detail, not available in sp_lock, is available if you need it. Things…
Read More

Finally, the GDR is Released

Visual Studio
From the Data Dude himself. This is great news. I've been working with the CTP's for several months now, telling the other DBA's on my team that they had to wait until it was completely ready for release. It's been a long wait, but I'm sure it's worth it. My congratulations to the team. I met several of you at the PASS Summit. I really apreciate the work you've put into this great tool. It really makes a difference in how we develop and deploy databases. The changes in the GDR are making a great utility even better.  Thanks Mr. Drapers. Thanks also to Jamie Laflen, especially for helping validate some of the ideas I presented at PASS. Thanks to all the rest of the team, whose names I don't…
Read More