SQL 2008 Encryption Scan


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.

Best Solution

I've been seeing this same question crop up in a few different places. It hasn't been proven definitively yet, but it looks like long-running BULK INSERT or SORT operations could be pessimistically holding an ENCRYPTION_SCAN lock in order 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'll link back to it here when/if it comes out.