Total Pageviews

Sunday, February 24, 2013

SQL Server Data Compression – Beauty or Beast?

Data compression is an Enterprise/Developer Edition feature starting from SQL 2008 and onward. The pros of this feature is that it reduces disk space and memory requirement, however on the other hand, it has some CPU cost for compressing and decompressing data pages repeatedly. Therefore, it is recommended that we should have some CPU headroom where it is being implemented.

We can easily estimate the benefits of saving disk space and memory, but how do we measure CPU cost associates with data compression? There is no any specific indicator till to date that can measure associated CPU headroom or cost.

What we can compress?
Unfortunately, we cannot compress the entire database in one shot, there is no such option. We can compress data table by table. We have following options:
1.       A HEAP or a Clustered index
2.       One or all non-clustered index
3.       One or any table partition with different compression option
4.       Indexed view

We have three options for data compression:
1.       ROW: Compresses only rows in a leaf-level page.
2.       PAGE: Compresses all leaf-level pages and all the rows as well
3.       NONE: default – no compression

Basic syntax of data compression:
The following command changes the compression of a table and the heap or clustered index will be rebuilt. Please note that if the table is a HEAP (has no clustered index), all non-clustered indexes will be rebuilt.
ALTER TABLE TableA REBUILD WITH (DATA_COMPRESSION = PAGE);

(a)    To rebuild an existing Clustered Index:
CREATE CLUSTERED INDEX [PK_TableA] ON [dbo].[TableA]
([xID] ASC)
WITH (DATA_COMPRESSION = PAGE, DROP_EXISTING = ON) 

(b)   To rebuild an existing non-clustered Index:
CREATE CLUSTERED INDEX [IX_TableA_Col1] ON [dbo].[TableA]
([Col1] ASC)
WITH (DATA_COMPRESSION = PAGE, DROP_EXISTING = ON) 

(c)    To rebuild all indexes:
ALTER INDEX ALL ON TableA
       REBUILD WITH ( DATA_COMPRESSION = PAGE)

(d)   To rebuild Partition number 1 (one)
ALTER TABLE TableA
REBUILD PARTITION = 1 WITH (DATA_COMPRESSION = PAGE)

(e)   To rebuild all partitions in the table:
ALTER TABLE TableA
REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = PAGE ON PARTITIONS(1) ) ;

Our Sample table and testing approach:
We will be cratering a sample table and populating it with 1,000,000 rows.

1.       Review data compression on HEAP with default fill factor
2.       Compare the physical size versus cached size
3.       Comparing IO and CPU

DMV and script for comparing compression and data buffer:

1.       We will use following script to get buffer and disk pages

/*************************************************************
** Script to check buffer and disk pages
*************************************************************/
-- dbcc dropcleanbuffers

SELECT  DB_NAME(bd.database_id) AS database_name ,
        obj.[name] AS 'table_name' ,
        obj.index_id ,
        si.name index_name ,
        obj.type_desc ,
        obj.total_pages AS total_pages_in_disk ,
        obj.size_in_disk_mb ,
        CONVERT(NUMERIC(10), ( COUNT(*) )) AS total_cached_pages ,
        CONVERT(NUMERIC(10, 2), ( COUNT(*) * 8 ) / 1024.0) AS 'cached_size_mb'
FROM    sys.dm_os_buffer_descriptors AS bd
        INNER JOIN ( SELECT OBJECT_NAME(object_id) AS name ,
                            type_desc ,
                            index_id ,
                            allocation_unit_id ,
                            total_pages ,
                            CONVERT(NUMERIC(10, 2), ( total_pages * 8 ) / 1024.0) AS 'size_in_disk_mb'
                     FROM   sys.allocation_units AS au
                            INNER JOIN sys.partitions AS p ON au.container_id = p.hobt_id
                                                              AND ( au.type = 1
                                                                    OR au.type = 3 )
                     UNION ALL
                     SELECT OBJECT_NAME(object_id) AS name ,
                            type_desc ,
                            index_id ,
                            allocation_unit_id ,
                            total_pages ,
                            CONVERT(NUMERIC(10, 2), ( total_pages * 8 ) / 1024.0) AS 'size_in_disk_mb'
                     FROM   sys.allocation_units AS au
                            INNER JOIN sys.partitions AS p ON au.container_id = p.hobt_id
                                                              AND au.type = 2
                   ) AS obj ON bd.allocation_unit_id = obj.allocation_unit_id
        JOIN sys.indexes si ON si.index_id = obj.index_id
                               AND si.[object_id] = OBJECT_ID(obj.name)
WHERE   bd.database_id = DB_ID()
        AND obj.[name] = 'tbllarge'
GROUP BY obj.name ,
        bd.database_id ,
        obj.index_id ,
        obj.type_desc ,
        si.name ,
        size_in_disk_mb ,
        obj.total_pages

Let’s get started:

1.       Create a “tblLarge” with default fill factor (which is 0 or 100) and default data compression (which is NONE)

SET NOCOUNT ON
IF OBJECT_ID('tblLarge') IS NOT NULL
    DROP TABLE tblLarge
GO

CREATE TABLE tblLarge
    (
      xID INT IDENTITY(1, 1) ,
      sName1 VARCHAR(100) ,
      sName2 VARCHAR(1000) ,
      sName3 VARCHAR(400) ,
      sIdentifier CHAR(100) ,
      dDOB DATETIME ,
      nWage NUMERIC(20, 2) ,
      sLicense VARCHAR(25)
     )
GO

2.       Populate 1000000 rows

INSERT  INTO tblLarge
        ( sName1 ,
          sName2 ,
          sName3 ,
          sIdentifier ,
          dDOB ,
          nWage ,
          sLicense
        )
VALUES  ( LEFT(CAST(NEWID() AS VARCHAR(36)), RAND()*50) ,          -- sName1
          LEFT(CAST(NEWID() AS VARCHAR(36)), RAND()*60) ,          -- sName2
          LEFT(CAST(NEWID() AS VARCHAR(36)), RAND()*70) ,          -- sName2
          LEFT(CAST(NEWID() AS VARCHAR(36)), 2) ,                  -- sIdentifier    
          DATEADD(dd, -RAND() * 20000, GETDATE()) ,                -- dDOB
          ( RAND() * 1000 ) ,                                      -- nWage
          SUBSTRING(CAST(NEWID() AS VARCHAR(36)), 6, 7)            -- sLicense
         )

GO 1000000

3.       We need to clear the data buffer cache with the following DBCC command
DBCC DROPCLEANBUFFERS
4.       We also like to get the STATISTICS IO and STATISTICS TIME output
5.       We will use the following query to perform the test
SELECT * FROM tblLarge WHERE sIdentifier='AB'
6.       Our preferable data compression method will be PAGE-level settings.

Reviewing performance on the HEAP with 0 Fill Factor without Data Compression:

We will run the following query at least ten times and average the ten outputs.

SET STATISTICS IO ON
SET STATISTICS TIME ON
DBCC DROPCLEANBUFFERS
SELECT * FROM tblLarge WHERE sIdentifier='AB'

After running the above query ten times in my system it seems that we have the following average from the STATISTICS TIME output:

-- Output from SET STATISTICS TIME ON
 SQL Server Execution Times:
   CPU time = 358 ms,  elapsed time = 3190 ms.
   CPU time = 406 ms,  elapsed time = 2926 ms.
   CPU time = 421 ms,  elapsed time = 3074 ms.
   CPU time = 297 ms,  elapsed time = 3291 ms.
   CPU time = 546 ms,  elapsed time = 3218 ms.
   CPU time = 359 ms,  elapsed time = 3169 ms.
   CPU time = 437 ms,  elapsed time = 3057 ms.
   CPU time = 437 ms,  elapsed time = 3007 ms.
   CPU time = 312 ms,  elapsed time = 3011 ms.
   CPU time = 327 ms,  elapsed time = 2931 ms.

Average CPU time = 390 ms
Average elapsed time = 3087.4 ms.

Whereas STATISTICS IO output as follows:
Table 'tblLarge'. Scan count 3, logical reads 28832, physical reads 0, read-ahead reads 28810, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Buffer review from the given DMV query:
Reviewing performance on the HEAP with 0 Fill Factor with Data Compression:

Now compress the HEAP “tblLarge” table using the PAGE-level setting:

DBCC DROPCLEANBUFFERS
ALTER TABLE tblLarge REBUILD WITH (DATA_COMPRESSION = PAGE);

STATISTICS TIME output from above is:

SQL Server Execution Times:
   CPU time = 6302 ms,  elapsed time = 18245 ms.

Once the data compression is done we run the same script we ran before.
SET STATISTICS IO ON
SET STATISTICS TIME ON
DBCC DROPCLEANBUFFERS
SELECT * FROM tblLarge WHERE sIdentifier='AB' 

From the STATISTICS TIME output we got following:

-- Output from SET STATISTICS TIME ON
 SQL Server Execution Times:
   CPU time = 342 ms,  elapsed time = 643 ms.
   CPU time = 313 ms,  elapsed time = 470 ms.
   CPU time = 281 ms,  elapsed time = 630 ms.
   CPU time = 375 ms,  elapsed time = 478 ms.
   CPU time = 359 ms,  elapsed time = 666 ms.
   CPU time = 327 ms,  elapsed time = 475 ms.
   CPU time = 375 ms,  elapsed time = 643 ms.
   CPU time = 312 ms,  elapsed time = 470 ms.
   CPU time = 297 ms,  elapsed time = 544 ms.
   CPU time = 328 ms,  elapsed time = 471 ms.

Average CPU time = 330.9 ms
Average elapsed time = 549 ms.

From the STATISTICS IO output we got the following:
Table 'tblLarge'. Scan count 3, logical reads 13981, physical reads 0, read-ahead reads 1640, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


Buffer review from the given DMV query:
Comparison Without vs. With:
STATISTICS IO
Without Compression
With Compression
Difference
Scan count
3
3
0
logical reads
28832
13981
14851
physical reads
0
0
0
read-ahead reads
28810
1640
13189
STATISTICS TIME
Without Compression
With Compression
Difference
Average CPU Time
379.3 ms
325.8 ms
53.5
Average elapsed time
3050.8 ms
1320.7 ms
1730.1
Data Pages
Without Compression
With Compression
Difference
# pages in disk
28841 (225.32 mb)
14001 (109.38 mb)
115.93 mb
# of pages In memory
28833 (225.26 mb)
13982 (109.23 mb)
116.03 mb


Comparison Summary:
From the above comparison it makes sense that data retrieval cost has been improved dramatically. It also indicates that the average CPU cost has also been reduced. In our example, there is a one time for Data compression. However, there are some (?) CPU costs associated with DML operations when Data Compression is enabled for a table, partition or index which I guess is much less than the overall benefits.

Benefits from Data compression:
1.       Reduces disk space overhead
2.       Reduces memory requirement
3.       Fewer reads – improves performance
4.       Less worker time – increases concurrency
5.       Reduces Lock contention

Disadvantages:
Other than some CPU overhead there is no significant side effects mentioned in Microsoft Books Online (BOL) or in the MSDN article. However, there are guidelines and information that we can read for better understanding before implementing data compression.

Data Compression: Strategy, Capacity Planning and Best Practices

“Microsoft SQL Server 2008 Internals” - Kalen Delancy
MS Press Book

Conclusion:
Data compression is an Enterprise Edition feature which helps to improve performance in many ways. We should take advantage of this feature to enhance performance.

So, beauty or beast? The answer is “The best way to predict your future is to create it” - Abraham Lincoln

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.