Introduction

• How to decide which tables and indexes to compress
• How to estimate the resources required to compress a table
• How to reclaim space released by data compression
• The performance impacts of data compression on typical workloads

About Data Compression

Deciding What to Compress

• Row-compress some tables, page-compress some others, and don’t compress the rest.
• Page-compress a heap or clustered index, but have no compression on its nonclustered indexes.
• Row-compress one index, and have no compression on another index.
• Row-compress some partitions of a table, page-compress some others, and don’t compress the rest.

• Estimated space savings
• Application workload

Estimated Space Savings

Data and Data Types

• Columns with numeric or fixed-length character data types where most values don’t require all the allocated bytes: For example, integers where most values are less than 1000
• Nullable columns where a significant number of the rows have a NULL value for the column
• Significant amounts of repeating data values or repeating prefix values in the data

• Columns with numeric or fixed-length character data types where most values require all the bytes allocated for the specific data type
• Not much repeating data
• Repeating data with non-repeating prefixes
• Data stored out of the row
• FILESTREAM data

Application Workload

• It is read for filtering, sorting, joining, as part of a query response.
• It is updated by an application.

• Physical I/O: Because physical I/O is expensive from a workload perspective, reduced physical I/O often results in a bigger saving than the additional CPU cost to compress and decompress the data. Note that physical I/O is saved both because a smaller volume of data is read from or written to disk, and because more data can remain cached in buffer pool memory.
• Logical I/O (if data is in memory): Because logical I/O consumes CPU, reduced logical I/O can sometimes compensate for the CPU cost to compress and decompress the data. 

• Start with less frequently used tables and indexes to ensure the understanding of the system behavior is accurate.
• If CPU headroom is not available, do not use page compression without thorough testing.
• Query operations such as filtering, joins, aggregates, and sorting see decompressed data, and hence the cost of these operations are not affected by data compression. A query, whose cost is dominated by complex processing operations (for example, a query involving multiple table joins or complex aggregate operations) is much less likely to see any significant change in performance or CPU utilization, due to data compression. These complex queries usually occur in data warehousing applications, but they can occur in other applications as well. If CPU time for an application consists primarily of complex queries, page compression may not impact CPU measurably. In such scenarios, space savings may be the prime driver for data compression.
• Most large-scale data warehousing workloads are scan-intensive, and storage is typically a premium as well. In a data warehouse or large-scale data mart, if there is CPU headroom available, we recommend page-compressing all objects in the database, rather than evaluating object-by-object as outlined below.

• U: The percentage of update operations on a specific table, index, or partition, relative to total operations on that object. The lower the value of U (that is, the table, index, or partition is infrequently updated), the better candidate it is for page compression.
• S: The percentage of scan operations on a table, index, or partition, relative to total operations on that object. The higher the value of S (that is, the table, index, or partition is mostly scanned), the better candidate it is for page compression.

U: Percent of Update Operations on the Object

SELECT o.name AS [Table_Name], x.name AS [Index_Name],
       i.partition_number AS [Partition],
       i.index_id AS [Index_ID], x.type_desc AS [Index_Type],
       i.leaf_update_count * 100.0 /
           (i.range_scan_count + i.leaf_insert_count
            + i.leaf_delete_count + i.leaf_update_count
            + i.leaf_page_merge_count + i.singleton_lookup_count
           ) AS [Percent_Update]
FROM sys.dm_db_index_operational_stats (db_id(), NULL, NULL, NULL) i
JOIN sys.objects o ON o.object_id = i.object_id
JOIN sys.indexes x ON x.object_id = i.object_id AND x.index_id = i.index_id
WHERE (i.range_scan_count + i.leaf_insert_count
       + i.leaf_delete_count + leaf_update_count
       + i.leaf_page_merge_count + i.singleton_lookup_count) != 0
AND objectproperty(i.object_id,'IsUserTable') = 1
ORDER BY [Percent_Update] ASC

S: Percent of Scan Operations on the Object

SELECT o.name AS [Table_Name], x.name AS [Index_Name],
       i.partition_number AS [Partition],
       i.index_id AS [Index_ID], x.type_desc AS [Index_Type],
       i.range_scan_count * 100.0 /
           (i.range_scan_count + i.leaf_insert_count
            + i.leaf_delete_count + i.leaf_update_count
            + i.leaf_page_merge_count + i.singleton_lookup_count
           ) AS [Percent_Scan]
FROM sys.dm_db_index_operational_stats (db_id(), NULL, NULL, NULL) i
JOIN sys.objects o ON o.object_id = i.object_id
JOIN sys.indexes x ON x.object_id = i.object_id AND x.index_id = i.index_id
WHERE (i.range_scan_count + i.leaf_insert_count
       + i.leaf_delete_count + leaf_update_count
       + i.leaf_page_merge_count + i.singleton_lookup_count) != 0
AND objectproperty(i.object_id,'IsUserTable') = 1
ORDER BY [Percent_Scan] DESC

Example

Planning Compression: Estimating Workspace, CPU, I/O

• Whether you are compressing a heap, a clustered index, or a nonclustered index
• Whether you  set the SORT_IN_TEMPDB option to ON
• Whether you are performing the compression operation with the ONLINE option set to ON
• Whether you are using the simple, bulk logged, or full recovery model

Workspace

• User database
• Transaction log
• tempdb

Workspace Required in the User Database

• The compressed table or index
• The mapping index if you are compressing a heap or a clustered index with the ONLINE option set to ON and the SORT_IN_TEMPDB option set to OFF. (The recommendation is to set SORT_IN_TEMPDB to ON. The workspace requirement for the mapping index is discussed in the tempdb section.).

Transaction Log Space

Workspace Required in tempdb

• For the mapping index, an internal structure used to map old bookmarks to new bookmarks, enabling concurrent DML transactions. This is stored in tempdb if SORT_IN_TEMPDB is set to ON.
• For the version store. This is only used if there are concurrent DML operations. Size depends upon the volume of ongoing modifications and duration of long running DML transactions.

I/O

CPU

• SQL Server uses statistics on the leading column to distribute work amongst multiple CPUs, thus multiple CPUs are not beneficial when creating, rebuilding, or compressing an index where the leading column of the index has relatively few unique values or when the data is heavily skewed to just a small number of leading key values – only limited effective parallelism will be achieved in this case.
• Compressing or rebuilding a heap with ONLINE set to ON uses a single CPU for compression or rebuild. However, SQL Server first needs to scan the table—the scan is parallelized, and after the table scan is complete, the rest of the compression processing of the heap is single-threaded.

Summary of Resource Requirements for Data Compression

• X = number of pages before compression (or rebuild)
• P = number of pages after compression (P < X)
• Y = number of new or updated pages (by a concurrent application, applies only to the ONLINE case)
• M = size of the mapping index (estimate based on guidelines in the TEMPDB Capacity Planning white paper)
• C = the CPU time taken to rebuild the uncompressed index

• Comparing to rebuild without compression, compression uses less workspace and I/O, but more CPU. The reduced workspace/I/O is due to the smaller size of the resulting structure.
• The lowest resource utilization is when running OFFLINE with the bulk-logged or simple recovery model. Offline, bulk operations involve reading the existing data, and writing new, compressed data. Minimal log is written—only the allocations are logged.
• If it is not possible to use the BULK_LOGGED or SIMPLE recovery model (but still using OFFLINE), additional I/O is needed to fully log the compressed data, with the full recovery model. No additional workspace (compared to bulk-logged) is allocated, because log space is reserved even in the bulk-logged and simple recovery models. 
• Many production systems cannot run in bulk-logged or simple mode, and many systems cannot afford downtime for OFFLINE compression. ONLINE operations require about twice as much CPU as OFFLINE operations.

How and When to Compress

• Online vs. Offline: Whether to set the value of ONLINE to ON or OFF depends upon what else is running on the database at the same time. OFF is faster and requires less resources than ON, but the table is locked for the duration of the compression operation. Be aware of the restrictions of online operations as discussed in SQL Server Books Online.
• One table, index, or partition at a time vs. many concurrently: Compressing one table, index, or partition at a time is recommended in most cases. When doing multiple simultaneous compressions, workspace, I/O, and CPU requirements outlined earlier must be available for all the compressions together. Be mindful of the risk of insufficient free space and the need to expand data files, as well as the likelihood of large amount of unused space in the data files at the end of compression. If you have sufficient resources (workspace, I/O, and CPU), and have an efficient mechanism to reclaim the unused space (refer to section 6 later in this white paper), executing multiple compressions simultaneously may be acceptable.
• Order of compressing the tables: After you have decided on the list of tables and indexes to compress, compress the objects starting with the smallest in this list. Compressing smaller objects requires less workspace, and it releases space to the data files that can be used as workspace for compressing the larger objects subsequently. This approach minimizes the need for additional disk space during the compression process.
• Setting SORT_IN_TEMPDB to ON or OFF: ON is recommended. This makes use of tempdb space for the mapping index, and therefore, it requires less workspace in the user database.

Side Effects of Compressing a Table or Index

• Compression includes a rebuild, thus removing fragmentation from the table or index.
• When a heap is compressed, if there are any nonclustered indexes on the heap, they are rebuilt as follows:

Manipulating Compressed Data

Newly-Inserted Rows

• The table organization: heap or clustered index
• How and where the new row is inserted

Updating or Deleting Compressed Rows

What Happens to the Supporting Data Structures

• Transaction log
• Mapping index
• Version store
• Sort pages

On a Page-Compressed Index, Nonleaf Pages are Row-Compressed

• The number of nonleaf pages in an index are relatively small, and the space saving that would result from page-compressing these pages is relatively insignificant.
• The nonleaf pages are accessed much more frequently, and having them row-compressed (instead of page-compressed) reduces the cost of decompressing them on each access.

Reclaiming the Space Released by Data Compression

CREATE UNIQUE CLUSTERED INDEX [PK_TRADE]
ON [TRADE_BULK] ([T_ID] ASC)
WITH (DATA_COMPRESSION=PAGE, DROP_EXISTING=ON, SORT_IN_TEMPDB=ON) ON [FG_Data2]

-- Create a new empty table in the new filegroup
ALTER DATABASE [TestDB] MODIFY FILEGROUP FG_COMP DEFAULT;
SELECT * INTO [Tab1] FROM [Tab] WHERE 1 = 2;
-- Compress the newly created empty table
ALTER TABLE [Tab1] REBUILD WITH (DATA_COMPRESSION = PAGE);
-- Create the appropriate indexes on the table
-- Copy the data over to the new table
INSERT INTO [Tab1] WITH (TABLOCK) SELECT * FROM [Tab]
-- The incoming data will be compressed as it gets inserted
-- TABLOCK is required if the target table is a heap
-- Trace flag 610 may help enable minimal logging
-- The LOB_DATA allocation unit will also be copied
-- Drop the old table and rename the new table as old table
-- After all the tables are copied like this, remove the old filegroup

Application Performance with Data Compression

Workload 1: An OLTP Application with High Volumes of DML Operations

Workload 2: A Reporting Application with Large Queries

Rebuilding Compressed Indexes

BULK INSERT with Data Compression

BULK INSERT Followed by CREATE CLUSTERED INDEX

• Option 1: BULK INSERT into uncompressed heap, followed by CREATE CLUSTERED INDEX WITH (DATA_COMPRESSION = PAGE). Because the data is loaded into an uncompressed heap, it allows for faster loading compared to the other two options. This option allows compressing the data at the same time as creating the clustered index, thereby reducing the total time. However, more free space is needed in the user database compared to option 3, because, the uncompressed heap and the compressed clustered index need to reside in the user database simultaneously while the index is being created.
• Option 2: BULK INSERT into a page-compressed heap, followed by CREATE CLUSTERED INDEX. Because the data is loaded into a heap, the loading is faster compared to option 3; however, because the heap is compressed, loading takes longer compared to option 1 (the data is compressed while being loaded). And, because the heap and the clustered index need to reside in the user database while the index is created, more free space is needed than option 3; but less than option 1, because both the heap and the clustered index are compressed.
• Option 3: BULK INSERT into a page-compressed clustered index. This option takes longer, because the data is loaded into a clustered index, and data is compressed during loading, but all the tasks (loading, compressing, creating clustered index) are completed together. Because there is no post-processing involved in the form of creating the clustered index or compressing the data, no extra free space is required in the user database.

Data Compression and Partition Manipulation

Switch

Split

Merge

Data Compression and Transparent Data Encryption

Conclusion

• Are you rating it high due to having good examples, excellent screen shots, clear writing, or another reason?
• Are you rating it low due to poor examples, fuzzy screen shots, or unclear writing?

Appendix A: Test Hardware and Software

Server

• 2 socket quad core
• AMD Opteron Processor 2354  @2.20 GHz
• 32 GB RAM

Storage

• 300 GB
• 15K rpm
• RAID 1+0

Software

• The 64-bit edition of the Windows Server 2008 Enterprise operating system
• The 64-bit edition of SQL Server 2008 Enterprise

Appendix B: Data Volume in Each Allocation Unit Type

-- Provided AS IS, without warranty of any kind
SELECT OBJECT_NAME(p.object_id) AS Object_Name
       , i.name AS Index_Name
       , ps.in_row_used_page_count AS IN_ROW_DATA
       , ps.row_overflow_used_page_count AS ROW_OVERFLOW_DATA
       , ps.lob_used_page_count AS LOB_DATA
FROM sys.dm_db_partition_stats ps
JOIN sys.partitions p ON ps.partition_id = p.partition_id
JOIN sys.indexes i ON p.index_id = i.index_id AND p.object_id = i.object_id
WHERE OBJECTPROPERTY (p.[object_id], 'IsUserTable') = 1

Appendix C: Free Space in Database Files

--Provided AS IS, without warranty of any kind
SELECT
a.file_id,
LOGICAL_NAME = a.name,
PHYSICAL_FILENAME = a.physical_name,
FILEGROUP_NAME = b.name,
FILE_SIZE_MB = CONVERT(DECIMAL(12,2),ROUND(a.size/128.000,2)),
SPACE_USED_MB = CONVERT(DECIMAL(12,2),ROUND(FILEPROPERTY(a.name,'SpaceUsed')/128.000,2)),
FREE_SPACE_MB = CONVERT(DECIMAL(12,2),ROUND((a.size-FILEPROPERTY(a.name,'SpaceUsed'))/128.000,2))
FROM sys.database_files a LEFT OUTER JOIN sys.data_spaces b
ON a.data_space_id = b.data_space_id

Appendix D: On a Page-Compressed Index, Verifying That Nonleaf Pages are Row-Compressed

SELECT
o.name, ips.index_type_desc, p.partition_number, p.data_compression_desc,
ips.index_level, ips.page_count, ips.compressed_page_count
FROM sys.dm_db_index_physical_stats
(DB_ID(), object_id(<insert index name here>), NULL, NULL, 'DETAILED') ips
JOIN sys.objects o ON o.object_id = ips.object_id
JOIN sys.partitions p ON p.object_id = o.object_id
ORDER BY ips.index_level

Appendix E: BULK INSERT into a Heap

SELECT
o.name, ips.index_type_desc, p.partition_number, p.data_compression_desc,
ips.page_count, ips.compressed_page_count
FROM sys.dm_db_index_physical_stats
(DB_ID(), object_id(<insert table name here>), NULL, NULL, 'DETAILED') ips
JOIN sys.objects o ON o.object_id = ips.object_id
JOIN sys.partitions p ON p.object_id = o.object_id