SQL,Server,Optimization,And,Th computer SQL Server Optimization And The Performance Impact Of Encryp
----------------------------------------------------------Permission is granted for the below article to forward,reprint, distribute, use for ezine, newsletter, website,offer as free bonus or part of a product for sale as longas no changes a Gone are those times when the companies and the organisations didn't need a hi-tech system to handle them. Owing to the considerable increase in the business sector and thus, an enormous increase in the complexity of the organisational struc
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. Article Tags: Server Optimization, Performance Impact, Cell-level Encryption
SQL,Server,Optimization,And,Th