Dynamic Management Views

  • Tim Ford

    SSC Enthusiast

    Points: 156

    Comments posted to this topic are about the item Dynamic Management Views

  • Dugi

    SSCoach

    Points: 17998

    Seems very nice book for DBAs. Thanks for the notice!

    ============================================================
    SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
    http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

  • TheRedneckDBA

    SSChampion

    Points: 13935

    Is there going to be a .pdf version of this book?

    The Redneck DBA

  • na1774

    SSCrazy

    Points: 2301

    Beautiful Book Cleared a lot of questions in my Mind. Thank you.

  • Andrew Diniz

    SSCommitted

    Points: 1852

    Does anyone know if the free ePub version been withdrawn or moved? The link appears to be broken.

  • SQLVoila

    Mr or Mrs. 500

    Points: 554

    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

  • SQLVoila

    Mr or Mrs. 500

    Points: 554

    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.

  • Jan Van der Eecken

    SSCrazy Eights

    Points: 8890

    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.

    --------------------------------------------------------------------------
    A little knowledge is a dangerous thing (Alexander Pope)
    In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]

  • SQLRNNR

    SSC Guru

    Points: 281210

    Reached out to Tim about this.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • sqlagentman

    SSCarpal Tunnel

    Points: 4373

    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.

    - Tim Ford, SQL Server MVPhttp://www.sqlcruise.comhttp://www.thesqlagentman.com http://www.linkedin.com/in/timothyford

  • Jan Van der Eecken

    SSCrazy Eights

    Points: 8890

    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.

    --------------------------------------------------------------------------
    A little knowledge is a dangerous thing (Alexander Pope)
    In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]

  • Jan Van der Eecken

    SSCrazy Eights

    Points: 8890

    Forgot to mention, Tim and Louis, this is an awesome book!

    --------------------------------------------------------------------------
    A little knowledge is a dangerous thing (Alexander Pope)
    In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]

  • Tony Davis

    SSCarpal Tunnel

    Points: 4305

    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.

  • Jan Van der Eecken

    SSCrazy Eights

    Points: 8890

    Got the code now, Tony. Thanks a lot for the help.

    --------------------------------------------------------------------------
    A little knowledge is a dangerous thing (Alexander Pope)
    In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]

  • dedicatedtosql

    Ten Centuries

    Points: 1147

    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.

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply