Recently I worked with a customer who noticed that several sessions[spid’s] in their SQL Server was acquiring ENCRYPTION_SCAN locks. So they got curious and started looking at this closer. Naturally, the first thing to check is presence of databases with Transparent Data Encryption [TDE] because encryption scan is the background process associated with TDE. Interestingly TDE was not enabled on any of the databases on this SQL Server. TDE was never enabled on any database on this server in the past also. So we started digging deeper into this. While researching this issue, we noticed that several customers reported this behavior in the past to the SQL Support team also. We see discussions on this topic in forums [an example post: ENCRYPTION_SCAN Lock without TDE enabled ].
There are three types of operations that acquire lock with the resource_type of DATABASE and resource_subtype of ENCRYPTION_SCAN :
- Encryption scan performed during TDE enable/disable
- Bulk Allocations that happen as part of bcp/bulk insert/select-into/index operations, etc
- Sort spills that are done as part of sort operators in the query plan
These locks are taken to serialize operations like bulk allocations and sorts with encryption scan.
Here is a simple repro I did to prove this. I opened a transaction and then did a select into on a decent size table.
Before I commit this transaction, I wanted to see all the lock types this session acquired.
You will notice multiple DATABASE locks with the different subtypes. One of them is ENCRYPTION_SCAN.
Now, If I enable TDE for this database, it will be blocked on this ENCRYPTION_SCAN lock resource.
The encryption scan happens on the background thread represented by session_id 27. The encryption scan of TDE will be able to proceed after my transaction (that performed the bulk allocation) completes.
Note that this lock does not cause any conflict between two bulk allocation operations or between two sorts themselves. This lock will cause conflict only with the background TDE encryption scan thread. This is because bulk allocators and sort acquire this lock in shared mode. The TDE encryption scan background thread requests this lock in BU mode which will conflict with shared mode.
Hope this post explains some of the mystery behind the presence of ENCRYPTION_SCAN locks on server that has nothing to do with TDE.
Thanks – Suresh Kandoth – SQL Server Escalation Team