APS/PDW Query Performance: Lock Status

A common scenario customers report is queries which ran quickly yesterday, are taking longer today.  Today I want to cover one aspect that may be causing this behavior: locks.

 

Most appliances out there have a combination of ETL, ELT, and ad hoc queries.  It is possible one of those ad hoc queries is blocking the load, or vice versa.  There are two types of resources needed to run a query in PDW, concurrency locks and object locks at a PDW level.  Without both of these, a query will be in a queued state. 

 Administration Console

If the issue is occurring now, you can always look in the Administration Console under the Parallel Data Warehouse, Resources tab.  Here you will find two tabs which show the different type of locks needed in PDW. 

 

  

The LOCKS tab will show all object related lock requests. The WAITS tab will show all concurrency lock requests.  Both tabs will show currently granted requests along with currently waiting requests.  The easiest way to check for any queued requests in either tab is to order each page by 'STATE' or 'ACQUIRED DATE' by clicking on the column header.  Any request that has not been granted will have a state of QUEUED and ACQUIRED DATE will be NULL.

 

 

 

Another useful column to sort by is the object name column.  This will show you all request grouped by object name. This view can help identify what session has a lock on the object and what session is waiting for a lock on the object. 

 

In this example, you can see SID545501 has queued locks for the database, schema, and the object.  Looking at the object locks, I can see  SID54493 has been granted an ExclusiveUpdate lock.  Since a session will not take any level of locks until all locks needed can be granted, all locks for SID545501 will be queued until they are able to be granted. 

 

The WAITS tab shows the concurrency locks granted.  The concurrency locks will only be granted once all object locks have been granted and the query is currently running. In this example, there is only one query that is currently active.

 

 

DMV Data

 As with all data presented in the admin console, the lock data is also available in DMV's.  It may be easier on a busier system to pull the data directly from the DMV's and filter it to only show the interesting points.

There are three DMV's which contain the data for lock requests. 

sys.dm_pdw_resource_waits - Will show all concurrency locks and is the source for the WAITS tab in the admin console

sys.dm_pdw_lock_waits - Will show all object locks and is the source for the LOCKS tab

sys.dm_pdw_waits - Combines the data from other two DMV's to give an aggregated view. 

 Historical Data

Both the admin console and the DMV data show only a current snapshot of the granted and queued requests.  They will not give any historical data.  It is possible though to look at the query execution to tell if it was blocked on concurrency locks, object locks, or in actual execution. The following query will show this information:

SELECT TOP 10 session_id,
request_id,
Cast(start_time - submit_time AS TIME(4)) AS queued_time,
Cast(end_compile_time - start_time AS TIME(4)) AS compile_time,
Cast(end_time - end_compile_time AS TIME(4)) AS execution_time,
total_elapsed_time
FROM sys.dm_pdw_exec_requests
WHERE total_elapsed_time > 0
--Use this to order by queries waiting on user concurrency locks
--order by queued_time desc
--Use this to order by queries waiting on object locks and PDW plan generation
ORDER BY compile_time DESC
--Use this to order by actual query execution time
--order by execution_time desc

 Example results:

 

This example shows SID54501 spent 23 minutes and 56 seconds in compilation time, which includes time spent waiting to acquire object locks. 

 

Feel free to comment with any queries of your own you have found useful and questions are always welcome.

 

The example used in this post included running a DML statement inside an explicit transaction from SID54493 and not commiting it.  SID54501 is then executing a DML operation against the same object, which will be blocked on exclusive object locks.