Performance Tuning with SQL Server Dynamic Management Views

  • Comments posted to this topic are about the item Performance Tuning with SQL Server Dynamic Management Views

  • 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]

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

    The Redneck DBA

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

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

  • 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

  • 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.

  • 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]

  • 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

  • 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

  • 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-2, 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]

  • 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]

  • 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.

  • 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]

  • 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 15 total)

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