透明数据加密(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个部分。
- 在启用透明数据加密前分析性能
- INSERT操作
- UPDATE语句
- SELECT语句
- 数据库备份
- 启用透明的数据加密
- 启用透明数据加密后的性能分析
- 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 TIME和SET STATISTICS IO语句来收集它们的细节。让我们从INSERT语句开始。
在INSERT操作中的性能统计
运行下面的INSERT语句,在上面新创建的表testtable中插入10000行。使用SET STATISTICS TIME和SET 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
下面是上述语句的输出。
上述执行的会话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)
上述语句的输出结果如下。
让我们运行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
下面是上述执行的性能统计。
我还检查了上述session-id 57的sysprocesses表,以查看其其他性能统计。
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
上面的执行结果显示了以下的统计数据。
我正在从sysprocesses系统视图中收集每个会话ID的详细信息,所以在这里我也做了同样的工作,下面是它的输出。
备份操作中的性能统计
我还执行了一个数据库备份,并观察其性能统计。我执行了下面的语句来捕获数据库备份期间的这些细节。
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
看一下这个输出。
系统对象sysprocesses对这个会话id 70有以下细节的捕获。
接下来,我将在这个数据库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。
现在,我们已经在用户数据库TESTDB上启用了TDE。接下来,我们将重复我们在本文第一部分所做的活动,收集INSERT、UPDATE、SELECT和BACKUP DATABASE每次执行的性能统计数据。重新启动你的SQL Server实例,从头开始收集性能统计数据。虽然这不是强制性的,但我在这次分析中做了。
分析启用透明数据加密后的性能
在本节中,我不会再给出T-SQL语句,因为我们将执行与第一节相同的语句。在启用加密功能后,使用你在启用前为INSERT、UPDATE、SELECT和BACKUP DATABASE执行的相同语句,如第一节所示。如果你要对一些不同的语句集进行分析,那么在数据库上启用TDE之前和之后也要使用相同的语句。
INSERT操作中的性能统计
我从第一部分复制了INSERT操作的查询,并执行了它,得到了下面的统计数据。
我还从sysprocesses系统对象中获取了这个会话ID 55的详细信息。我从sysprocesses系统视图中得到以下结果。
现在你可以把它的细节与我们在第一节中捕获的细节进行比较。下面是我们在比较一个简单的INSERT语句执行时的发现。
- CPU时间从2078ms增加到2091ms
- Physical_io也从194增加到483
- 耗时从44秒增加到46秒
上述发现清楚地表明,在用户数据库上启用TDE后,其利用率和执行时间都有所增加。让我们在下一节中检查UPDATE语句的性能统计。
UPDATE操作期间的性能统计
再次使用相同的UPDATE T-SQL语句,在第一节中启用透明数据加密之前,你已经执行了该语句来收集性能统计。我执行了同样的脚本,得到了下面的输出。
我再次从sysprocesses系统对象中检查了会话id 65的细节。请看它的输出。
下面是执行UPDATE语句的结果。
- CPU时间与启用加密前相同
- 启用加密后,耗时从32ms明显增加到259ms
- 加密后物理_IO也从146增加到169
SELECT操作期间的性能统计
我们来分析一下SELECT语句。执行你在第一节中使用过的相同的T-SQL语句,以收集其性能统计数据。
下面显示的是来自synsprocesses系统表的其他性能统计,该系统表的会话ID为52,上述事务是在该表下执行的。
找到在用户数据库TESTDB上启用透明数据加密后的SELECT语句的观察结果。
- 加密后CPU时间从0增加到16ms
- 启用加密后,耗时从242ms增加到244ms
- 加密后,Physical_IO出人意料地从248减少到162。这可能有多种因素。这并不是说每次物理_IO都会减少。
备份操作期间的性能统计
最后一个用例是数据库备份。使用你在启用TDE之前使用的相同脚本运行数据库备份,看看性能统计的差异。下面是我执行的输出。
系统对象sysprocesses对上面的会话id 72有以下细节。
以下是在用户数据库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 |