SQL 2008 Row and Page Compression & SQL 2005 (post SP2) VarDecimal Conversion : SQL 2008 Row and Page Compression & SQL 2005 (post SP2) VarDecimal Conversion Hugo Shebbeare, MCDBA, MCITP, PMP
April 15th, 2009 Burlington, VT Increase the flow of your business : Increase the flow of your business Moved to Montreal from Vancouver in 1998, after completing University Degree in Brussels, Belgium ( major International Affairs & History ) from Vesalius College (in association with Boston University) VUB
Director of profitable small business Intellabase Solutions ( Intellabase.com ) since 2002. Launched with help from the Plateau’s (Montreal, inner city suburb) Local Economic Development Center, and their course ‘Start-up your business, for good.’ ( Etre en affaires pour de bon ) : After three years working intensely with SQL Server 7.0 and 2000, attained MCDBA certification in 2001 after five exams. Renewed in October 2008 (MCITP – MCDBA upgrade exam to SQL 2005)
Managed a multitude of IT projects since 1999 &Attained PMP certification from the Project Management Institute in 2005, just renewed April 2009 for three years. : Early spring 2008 began internal Meilleures practiques (Best Practices) monthly newsletters at the Caisse de Dépôt et Placement du Québec ( CDPQ ) – largest public pension fund manager in Canada
Evolved into bilingual blog DatabaseHive.com ( dbhive.blogspot.com )
Led to invitation* to contribute as blogger on SQL Server Central (SSC) www.SQLServerCentral.com/blogs/Hugo (reached top Five April 3rd, , top Three April 10th, 2009 – Front Page articles 5 times)
Now writing for Simple-Talk.com Up and coming article – A Real Disaster Recovery Plan using SQLBackup
*After meeting Brad McGehee at DevTech ’08, encouraged to blog for SSC Jan ‘09 Blogging: The Database Hive for SQL Server DBAs
This presentation developed from recent SSC article ‘SQL 2008 Row and Page compression – or SQL 2005 (post SP2) VarDecimal conversion Preface : Preface The content for the talk was a quick pick thanks to Roman Rehak’s request – by the end of this talk you will seriously think about compression.
Contents of this presentation shall be posted on the SSC blog shortly afterwards, with periodic revisions – and I welcome comments so if you wish to indirectly help the SQL Server User Community, please do not hesitate. SQL Server Database Compression - What to take away: : SQL Server Database Compression - What to take away: Not entirely new to SQL Server for version 10, but way more features added.
Option for compression on decimal columns in version 9 ( 2005, with sp2 )
SQL 2008/5 require Developer or Entreprise Edition (I was too optimistic with 2005 std).
Space savings can be up to less than half the size (e.g 1.3TB down to 650GB) SQL Server Database Compression - What to take away : SQL Server Database Compression - What to take away SELECT statements, after database is compressed, then optimised, can run at 40%
Tools to determine usefulness to your situation: system stored procedures
2005: sys.sp_estimated_rowsize_reduction_for_vardecimal Why this is a feature from SQL Server 2005/8 not to miss : Why this is a feature from SQL Server 2005/8 not to miss The cumulative space savings after optimisation, and saved result sets execution time translate into proactive cost gains
No bitter [optimisation] pill to swallow, since no front-end application changes are required Demos : Demos SQL 2008 Row and Page Compression
SQL 2005 VarDecimal Conversion Compression Prerequisites: : Compression Prerequisites: Ensure enough space for the MDF (data file) and LDF (log file especially) to swell
Use console command (dbcc) Shrinkdatabase to recover the huge amount of free space after conversion
The right edition: SQL 2008 Dev / Entrep,
or SQL 2005, post sp2 Dev / Entreprise : Business Case:
Cumulative Space savings for Backups, as well as performance gains, since client requests response time will be more or less cut in half.
How to deal with a great deal of tables to compress? What type?
Use T-SQL and sort your tables by the largest first, data pages should be in the thousands – for very large databases with millions of rows, great target for this type of optimisation
Exceptional DBA guru Brad M. McGehee clearly states preference for Row Compression (still too new for me to know, so Brad is really the authority) : SQL Database Compression Recap
I/O bound systems, waiting on disks constantly, fit the profile for significant compression benefits
Relatively simple options
Individual Table level Compression (performed per object only)
No application changes on the front end
Sweet Performance and Space gains : Thank you, happy compressing
Tel/txt: 514 812 5087
http://linkedin.com/in/intellabase - over ten recommendations
firstname.lastname@example.org -> email + messenger
Will be joining Roman Rehak, Mario Cardinal, J-Y Roy, and Erik Renaud at Vancouver’s Developer Conference in June - www.SQLteach.com/Session.aspx
Discussion on why Developers should care about Transaction Log Files SSC Blog topics : SSC Blog topics An optimised Disaster Recovery Plan thanks to SQLBackup
The Importance of the Segregation of Duties with Respect to Internal Controls
SQL Server 2008 Activity Monitor: Embedded Gizmo for the Thrifty DBA
How Certification can help you stay current with Technology, as well as Continuing Your Education
Microsoft Project Template for the Migration to SQL Server 2008