Tuesday, October 28, 2014

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

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
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.

No comments:

Post a Comment