次の方法で共有

Why do we see ENCRYPTION_SCAN locks in a SQL server

  • [アーティクル]

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.

encryption_scan_select_into

Before I commit this transaction, I wanted to see all the lock types this session acquired.

encryption_scan_locks_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.

encryption_scan_blocked

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.

encryption_scan_multiple_spids

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

Comments

  • Anonymous
    March 17, 2017
    Well, no, this post does not explain the mystery. In the first example, where you said "You will notice multiple DATABASE locks with the different subtypes. One of them is ENCRYPTION_SCAN." I have to ask: WHY is the query taking an Encryption Scan lock when there is no TDE anywhere? That is not explained anywhere in the post. Sure, when you enable TDE, that lock is expected. But not before.