大网数据广告活动
数据库与应用安全

Decreased performance when using EFS to encrypt database files

· 52CE 我爱测

Provides workarounds for the decreased performance issue in SQL Server when you use Encrypting File System 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

Configure a Secure File System

Source: Microsoft Learn / IT Pro troubleshooting by Microsoft and contributors (upstream author metadata: aartig13). Documentation licensed CC BY 4.0. Code samples are covered by the MIT License. Converted from Markdown, with links normalized and embedded media omitted.

资料来源与适用范围

资料来源
Microsoft Learn / IT Pro troubleshooting
适用范围
See source applicability · Source date 12/08/2021
内容语言
中文;命令、路径和错误标识保留技术原文
本地收录日期
2026-11-15
授权说明
CC BY 4.0 (documentation), MIT (code samples)

正文与所需资源由本站本地存储。查看来源及更新记录 ↗

请按适用版本和实际日志确认原因;本站没有对每一种系统或设备逐项执行文中的操作。