Decreased performance in SQL Server when you use EFS to encrypt database files

Applies to:   SQL Server
Original KB number:   922121


When you use Encrypting File System (EFS) to encrypt database files in SQL Server, the performance of some SQL Server features is decreased. For example, the read-ahead and checkpoint features.


This issue occurs because asynchronous I/O requests from SQL Server are converted to synchronous I/O operations on an EFS-encrypted database file. See Asynchronous disk I/O appears as synchronous on Windows for more information. During the I/O operation, the worker thread waits until the I/O operation is complete. When the thread waits for the I/O operation, the SQL Server scheduler will be suspended until the current worker thread continues. Therefore, the worker threads that remain on the scheduler will be pending until the first worker thread continues the I/O operation. However, for asynchronous I/O, the thread requests the I/O and continues performing other tasks.


Asynchronous I/O still appears to be synchronous because of the New Technology File System (NTFS) compression. The file system driver will not access compressed files asynchronously. Instead, all operations are made synchronous.


SQL Server offers many encryption technologies, such as Transparent Data Encryption (TDE), Always Encrypted, and column-level encryption Transact-SQL functions. Consider using these encryption features instead of EFS.


When you use EFS to encrypt a database file, the whole database file is encrypted, regardless of the actual data and metadata that're contained in the database file. You can also use EFS in case of possible loss of physical media.


Configure a Secure File System