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'
 

request_id

session_id

status

submit_time

start_time

end_compile_time

end_time

total_elapsed_time

command

QID2119773

SID197457

Running

2016-02-15 08:25:42.637

2016-02-15 08:25:42.637

2016-02-15 08:25:42.650

NULL

106731

BACKUP DATABASE AdventureworksPdw2012 TO DISK = ‘\\xx.xx.xx.xx\backups\AWWorks2012.bak’
  WITH description = ‘Backup Test’

QID2119774

SID197277

Running

2016-02-15 08:25:47.243

2016-02-15 08:25:47.243

NULL

NULL

102121

TRUNCATE TABLE Dim_Product         

 

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

 

wait_id

session_id

type

object_type

object_name

request_id

request_time

acquire_time

state

0

SID197277

SharedUpdate

DATABASE

AdventureWorksPDW2012

QID2119774

2016-02-15 08:25:47.243

NULL

Queued

1

SID197277

Shared

SCHEMA

AdventureWorksPDW2012.dbo

QID2119774

2016-02-15 08:25:47.243

NULL

Queued

2

SID197277

Exclusive

OBJECT

AdventureWorksPDW2012.dbo.Dim_Product

QID2119774

2016-02-15 08:25:47.243

NULL

Queued

 

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.

 

 

Comments (0)

Skip to main content