SQLServerCentral

Dynamic Management Views


https://www.sqlservercentral.com/Forums/Topic937132.aspx

By Tim Ford - Monday, June 14, 2010 7:36 AM

Comments posted to this topic are about the item Dynamic Management Views
By Dugi - Saturday, June 19, 2010 7:18 PM

Seems very nice book for DBAs. Thanks for the notice!
By TheRedneckDBA - Wednesday, October 6, 2010 7:11 AM

Is there going to be a .pdf version of this book?
By na1774 - Monday, May 14, 2012 1:10 PM

Beautiful Book Cleared a lot of questions in my Mind. Thank you.
By SQLRNNR - Thursday, March 28, 2013 8:45 AM

Reached out to Tim about this.
By Jan Van der Eecken - Thursday, March 28, 2013 9:09 AM

Thanks for the replies Jason and Tim, much appreciated. Steve also replied to it in The Thread, he passed the link on. Now that's what a community is there for.

@Tim, is there a SQL2012 edition of the book in the making?

@Jason, my family and I went to watch CirqueDuSOleil last week here in Cape Town. Guess that's where you got your nick from? These guys are awesome.
By Jan Van der Eecken - Thursday, March 28, 2013 9:13 AM

Forgot to mention, Tim and Louis, this is an awesome book!
By Jan Van der Eecken - Thursday, March 28, 2013 8:26 AM

Does anyone know where to find the sample code for this book? I've got the printed version (courtesy of RedGate, won it in a competition). But the link in the Introduction section doesn't work, despite registering on the SimpleTalk web site.
By Timothy Ford-473880 - Thursday, March 28, 2013 8:49 AM

This is a very good question! If the link in the book no longer works I recommend contacting Red Gate. They should be able to point you in the right direction. I would have pointed you to the link.
By Andrew Diniz - Tuesday, July 3, 2012 8:57 PM

Does anyone know if the free ePub version been withdrawn or moved? The link appears to be broken.
By SQLVoila - Tuesday, July 31, 2012 6:26 AM

Andrew Diniz (7/4/2012)
Does anyone know if the free ePub version been withdrawn or moved? The link appears to be broken.

co-ask. The link seems broken as of now. Thanks
By SQLVoila - Wednesday, August 1, 2012 1:16 AM

Andrew Diniz (7/4/2012)
Does anyone know if the free ePub version been withdrawn or moved? The link appears to be broken.

I went on a bit digging and found those books on simple-talk.com where you need register and login to download the free epub version of books. The direct link posted here won't let you download the book. Hope this help others like me.
By Tony Davis - Sunday, March 31, 2013 4:21 AM

Is anyone still having problems with reaching the various files? I know that our ftp site was down for a period but it is back up now and the book files (pdf and epub) plus the code download file all seem to be accessible again.

Best,
Tony.
By Jan Van der Eecken - Monday, April 1, 2013 6:58 PM

Got the code now, Tony. Thanks a lot for the help.
By dedicatedtosql - Tuesday, July 16, 2013 10:40 AM

Hello Tim,

Outstanding Book. Actually your book has shaped me into being better DBA. The more I read the better I am becoming at trouble shooting the DBs in my environment.

I just wanted to point out one thing thou. As I was reading the chapter on Locking and Transaction related DMVs I came across the script which will give us in a single row both the blocking and blocked session details. The query is as below.

SELECT DTL.[resource_type] AS [resource type] ,
CASE WHEN DTL.[resource_type] IN ( 'DATABASE', 'FILE', 'METADATA' )
THEN DTL.[resource_type]
WHEN DTL.[resource_type] = 'OBJECT'
THEN OBJECT_NAME(DTL.resource_associated_entity_id)
WHEN DTL.[resource_type] IN ( 'KEY', 'PAGE', 'RID' )
THEN ( SELECT OBJECT_NAME([object_id])
FROM sys.partitions
WHERE sys.partitions.[hobt_id] =
DTL.[resource_associated_entity_id]

)
ELSE 'Unidentified'
END AS [Parent Object] ,
DTL.[request_mode] AS [Lock Type] ,
DTL.[request_status] AS [Request Status] ,
DOWT.[wait_duration_ms] AS [wait duration ms] ,
DOWT.[wait_type] AS [wait type] , DOWT.[session_id] AS [blocked session id] ,
DES_blocked.[login_name] AS [blocked_user] ,
SUBSTRING(dest_blocked.text, der.statement_start_offset / 2,
( CASE WHEN der.statement_end_offset = -1
THEN DATALENGTH(dest_blocked.text)
ELSE der.statement_end_offset
END - der.statement_start_offset ) / 2)
AS [blocked_command] ,
DOWT.[blocking_session_id] AS [blocking session id] ,
DES_blocking.[login_name] AS [blocking user] ,
DEST_blocking.[text] AS [blocking command] ,
DOWT.resource_description AS [blocking resource detail]
FROM sys.dm_tran_locks DTL
INNER JOIN sys.dm_os_waiting_tasks DOWT
ON DTL.lock_owner_address = DOWT.resource_address
INNER JOIN sys.[dm_exec_requests] DER
ON DOWT.[session_id] = DER.[session_id]
INNER JOIN sys.dm_exec_sessions DES_blocked
ON DOWT.[session_id] = DES_Blocked.[session_id]
INNER JOIN sys.dm_exec_sessions DES_blocking
ON DOWT.[blocking_session_id] = DES_Blocking.[session_id]
INNER JOIN sys.dm_exec_connections DEC
ON DTL.[request_session_id] = DEC.[most_recent_session_id]
CROSS APPLY sys.dm_exec_sql_text(DEC.[most_recent_sql_handle])
AS DEST_Blocking
CROSS APPLY sys.dm_exec_sql_text(DER.sql_handle) AS DEST_Blocked
WHERE DTL.[resource_database_id] = DB_ID()



When I run the above query I get both the blocking query text as well as Blocked query text as the same.

I have modified the query as below. It works good now.


SELECT DTL.[resource_type] AS [resource type] ,
CASE WHEN DTL.[resource_type] IN ( 'DATABASE', 'FILE', 'METADATA' )
THEN DTL.[resource_type]
WHEN DTL.[resource_type] = 'OBJECT'
THEN OBJECT_NAME(DTL.resource_associated_entity_id)
WHEN DTL.[resource_type] IN ( 'KEY', 'PAGE', 'RID' )
THEN ( SELECT OBJECT_NAME([object_id])
FROM sys.partitions
WHERE sys.partitions.[hobt_id] =
DTL.[resource_associated_entity_id]

)
ELSE 'Unidentified'
END AS [Parent Object] ,
DTL.[request_mode] AS [Lock Type] ,
DTL.[request_status] AS [Request Status] ,
DOWT.[wait_duration_ms] AS [wait duration ms] ,
DOWT.[wait_type] AS [wait type] ,
DOWT.[session_id] AS [blocked session id] ,
DES_blocked.[login_name] AS [blocked_user] ,
SUBSTRING(dest_blocked.text, der.statement_start_offset / 2,
( CASE WHEN der.statement_end_offset = -1
THEN DATALENGTH(dest_blocked.text)
ELSE der.statement_end_offset
END - der.statement_start_offset ) / 2)
AS [blocked_command] ,
DOWT.[blocking_session_id] AS [blocking session id] ,
DES_blocking.[login_name] AS [blocking user] ,
DEST_blocking.[text] AS [blocking command] ,
DOWT.resource_description AS [blocking resource detail]
FROM sys.dm_tran_locks DTL
INNER JOIN sys.dm_os_waiting_tasks DOWT
ON DTL.lock_owner_address = DOWT.resource_address
INNER JOIN sys.[dm_exec_requests] DER
ON DOWT.[session_id] = DER.[session_id]
INNER JOIN sys.dm_exec_sessions DES_blocked
ON DOWT.[session_id] = DES_Blocked.[session_id]
INNER JOIN sys.dm_exec_sessions DES_blocking
ON DOWT.[blocking_session_id] = DES_Blocking.[session_id]
INNER JOIN sys.dm_exec_connections DEC
ON DOWT.[blocking_session_id] = DEC.[most_recent_session_id]
CROSS APPLY sys.dm_exec_sql_text(DEC.[most_recent_sql_handle])
AS DEST_Blocking
CROSS APPLY sys.dm_exec_sql_text(DER.sql_handle) AS DEST_Blocked
WHERE DTL.[resource_database_id] = DB_ID()



The change I made was when joining to sys.dm_exec_connections I used the below join condition
ON DOWT.[blocking_session_id] = DEC.[most_recent_session_id]

rather than
DTL.[request_session_id] = DEC.[most_recent_session_id]

that was causing a problem.

I am still no expert. Let me know If I have opened a door for some other bugs by making this change.

Regards,
Nawaz.
By vijred - Tuesday, February 11, 2014 9:18 PM

This is great book, it helped me to enhance my DBA skills;
I have noticed a correction in the Query (session 4.6) which is already discussed in previous response.

Thanks,
Vijay
http://vijredblog.wordpress.com/