SQL Server Deadlock & Performance Monitoring Made Easy

IF OBJECT_ID('dbusp_DeadLockTree','P') IS NOT NULL
BEGIN
 drop proc dbusp_DeadLockTree
END 
GO

CREATE PROCEDURE dbusp_DeadLockTree
AS
BEGIN
    SET NOCOUNT ON;

    -- Step 1: Process details with waits
    SELECT 
        R.spid AS SPID,
        R.blocked AS BLOCKED,
        R.waittime / 1000.0 AS WaitTimeSec,
        R.lastwaittype AS WaitType,
        R.loginame AS LoginName,
        R.hostname AS HostName,
        DB_NAME(R.dbid) AS DBName,
        REPLACE(REPLACE(T.TEXT, CHAR(10), ' '), CHAR(13), ' ') AS BatchText
    INTO #tmpProcess
    FROM sys.sysprocesses R
    CROSS APPLY sys.dm_exec_sql_text(R.sql_handle) T;

    -- Step 2: Recursive blocking chain
    WITH BlockerTree (SPID, BLOCKED, LEVEL, WaitTimeSec, WaitType, LoginName, HostName, DBName, BatchText)
    AS
    (
        SELECT SPID, BLOCKED,
               CAST(RIGHT('0000' + CAST(SPID AS VARCHAR), 4) AS VARCHAR(1000)) AS LEVEL,
               WaitTimeSec, WaitType, LoginName, HostName, DBName, BatchText
        FROM #tmpProcess R
        WHERE (BLOCKED = 0 OR BLOCKED = SPID)
          AND EXISTS (SELECT 1 FROM #tmpProcess R2 WHERE R2.BLOCKED = R.SPID AND R2.BLOCKED <> R2.SPID)

        UNION ALL

        SELECT R.SPID, R.BLOCKED,
               CAST(BlockerTree.LEVEL + RIGHT('0000' + CAST(R.SPID AS VARCHAR), 4) AS VARCHAR(1000)),
               R.WaitTimeSec, R.WaitType, R.LoginName, R.HostName, R.DBName, R.BatchText
        FROM #tmpProcess R
        INNER JOIN BlockerTree ON R.BLOCKED = BlockerTree.SPID
        WHERE R.BLOCKED > 0 AND R.BLOCKED <> R.SPID
    )

    -- Step 3: Final output
    SELECT 
       REPLICATE('   ', LEN(LEVEL)/4 - 1) + 
        CASE WHEN LEN(LEVEL)/4 - 1 = 0 THEN 'HEAD ---> ' ELSE '├── ' END +
        CAST(SPID AS NVARCHAR(10)) + 
        ' (Blocked by: ' + CAST(BLOCKED AS NVARCHAR(10)) + ')' AS BlockingTree,
        BLOCKED, WaitTimeSec, WaitType, LoginName, HostName, DBName, BatchText
    FROM BlockerTree
    ORDER BY LEVEL;

    DROP TABLE #tmpProcess;
END;
GO

EXEC dbusp_DeadLockTree;

-- Drop if exists
IF OBJECT_ID('dbo.dbusp_PerformanceStats', 'P') IS NOT NULL
    DROP PROCEDURE dbo.dbusp_PerformanceStats;
GO

CREATE PROCEDURE dbo.dbusp_PerformanceStats
    @ThresholdAvgSec   FLOAT  = 2,
    @ThresholdCPUSec   FLOAT  = 1,
    @ThresholdReads    BIGINT = 10000,
    @TableSizeInMB     INT    = 1024,
    @RowCount          BIGINT = 10000000,
    @TopRows           INT    = 10
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY  
        -- 1. Large Tables
        SELECT  
            'Table Size' AS ,
            t.NAME AS ,
            SUM(p.rows) AS ,
            CAST(((SUM(a.total_pages) * 8.0) / 1024 / 1024) AS DECIMAL(10,2)) AS 
        FROM sys.tables t
        INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
        INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
        INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
        GROUP BY t.Name
        HAVING ((SUM(a.total_pages) * 8.0) / 1024) >= @TableSizeInMB
            OR SUM(p.rows) >= @RowCount;

        -- 2. Top CPU Heavy Queries
        SELECT TOP (@TopRows)
            QS.execution_count,
            QS.total_worker_time / 1000000.0 AS TotalCPU_Sec,
            (QS.total_worker_time / NULLIF(QS.execution_count,0)) / 1000000.0 AS AvgCPU_Sec,
            QS.total_elapsed_time / 1000000.0 AS TotalElapsed_Sec,
            T.text AS QueryText,
            DB_NAME(T.dbid) AS DBName
        FROM sys.dm_exec_query_stats QS
        CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) T
        ORDER BY TotalCPU_Sec DESC;

        -- 3. Top Duration Queries
        SELECT TOP (@TopRows)
            QS.execution_count,
            (QS.total_elapsed_time / NULLIF(QS.execution_count,0)) / 1000000.0 AS AvgElapsed_Sec,
            QS.total_worker_time / 1000000.0 AS TotalCPU_Sec,
            T.text AS QueryText,
            DB_NAME(T.dbid) AS DBName
        FROM sys.dm_exec_query_stats QS
        CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) T
        ORDER BY AvgElapsed_Sec DESC;

        -- 4. Top IO Heavy Queries
        SELECT TOP (@TopRows)
            QS.execution_count,
            QS.total_logical_reads + QS.total_physical_reads AS TotalReads,
            (QS.total_logical_reads + QS.total_physical_reads) / NULLIF(QS.execution_count,0) AS AvgReads,
            T.text AS QueryText,
            DB_NAME(T.dbid) AS DBName
        FROM sys.dm_exec_query_stats QS
        CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) T
        ORDER BY TotalReads DESC;

        -- 5. Currently Running Queries + Blocking
        SELECT 
            R.session_id, R.status, R.command,
            DB_NAME(R.database_id) AS DBName,
            T.text AS QueryText,
            R.blocking_session_id
        FROM sys.dm_exec_requests R
        CROSS APPLY sys.dm_exec_sql_text(R.sql_handle) T
        WHERE R.session_id > 50 AND R.session_id <> @@SPID
        ORDER BY R.total_elapsed_time DESC;

        -- 6. Execution Plan for Top Queries
        SELECT TOP (@TopRows)
            (QS.total_elapsed_time / NULLIF(QS.execution_count,0)) / 1000000.0 AS AvgElapsed_Sec,
            T.text AS QueryText,
            P.query_plan AS ExecutionPlanXml
        FROM sys.dm_exec_query_stats QS
        CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) T
        CROSS APPLY sys.dm_exec_query_plan(QS.plan_handle) P
        ORDER BY AvgElapsed_Sec DESC;

        -- 7. Stored Procedure Performance
        SELECT  
            DB_NAME(PS.database_id) AS DBName,
            OBJECT_NAME(PS.object_id, PS.database_id) AS ProcedureName,
            PS.execution_count,
            (PS.total_elapsed_time / NULLIF(PS.execution_count,0)) / 1000000.0 AS AvgElapsedSec,
            (PS.total_worker_time / NULLIF(PS.execution_count,0)) / 1000000.0 AS AvgCPUSec,
            (PS.total_logical_reads / NULLIF(PS.execution_count,0)) AS AvgLogicalReads
        FROM sys.dm_exec_procedure_stats PS
        WHERE PS.database_id = DB_ID()
        ORDER BY AvgElapsedSec DESC;
    END TRY  
    BEGIN CATCH  
        SELECT ERROR_NUMBER() AS ErrorNum, ERROR_MESSAGE() AS ErrorMessage;
    END CATCH  
END
GO

EXEC dbo.dbusp_PerformanceStats;