Through this script, you will have the ability to know at run time, how Microsoft SQL Server performs data page locks or even rows from a table. To simulate this behavior, the code shared in this script makes use of a small environment composed of the paglocktable table, this being the storage source of the small portion of data.
Your order of execution is defined as follows:
1- Creation of table paglocktable;
2- Inserting a small portion of data;
3- Query the data entered in the table;
4- Simulation and use of the Paglock table hint, through the use of a new transaction defined at run time through the Begin transaction command, setting the TPL name for the transaction block;
5- Opening a new Query, to execute the Select command while the previously created TPL transaction is running;
6- After the 10-second processing period of the TPL transaction, Microsoft SQL Server must present the result of the data requested in step 5;
7- In parallel to the execution of steps 5 and 6, if you want to identify what is being blocked at the level of data pages or line run in another query the code blocks named:
-- Identify locks at level data pages --
SELECT OBJECT_SCHEMA_NAME(ios.object_id) + '.' + OBJECT_NAME(ios.object_id) as table_name,
i.name as index_name,
page_lock_count,
page_lock_wait_count,
CAST(100. * page_lock_wait_count / NULLIF(page_lock_count,0) AS decimal(6,2)) AS page_block_pct,
page_lock_wait_in_ms,
CAST(1. * page_lock_wait_in_ms / NULLIF(page_lock_wait_count,0) AS decimal(12,2)) AS page_avg_lock_wait_ms
FROM sys.dm_db_index_operational_stats (DB_ID(), NULL, NULL, NULL) ios INNER JOIN sys.indexes i
ON i.object_id = ios.object_id AND i.index_id = ios.index_id
WHERE OBJECTPROPERTY(ios.object_id,'IsUserTable') = 1
ORDER BY row_lock_wait_count + page_lock_wait_count DESC, row_lock_count + page_lock_count DESC
Go
Or
-- Identify locks at line level --
SELECT OBJECT_SCHEMA_NAME(ios.object_id) + '.' + OBJECT_NAME(ios.object_id) as table_name,
i.name as index_name,
row_lock_count,
row_lock_wait_count,
CAST(1. * row_lock_wait_in_ms / NULLIF(row_lock_wait_count,0) AS decimal(12,2)) AS row_avg_lock_wait_ms
FROM sys.dm_db_index_operational_stats (DB_ID(), NULL, NULL, NULL) ios INNER JOIN sys.indexes i
ON i.object_id = ios.object_id AND i.index_id = ios.index_id
WHERE OBJECTPROPERTY(ios.object_id,'IsUserTable') = 1
ORDER BY row_lock_wait_count + row_lock_wait_count DESC, row_lock_count + row_lock_count DESC
Go
