Decreased performance in SQL Server when you use EFS to encrypt database files
Applies to: SQL Server
Original KB number: 922121
Symptoms
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.
Cause
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.
Note
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.
Workaround
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.
Note
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.
References
Feedback
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see: https://aka.ms/ContentUserFeedback.
Submit and view feedback for