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

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.