Scan SQL 2008 encryption

Question

We recently upgraded a database server from SQL 2005 to SQL 2008 64 bit. CPU utilization often runs 100% on all four processors (this never happened on SQL 2005 server). When I run sp_lock, I see several processes waiting for a resource named [ENCRYPTION_SCAN]. I do not use any SQL 2008 encryption features. Does anyone know why I will have tasks waiting for this resource? It seems that whenever I have four processes waiting for this resource, the CPU reaches 100% on all four processors.

Answer 1

I saw the same question in several different places. This has not yet been proven, but it looks like long-running BULK INSERT or SORT operations can pessimistically hold the ENCRYPTION_SCAN lock to prevent an encryption scan from starting in the middle of the operation. Look for a blog from CSS on this in the near future, I will contact him here when / if it comes out.

Answer 2

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

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

Do you have it?

Update:

Can you tell which operations are being blocked? for example, inserts, deletes, updates, scans an index, etc.

TDE - , - " ". :

... ( ).... DDL , . , DDL, . , , . .

msdn

, " ", , , , . , , , .

:

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