Encryption and External Key Management in Microsoft SQL Server 2008

A comprehensive solution for encryption and key management is provided by Microsoft SQL Server 2008. The growing need for greater information security within the data centers of enterprises is managed by security keys. 


This could be done by excellent support given by Microsoft SQL Server 2008, by supporting third-party keys management and hardware security module products.

Plan Freezing in Microsoft SQL Server 2008

Plan freezing is a new concept that allows the DBAs to persist plan guides. These guides could be reverted to when the queries either fail or drain the resources after the upgrade. The stability to queries is achieved by Plan Freezing. 


Several monitoring features for checking when the query is succeeded or failed are included with Plan Freezing.

Role of Resource Governor in Microsoft SQL Server 2008

Resource Governor enables the DBA for managing the work load of Microsoft SQL Server and critical system resource consumption. The limits of CPU and memory which are the incoming sessions to the SQL Server will be controlled by Resource Governor.

The various scenarios that occur when sudden spike in CPU and memory utilization that result in slow responses for querying requests. 

The Resource Governor enables the DBA’s to differentiate the workloads and allocates the shared resources which allow the available CPU and memory resources.

Filtered index in Microsoft SQL Server 2008

Filtered index in SQL Server 2008 is an index WHERE clause. A filtered index is an optimized non-clustered index. It allows for defining the filter predicate with WHERE clause at the time of creating index. The rows from the filtered index are available in B-Tree will contain the rows which only satisfy the criteria of the filter while creating the index.

The advantages of Filtered indexes are :

1. Has only that row which satisfies the filter criteria defined. This results in reducing the storage space need for the index.
2. The filtered index statistics are accurate and more compact. The reason is they consider only the tuples / rows in the filtered index and it reduces the cost of overhead of updating the statistics.
3. The data modification impact is less by using filtered index. Because it is updated only at the time where the data of the index is impacted.
4. The cost of maintenance will be reduced, because only the subset of rows will be considered which rebuilding the index.

Row Level and Page Level Data Compression in Microsoft SQL Server 2008

Data compression is a feature which is used to reduce disk storage space and increase the performance of the query by reducing the I/O operations.

SQL Server 2008 supports two types of compression :

Row-level compression 

Page-level compression.

A row-level and page-level compression takes place on the metadata. Page level compression results in persisting certain common data that affects rows in a single location. 

The compression takes place into number of bits. For example, the length of ‘varchar’ will be stored in 3 bits.