240 likes | 342 Vues
Explore the benefits of backup and data compression in SQL Server 2008 with expert guidance on implementation, maintenance, and monitoring. Learn about row, page, and Unicode compression techniques to optimize storage and performance.
E N D
André Kamman Friday November 20th @ SQLBITS IV Data & Backup Compressionin 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) • 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 aaaab abcd 0 4b 0 4b 1 0bbbb aaabcc bbbb abcd 3ccc 1 0bbbb aaaccc aaaacc 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 • 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
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)
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 • 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)
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
Thank you ! Don’t forget to fill out the evals please !
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