SQL Server 2008 New Features

What’s new in SQL Server 2008:

Hot Add CPU —If your hardware or software environment supports it, SQL Server 2008 will

allow you to dynamically add one or more CPUs to a running system. These CPUs can be physical,

logical, or virtual.

Option to Optimize for Ad Hoc Workloads — SQL Server 2008 includes a new feature that

allows administrators to configure the server to improve plan cache efficiency for ad hoc batches.

With this feature enabled, the Database Engine no longer needs to store fully compiled plans that

will not be reused. Instead, the plan cache stores a stub of the ad hoc workload.

SQL Server Extended Events — SQL Server 2005 introduced the ability to associate SQL Profiler

traces with Windows Performance Log counters. This was extremely helpful in identifying

poorly performing queries or the lack of sufficient resources in the system to handle certain

events. SQL Server 2008 takes this a step further by introducing SQL Server Extended Events.

Extended events allow database administrators to get a better understanding of the system

behavior by correlating SQL Server data to the operating system or database applications. This

is handled by directing output from extended events to Event Tracing forWindows (ETW).

Resource Governor — The Resource Governor is a new feature that allows administrators to

specify configuration options that limit the amount of CPU and memory available to incoming

requests. This can help prevent applications or queries from consuming 100 percent of the CPU

or all available memory. The Resource Governor uses configurable workload groups, which

define what the CPU and memory limits are for any session that is classified as being a member

of that group. Classification can be performed based on a number of system functions or

user-defined functions.

Policy-Based Management— SQL Server 2008 includes features that allow administrators

greater control over their server environments by enforcing behaviors or constraints through a

policy-based mechanism. In addition to using the included policies, administrators can create

their own policies to configure servers to meet compliance requirements and standardize

naming conventions, thereby simplifying administration.

Centralized Management— Central Management servers are SQL Servers that can be configured

to manage multiple servers as part of a group. You can also execute queries against a SQL

Server group that can return results to either a combined set or a separate pane per server. A

Central Management server can also be used to enforce management policies against multiple

target servers simultaneously.

Query Editor IntelliSense — SQL Server Management Studio now provides IntelliSense functionality

in the Query Editor. The IntelliSense functionality provides auto-completion ability,

error underlining, quick info help, syntax pair matching, and parameter help.

PowerShell Provider — SQL Server 2008 includes new features that integrate with Windows

PowerShell to help administrators automate many SQL Server 2008 tasks. PowerShell is an

administrative command-line shell and scripting language that can make it easier to perform

many common tasks through automation. The PowerShell provider in SQL Server 2008 exposes

SQL Server Management Objects (SMO) in a structure similar to file system paths. SQL Server

PowerShell also includes several SQL Server cmdlets for running scripts and other common

tasks.

Compressed Indexes and Tables— Compression is now supported for tables, indexes, and

indexed views on either rows or pages. Compression operations will have an effect on performance.

Because of this, row and page compression can be configured on a per-partition basis.

For example, you could choose to compress a Read Only partition, but leave a Write-intensive

partition uncompressed to minimize impact on the CPU.

FILESTREAM — FILESTREAM is a new storage mechanism for storing data on the file system,

rather than in the database itself. SQL Server 2008 applications can use FILESTREAM to take

advantage of the storage and performance benefits of the NTFS file system while maintaining

transactional consistency with the files themselves. Developers can leverage FILESTREAM as

a mechanism for allowing large files to be maintained by the application database, without

causing the database to become unnecessarily bloated. (Although this is just speculation on

my part, I would be surprised if future releases of SharePoint didn’t leverage FILESTREAM

storage.)

Partition Switching —Simply put, Partition Switching enables you to move data between partitions

for a table or index. Data can be transferred between partitions without disrupting the

integrity of the table or index.

Spatial Data Types — Two new data types have been created for storing planar, or ‘‘flat-earth’’

data as well as ellipsoidal, or ‘‘round-earth’’ data. These data types are known as the geometry

data type and geography data type, respectively.

MERGE Statement— Transact-SQL includes a new MERGE statement that, based on the results of a

join with a source table, can perform INSERT, UPDATE, or DELETE operations against a target table.

For example, you can use MERGE to incrementally update a destination table by comparing the

differences from a source table.