Using data compression for the Workflow Manager workspace in SQL Server

Using data compression

Row and page compression for tables and indexes enables you to save storage space by reducing the size of the database. Data compression has the drawback of increasing CPU usage because the data must be compressed and decompressed when being accessed. You cannot use data compression with system tables, and only the Enterprise and Developer editions of SQL Server 2012 support data compression.

You can configure data compression on the following:

There are three forms of data compression you can use with SQL Server 2012: row-level compression, unicode compression, and page-level compression.

For more information, see Heaps (Tables without Clustered Indexes) in the MSDN library

Row-level compression

Row-level compression works by using more efficient storage formats for fixed-length data. Row-level compression uses the following strategies to save space:

Although it does reduce the amount of space that data uses, row-level compression does not provide the storage improvements of page-level compression. The advantage of row-level compression is that it requires less CPU usage than page-level compression. You use the following syntax to compress a table by using row-level compression:

ALTER TABLE tableName REBUILD WITH (DATA_COMPRESSION=ROW)

For example, to rebuild all partitions of the wmx.TableA table of the WMXDB database by using row compression, use the following query:

USE [WMXDB]
ALTER TABLE [wmx].[TableA] REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = ROW)

Use the following syntax to configure an index with row-level compression:

ALTER INDEX indexName ON tableName REBUILD PARTITION ALL WITH (DATA_COMPRESSION=ROW)

For more information about row-level compression, see Row Compression Implementation in the MSDN library.

Unicode compression

Unicode compression enables the database engine to compress unicode values stored in page- or row-compressed objects. You can use unicode compression with the fixed-length nchar(n) and nvarchar(n) data types. Unicode compression is automatically used where appropriate when you enable row and page compression.

For more information about unicode compression, see Unicode Compression Implementation in the MSDN library.

Page-level compression

Page-level compression compresses data by storing repeating values and common prefixes only once and then making references to those values from other locations within the table. When page compression is applied to a table, row compression techniques are also applied. Page-level compression uses the following strategies:

The benefits of page compression depend on the type of data compressed. Data that involves many repeating values will be more compressed than data populated by more unique values. Use the following general syntax to apply page-level compression:

ALTER TABLE name REBUILD WITH (DATA_COMPRESSION=PAGE)

For example, to rebuild all partitions of the wmx.TableA table of the WMXDB database by using page compression, use the following query:

USE [WMXDB]
ALTER TABLE [wmx].[TableA] REBUILD PARTITION = ALL
WITH
(DATA_COMPRESSION = PAGE)

Use the following syntax to configure an index with page-level compression:

ALTER INDEX indexName ON tableName REBUILD PARTITION ALL WITH (DATA_COMPRESSION=PAGE)

For more information about page-level compression, see Page Compression Implementation in the MSDN library.

If tables or indexes are partitioned, you can configure compression on a per-partition basis. If you split a partition by using the ALTER PARTITION statement, the new partitions inherit the data compression attribute of the original partition. If you merge two partitions, the resulting partition has the compression attribute of the destination partition. Although compression does allow more rows to be stored on a page, it doesn't alter the maximum row size of a table or index. You can't enable a table for compression if the maximum row size and the compression overhead exceed 8,060 bytes. The default compression setting for indexes is NONE, and you must specify the compression property for indexes when you create them. Nonclustered indexes do not inherit the compression property of the table, but clustered indexes created on a heap inherit the compression state of the heap. Data compression applies only at the source, so when you export data from a compressed source, SQL Server will output the data in uncompressed row format. Importing uncompressed data into a target table enabled for compression will compress the data.

For more information about data compression, see Data Compression in the MSDN library.

You can configure compression by using the preceding Transact-SQL statements or from SQL Server Management Studio by using the Data Compression wizard on either tables or indexes. You can use the Data Compression wizard to add and remove compression. To use the Data Compression wizard to change the compression settings for both tables and indexes, perform the following steps:

Steps:
  1. In SQL Server Management Studio, right-click the table or index you want to compress, click Storage, and click Manage Compression.
  2. On the Welcome To The Data Compression Wizard page, click Next.
  3. On the Select Compression Type page, you can choose to use the same compression type for all partitions or choose among Row, Page, and None on a per-partition basis. Click Calculate to determine the difference between current space usage and compressed usage.
  4. On the Select An Output Option page, choose whether to create a script, to perform the operation immediately, or to perform the option according to a schedule. Click Next and then click Finish to complete the wizard.

For more information about the Data Compression wizard, see Data Compression Wizard Help in the MSDN library.

Estimating compression

The best way to determine the benefits of compression on an object is to use the sp_estimate_data_compression_savings stored procedure. The benefits of compression depend on factors such as the uniqueness of data. The sp_estimate_data_compression_savings stored procedure is available in the Enterprise edition of SQL Server 2012 only.

The syntax of the stored procedure is as follows:

sp_estimate_data_compression_savings[ @schema_name = ] 'schema_name', [ @object_name = ]
'object_name', [@index_id = ] index_id,[@partition_number = ] partition_number,
[@data_compression = ] 'data_compression'

For example, to configure an estimate of the compression benefits of using row compression on the wmx.TableA table in the WMXDB database, execute the following Transact-SQL statement:

USE WMXDB;
GO
EXEC sp_estimate_data_compression_savings 'wmx', 'TableA', NULL, NULL,
'ROW';
GO

To configure an estimate of the compression benefits of using page compression on the same table, execute the following Transact-SQL statement:

USE WMXDB;
GO
EXEC sp_estimate_data_compression_savings 'wmx', 'TableA', NULL, NULL,
'PAGE';
GO

For more information about how to estimate compression savings, see sp_estimate_data_compression_savings (Transact-SQL) in the MSDN library.

You can use the SQL script below to the rev user tables and indexes and to generate the SQL statements to set page compression for the tables and indexes.

/*---------------------------------------------------------------------
-- Verify WMX Schema Storage
---------------------------------------------------------------------*/
USE [wmxdb]
GO
/*---------------------------------------------------------------------
--List all tables
---------------------------------------------------------------------*/
SELECT USER_NAME(o.uid) [owner], o.name,o.id,o.type,o.status
FROM sysobjects o
WHERE USER_NAME(o.uid) = 'wmx'
AND o.type <> 'S' and o.type = 'U'
ORDER BY o.name,o.type;
GO
/*---------------------------------------------------------------------
--List all indexes
---------------------------------------------------------------------*/
SELECT USER_NAME(o.uid) [owner], OBJECT_NAME(i.id) [table], i.name [index],o.type [type]
FROM sysindexes i inner join sysobjects o ON i.id = o.id
WHERE USER_NAME(o.uid) = 'wmx'
AND o.type <> 'S' and o.type = 'U' and i.indid = 1
ORDER BY USER_NAME(o.uid),OBJECT_NAME(i.id),i.name;
GO

/*---------------------------------------------------------------------
--Table page compression
--Example:
/*
ALTER TABLE WMX.JTX_JOBS_AOI 
REBUILD WITH (DATA_COMPRESSION = PAGE);
GO
*/
---------------------------------------------------------------------*/
--Generate script to set table page compression:
SELECT 'ALTER TABLE ' + USER_NAME(o.uid) + '.' + o.name + ' REBUILD WITH (DATA_COMPRESSION = PAGE);' [TXTSQL]
FROM sysobjects o
WHERE USER_NAME(o.uid) = 'wmx'
AND o.type <> 'S' and o.type = 'U'
ORDER BY o.name,o.type;
GO

/*---------------------------------------------------------------------
--Index page compression
--Example:
/*
ALTER INDEX R125_pk 
ON WMX.JTX_JOBS_AOI
REBUILD WITH ( DATA_COMPRESSION = PAGE ) ;
GO
*/
---------------------------------------------------------------------*/
--Generate script to set index page compression:
SELECT 'ALTER INDEX ' + i.name + ' ON ' + USER_NAME(o.uid) + '.' + OBJECT_NAME(i.id) +
       ' REBUILD WITH ( DATA_COMPRESSION = PAGE );' [TXTSQL]
FROM sysindexes i inner join sysobjects o ON i.id = o.id
WHERE USER_NAME(o.uid) = 'wmx'
AND o.type <> 'S' and o.type = 'U' and i.indid = 1
ORDER BY USER_NAME(o.uid),OBJECT_NAME(i.id),i.name;
GO

Compression and TDE

Encryption of the database file is performed at the page level. The pages in an encrypted database are encrypted before they are written to disk and decrypted when read into memory. TDE does not increase the size of the encrypted database.

For more information, see SQL Server 2012 - Transparent Data Encryption (TDE) in the MSDN library.

Encrypted data compresses significantly less than equivalent unencrypted data. If TDE is used to encrypt a database, backup compression will not be able to significantly compress the backup storage.

For more information, see SQL Server 2012 - Data Compression in the MSDN library.

Copyright © 1995-2014 Esri. All rights reserved.
3/3/2014