1 / 21

SQL Server Compression Estimation

SQL Server Compression Estimation. Presented by Warwick Rudd – warwick.rudd@wardyit.com. About Warwick Rudd. SQL Server Consultant WARDY IT Solutions Email: warwick.rudd@wardyit.com Microsoft Certified IT Professional – SQL 2008 Admin (MCITP SQL 2008 Admin)

badru
Download Presentation

SQL Server Compression Estimation

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. SQL Server Compression Estimation Presented by Warwick Rudd – warwick.rudd@wardyit.com

  2. About Warwick Rudd • SQL Server Consultant WARDY IT Solutions • Email: warwick.rudd@wardyit.com • Microsoft Certified IT Professional – SQL 2008 Admin (MCITP SQL 2008 Admin) • Microsoft Certified Trainer (MCT) • WARDY IT Solutions • Australia’s leading SQL Server specialists • Deloitte Technology Fast 500 Asia Pacific 2010 Winner

  3. Contents • History of Compression • SQL Server Database Compression • SQL Server Backup Compression • Compression Estimation Tool

  4. What is Compression ? The reduction in size of data to save space or processing time.

  5. History of Compression • SQL Server 2005 SP2 • New Data type - VarDecimal • SQL Server 2008 • Database & Backup compression • SQL Server 2008 R2 • Unicode data compression

  6. Database Compression Matrix

  7. Database Compression Cost Benefit Analysis

  8. Database Compression Estimation • SQL Server 2005 SP2 • Sys.sp_estimated_rowsize_reduction_for_vardecimal • SQL Server 2008 & R2 • Sp_estimate_data_compression_savings

  9. Enabling Database Compression • SQL Server 2005 SP2 • Enable database for Vardecimal Storage format • Sp_db_vardecimal_storage_format • Enabling table for Vardecimal Storage format • Sp_tableoption • SQL Server 2008 & R2 • Alter Table [TableName] Rebuild with (Data_compression = Compression Type) • Compression Types • Row • Page • None

  10. Enabling Database Compression • Alter Table [TableName] Rebuild Partition = All with (Data_compression = Compression Type on Partitions (x to n)) • Compression Types • Row • Page • None • Alter Index [IndexName] on [TableName] Rebuild with (Data_compression = Compression Type) • Compression Types • Row • Page • None

  11. Things to consider • Data types not able to be compressed • XML • BLOB • MAX Datatypes • Tables not eligible for compression • Maximum row size + compression overhead > 8060 bytes

  12. Things to consider • Compression with partitions • Splitting a range • Merging a range • Switching partitions

  13. SQL Server Database Compression Demo

  14. Backup Compression Matrix

  15. Backup Compression Cost Benefit Analysis

  16. Enabling Backup Compression • Instance level • SSMS • Transact SQL • Defaults can be overidden • Transaction Log Shipping • Maintenance Plans • SSMS • Transact SQL

  17. SQL Server Backup Compression Demo

  18. Compression Estimation Tool • Available from Codeplex • http://ssce.codeplex.com/

  19. Compression Estimation Tool Demo

  20. Thanks for listening Warwick.rudd@wardyit.com 0403 144 302 http://www.wardyit.com http://www.wardyit.com/blog

  21. Other Resources • http://blogs.msdn.com/b/sqlserverstorageengine/archive/tags/data+compression/ • http://msdn.microsoft.com/en-us/library/dd894051(v=sql.100).aspx#_Appendix_C:_Script • http://msdn.microsoft.com/en-us/library/cc280576.aspx • http://msdn.microsoft.com/en-us/library/cc280464.aspx

More Related