Microsoft SQL Server 2008 Extended Events

Extended Events are the enhanced event handling system in Microsoft SQL Server. It is an architecture that is highly scalable, highly configurable. It allows the DBAs for collecting the required information, could it be little or much, for problem identification or problem trouble shooting.

Data collection which can output to an Event Tracing for Windows target is one of the key features of Extended Events. This allows the correlating data with the data collected from the corresponding operating system with the help of Event Tracing for Windows. Even the wait events could be correlated with the kernel EWT data. This process is done within a single view for isolating the waits for a specific reason.

The events are handled by an engine which is a services and objects collection which allows defining, process and managing event sessions, event data and event sessions respectively.

Microsoft SQL Server 2008 Filestream Storage

The complexity of application development reduces the managing unstructured data cost. The manageability is increased by extending various capabilities which are available only to relational data to non-relational data.

There are 2 new capabilities in Microsoft SQL Server 2008 for persisting BLOB data.

1. FILESTREAM
2. Remote BLOB storage

FILESTREAM

It is an attribute that can be set on a ‘varbinary’ column for data persistence on the file streams. This enables the benefits from fast streaming capabilities and storage capabilities.

Remote BLOB storage

It is a client-side API which reduces the application building complexity and relies on an external persistence for BLOBs and a database for relational data. SQL Server 2008 will also continue the support for standard BLOB type through the data type ‘varbinary’.

Compression Backup in Microsoft SQL Server 2008

A compression backup is smaller than uncompressed backup. The backup speed significantly increases because it needs less disk space and I/O operations. The CPU usage is increased and the increased and the additional CPU that is consumed by the process of compression would impact the operations that are running concurrently.

The following processes can be isolated for obtaining a good picture.

1. Physical disk counters
2. Device throughput bytes / second counter of SQL Server Backup Device object
3. Backup/Restore throughput / second counter of SQL Server Databases object.

Microsoft SQL Server 2008 Policy Based Administration Feature

Policy based database administration allows the DBA for managing the instances and objects of Microsoft SQL Server 2008 across the enterprise by a defined policies that are set. These policies are the rules and regulations which determine the possible ways of what to do and what not to do and the way the violations of policies are enforced and reported. The policies are well defined by using facets and conditions. The details of facets and conditions are below.

Facet

An object which contains the properties which well describes a component.

Condition

A logical expression that is defined on a facet. It is used for identification of acceptable property values of the facet.

Use Sparse Columns of SQL Server 2008

A column with an optimized storage for null values is known as sparse column. Sparse columns reduce the storage space needs for null values. In a scenario of saving 20 percent to 40 percent of storage space, sparse columns can be considered. They are created using CREATE TABLE or ALTER TABLE statements. Sparse columns can be used with Column sets and Filtered index.

Column sets

The statements INSERT, DELETE, UPDATE could be referred the sparse columns by name. The same an also be combined into a single XML Column. This is a column set.

Filtered index

As there are several null value rows in sparse columns, they are appropriate for filtered indexes. The filtered index on a sparse column can accommodate only the rows / tuples which populate values. The index created with filtered index is small and more efficient index.

A large number of user defined properties can be accessed by using sparse columns and filtered indexes enabled applications such as Share Point Services of windows are efficiently store and access.