1 / 24

Data & Backup Compression in SQL Server 2008

André Kamman Friday November 20 th @ SQLBITS IV. Data & Backup Compression in SQL Server 2008. 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)

galya
Download Presentation

Data & Backup Compression in SQL Server 2008

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. André Kamman Friday November 20th @ SQLBITS IV Data & Backup Compressionin SQL Server 2008

  2. 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

  3. Agenda • Why compress ? • Backup Compression • Data Compression • Row, Page, Unicode (R2) • How to choose • How to implement • Maintainability

  4. 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

  5. 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

  6. 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.

  7. 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 !

  8. Page Compression Pre-Fix Dictionary Page Header aaabb aaaab abcd 0 4b 0 4b 1 0bbbb aaabcc bbbb abcd 3ccc 1 0bbbb aaaccc aaaacc bbbb

  9. Unicode Compression in SQL Server 2008 R2 • SCSU Simple Compression Scheme for Unicode Data • Uses 1 byte instead of 2 when it can.

  10. How to Choose - Effectiveness • Good Compression • Numeric or fixed length character fields that don’t use all the allocated bytes • Repeating data or prefix values • Poor or no Compression • Fields using up all the allocated bytes • Not much repeated data • Repeated with non-repeating prefixes • Out of row data • FILESTREAM data

  11. 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)

  12. Example of decision matrix

  13. 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

  14. Some code examples

  15. 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

  16. 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

  17. 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

  18. 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)

  19. Maintenance • HeapPages are only compressed when bulk inserted, inserted with tablock or rebuild • Table with clustered indexPages are compressed when page is full. (before page split is attempted)

  20. Quiz

  21. Questions ?

  22. 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

  23. Thank you ! Don’t forget to fill out the evals please !

  24. 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+Compression/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

More Related