{"id":984,"date":"2008-11-11T09:29:00","date_gmt":"2008-11-11T09:29:00","guid":{"rendered":"\/blogs\/paul\/post\/Conference-Questions-Pot-Pourri-9-QA-around-compression-features.aspx"},"modified":"2017-04-13T09:51:53","modified_gmt":"2017-04-13T16:51:53","slug":"conference-questions-pot-pourri-9-qa-around-compression-features","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/conference-questions-pot-pourri-9-qa-around-compression-features\/","title":{"rendered":"Conference Questions Pot-Pourri #9: Q&#038;A around compression features"},"content":{"rendered":"<p>\n<font face=\"verdana,geneva\" size=\"2\">Today&#39;s post is based on a bunch of questions I&#39;ve had around the various compression features in SQL Server 2008.<\/font>\n<\/p>\n<p>\n<font size=\"2\"><em>Does turning on data compression or backup compression compress the transaction log files on disk?<\/em><\/font>\n<\/p>\n<p>\n<font size=\"2\">No. The transaction log is not compressed in any way under any circumstances. Rows from tables and indexes that have compression enabled will be logged in their row compressed forms, even if page compression is enabled.<\/font>\n<\/p>\n<p>\n<em><font size=\"2\">Does data compression compress LOB columns?<\/font><\/em>\n<\/p>\n<p>\n<font size=\"2\">No. There is no native compression support for any LOB columns (n\/text, image, n\/varchar(max), varbinary(max), XML), whether stored in-row or out-of-row. There&#39;s also no native compression support for FILESTREAM data.<\/font>\n<\/p>\n<p>\n<em><font size=\"2\">Does log shipping use compression to compress the logs being shipped?<\/font><\/em>\n<\/p>\n<p>\n<font size=\"2\">Log shipping does not ship transaction logs &#8211; it ships log *backups*. If backup compression is enabled for the instance hosting the log shipping primary database, or the log shipping job is changed to enable backup compression, then the log backups will be compressed and less data will be sent over the wire to the log shipping secondary(s).<\/font>\n<\/p>\n<p>\n<font size=\"2\"><em>Is backup compression the same as log stream compression with database mirroring?<\/em><\/font>\n<\/p>\n<p>\n<font size=\"2\">No. Backup compression compresses backups (see my previous blog post <a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/sql-server-2008-backup-compression\/\">here<\/a>). Log stream compression with database mirroring compresses transaction log records before sending them between the principal and the mirror (see my previous blog post <a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/sql-server-2008-performance-boost-for-database-mirroring\/\">here<\/a>).<\/font>\n<\/p>\n<p>\n<em><font size=\"2\">Should I just turn on backup compression at the instance level?<\/font><\/em>\n<\/p>\n<p>\n<font size=\"2\">Not necessarily. It depends whether the majority of database on the instance will benefit from backup compression. Backup compression (and any compression algorithm) uses CPU whether a decent compression ratio is achieved or not. Check what compression ratio is achieved first and then enable backup compression if its worth it. Otherwise, just enable it for individual databases.<\/font>\n<\/p>\n<p>\n<em><font size=\"2\">Does data compression use the same algorithm as backup compression?<\/font><\/em>\n<\/p>\n<p>\n<font size=\"2\">No. Backup compression uses a proprietary block-based compression algorithm that is part of Windows. Data compression uses up to 3 algorithms, depending on the level of compression configured. Row compression just makes all non-LOB columns into variable-length columns. Page compression does row compression, then common-prefix compression for each table columns, then common-value dictionary compression for each page. Details can be found at the following BOL sections: <a href=\"https:\/\/msdn.microsoft.com\/Areas\/Epx\/Content\/500.htm?aspxerrorpath=\/en-us\/library\/cc280576.aspx\">Row Compression Implementation<\/a>&nbsp; and <a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/cc280464.aspx\">Page Compression Implementation<\/a>.<\/font>\n<\/p>\n<p>\n<font size=\"2\">Hope this helps!<\/font><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Today&#39;s post is based on a bunch of questions I&#39;ve had around the various compression features in SQL Server 2008. Does turning on data compression or backup compression compress the transaction log files on disk? No. The transaction log is not compressed in any way under any circumstances. Rows from tables and indexes that have [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[13,25,26,32,58,86,98],"tags":[],"class_list":["post-984","post","type-post","status-publish","format-standard","hentry","category-backuprestore","category-compression","category-conference-questions-pot-pourri","category-database-mirroring","category-log-shipping","category-sql-server-2008","category-transaction-log"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Conference Questions Pot-Pourri #9: Q&amp;A around compression features - Paul S. Randal<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/conference-questions-pot-pourri-9-qa-around-compression-features\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Conference Questions Pot-Pourri #9: Q&amp;A around compression features - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"Today&#039;s post is based on a bunch of questions I&#039;ve had around the various compression features in SQL Server 2008. Does turning on data compression or backup compression compress the transaction log files on disk? No. The transaction log is not compressed in any way under any circumstances. Rows from tables and indexes that have [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/conference-questions-pot-pourri-9-qa-around-compression-features\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2008-11-11T09:29:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T16:51:53+00:00\" \/>\n<meta name=\"author\" content=\"Paul Randal\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Paul Randal\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/conference-questions-pot-pourri-9-qa-around-compression-features\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/conference-questions-pot-pourri-9-qa-around-compression-features\/\",\"name\":\"Conference Questions Pot-Pourri #9: Q&A around compression features - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2008-11-11T09:29:00+00:00\",\"dateModified\":\"2017-04-13T16:51:53+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/conference-questions-pot-pourri-9-qa-around-compression-features\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/conference-questions-pot-pourri-9-qa-around-compression-features\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/conference-questions-pot-pourri-9-qa-around-compression-features\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Conference Questions Pot-Pourri #9: Q&#038;A around compression features\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\",\"name\":\"Paul S. Randal\",\"description\":\"In Recovery...\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\",\"name\":\"Paul Randal\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"caption\":\"Paul Randal\"},\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/paul\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Conference Questions Pot-Pourri #9: Q&A around compression features - Paul S. Randal","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/paul\/conference-questions-pot-pourri-9-qa-around-compression-features\/","og_locale":"en_US","og_type":"article","og_title":"Conference Questions Pot-Pourri #9: Q&A around compression features - Paul S. Randal","og_description":"Today&#39;s post is based on a bunch of questions I&#39;ve had around the various compression features in SQL Server 2008. Does turning on data compression or backup compression compress the transaction log files on disk? No. The transaction log is not compressed in any way under any circumstances. Rows from tables and indexes that have [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/conference-questions-pot-pourri-9-qa-around-compression-features\/","og_site_name":"Paul S. Randal","article_published_time":"2008-11-11T09:29:00+00:00","article_modified_time":"2017-04-13T16:51:53+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/conference-questions-pot-pourri-9-qa-around-compression-features\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/conference-questions-pot-pourri-9-qa-around-compression-features\/","name":"Conference Questions Pot-Pourri #9: Q&A around compression features - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2008-11-11T09:29:00+00:00","dateModified":"2017-04-13T16:51:53+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/conference-questions-pot-pourri-9-qa-around-compression-features\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/conference-questions-pot-pourri-9-qa-around-compression-features\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/conference-questions-pot-pourri-9-qa-around-compression-features\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"Conference Questions Pot-Pourri #9: Q&#038;A around compression features"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/","name":"Paul S. Randal","description":"In Recovery...","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce","name":"Paul Randal","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","caption":"Paul Randal"},"sameAs":["http:\/\/3.209.169.194\/blogs\/paul"],"url":"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/984","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/comments?post=984"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/984\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=984"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=984"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=984"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}