Top 10 SQL Server 2008 Features for ISV
Applications
Author: Burzin Patel
Contributor: Kun Cheng
Reviewers: Sanjay Mishra, Denny Lee, Mike
Ruthruff, Sharon Bjeletich, Mark Souza, Peter Carlin, Hermann Daeubler, Peter
Scharlock, Wanda He
Microsoft® SQL Server® 2008 has hundreds
of new and improved features, many of which are specifically designed for large
scale independent software vendor (ISV) applications, which need to leverage
the power of the underlying database while keeping their code database
agnostic. This article presents details of the top 10 features that we believe
are most applicable to such applications based on our work with strategic ISV
partners. Along with the description of each feature, the main pain-points the
feature helps resolve and some of the important limitations that need to be
considered are also presented. The features are grouped into two categories:
ones that do not require any application change (features 1-8) and those that
require some application code change (features 9-10). The features are not
prioritized in any particular order.
Data Compression
The disk I/O subsystem is the most common bottleneck for many database
implementations. More disks are needed to reduce the read/write latencies; but
this is expensive, especially on high-performing storage systems. At the same
time, the need for storage space continues to increase due to rapid growth of
the data, and so does the cost of managing databases (backup, restore,
transfer, etc.).
Data compression introduced in SQL Server 2008 provides a resolution to
address all these problems. Using this feature one can selectively compress any
table, table partition, or index, resulting in a smaller on-disk footprint,
smaller memory working-set size, and reduced I/O. Configurations that are
bottlenecked on I/O may also see an increase in performance. In our lab test,
enabling data compression for some ISV applications resulted in a 50-80% saving
in disk space.
SQL Server supports two types of compressions: ROW
compression, which compresses the individual
columns of a table, and PAGE
compression which compresses data pages using
row, prefix, and dictionary compression. The compression results are highly
dependent on the data types and data contained in the database; however, in
general we’ve observed that using ROW compression results in lower overhead on
the application throughput but saves less space. PAGE compression, on the other
hand, has a higher impact on application throughput and processor utilization,
but it results in much larger space savings. PAGE compression is a superset of
ROW compression, implying that an object or partition of an object that is
compressed using PAGE compression also has ROW compression applied to it.
Compressed pages remain compressed in memory until rows/columns on the pages
are accessed.
Both ROW and PAGE compression can be applied to a table or index in an
online mode that is without any interruption to the application availability.
However, partitions of a partitioned table cannot be compressed or uncompressed
online. In our testing we found that using a hybrid approach where only the
largest few tables were compressed resulted in the best overall performance,
saving significant disk space while having a minimal negative impact on
performance. We also found that compressing the smallest objects first
minimized the need for additional disk space during the compression process.
To determine how compressing an object will affect its size you can use
thesp_estimate_data_compression_savings system stored procedure.
Database compression is only supported in SQL Server 2008 Enterprise and
Developer editions. It is fully controlled at the database level and does not
require any application change.
Backup Compression
The amount of data stored in databases has grown significantly in the last
decade. resulting in larger database sizes. At the same time the demands for
applications to be available 24x7 have forced the backup time-windows to
shrink. In order to speed up the backup procedure, database backups are usually
first streamed to fast disk-based storage and moved out to slower media later.
Keeping such large disk-based backups online is expensive, and moving them
around is time consuming.
With SQL Server 2008 backup compression, the backup file is
compressed as it is written out, thereby requiring less storage, less disk I/O,
and less time, and utilizing less network bandwidth for backups that are
written out to a remote server. However, the additional processing results in
higher processor utilization. In a lab test conducted with an ISV workload we
observed a 40% reduction in the backup file size and a 43% reduction in the
backup time.The compression is achieved by specifying the WITH COMPRESSION
clause in the backup command (for more information, see SQL Server Books Online). To prevent having to modify all the
existing backup scripts, there is also a global setting (using the Database
Settings page of the Server Properties dialog box) to
enable compression of all backups taken on that server instance by default;
this eliminates the need to modify existing backup scripts. While the
compression option on the backup command needs to be explicitly specified, the
restore command automatically detects that a backup is compressed and
decompresses it during the restore operation. Overall, backup compression is a
very useful feature that does not require any change to the ISV application.
For more information about tuning backup compression, see the technical note on Tuning the Performance of
Backup Compression in SQL Server 2008.
Note: Creating compressed backups is only supported in SQL Server 2008
Enterprise and Developer editions; however, every SQL Server 2008 edition can
restore a compressed backup.
Transparent Data Encryption
In most cases, organizations do well at securing their active data via the
use of firewalls, physical security, and tightly controlled access policies.
However, when the physical media such as the backup tape or disk on which the
data resides is compromised, the above security measures are of no use, since a
rogue user can simply restore the database and get full access to the data.
SQL Server 2008 offers a solution to this problem by way of
Transparent Data Encryption (TDE). TDE performs real-time I/O encryption and
decryption of the data and log files using a database encryption key (DEK). The
DEK is a symmetric key secured by using a certificate stored in the master database
of the server, or an asymmetric key protected by an Extensible Key Management
(EKM) module.TDE is designed to protect data ‘at rest’; this means that the
data stored in the .mdf, .ndf, and .ldf files cannot be viewed using a hex
editor or some other such means. However, data that is not at rest, such as the
results of a select statement in SQL Server Management Studio, continues to be
visible to users who have rights to view the table. TDE should not be confused
with column-level encryption, which is a separate feature that allows
encryption of data even when it is not at rest.Encrypting a database is a
one-time process that can be initiated via a Transact-SQL command and is
executed as a background thread. You can monitor the encryption/decryption status
using thesys.dm_database_encryption_keys dynamic management view (DMV).
In a lab test we conducted we were able to encrypt a 100-gigabyte (GB)
database using the AES_128 encryption algorithm in about one hour. While the
overheads of using TDE are largely dictated by the application workload, in
some of the testing we conducted, the overhead was measured to be less than 5%.
One point worth mentioning is when backup compression is used to compress
an encrypted database, the size of the compressed backup is larger than if the
database were not encrypted; this is because encrypted data does not compress
well.
TDE enables organizations to meet the demands of regulatory compliance and
overall concern for data privacy.
TDE is only supported in the SQL Server 2008 Enterprise and Developer
editions, and it can be enabled without changing an existing application.
Data
Collector and Management Data Warehouse
Performance tuning and troubleshooting is a time-consuming task that
usually requires deep SQL Server skills and an understanding of the database
internals. Windows® System monitor (Perfmon), SQL Server Profiler, and dynamic
management views helped with some of this, but they were often too intrusive or
laborious to use, or the data was too difficult to interpret.
To provide actionable performance insights, SQL Server 2008 delivers
a fully extensible performance data collection and warehouse tool also known as
the Data Collector. The tool includes several out-of-the-box data collection
agents, a centralized data repository for storing performance data called
management data warehouse (MDW), and several precanned reports to present the
captured data. The Data Collector is a scalable tool that can collect and
assimilate data from multiple sources such as dynamic management views,
Perfmon, Transact-SQL queries, etc., using a fully customizable data collection
and assimilation frequency. The Data Collector can be extended to
collect data for any measurable attribute of an application. For example, in
our lab test we wrote a custom Data Collector agent job (40 lines of code) to
measure the processing throughput of the workload.
The diagram below depicts a typical Data Collector report.
The Performance data collection and warehouse feature is supported in all
editions of SQL Server 2008.
Lock Escalation
Lock escalation has often caused blocking and sometimes even deadlocking
problems for many ISV applications. Previous versions of SQL Server permitted
controlling lock escalation (trace flags 1211 and 1224), but this was only
possible at an instance-level granularity. While this helped some applications
work around the problem, it caused severe issues for others. Another problem
with the SQL Server 2005 lock escalation algorithm was that locks on
partitioned tables were directly escalated to the table level, rather than the
partition level.
SQL Server 2008 offers a solution for both these issues. A new option has
been introduced to control lock escalation at a table level. If an ALTER TABLE
command option is used, locks can be specified to not escalate, or to escalate
to the partition level for partitioned tables. Both these enhancements help
improve the scalability and performance without having negative side-effects on
other objects in the instance. Lock escalation is specified at the
database-object level and does not require any application change. It is
supported in all editions of SQL Server 2008.
Plan Freezing
SQL Server 2005 enabled greater query performance stability and
predictability by providing a new feature called plan guides to enable specifying
hints for queries that could not be modified directly in the application (for
more information, see the white paper Forcing Query Plans). While a very
powerful feature, plan guides were often cumbersome to use due to the
sensitivity of the plan guides to the formatting, and only supported SELECT DML
operations when used in conjunction the USE PLAN query hint.
SQL Server 2008 builds on the plan guides mechanism in two ways: it
expands the support for plan guides to cover all DML statements (INSERT,
UPDATE, DELETE, MERGE), and introduces a new feature,Plan Freezing, that
can be used to directly create a plan guide (freeze) for any query plan that
exists in the SQL Server plan cache, for example:
sp_create_plan_guide_from_handle
@name = N'MyQueryPlan',
@plan_handle = @plan_handle,
@statement_start_offset = @offset;
A plan guide created by either means have a database scope and are stored
in the sys.plan_guidestable. Plan guides are only used to influence
the query plan selection process of the optimizer and do not eliminate the need
for the query to be compiled. A new function sys.fn_validate_plan_guidehas
also been introduced to validate existing SQL Server 2005 plan guides and
ensure their compatibility with SQL Server 2008. Plan freezing is available in
the SQL Server 2008 Standard, Enterprise, and Developer editions.
Optimize
for Ad hoc Workloads Option
Applications that execute many single use ad hoc batches (e.g.,
nonparameterized workloads) can cause the plan cache to grow excessively large
and result in reduced efficiency. SQL Server 2005 offered the Parameterization
Forced database option to address such scenarios, but that sometimes resulted
in adverse side-effects on workloads that had a large skew in the data and had
queries that were very sensitive to the underlying data.
SQL Server 2008 introduces a new option, optimize for ad hoc
workloads, which is used to improve the efficiency of the plan cache. When
this option is set to 1, the SQL Server engine stores a small stub for the
compiled ad hoc plan in the plan cache instead of the entire compiled plan,
when a batch is compiled for the first time. The compiled plan stub is used to
identify that the ad hoc batch has been compiled before but has only stored a
compiled plan stub, so that when this batch is invoked again the database
engine compiles the batch, removes the compiled plan stub from the plan cache,
and replaces it with the full compiled plan.
This mechanism helps to relieve memory pressure by not allowing the plan
cache to become filled with large compiled plans that are not reused. Unlike
the Forced Parameterization option, optimizing for ad hoc workloads does not
parameterize the query plan and therefore does not result in saving any
processor cycles by way of eliminating compilations. This option does not
require any application change and is available in all editions of SQL Server
2008.
Resource
Governor
Maintaining a consistent level of service by preventing runaway queries
and guaranteeing resources for mission-critical workloads has been a challenge
for SQL Server. In the past there was no way of guaranteeing a certain amount
of resources to a set of queries and prioritizing the access; all queries had
equal access to all the available resources.
SQL Server 2008 introduces a new feature, Resource Governor, which helps
address this issue by enabling users to differentiate workloads and allocate
resources as they are requested. The Resource Governor limits can easily be
reconfigured in real time with minimal impact on the workloads that are
executing. The allocation of the workload to a resource pool is configurable at
the connection level, and the process is completely transparent to the
application.
The diagram below depicts the resource allocation process. In this
scenario three workload pools (Admin workload, OLTP workload, and Report
workload) are configured, and the OLTP workload pool is assigned a high
priority. In parallel two resource pools (Admin pool and Application pool) are
configured with specific memory and processor (CPU) limits as shown. As final
steps, the Admin workload is assigned to the Admin pool, and the OLTP and
Report workloads are assigned to the Application pool.
Below are some other points you need to consider when using resource
governor:
·
Since Resource Governor relies on login
credentials, host name, or application name as a resource pool identifier, most
ISV applications that use a single login to connect multiple application users
to SQL Server will not be able to use Resource Governor without reworking the
application. This rework would require the application to utilize one of the
resource identifiers from within the application to help differentiate the
workload.
·
Database-level object grouping, in
which the resource governing is done based on the database objects being
referenced, is not supported.
·
Resource Governor only allows resource
management within a single SQL Server instance. For multiple instances. Windows System Resource Manager should be
considered.
·
Only processor and memory resources can
be configured. I/O resource cannot be controlled.
·
Dynamically switching workloads between
resource pools once a connection is made is not possible.
·
Resource Governor is only supported in
SQL Server 2008 Enterprise and Developer editions and can only be used for the
SQL Server database engine; SQL Server Analysis Services (SSAS), SQL Server
Integration Services (SSIS), and SQL Server Reporting Services (SSRS) cannot be
controlled.
Table-Valued Parameters
Often one of the biggest problems ISVs encountered while developing
applications on earlier versions of SQL Server was the lack of an easy way to
execute a set of UPDATE, DELETE, INSERT operations from a client as a single
batch on the server. Executing the set of statements as singleton operations
resulted in a round trip from the client to the server for each operation and
could result in as much as a 3x slowdown in performance.
SQL Server 2008 introduces the table-valued parameter (TVP) feature, which
helps resolve this problem. Using the new TVP data type, a client application
can pass a potentially unlimited sized array of data directly from the client
to the server in a single-batch operation. TVPs are first-class data types and
are fully supported by the SQL Server tools and SQL Server 2008 client
libraries (SNAC 10 or later). TVPs are read-only, implying that they can only
be used to pass array-type data into SQL Server; they cannot be used to return
array-type data.
The graph below plots the performance of executing a batch of insert
statements using a parameter array (sequence of singleton operations) vs.
executing the same batch using a TVP. For batches of 10 statements or less,
parameter arrays perform better than TVPs. This is due to the one-time overhead
associated with initiating the TVP, which outweighs the benefits of
transferring and executing the inserts as a single batch on the server.
However, for batches larger than 10 statements, TVPs outperform parameter
arrays, because the entire batch is transferred to the server and executed as a
single operation. As can be seen in the graph for a batch of 250 inserts the
amount of time taken to execute the batch is 2.5 times more when the operations
are performed using a parameter array versus a TVP. The performance benefits
scale almost linearly and when the size of the batch increases to 2,000 insert
statements, executing the batch using a parameter array takes more than four
times longer than using a TVP.
TVPs can also be used to perform other functions such as passing a large
batch of parameters to a stored procedure. TVPs are supported in all editions
of SQL Server 2008 and require the application to be modified.
Filestream
In recent years there has been an increase in the amount of unstructured
data (e-mail messages, documents, images, videos, etc.) created. This
unstructured data is often stored outside the database, separate from its
structured metadata. This separation can cause challenges and complexities in
keeping the data consistent, managing the data, and performing backup/restores.
The new Filestream data type in SQL Server 2008 allows large
unstructured data to be stored as files on the file system. Transact-SQL
statements can be used to read, insert, update and manage the Filestream data,
while Win32® file system interfaces can be used to provide streaming access to
the data. Using the NTFS streaming APIs allows efficient performance of common
file operations while providing all of the rich database services, including
security and backup. In our lab tests we observed the biggest performance
advantage of streaming access when the size of binary large objects (BLOBs) was
greater than 256 kilobytes (KB). The Filestream feature is initially targeted
to objects that do not need to be updated in place, as that is not yet
supported.
Filestream is not automatically enabled when you install or upgrade SQL
Server 2008. You need to enable it by using SQL Server Configuration Manager
and SQL Server Management Studio. Filestream requires a special dedicated
filegroup to be created to store the Filestream (varbinary(max)) data
that has been qualified with the Filestream attribute. This filegroup points to
an NTFS directory on a file system and is created similar to all the other
filegroups. The Filestream feature is supported in all editions of SQL
Server 2008, and it requires the application to be modified to
leverage the Win32 APIs (if required) and to migrate the existing varbinary
data.
SQL Server 2008 is a significant release that delivers many new features
and key improvements, many of which have been designed specifically for ISV
workloads and require zero or minimal application change.
This article presented an overview of only the top-10 features that are
most applicable to ISV applications and help resolve key ISV problems that
couldn’t easily be addressed in the past. For more information, including a
full list of features and detailed descriptions, see SQL Server Books Online and the SQL Server
web site.
Published Nov 24 2008, 01:24 PM by Burzin
Filed under: Maintenance, Query, Performance, OLTP, Table Valued Parameters, Transparent Data Encryption, Backup Compression, Filestream, Resource Governor, Encryption, Data Compression, MDW, Data Collector, TDE, Compression, Statistics
출처 : http://sqlcat.com/top10lists/archive/2008/11/24/top-10-sql-server-2008-features-for-isv-applications-burzin.aspx
BackupScript.sql
