Locking Behaviour with PDW Backups

You may notice your daily ETL jobs or queries running slower than usual or in a queued state when a PDW backup is running. By design, a Backup operation in PDW requires an Exclusive Update (XU) on the database in order to
execute. This is implemented using a standard Lock Manager in PDW engine and the behaviour is different than what you see in a normal SQL SMP. In SQL Server writes are allowed to happen while the Backup operation is active and does not acquire an XU lock. PDW as a distributed architecture, uses this design to guarantee data consistency across nodes while backups are being taken.

You can easily reproduce this behaviour using the following queries:

1. BACKUP DATABASE Adventureworkspdw2012 TO DISK = '\\<your Backup server IP>\backups\AdventureWorks2012.bak' WITH description = 'Backup Test';

2. SELECT * FROM FactInternetSales

This query completes in few seconds when the Backup is active. SharedReads are allowed when the Backup is running.

The following query can be used to see all active requests in PDW.

 SELECT * FROM sys.dm_pdw_exec_requests where status = 'Running'

Alternatively, you can see similar details from Admin Console under the Backups/Restore section. An ExclusiveUpdate lock is granted for Request_ID = QID2119773.

3. TRUNCATE TABLE Dim_Product

This table contains only 606 rows and the Truncate should normally take less than a second but it is still running after 102 secs.  This Truncate operation corresponding to QID2119774 is in queued state while the Backup is active. This info can be found in Admin Console under the Queries section.

Note below the Acquire_Time column is NULL and the state = QUEUED when the Backup is still running. 

 SELECT * FROM sys.dm_pdw_waits

A SharedUpdate is required for INSERTS and UPDATES. When an ExclusiveUpdate is acquired, any writings to the locked object will be prohibited until the lock is released.