Already on ArticleSlash?

Forgot your password? Sign Up

SQL Server Optimization and the Performance Impact of Encryption


Visitors: 278

In the Enterprise and Developer editions of the software SQL Server 2008, a new security feature of transparent data encryption (TDE) has been included. It has a slightly negative overall performance impact on the SQL Server optimization. It is to the extent of 3 to 5 %. However, if most of the data is stored in memory, the impact is lesser.

The encryption is highly CPU (central processing unit) intensive as it takes place at the database level. Moreover, it is performed at I/O (input/output).

Therefore, servers that experience a high CPU load and high I/O are impacted more. The performance impact is to the extent of 28% in such a case. However, in the case of servers with low I/O and low CPU load the performance impact is low. Even so, the primary reason for the performance impact is the CPU load and the high I/O causes minimal impact if the CPU load is low.

The encryption is at the file level but does not occupy disk space as TDE does not pad the database files on disk, even if it does pad transaction logs. Therefore, the experience of a user who accesses the database and sends a query is that the response of the software is as fast as it was in SQL Server 2005 in which only cell-level encryption feature is available.

Even backups of a database become encrypted when TDE is enabled. Therefore the certificate that protected the DEK (data encryption key) is backed up and stored along with the database backup. If the certificate is lost, the data will become unreadable. Therefore, it is better to have two backups of the certificate and both of which are sent for archiving safely separate from the database backup for security.

On the other hand, in the case of cell-level encryption, the performance impact on SQL Server optimization is 20% more than it is for TDE. This is because the encryption and decryption process in this case is manual. Further, due to encryption the value of the column attributes changes and therefore the data type of the column must be changed to varbinary and then recast back to the original value after decryption. The manual process means that none of the automatic techniques used by the SQL Server query optimizer will work.

Cell-level encryption has a number of advantages over the database level encryption provided by TDE in that the encryption is more granular and that data is not decrypted until used. It can be useful for a targeted security environment. However, the burden of extensible key management for the administrator is a distinct disadvantage.

The greatest disadvantage of cell-level encryption on SQL Server optimization is in the high performance penalty and the cost of administration. Even query optimization is affected as indexes on encrypted columns offer no benefit.

Learn more about Database Management and SQL server database at =>


Article Source:

Rate this Article: 
SQL Server Compare and SQL Server Migration - Make your Life Easier
Rated 4 / 5
based on 5 votes

Related Articles:

Disk Controllers and Oracle Server Performance Tuning

by: Gitesh Trivedi (August 22, 2012) 
(Computers and Technology/Software)

Search Engine Optimization – Article Marketing And Its Impact On SEO

by: Sean Ray (February 21, 2007) 
(Internet and Businesses Online)

Search Engine Optimization - Impact of Hosting Location

by: Ridzwan Abdul Rahman (January 02, 2008) 
(Internet and Businesses Online/SEO)

Sql Server Interview Questions on File Server and Client Server Database

by: Adam Sturo (April 25, 2007) 
(Computers and Technology)

Difference between VPS(Virtual Private Server) server and Dedicated Server.

by: Debasish Pramanik (February 25, 2015) 
(Internet and Businesses Online/Web Hosting)

Impact of Test Anxiety and Self-Efficacy on Test Performance

by: Maureen Archer (April 15, 2008) 
(Reference and Education/Psychology)

About 70-458: Transition your MCTS on SQL Server 2008 to MCSA SQL Server 2012 ..

by: Jason S. Lawson (July 08, 2014) 
(Computers and Technology/Certification Tests)

70-457: Transition your MCTS on SQL server 2008 to MCSA: SQL server 2012 Part 1 .

by: Jason S. Lawson (July 08, 2014) 
(Computers and Technology/Certification Tests)

The Benefits of Cloud Server Technology over Traditional Server Hosting

by: Rahul Gupta (April 18, 2012) 
(Internet and Businesses Online/Web Hosting)

SQL Server Compare and SQL Server Migration - Make your Life Easier

by: Daniel Zr (April 24, 2011) 
(Internet and Businesses Online/Web Development)