Data _ Backup Compression in SQL Server 2008

Document Sample
Data _ Backup Compression in SQL Server 2008 Powered By Docstoc
					                    André Kamman
Friday November 20th @ SQLBITS IV
About Me
 André Kamman
 > 20 years in IT


 Main focus on complex SQL Server
  environments
  (or a whole lot of less complex ones)
 Co-founder, current chairman of Dutch
  PASS Chapter (sqlpass.nl)
 Organizing Committee for the
  PASS European Conference
Agenda
 Why compress ?
 Backup Compression
 Data Compression
     Row, Page, Unicode (R2)
 How to choose
 How to implement
 Maintainability
Why Compress ?
 Disk throughput (a lot) slower than
  memory and CPU
 David DeWitt keynote at PASS Summit :
     Since 1985 CPU x 1000 faster
     Since 1985 Disk x 65 (!) faster
     Much larger capacity for disks but bandwith
     and seek times not so much result in a 150 x
     relatively slower disk.

   Need less disk space
Backup Compression
   Enterprise & Developer Edition only
     Can restore to Standard Edition
 Also in Standard Edition starting at R2 !
 Closest to the data, generally better than
  compressing when copying or deduping on
  the san for instance.
 Lots of competition from Quest, Redgate,
  Idera, Hyperbac, etc.
 Still adds value after implementing
  Data Compression
Row Compression
 Stores fixed length types as variable
  length. (with improved overhead)
 i.e.: int (4 bytes) can be stored in 1 byte
  if the value is low enough
 Makes vardecimal obsolete. Can still be
  used but is deprecated.
Good to know
 Compression also in memory
 Implications on replication
 Transaction log, Version store and Non-
  leaf pages stored row compressed only
 Can’t restore to standard edition
 Fix : KB963658 Compression is
  removed after shrink
 Don’t shrink !
  Or at least mind the fragmentation !
Page Compression
  Pre-Fix                           Dictionary

              Page Header




   aaabb
   0 4b     aaaab
            0 4b            abcd

   aaabcc   0bbbb
            1
            bbbb            abcd

     3ccc
   aaaccc   aaaacc          0bbbb
                            1
                            bbbb
Unicode Compression
in SQL Server 2008 R2
   SCSU
    Simple Compression Scheme for Unicode Data
   Uses 1 byte instead of 2 when it can.
How to Choose - Effectiveness
   Good Compression           Poor or no Compression

   Numeric or fixed           Fields using up all the
    length character            allocated bytes
    fields that don’t use      Not much repeated data
    all the allocated          Repeated with non-
    bytes                       repeating prefixes
   Repeating data or          Out of row data
    prefix values              FILESTREAM data
How to choose
 sp_estimate_data_compression_savings
 Quick Rule of thumb :
     ROW is lowcost, generally 10% CPU
      overhead. Use it on everything on OLTP
     PAGE is more expensive, but compresses
      more. Us it on everything on DWH
   Analyze Workload Characteristics
    (to help decide on Page Compression
    candidates)
Example of decision matrix
How to Implement
 Table, Index, Partition
 Create, alter …rebuild
 Can only set ROW or PAGE


 Mind tempspace, cpu etc. This is just
  like an Index Rebuild
 Mind cpu even more with ONLINE
  option
CREATE TABLE T1 (c1 int, c2 nvarchar(50) )
WITH (DATA_COMPRESSION = ROW)

CREATE TABLE PartitionTable1
(col1 int, col2 varchar(max))
ON myRangePS1 (col1)
WITH
(
  DATA_COMPRESSION = ROW ON PARTITIONS (1),
  DATA_COMPRESSION = PAGE ON PARTITIONS (2 TO 4)
);
GO
ALTER TABLE T1
REBUILD WITH (DATA_COMPRESSION = PAGE);
GO


ALTER TABLE PartitionTable1
REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = PAGE ON PARTITIONS(1) ) ;
GO


ALTER TABLE PartitionTable1
REBUILD PARTITION = ALL
WITH
(
DATA_COMPRESSION = PAGE ON PARTITIONS(1),
DATA_COMPRESSION = ROW ON PARTITIONS(2 TO 4)
);
GO
ALTER INDEX IX_INDEX_1
ON T1
REBUILD WITH ( DATA_COMPRESSION = PAGE ) ;
GO



ALTER INDEX IX_PartTab2Col1 ON PartitionTable1
REBUILD PARTITION = ALL
WITH
(
DATA_COMPRESSION = PAGE ON PARTITIONS(1),
DATA_COMPRESSION = ROW ON PARTITIONS(2 TO 4)
);
GO
Maintainability - Monitor
   Perfmon
     Page compression attempts/sec
     Pages compressed/sec
     (SQL Server, Access Methods object)
 Sys.dm_db_index_operational_stats
 Sys.dm_db_persisted_sku_features
 Sys.partitions
  (column data_compression_desc)
Maintenance
 Heap
  Pages are only compressed when bulk
  inserted, inserted with tablock or rebuild
 Table with clustered index
  Pages are compressed when page is
  full. (before page split is attempted)
Contact me :
 Email : andre@sqlserverdba.nl
 Twitter : @AndreKamman
 Blog : http://andrekamman.com


 This presentation :
 http://andrekamman.com/presentations


   Or just stop me when you run into me
Links / Resources
   SQLCAT.com whitepaper :
   http://sqlcat.com/whitepapers/archive/2009/05/29/data-compression-
    strategy-capacity-planning-and-best-practices.aspx

    (Couple of very useful links to blogs etc. inside this whitepaper!)

 Storage Engine Team Blog on
    Compression :
   http://blogs.msdn.com/sqlserverstorageengine/archive/tags/Data+Compress
    ion/default.aspx

   Don’t shrink :
   http://www.sqlskills.com/BLOGS/PAUL/post/Auto-shrink-e28093-turn-it-
    OFF!.aspx
   http://www.sqlskills.com/BLOGS/PAUL/post/Why-you-should-not-shrink-
    your-data-files.aspx

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:0
posted:4/17/2013
language:English
pages:24