Specific TempDB insert of UserDB select results in SOS_SCHEDULER_YIELD to ENCRYPTION_SCAN

One of our production systems has exhibited a problem with a single insertion statement into a temp table from our user database. When I comment out the insert/select, the stored proc in question runs in a timely fashion, so I am confident in my isolation of the problem.

The series of stored procs invoked basically grinds to a halt when I un-comment the insert/select in question. I cannot see anything in Top Transactions by Age in tempdb or any of our user databases. I do not see anything in Activity Monitor that deviates from Activity Monitor's information when the database is "at rest", other than CPU being flatlined at ~20%.

The behavior is as follows: when I setup and then execute the reproduction case, upon arrival at the insert/select in question, I see an SOS_SCHEDULER_YIELD and there is an ENCRYPTION_SCAN. About five hours later, I will see processing of our stored procedure resume and the activity will complete (I put quick and dirty log statements around every distinct operation).

I also replaced the variables in the select portion of the insert with the values as executed, and ran the selection query itself, and it returned in five seconds.

The user database in question has FALSE as its encryption enabled value, as does tempdb. The operation in question happens over about 65k rows of data, and I have tried it with only 1k rows, and the behavior persisted, although the time it took was far less.

A single user database is the only instance of this behavior. I have reproduced it locally over a backup of that user databse. We have about 70 other users of the software that do not exhibit this problem.

Given the above information, my question is, why is processing of our stored procedures stopping? As it is probably optimistic to expect a precise answer, what is the correct step to debugging this? Perhaps there is something in one of the DMVs like dm_tran_locks, dm_exec_requests, dm_tran_database_transactions, dm_os_schedulers, dm_exec_sessions that, whil

You won't only see ENCRYPTION_SCAN resource in your wait list when Encryption (like TDE) is used.

Certain operations will take a shared lock on this resource to make sure the database is not being encrypted during the operation.

The moment you would encrypt a user database with TDE, the tempdb will also be encrypted (otherwise, you would have security risk when User data is used in temp db).

Therefore, some operations will take a shared lock on ENCRYPTION_SCAN in Tempdb to prevent Tempdb from getting encrypted.

Here are two examples:

BULK INSERT

IF object_id('tempdb..##NumberCreation') IS NOT NULL
    drop table ##NumberCreation
GO

--create temp table to hold numbers
create table ##NumberCreation (C int NOT NULL);
GO

-- CREATE Numbers by using trick from Itzik -> http://sqlmag.com/sql-server/virtual-auxiliary-table-numbers 
WITH L1 AS ( SELECT 1 as C UNION SELECT 0 ),
    L2 AS ( SELECT 1 as C FROM L1 CROSS JOIN L1 as B ),
    L3 AS ( SELECT 1 as C FROM L2 CROSS JOIN L2 as B ),
    L4 AS ( SELECT 1 as C FROM L3 CROSS JOIN L3 as B ),
    L5 AS ( SELECT 1 as C FROM L4 CROSS JOIN L4 as B ),
    L6 AS ( SELECT 1 as C FROM L5 CROSS JOIN L5 as B),
    Nums as (SELECT ROW_NUMBER() OVER (ORDER BY C) as C FROM L6) 
insert ##NumberCreation(C)
SELECT TOP 500000 C
FROM Nums

The above code will generate 500k records in a global temp table, you can export these with the following commands. If you run this from SSMS, make sure you are in SQLCMD mode:

--Export
!!bcp ##NumberCreation out "E:\SQLServer\Backup\test\export.dat" -T -n

--format file
!!bcp ##NumberCreation format nul -T -n  -f "E:\SQLServer\Backup\test\export.fmt"

Make sure to choose a directory where SQL Server service account has write permissions and if you run this from SSMS, run it locally on the SQL Server.

Next thing is to start a bulk insert loop. While the loop is running, open a second screen and start running sp_lock untill you see the ENCRYPTION_SCAN shared lock in DB_ID 2 (Which is Tempdb).

The bulk import loop:

BEGIN
    IF OBJECT_ID('tempdb..#Import') IS NOT NULL
        DROP TABLE #Import ;

    CREATE TABLE #Import (C INT) ;
    BULK INSERT #Import
    FROM 'E:\SQLServer\Backup\test\export.dat' WITH (FORMATFILE='E:\SQLServer\Backup\test\export.fmt', FIRSTROW=1, TABLOCK) ;
END
GO 500 --run it 500 times

See the result of sp_lock in second window:

SORT IN TEMPDB

With the same Temp table in place start this very simple loop:

SELECT * from #Import order by C
go 50

It will produce the following Execution plan:

(Make sure that #Import is actually populated, since depending on when you stopped the previous bulk import loop, it could be empty!)

Again, run sp_lock in a second window until you see the ENCRYPTION_SCAN Resource popping up:

Now you know, why this resource wait is showing up. It could be very well that this is not your problem. I'd just wanted to point out the other reasons that make ENCRYPTION_SCAN show up. The reason for your query slowdown might be something else. I'll leave improving your query plan up to the query plan experts on this site ;-) However, could you post the actual execution plan as well instead of just the estimated plan?

StackExchange Database Administrators Q#54932, answer score: 5