SQL Server Database Compression Notes

SQL Server Data Compression

Kathi Kellenberger

[e-mail]

Database compression started from SQL 2008.

Agenda

1       Would compression cause a performance hit?

2.      How does it work?

We can store fixed size data as variable sized data in the row compression;

3.      What can I compress?

SQL 2008+, Developer or Enterprise, or Data Center

Tables, indexes, partitions

4.      What should I compress?

·        For read-only databases, we should compress with PAGE and then SHRINK and then defrag indexes.

·        For read-mostly databases, we should

Archive partitions with PAGE;

Medium age data with ROW;

Current data with NONE;

·        Record performance before and after

Two-level compressions

·        Row-level compression

We would not get a lot of benefit

·        Page-level compression

Syntax

CREATE NONCLUSTER INDEX [Index_ProductID] ON [dbo].SsalesOrderDetail_NC]

(

[ProductionID] ASC

)

GO

CREATE NONCLUSTER INDEX [Index_ProductID] ON [dbo].[SalesOrderDetail_C]

(

[ProductionID] ASC

) WITH (DATA_COMPRESSION = PAGE)

GO

ALTER TABLE dbo.SalesOrderDetail_C

REBUILD WITH (DATA_COMPRESSION = ROW)

GO

SELECT

OBJECT_NAME(sp.object_id) AS tableName,

si.name AS indexName, sp.partition_number, sp.rows, sp.data_compression_desc

FROM sys.partition sp

JOIN sys.indexes si ON si.object_id = sp.object_id AND si.index_id = sp.index_id

WHERE OBJECT(sp.object_id) IN (‘SalesOrderDetail_NC’, ‘SalesOrderDetail_C’)

SET STATISTICS IO ON

We can display IO information after executing a script.

In the demo 1, it was found that the performance is enhanced when having table scans and there is no difference when having index scan for the row compressed data.

Page compression takes less space than row compression.

SET STATISTICS TIME ON

How performance is affected when using UPDATE in compressed data?

The UPDATE command for a single row uses more CPU time and less elapsed time in the page compressed data.

DBCC IND(‘DBName’, ‘tblName’, partitionNumber)

partitionNumber (or indexed):

1: Data page

2: Index page

DBCC PAGE (DBName, field, pageID, viewType);

CREATE TABLE #RowEstimate ([object_name] SYSNAME, [schema_name] SYSNAME, index_id INT, partition_number INT, [size_with_current_compression_setting], [size_with_ROW(kb)] INT, [sample_size_with_current_compression_setting(kb)] INT, sample_size_with_ROW(kb)) INT)

CREATE TABLE #PageEstimate ([object_name] SYSNAME, [schema_name] SYSNAME, index_id INT, partition_number INT, [size_with_current_compression_setting], [size_with_PAGE(kb)] INT, [sample_size_with_current_compression_setting(kb)] INT, sample_size_with_PAGE(kb)) INT)

DECLARE @schema_name SYSNAME, @table_name SYSNAME

DECLARE tableList CURSOR FAST_FORWARD FOR

SELECT SCHEMA_NAME(schema_id), name FROM sys.tables WHERE SCHEMA_NAME(schema_id) = ‘Sales’

OPEN tableList

Fetch NEXT FROM tableList INTO @schema_name, @table_name

WHILE @@FETCH_STATUS =  0

BEGIN

INSERT INTO #RowEstimate EXEC sp_estimate_data_compression_savings

@schema_name = @schema_name, @object_name = @table_name, @index_id = NULL,

@partition_number = NULL, @data_compression = ‘PAGE;

 

INSERT INTO #PageEstimate EXEC sp_estimate_data_compression_savings

@schema_name = @schema_name, @object_name = @table_name, @index_id = NULL,

@partition_number = NULL, @data_compression = ‘PAGE’;

END

CLOSE tableList

DEALLOCATE tableList

 

Turn off compression

ALTER TABLE tableName REBUILD WITH (DATA_COMPRESSION = NONE);