在SQL服务器上启用透明数据加密(TDE)的性能影响分析

315 阅读10分钟

透明数据加密(TDE)对数据库文件进行加密以保护你的数据。它还对tempdb数据库进行加密,以确保你在临时空间的数据安全。加密和解密的过程给数据库系统增加了额外的开销。由于tempdb的加密,即使是托管在同一SQL Server实例上的非加密数据库也会有一些性能下降。今天,我将用几个简单的T-SQL语句向你展示性能影响分析,比较它们在启用TDE之前和之后收集的统计数据。

我将在启用加密(透明数据加密)之前和之后执行T-SQL语句(INSERT, UPDATE, SELECT, BACKUP DATABASE),并在每次执行期间收集它们的性能统计数据。最后,一旦我们有了启用加密前后的性能统计,我们就会对它们进行比较,以了解性能影响分析。在执行每个查询之前,我都使用了DBCC DROPCLEANBUFFERS来清理缓冲区的缓存。

性能分析将基于以下3个参数来观察。

  • CPU时间
  • 物理_IO
  • 消耗的时间

CPU时间是一个进程在CPU上花费的总时间,而耗时是该任务端到端的总时间。Physical_IO是指该进程执行的磁盘读写次数。

读完这篇文章后,你会得出一个结论,TDE是否给数据库事务带来了一些额外的工作量,或者它是一个神话。我将使用非常简单明了的T-SQL语句来保持它在系统上的执行非常简单。

系统性能取决于各种因素,如你的系统配置,现有的工作量,糟糕的应用程序编程,和一些高级配置等。除了为我们的目标数据库启用TDE之外,我没有在系统中做任何改变。其余的所有因素在启用加密之前和之后都是一样的。

我把这个分析分为3个部分。

  1. 在启用透明数据加密前分析性能
    • INSERT操作
    • UPDATE语句
    • SELECT语句
    • 数据库备份
  2. 启用透明的数据加密
  3. 启用透明数据加密后的性能分析
    • INSERT操作
    • UPDATE语句
    • SELECT语句
    • 数据库备份

在第一部分中,我将在启用TDE之前收集上述每个操作的性能统计,然后我将在用户数据库testdb上启用加密,我正在做这个测试,最后我将再次收集启用加密后每个操作的性能统计。

准备工作

为这次测试创建一个名为TESTDB的数据库,然后创建一个有3列代码名称日期的行表testable。在测试过程中,运行下面的语句来创建它们。

--Sample database and table creation
CREATE DATABASE TESTDB
GO
USE TESTDB
GO
CREATE TABLE testtable (
    [code] INT IDENTITY,
    [date] DATETIME DEFAULT GETDATE (),
    [name] CHAR (25) DEFAULT 'SQLSHACK');

现在,我将在这个表中INSERT、UPDATE和SELECT数据,并收集它们的性能统计,以便进一步分析。

分析启用透明数据加密前的性能

本节将收集下面给出的每个T-SQL语句的性能统计数据。我使用了SET STATISTICS TIMESET STATISTICS IO语句来收集它们的细节。让我们从INSERT语句开始。

在INSERT操作中的性能统计

运行下面的INSERT语句,在上面新创建的表testtable中插入10000行。使用SET STATISTICS TIMESET STATISTICS IO语句来收集它们的性能统计细节。

USE Testdb
GO
SET STATISTICS TIME ON
SET STATISTICS IO ON
GO
INSERT INTO testtable DEFAULT VALUES ;
GO 10000
SET STATISTICS IO OFF
SET STATISTICS TIME OFF

下面是上述语句的输出。

Performance stats of INSERT statement before data encryption

上述执行的会话ID显示为56,所以我使用下面的T-SQL语句从sysprocesses系统视图中检索了该会话的性能统计。

SELECT spid, cpu, physical_io, memusage, st.text 
FROM sysprocesses AS sp
CROSS APPLY sys.dm_exec_sql_text(sp.sql_handle) st
WHERE spid IN (56)

上述语句的输出结果如下。

Check sysprocesses for INSERT statement

让我们运行UPDATE语句并收集类似的细节。

在UPDATE操作中的性能统计

在运行这些语句之前,我使用了DBCC DROPCLEANBUFFERS,以确保我们收集了正确的细节进行比较。运行下面的T-SQL语句来更新上面插入的行。

USE Testdb
GO
SET STATISTICS TIME ON
SET STATISTICS IO ON
GO
UPDATE testtable
SET name='SQL Server'
GO
SET STATISTICS IO OFF
SET STATISTICS TIME OFF

下面是上述执行的性能统计。

Performance stats for UPDATE statement before encryption

我还检查了上述session-id 57的sysprocesses表,以查看其其他性能统计。

sysprocesses for UPDATE statement

SELECT操作期间的性能统计

我还收集了SELECT语句的性能统计资料。我运行了下面的SELECT语句来返回我们在上述步骤中插入和更新的所有记录。

USE testdb
GO
SET STATISTICS TIME ON
SET STATISTICS IO ON
GO
SELECT code, name, date FROM testtable 
Go
SET STATISTICS IO OFF
SET STATISTICS TIME OFF

上面的执行结果显示了以下的统计数据。

Performance stats for SELECT statement before encryption

我正在从sysprocesses系统视图中收集每个会话ID的详细信息,所以在这里我也做了同样的工作,下面是它的输出。

sysprocesses for SELECT statement

备份操作中的性能统计

我还执行了一个数据库备份,并观察其性能统计。我执行了下面的语句来捕获数据库备份期间的这些细节。

SET STATISTICS TIME ON
SET STATISTICS IO ON
GO
BACKUP DATABASE testdb 
TO DISK =’C:\program files\testdb.bak
Go
SET STATISTICS IO OFF
SET STATISTICS TIME OFF

看一下这个输出。

BACKUP DATABASE performance stats before enabling Transparent Data Encryption

系统对象sysprocesses对这个会话id 70有以下细节的捕获。

sysprocesses for backup database

接下来,我将在这个数据库TestDB上启用透明数据加密,然后我将重新启动SQL Server。一旦SQL Server实例上线,我将以相同的顺序执行相同的查询,以收集它们的性能统计数据,看看它们的比较。

启用透明数据加密

本节将帮助我们在用户数据库TESTDB上启用TDE,我们正在研究启用加密(透明数据加密)之前和之后的性能分析。

运行下面一组T-SQL语句,在用户数据库TESTDB上启用TDE。

--Create master key
USE master;
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD='$ql$h@ck@12';
GO
 
--Create Certificate using above master key
CREATE CERTIFICATE TestDB_TDECert
WITH 
SUBJECT='TestDB_Encryption';
GO
 
--Create a database encryption key
USE TestDB
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TestDB_TDECert;
GO
 
--Enable encryption
ALTER DATABASE TestDB
SET ENCRYPTION ON;
GO

一旦你执行了上述语句,TDE将为数据库TESTDB被启用。接下来,检查并验证TESTDB数据库是否已经使用透明的数据加密技术进行了加密。我已经执行了下面的语句,从这个SQL Server实例中返回所有加密的数据库。

--Check TDE for user database
SELECT DB_Name(database_id) As [DB Name], encryption_state_desc
FROM sys.dm_database_encryption_keys
Go

这里是它的输出,显示用户数据库TESTDB现在已经被加密了。我们知道,如果我们为任何用户数据库启用透明数据加密,tempdb数据库也会被加密,这就是为什么tempdb在下面的图片中显示为ENCRYPTED

Check Transparent Data Encryption

现在,我们已经在用户数据库TESTDB上启用了TDE。接下来,我们将重复我们在本文第一部分所做的活动,收集INSERT、UPDATE、SELECT和BACKUP DATABASE每次执行的性能统计数据。重新启动你的SQL Server实例,从头开始收集性能统计数据。虽然这不是强制性的,但我在这次分析中做了。

分析启用透明数据加密后的性能

在本节中,我不会再给出T-SQL语句,因为我们将执行与第一节相同的语句。在启用加密功能后,使用你在启用前为INSERT、UPDATE、SELECT和BACKUP DATABASE执行的相同语句,如第一节所示。如果你要对一些不同的语句集进行分析,那么在数据库上启用TDE之前和之后也要使用相同的语句。

INSERT操作中的性能统计

我从第一部分复制了INSERT操作的查询,并执行了它,得到了下面的统计数据。

INSERT statement performance stats after TDE

我还从sysprocesses系统对象中获取了这个会话ID 55的详细信息。我从sysprocesses系统视图中得到以下结果。

sysprocesses for INSERT statement post encryption

现在你可以把它的细节与我们在第一节中捕获的细节进行比较。下面是我们在比较一个简单的INSERT语句执行时的发现。

  • CPU时间从2078ms增加到2091ms
  • Physical_io也从194增加到483
  • 耗时从44秒增加到46秒

上述发现清楚地表明,在用户数据库上启用TDE后,其利用率和执行时间都有所增加。让我们在下一节中检查UPDATE语句的性能统计。

UPDATE操作期间的性能统计

再次使用相同的UPDATE T-SQL语句,在第一节中启用透明数据加密之前,你已经执行了该语句来收集性能统计。我执行了同样的脚本,得到了下面的输出。

Performance stats for UPDATE statement post encryption

我再次从sysprocesses系统对象中检查了会话id 65的细节。请看它的输出。

sysprocesses details for UPDATE statement after encryption

下面是执行UPDATE语句的结果。

  • CPU时间与启用加密前相同
  • 启用加密后,耗时从32ms明显增加到259ms
  • 加密后物理_IO也从146增加到169

SELECT操作期间的性能统计

我们来分析一下SELECT语句。执行你在第一节中使用过的相同的T-SQL语句,以收集其性能统计数据。

SELECT statement performance stats after encryption

下面显示的是来自synsprocesses系统表的其他性能统计,该系统表的会话ID为52,上述事务是在该表下执行的。

sysprocesses details for SELECT statement after encryption

找到在用户数据库TESTDB上启用透明数据加密后的SELECT语句的观察结果。

  • 加密后CPU时间从0增加到16ms
  • 启用加密后,耗时从242ms增加到244ms
  • 加密后,Physical_IO出人意料地从248减少到162。这可能有多种因素。这并不是说每次物理_IO都会减少。

备份操作期间的性能统计

最后一个用例是数据库备份。使用你在启用TDE之前使用的相同脚本运行数据库备份,看看性能统计的差异。下面是我执行的输出。

performance stats of BACKUP DATABASE statement after enabling Transparent Data Encryption

系统对象sysprocesses对上面的会话id 72有以下细节。

sysprocesses details for BACKUP DATABASE statement after encryption

以下是在用户数据库TESTDB上启用透明数据加密后的BACKUP DATABASE语句的发现。

  • 加密后CPU时间从124ms增加到185ms
  • 启用加密后,耗时从791ms增加到848ms
  • 加密后的Physical_IO也从2317增加到2950

总结

我对一些DML语句和数据库备份做了一个简单的比较,看看TDE的性能影响是否对数据库性能有任何影响。我们可以看到透明数据加密增加了CPU和物理IO的开销,在这个分析中我们测试了每一个执行。我还在下面的表格中给出了每个执行的逐行比较。看一下这些数据,你就会知道,这种加密方式会给你的数据库系统带来一些额外的负担。

操作类型

启用TDE之前的性能统计

启用TDE后的性能统计

CPU

毫秒

耗时

毫秒

物理读取

CPU

ms

经过的时间

毫秒

物理读取

INSERT

2078

44秒

194

2091

46秒

483

更新

31

32

146

15

259

169

选择

0

242

248

16

244

162

备份

124

791

2317

185

848

2950