Database Reference
In-Depth Information
markedly in 2012. One of the most important things to understand about Data
Compression is that it's not just about space savings, although the savings can be
significant. Using Data Compression can also have a very positive impact on storage
performance and Buffer Pool usage by reducing the number of IOPS and allowing the
database to store more pages in memory in the Buffer Pool in compressed form. Using
compression can also dramatically reduce query execution time, as fewer pages need to
be read from cache or disk and analyzed for a given query.
In SQL Server 2012, you can choose to compress either a table or index using row or
page compression. By default, when you choose page compression, it automatically
does row compression at the same time. Based on our experience, space savings and
performance improvements of up to 75% with SQL Server 2012 can be achieved in
many cases. Data Compression can be used with both OLTP and OLAP type workloads,
including Data Warehouse and Batch.
Caution
Data Compression introduces a CPU overhead and may increase CPU utilization
on your database. In most cases, this overhead is outweighed by the benefit in
performance you receive. In most virtualized environments, CPU performance
will not be your constraint; memory and storage IO are usually the bottleneck.
However, it won't benefit every workload and is not likely suitable for small
tables that change very often. The best workloads for data compression consist of
large tables that are predominately read oriented. Also Data Compression can't
us/library/dd894051(SQL.100).aspx
.
Tip
If you are using SAP with SQL Server 2012, then Page Compression is turned on
by default. For detailed information about using SQL Server Data Compression
with SAP, refer to
http://scn.sap.com/docs/DOC-1009
and the SAP on SQL
Server Page (
http://scn.sap.com/community/sqlserver
).
Column Storage
Column Storage, also known as xVelocity memory optimized column store index, is a
new feature of SQL Server 2012 aimed at data warehouse workloads and batch
processing. Column Storage is much more space and memory efficient at storing and
aggregating massive amounts of data. Leveraging this feature can greatly improve the
performance of data warehouse queries. However, to use it you must make some
