SQL Server Diagnostics

BlockingPerDbObject.sql

SELECT DB_NAME([database_id]) AS [Database]
    ,iops.[object_id] AS [ObjectID]
    ,QUOTENAME(OBJECT_SCHEMA_NAME(iops.[object_id], [database_id])) + N'.' + QUOTENAME(OBJECT_NAME(iops.[object_id], [database_id])) AS [ObjectName]
    ,i.[index_id] AS [IndexID]
    ,i.[name] AS [IndexName]
    ,i.[fill_factor] AS [IndexFillFactor]
    ,iops.[partition_number] AS [PartitionNumber]
    ,CASE
        WHEN i.[is_unique] = 1
            THEN 'UNIQUE '
        ELSE ''
        END + i.[type_desc] AS [IndexType]
    ,iops.[row_lock_count] AS [RowLockCount]
    ,iops.[row_lock_wait_count] AS [RowLockWaitCount]
    ,CAST(100.0 * iops.[row_lock_wait_count] / (iops.[row_lock_count] + 1) AS NUMERIC(15, 2)) AS [BlockedPercent]
    ,iops.[row_lock_wait_in_ms] AS [RowLockWaitInMilliseconds]
    ,CAST(1.0 * iops.[row_lock_wait_in_ms] / (1 + iops.[row_lock_wait_count]) AS NUMERIC(15, 2)) AS [AverageRowLockWaitInMilliseconds]
FROM [sys].[dm_db_index_operational_stats](DB_ID(), NULL, NULL, NULL) iops
INNER JOIN [sys].[indexes] i
    ON i.[object_id] = iops.[object_id]
        AND i.[index_id] = iops.[index_id]
        AND iops.[row_lock_wait_count] > 0
WHERE OBJECTPROPERTY(iops.[object_id], 'IsUserTable') = 1
ORDER BY iops.[row_lock_wait_count] DESC;

DbEngineRowPageLock.sql

-- Query 3 – Execute to find how many times Database Engine row or page lock:

SELECT DB_NAME([database_id]) AS [Database]
    ,iops.[object_id] AS [ObjectID]
    ,QUOTENAME(OBJECT_SCHEMA_NAME(iops.[object_id], [database_id])) + N'.' + QUOTENAME(OBJECT_NAME(iops.[object_id], [database_id])) AS [ObjectName]
    ,iops.[row_lock_count] AS [RowLockCount]
    ,iops.[page_lock_count] AS [PageLockCount]
FROM [sys].[dm_db_index_operational_stats](DB_ID(), NULL, NULL, NULL) iops
INNER JOIN [sys].[indexes] i
    ON i.[object_id] = iops.[object_id]
        AND i.[index_id] = iops.[index_id]
ORDER BY iops.[page_latch_wait_count] + iops.[page_io_latch_wait_count] DESC

PhysicalIOsPerIndex.sql

--Query 2 – Execute to analyse statistics of physical I/Os on an index or heap partition:

SELECT DB_NAME([database_id]) AS [Database]
    ,iops.[object_id] AS [ObjectID]
    ,QUOTENAME(OBJECT_SCHEMA_NAME(iops.[object_id], [database_id])) + N'.' + QUOTENAME(OBJECT_NAME(iops.[object_id], [database_id])) AS [ObjectName]
    ,i.[name] AS [IndexName]
    ,CASE
        WHEN i.[is_unique] = 1
            THEN 'UNIQUE '
        ELSE ''
        END + i.[type_desc] AS [IndexType]
    ,iops.[page_latch_wait_count] AS [PageLatchWaitCount]
    ,iops.[page_io_latch_wait_count] AS [PageIOLatchWaitCount]
    ,iops.[page_io_latch_wait_in_ms] AS [PageIOLatchWaitInMilliseconds]
FROM [sys].[dm_db_index_operational_stats](DB_ID(), NULL, NULL, NULL) iops
INNER JOIN [sys].[indexes] i
    ON i.[object_id] = iops.[object_id]
        AND i.[index_id] = iops.[index_id]
ORDER BY iops.[page_latch_wait_count] + iops.[page_io_latch_wait_count] DESC

sp_who3.sql

CREATE PROCEDURE [dbo].[sp_who3] 

AS
BEGIN

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT
    SPID                = er.session_id
    ,BlkBy              = CASE WHEN lead_blocker = 1 THEN -1 ELSE er.blocking_session_id END
    ,ElapsedMS          = er.total_elapsed_time
    ,CPU                = er.cpu_time
    ,IOReads            = er.logical_reads + er.reads
    ,IOWrites           = er.writes     
    ,Executions         = ec.execution_count  
    ,CommandType        = er.command         
    ,LastWaitType       = er.last_wait_type    
    ,ObjectName         = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid)  
    ,SQLStatement       =
        SUBSTRING
        (
            qt.text,
            er.statement_start_offset/2,
            (CASE WHEN er.statement_end_offset = -1
                THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2
                ELSE er.statement_end_offset
                END - er.statement_start_offset)/2
        )        
    ,STATUS             = ses.STATUS
    ,[Login]            = ses.login_name
    ,Host               = ses.host_name
    ,DBName             = DB_Name(er.database_id)
    ,StartTime          = er.start_time
    ,Protocol           = con.net_transport
    ,transaction_isolation =
        CASE ses.transaction_isolation_level
            WHEN 0 THEN 'Unspecified'
            WHEN 1 THEN 'Read Uncommitted'
            WHEN 2 THEN 'Read Committed'
            WHEN 3 THEN 'Repeatable'
            WHEN 4 THEN 'Serializable'
            WHEN 5 THEN 'Snapshot'
        END
    ,ConnectionWrites   = con.num_writes
    ,ConnectionReads    = con.num_reads
    ,ClientAddress      = con.client_net_address
    ,Authentication     = con.auth_scheme
    ,DatetimeSnapshot   = GETDATE()
    ,plan_handle        = er.plan_handle
FROM sys.dm_exec_requests er
LEFT JOIN sys.dm_exec_sessions ses
ON ses.session_id = er.session_id
LEFT JOIN sys.dm_exec_connections con
ON con.session_id = ses.session_id
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
OUTER APPLY 
(
    SELECT execution_count = MAX(cp.usecounts)
    FROM sys.dm_exec_cached_plans cp
    WHERE cp.plan_handle = er.plan_handle
) ec
OUTER APPLY
(
    SELECT
        lead_blocker = 1
    FROM master.dbo.sysprocesses sp
    WHERE sp.spid IN (SELECT blocked FROM master.dbo.sysprocesses)
    AND sp.blocked = 0
    AND sp.spid = er.session_id
) lb
WHERE er.sql_handle IS NOT NULL
AND er.session_id != @@SPID
ORDER BY
    CASE WHEN lead_blocker = 1 THEN -1 * 1000 ELSE -er.blocking_session_id END,
    er.blocking_session_id DESC,
    er.logical_reads + er.reads DESC,
    er.session_id

END

UpdateInsertDeleteIndces.sql

--Query 4 – Execute to determine number of update, insert, and delete operations against each index of the database:

SELECT DB_NAME([database_id]) AS [Database]
    ,iops.[object_id] AS [ObjectID]
    ,QUOTENAME(OBJECT_SCHEMA_NAME(iops.[object_id], [database_id])) + N'.' + QUOTENAME(OBJECT_NAME(iops.[object_id], [database_id])) AS [ObjectName]
    ,i.[index_id] AS [IndexID]
    ,i.[name] AS [IndexName]
    ,i.[fill_factor] AS [IndexFillFactor]
    ,iops.[partition_number] AS [PartitionNumber]
    ,CASE
        WHEN i.[is_unique] = 1
            THEN 'UNIQUE '
        ELSE ''
        END + i.[type_desc] AS [IndexType]
    ,iops.[leaf_insert_count] AS [LeafInsertCount]
    ,iops.[leaf_delete_count] AS [LeafDeleteCount]
    ,iops.[leaf_update_count] AS [LeafUpdateCount]
FROM [sys].[dm_db_index_operational_stats](DB_ID(), NULL, NULL, NULL) iops
INNER JOIN [sys].[indexes] i
    ON i.[object_id] = iops.[object_id]
        AND i.[index_id] = iops.[index_id]
ORDER BY iops.[page_latch_wait_count] + iops.[page_io_latch_wait_count]