Advanced Index, Partitioning and Compression Strategies for SQL Server
Download as pptx, pdf3 likes827 views
This document discusses advanced indexing, partitioning, and compression strategies for SQL Server. It provides an overview of SQL Server data compression features, including vardecimal compression introduced in SQL 2005, and page and row compression available since 2008. The document outlines the different data types that benefit most from compression and estimates for space savings. It also discusses using Unicode compression and how compression can be combined with other features like partitioning to further optimize database performance. Real-world demos and scripts are provided to help identify objects in a database that would benefit from these advanced strategies.
More Related Content
What's hot (15)
Viewers also liked (7)
Similar to Advanced Index, Partitioning and Compression Strategies for SQL Server (20)
Recently uploaded (20)
Advanced Index, Partitioning and Compression Strategies for SQL Server
- 1. Advanced Index, Partitioning and Compression Strategies for SQL Server Neil Hambly Senior DBA & Engineer, Confio Software 8/16/2013 1
- 2. Who Am I? • Senior DBA/Engineer for Confio Software – NeilHambly@confio.com {Based in the UK} • DBA – 14+ Years, SQL Server 6.5 - 2012 • Held # of DB roles @ many leading organizations • Regular International PASS Presenter @ SQL Conferences, User-Groups & Webinars • SQL London PASS Chapter Leader, UK Events Organizer • Regular @ UK & International community events • Twitter: @Neil_Hambly & @Confio Contributing Author • Articles @ Confio.com & My blog 8/16/2013 2
- 3. Agenda • Talk about the two features – Compression & Partitioning • Using Data Compression in SQL Server 2005+ – Compression • Editions, Data types • Vardecimal Compression (2005 SP2) • Page | Row Compression (2008+) • Unicode Compression • Estimate space & resources – Partitioning – Combining with other features – Demos • Partition-Info Script • Finding objects for compression • Q&A + further links 8/16/2013 3
- 4. Data Types 8/16/2013 4 Data Compression was introduced in SQL 2005 (SP2) via vardecimal storage This was superseded in SQL Server in 2008 Version (Row & Page level) Compression feature is only available in Enterprise and Developer SKU’s Note: NULL and 0 values across all data types are optimized to take 0 bytes in addition to 4 bits/per Column Data Type / Benefits with Data Compression Yes – depends on data No or little benefit (Normally) bit / smallint / int / bigint tinyint / uniqueidentifier decimal / numeric datetime / datetime2 / datetimeoffset smalldatetime / date / time char / nchar / nvarchar varchar / text / ntext / image binary / timestamp / Rowversion varbinary money / smallmoney float / real sql_variant / FileStream cursor / table / xml
- 5. SQL Server 2005 SP2 (Vardecimal) The vardecimal works the same way as the varchar works for efficiently storing alphanumeric data. 8/16/2013 5 HOW TO TURN ON THE VARDECIMAL OPTION Step 1: exec sp_db_vardecimal_storage_format '<dbname>', 'ON' This option will enable the vardecimal storage on the specified database. Step 2: exec sp_tableoption '<table>', 'vardecimal storage format', 1 This option will turn on the vardecimal storage for the existing table. HOW TO ESTIMATE WHETHER IT IS WORTH TURNING ON THIS OPTION sys.sp_estimated_rowsize_reduction_for_vardecimal '<table>' Column precision Original fixed decimal size (bytes) Maximum vardecimal data area (bytes) Overhead to store offset (bytes) Maximum vardecimal storage used (bytes) 1-3 5 3 2 5 4-6 5 4 2 6 7-9 5 5 2 7 10-12 9 6 2 8 13-15 9 8 2 10 16-18 9 9 2 11 19 9 10 2 12 20-21 13 10 2 12 22-24 13 11 2 13 25-27 13 13 2 15 28 13 14 2 16 29-30 17 14 2 16 31-33 17 15 2 17 34-36 17 16 2 18 37-38 17 18 2 20
- 6. Page & Row Level Compression 8/16/2013 6 Since 2008 version, we have new compression algorithms available Compressing the leaf level of tables and indexes with page compression consists of three operations, these are done in the following order: 1. Row compression 2. Prefix compression 3. Dictionary compression Note: When using page compression, the non–leaf-level pages of indexes are only row compressed Row Compression Prefix Compression Dictionary Compression
- 7. Unicode Compression Space savings that can be achieved for different locales (15-50%) 8/16/2013 7 Standard Compression Scheme for Unicode (SCSU) SCSU algorithm to compress Unicode values that are stored in row or page compressed objects Unicode compression supports the fixed- length nchar(n) and nvarchar(n) data types. Data values that are stored off row or in nvarchar(max) columns are not compressed Locale Compression % English 50% German 50% Hindi 50% Turkish 48% Vietnamese 39% Japanese 15% SCSU can also switch to UTF-16 internally to handle non-alphabetic languages
- 8. Supercharge SQL Server Performance with Indexing, Partitioning and Compression View this full 60-minute webinar, compliments of Confio Software, to supercharge SQL Server performance using advanced techniques for indexing, partitioning and/or compression. You’ll learn: • How to identify which objects will benefit the most from indexing • How to decide when you should combine indexing, partitioning and compression • How these techniques have been applied in real-world scenarios • How to you can use available demo scripts to determine which objects in your environment will benefit from these techniques www.confio.com 8/16/2013 8