SQLdbCompressionVT

Views:
 
Category: Entertainment
     
 

Presentation Description

No description available.

Comments

Presentation Transcript

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 2008: sys.sp_estimate_data_compression_savings 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  Contact info: Tel/txt: 514 812 5087 http://intellabase.com http://linkedin.com/in/intellabase - over ten recommendations hugo@intellabase.com -> email + messenger BLOG: http://www.SqlServerCentral.com/Blogs/Hugo 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