SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Simulating locking in page level

By Junior Galvão - MVP,

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

Total article views: 119 | Views in the last 30 days: 119
 
Related Articles
FORUM

Resolving Transaction Blocking Issues?

Transaction Blocking

FORUM

Blocking transaction

need help to recover from blocking transaction

FORUM

Query Problem - Counting Blocks

I need to count blocks

ARTICLE

Using Indexes to Reduce Blocking in Concurrent Transactions

New author Nilav Ghosh brings us a performance tuning article to help your queries run better. This ...

FORUM

Index Key Count

Index Key Count

 
Contribute