Transparent Data Encryption in a SQL Server Fast Track Data Warehouse

SQL Server Fast Track Reference Architecture is a set of prescriptive hardware configurations and operational practices for building large data warehouses on SQL Server.  There are reference configurations ranging from a few terabytes to 40TB and more.  The really useful thing about the Fast Track configurations is that they have predictable performance.  Typically when you put together a data warehouse solution with mix-and-match server, SAN (HBA, SAN Controller, RAID sets, disks), or direct-attached storage (RAID Controllers, RAID sets, disks), it's impossible to predict the performance when you start, and difficult to know if you're getting the best possible performance once it's built.  Additionally the build and configuration requires expertise crossing many different IT trades, so you may need an entire village of IT support to design, build and validate the solution.

Here in the Microsoft Technology Center in Dallas we have a Fast Track configuration from HP rated for about 20TB of uncompressed user data, comprised of an HP DL 580 G7 and 6 HP StorageWorks P2000 disk enclosures. 

A customer recently asked us about the impact of Transparent Data Encryption (TDE) on Fast Track, and we have no published numbers of this combination.  Normally I would tell them the downsides of TDE:

 

· Data is encrypted as it is read from disk and encrypted as it is written to disk, so expect additional CPU usage on large scan queries and on Checkpoint.

· TempDB is encrypted if any user database is encrypted, so these affects spill over to TempDb.

· Backup Compression is not recommended when using TDE, as encrypted data is hard to compress.

 

But, again, the cool thing about Fast Track is predictable performance, so a simple test on a particular Fast Track config is a lot more informative than the same test on a random lab system.  So I decided to do some simple testing.

First I created a table and filled it with 100,000,000 rows of data, with PAGE compression enabled.

 

CREATE TABLE [dbo].[FactInternetSales_100M](

      [ProductKey] [int] NOT NULL,

      [OrderDateKey] [int] NOT NULL,

      [DueDateKey] [int] NOT NULL,

      [ShipDateKey] [int] NOT NULL,

      [CustomerKey] [int] NOT NULL,

      [PromotionKey] [int] NOT NULL,

      [CurrencyKey] [int] NOT NULL,

      [SalesTerritoryKey] [int] NOT NULL,

      [SalesOrderNumber] [nvarchar](20) NOT NULL,

      [SalesOrderLineNumber] [tinyint] NOT NULL,

      [RevisionNumber] [tinyint] NOT NULL,

      [OrderQuantity] [smallint] NOT NULL,

      [UnitPrice] [money] NOT NULL,

      [ExtendedAmount] [money] NOT NULL,

      [UnitPriceDiscountPct] [float] NOT NULL,

      [DiscountAmount] [float] NOT NULL,

      [ProductStandardCost] [money] NOT NULL,

      [TotalProductCost] [money] NOT NULL,

      [SalesAmount] [money] NOT NULL,

      [TaxAmt] [money] NOT NULL,

      [Freight] [money] NOT NULL,

      [CarrierTrackingNumber] [nvarchar](25) NULL,

      [CustomerPONumber] [nvarchar](25) NULL,

 CONSTRAINT [PK_FactInternetSales_100m] PRIMARY KEY CLUSTERED

(

      [SalesOrderNumber] ASC,

      [SalesOrderLineNumber] ASC,

      [OrderDateKey] ASC

)WITH (DATA_COMPRESSION = PAGE) ON [AWFactData]

) ON [AWFactData]

 

And put one copy of this table in each of two databases, one with TDE on and one with TDE off.  Now if you were paying attention earlier, you may complain that this isn’t a fair comparison as TempDb is going to be encrypted since there’s an encrypted user database on the system, and this may degrade the performance of the non-encrypted database.  But, oh well.  No test is perfect.  The first test, after loading the tables, is to rebuild and recompress them.  The rebuild statement is:

 

ALTER TABLE [dbo].[FactInternetSales_100m] ADD

  CONSTRAINT [PK_FactInternetSales_100m] PRIMARY KEY CLUSTERED

(

      [SalesOrderNumber] ASC,

      [SalesOrderLineNumber] ASC,

      [OrderDateKey] ASC

)WITH (data_compression=page,SORT_IN_TEMPDB = ON, maxdop=0)

--rebuilding without tde

-- SQL Server Execution Times:

-- CPU time = 2721562 ms, elapsed time = 66772 ms.

--rebuilding with tde

-- SQL Server Execution Times:

-- CPU time = 3036202 ms, elapsed time = 86807 ms.

Or 12% more CPU time, and 30% greater elapsed time.  After rebuild the size on disk of both tables is the same, 8081MB:

 

select SUM(page_count) * 8 / 1024 size_mb

from sys.dm_db_index_physical_stats(DB_ID(),object_id('FactInternetSales_100M'),null,null,null)

--size_mb

--------------------

--8081

use TDE_DW2008

select SUM(page_count) * 8 / 1024 size_mb

from sys.dm_db_index_physical_stats(DB_ID(),object_id('FactInternetSales_100M'),null,null,null)

--size_mb

----------------------

--8081

Now for the queries.  On both tables, I ran a simple aggregate query with a cold cache and with the table completely loaded in memory.

select SUM(salesAmount)

from AWDW2008..FactInternetSales_100M

The results are in the following matrix:

	Without Encryption		With Encryption		Cost of Encryption
	CPU (ms)	Elapsed (ms)	CPU (ms)	Elapsed (ms)	CPU (ms)	Elapsed (ms)
From Disk	43,694	2,805	206,093	4,206	372%	50%
From Memory	59,503	941	59,889	940	0%	0%

 

As expected the CPU use when scanning from disk was much higher, but even with a completely empty page cache the query execution time only increased by 50%.  The reason for this is that the V3 Fast Track configuration isn’t as “balanced” quite the same way as the V2 Fast Track configurations.  The V3 Fast Track configurations are a little more CPU-heavy, while the V2 were a bit disk-heavy. So when SQL Server is performing a massive parallel scan from disk in there is typically some extra available CPU.   In a typical Data Warehouse workload the extra CPU would be used to process queries and query operators that hit smaller, cached tables and indexes. 

Conclusions:

· TDE has very little cost for data queried from the page cache.

· With TDE enabled the CPU’s can be limiting factor in large scans. 

· However because of the free CPU capacity on the system the overall throughput even on a scan is not drastically impacted.

· Turning on TDE “unbalances” the system, taking it from being a little heavy on CPU to being a little light on CPU (compared to storage throughput).

Guidance:

Again, the useful thing about Fast Track is the predictable performance.  If you can see from these numbers that an encrypted data warehouse will meet your performance needs, using a Fast Track configuration with an encrypted database would be a simple and cost-effective solution.  If you know you are going to be using TDE, consider selecting a Fast Track variant that uses the larger disks and fastest CPU’s available.   The fast CPU’s help with the encryption and the larger disks provide more space without providing more storage performance.  Your CPU’s are likely to be a bottleneck in large IO operations, so you might as well get the extra space.  Also consider a more memory-heavy configuration, as reads from the page cache don't encur the encryption overhead.

David

dbrowne_at_microsoft

Special thanks to Mike Saleme from HP for reviewing this post.