How Using the Windows 2000 Encrypted File System to Secure SQL Server Databases and Backups Affects SQL Server's Performance

Now for the actual testing. The following operations were repeated five times on each database (encrypted and non-encrypted). The instance was re-started after each test cycle and the order in which the tests were done, i.e., EFS database (encrypted) then Non-EFS database (non-encrypted), and visa versa, also changed. The figures below are averages of the runs made.

Table Name

Rows

189,362

DB

DML/DDL Operation

Results

EFS

select * into tc from otherdb.dbo.tc

checkpoint

SQL Server parse and compile time:

   CPU time = 0 ms, elapsed time = 140 ms.

Table ‘tc’. Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0.

Table ‘tc’. Scan count 1, logical reads 6321, physical reads 0, read-ahead reads 6313.

SQL Server Execution Times:

   CPU time = 3766 ms,  elapsed time = 4559 ms.

EFS

drop table tc

SQL Server Execution Times:

   CPU time = 16 ms,  elapsed time = 68 ms.

EFS

select count(*) from tc

Table ‘tc’. Scan count 1, logical reads 5911, physical reads 0, read-ahead reads 5912.

SQL Server Execution Times:

   CPU time = 93 ms,  elapsed time = 4296 ms.

EFS

update tc
set tc_admindiv_id = ‘R8’
where tc_prob_expired = ‘2003-01-24 00:00:00.000’

Table ‘tc’. Scan count 1, logical reads 5933, physical reads 187, read-ahead reads 3622.

SQL Server Execution Times:

   CPU time = 141 ms,  elapsed time = 4861 ms.

EFS

delete from tc where tc_prob_expired = ‘2003-01-24 00:00:00.000’

Table ‘tc’. Scan count 1, logical reads 5933, physical reads 0, read-ahead reads 5912.

SQL Server Execution Times:

   CPU time = 234 ms,  elapsed time = 4281 ms.

EFS

CREATE CLUSTERED
INDEX [ix_tc] ON [dbo].[tc] ([tc_id])

SQL Server parse and compile time:

   CPU time = 219 ms, elapsed time = 2556 ms.

Table ‘tc’. Scan count 2, logical reads 11822, physical reads 187, read-ahead reads 3622.

SQL Server Execution Times:

   CPU time = 5078 ms,  elapsed time = 12452 ms.

SQL Server Execution Times:

   CPU time = 5203 ms,  elapsed time = 12587 ms.

EFS

select * from tc where tc_id = ‘22343246641’

Table ‘tc’. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0.

SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 28 ms.

NON

EFS

select * into tc from otherdb.dbo.tc

checkpoint

SQL Server parse and compile time:

   CPU time = 16 ms, elapsed time = 143 ms.

Table ‘tc’. Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0.

Table ‘tc’. Scan count 1, logical reads 6321, physical reads 0, read-ahead reads 6313.

SQL Server Execution Times:

   CPU time = 3547 ms,  elapsed time = 4468 ms.

NON

EFS

drop table tc

SQL Server Execution Times:

   CPU time = 16 ms,  elapsed time = 57 ms.

NON

EFS

select count(*) from tc

Table ‘tc’. Scan count 1, logical reads 5911, physical reads 0, read-ahead reads 5912.

SQL Server Execution Times:

   CPU time = 140 ms,  elapsed time = 1604 ms.

NON

EFS

update tc
set tc_admindiv_id = ‘R8’
where tc_prob_expired = ‘2003-01-24 00:00:00.000’

Table ‘tc’. Scan count 1, logical reads 5933, physical reads 182, read-ahead reads 3384.

SQL Server Execution Times:

   CPU time = 250 ms,  elapsed time = 1902 ms.

NON

EFS

delete from tc where tc_prob_expired = ‘2003-01-24 00:00:00.000’

Table ‘tc’. Scan count 1, logical reads 5933, physical reads 0, read-ahead reads 5912.

SQL Server Execution Times:

   CPU time = 188 ms,  elapsed time = 1673 ms.

NON

EFS

CREATE CLUSTERED
INDEX [ix_tc] ON [dbo].[tc] ([tc_id])

SQL Server parse and compile time:

   CPU time = 109 ms, elapsed time = 977 ms.

Table ‘tc’. Scan count 2, logical reads 11822, physical reads 182, read-ahead reads 3384.

SQL Server Execution Times:

   CPU time = 5250 ms,  elapsed time = 8430 ms.

SQL Server Execution Times:

   CPU time = 5375 ms,  elapsed time = 8562 ms.

NON

EFS

select * from tc where tc_id = ‘22343246641’

Table ‘tc’. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0.

SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 18 ms.

Continues…

Leave a comment

Your email address will not be published.