As we have discussed about introduction locks here(in our earlier blog). Here we will discuss how to trouble shoot locking. Locking is good to maintain the consistency of our data. Means when someone is update our data no one should access it until we successfully completes the update. For example :it’s a common example in bank account if we are withdraw amount from our account during the transaction no one should get the dirty data until our transaction completes successfully.
request_session_id as spid,
resource_type as rt,
resource_database_id as rdb,
WHEN ‘OBJECT’ then object_name(resource_associated_entity_id)
WHEN ‘DATABASE’ then ‘ ‘
ELSE (select object_name(object_id)
END) as objname,
resource_description as rd,
request_mode as rm,
request_status as rs
%%lockres%% -inline function
Lock is normal and it maintains the transaction consistency.
Generally we cannot do much on locks just to track are we getting lock escalation.
TraceFlag-1211: It disables lock escalation at the current threshold (5000)
TraceFlag-1224: This trace flag is similar to trace flag 1211 with one key difference. It enables lock escalation when lock manager acquires 40% of the statically allocated memory or (40%) non-AWE(32-bit)/regular(64-bit) dynamically allocated memory.
as discussed earlier –Locks escalation is Row/Page to Table/Partition to Database.