SQL 2008 Encryption Scan

Question

We recently upgraded a database server from SQL 2005 to SQL 2008 64 bit. CPU utilization is oftentimes running at 100% on all four processors now (this never happended on the SQL 2005 server). When I run sp_lock I see a number of processes waiting on a resource called [ENCRYPTION_SCAN]. I am not using any SQL 2008 encryption features. Does anyone know why I would have tasks waiting on this resource? It appears that whenever I have four processes waiting on this resource, CPU hits 100% on all four processors.

Answer 1

Transparent data encryption (TDE) performs real-time I/O encryption and decryption of the data and log files.

http://msdn.microsoft.com/en-us/library/bb934049.aspx

Do you have this enabled?

Update:

Can you tell what operations are being blocked? e.g., inserts, deletes, updates, index scans, etc..

TDE is the only scenario where I've ever seen any reference to an "encryption scan". A quick search only validates this:

... The server starts a background thread (called the encryption scan or scan) ... . While the DDL executes, an update lock is taken on the database. The encryption scan, which runs asynchronously to the DDL, takes a shared lock. All normal operations that do not conflict with these locks can proceed. Excluded operations include modifying the file structure and detaching the database.

msdn

This makes it sound like the ongoing "encryption scan" you're seeing implies that encryption is being enabled and disabled repeatedly on the database, or the key is changing, because these delays should only be seen when the encryption state changes. Either that or you have a very large database, and when it eventually finishes the problem will just go away.

Try the following query:

SELECT DB_NAME(e.database_id) AS DatabaseName,
            e.database_id,
            e.encryption_state,
    CASE e.encryption_state
                WHEN 0 THEN 'No key present - encryption unavailable'
                WHEN 1 THEN 'Unencrypted'
                WHEN 2 THEN 'Encryption in progress'
                WHEN 3 THEN 'Encrypted'
                WHEN 4 THEN 'Key change in progress'
                WHEN 5 THEN 'Decryption in progress'
    END AS encryption_state_desc,
            c.name,
            e.percent_complete
    FROM sys.dm_database_encryption_keys AS e 
    LEFT JOIN master.sys.certificates AS c 
    ON e.encryptor_thumbprint = c.thumbprint