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.