Analyzing the Results
Here are the summarized results, based on the averaged results from the above table:
|
Database |
Operation |
CPU Time (ms) |
Elapsed Time (ms) |
|
EFS (encrypted) |
select * into … |
3766 (6%) |
4559 (2%) |
|
NON-EFS |
3547 |
4468 |
|
|
EFS (encrypted) |
drop table … |
16 (0) |
68 (16%) |
|
NON-EFS |
16 |
57 |
|
|
EFS (encrypted) |
select count(*) |
93 (-50%) |
4296 (62%) |
|
NON-EFS |
140 |
1604 |
|
|
EFS (encrypted) |
update tc… |
141 (-77%) |
4861 (61%) |
|
NON-EFS |
250 |
1902 |
|
|
EFS (encrypted) |
delete from … |
234 (19%) |
4281 (60%) |
|
NON-EFS |
188 |
1673 |
|
|
EFS (encrypted) |
create index… |
5078 (-3%) |
12452 (32%) |
|
NON-EFS |
5250 |
8430 |
|
|
EFS (encrypted) |
select … |
0 (0) |
28 (35%) |
|
NON-EFS |
0 |
18 |
The penalty for EFS is obvious, and therefore must be careful considered by the DBA in terms of their over-arching security framework. The figures for CPU time is somewhat strange and at times I found it to produce inconsistent figures. Elapsed time, on the other hand, seemed to be accurate.
The DBA should remember that EFS not only has a place in terms of database file security, but is equally important for database backup directories (which are naturally un-encrypted). A SLA may dictate a specific level of security for all backups which is very important when tapes are taken off-site or placed onto remote servers (duplexing). Again, given the figures above though, take care with dumping large transaction logs (although this architecture is somewhat different from the operations performed in the testing above).
At the end of the day, be very careful with EFS. It is an advanced option and having read a variety of news-group posts regarding recovery of servers and associated tape backups from EFS files, the process is far from simple. Take time to understand the EFS architecture via Microsoft Support and MSDN if you decide to use it, as this is critical in the wide gamete of recovery scenarios.
]]>