SQL Server – Deadlock Detection

I would like to share the steps to find/detect the deadlocks in SQL Server. It is very easy to find the deadlock using SQL Profiler.

Steps #1:

Step #2:

Step #3:

Step #4:

Step #5:

Step #6:

Step #7:

Now run following queries to generate deadlock.

CREATE TABLE dbo.foo (col1 INT)
INSERT dbo.foo SELECT 1

CREATE TABLE dbo.bar (col1 INT)
INSERT dbo.bar SELECT 1

— 2) Run in first connection window
BEGIN TRAN
UPDATE foo SET col1 = 1

— 3) Run in second connection window
BEGIN TRAN
UPDATE bar SET col1 = 1
UPDATE foo SET col1 = 1

–4) Run in first connection window
UPDATE bar SET col1 = 1

When you will run all 3 above given queries then it will give you a deadlock message. Now you can find the deadlock.xdl file in “C:\” drive as below:

Step #8:

There you go. Dead lock has been identified. Now you can easily detect the deadlocks on production server.

Happy Programming!!!

 

SQL SERVER – Finding Long Running Queries

SELECT
    CREATION_TIME
    ,LAST_EXECUTION_TIME
    ,TOTAL_PHYSICAL_READS
    ,TOTAL_LOGICAL_READS
    ,TOTAL_LOGICAL_WRITES
    , EXECUTION_COUNT
    , TOTAL_WORKER_TIME
    , TOTAL_ELAPSED_TIME
    , TOTAL_ELAPSED_TIME / EXECUTION_COUNT AVG_ELAPSED_TIME
    ,SUBSTRING(ST.TEXT, (QS.STATEMENT_START_OFFSET/2) + 1,
    ((CASE STATEMENT_END_OFFSET
        WHEN -1 THEN DATALENGTH(ST.TEXT)
        ELSE QS.STATEMENT_END_OFFSET END
    – QS.STATEMENT_START_OFFSET)/2) + 1) AS STATEMENT_TEXT
FROM SYS.DM_EXEC_QUERY_STATS AS QS
    CROSS APPLY SYS.DM_EXEC_SQL_TEXT(QS.SQL_HANDLE) ST  
WHERE TOTAL_ELAPSED_TIME >= 300000000 –5 MIN
ORDER BY TOTAL_ELAPSED_TIME / EXECUTION_COUNT DESC;

By writing above mentioned query, you will get all the queries which are taking more-then 5 minutes.

You can find more details from this article.

Happy Programming!!!