-- From: SQL Server Concurrency: Locking, Blocking and Row Versioning
-- https://www.red-gate.com/simple-talk/books/sql-books/sql-server-concurrency-locking-blocking-and-row-versioning/

IF EXISTS ( SELECT 1 FROM sys.views WHERE name = 'DBlocks')
    DROP VIEW DBlocks;
GO

CREATE VIEW DBlocks
AS
    SELECT request_session_id AS spid ,
        DB_NAME(resource_database_id) AS dbname ,
        CASE WHEN resource_type = 'OBJECT' THEN OBJECT_NAME(resource_associated_entity_id)
             WHEN resource_associated_entity_id = 0 THEN 'n/a' 
             ELSE OBJECT_NAME(p.object_id)
        END AS entity_name,
        index_id,
        resource_type AS resource,
        resource_description AS description,
        request_mode AS mode,
        request_status AS status
    FROM sys.dm_tran_locks t
        LEFT JOIN sys.partitions p ON p.partition_id = t.resource_associated_entity_id
    WHERE resource_database_id = DB_ID()
        AND resource_type <> 'DATABASE';

-- 3604: Redirect DBCC command output to query window, 1200: Prints detailed lock information, -1: Enable globally on server
DBCC TRACEON(3604, 1200, -1);


-- {insert SQL to test here}


-- Turn tracing off
DBCC TRACEOFF(3604, 1200, -1);

-- clear stats globally on server
DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);


-- {insert SQL to test here}


-- Get lock wait stats
SELECT wait_type, waiting_tasks_count, wait_time_ms, max_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE (waiting_tasks_count > 0 OR wait_time_ms > 0 OR max_wait_time_ms > 0)
AND wait_type LIKE '%LCK%'
ORDER BY waiting_tasks_count DESC;