{"id":5229,"date":"2022-01-12T10:52:05","date_gmt":"2022-01-12T18:52:05","guid":{"rendered":"https:\/\/www.sqlskills.com\/blogs\/paul\/?p=5229"},"modified":"2022-01-12T10:52:05","modified_gmt":"2022-01-12T18:52:05","slug":"the-curious-case-of-tracking-page-compression-success-rates","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-tracking-page-compression-success-rates\/","title":{"rendered":"The Curious Case of&#8230; tracking page compression success rates"},"content":{"rendered":"<p style=\"text-align: justify;\"><em>(The Curious Case of\u2026<\/em>\u00a0used to be part of our bi-weekly\u00a0<a href=\"https:\/\/www.sqlskills.com\/join-the-sqlskills-insider-community\/\" target=\"_blank\" rel=\"noopener noreferrer\">newsletter<\/a>\u00a0but we decided to make it a\u00a0regular blog post instead so it can sometimes be more frequent. It covers something interesting one of us encountered when working with a client, doing some testing, or were asked in a random question from the community.)<\/p>\n<p style=\"text-align: justify;\">Yesterday I was chatting with Jonathan about some of the internals of the page compression algorithms for a client discussion. When you enable page compression, rows are first just row compressed (storing values with empty bytes stripped out) until a page fills up. The Storage Engine then attempts page compression for the page. This involves these steps, in the order listed:<\/p>\n<ol style=\"text-align: justify;\">\n<li>Creating a compression information (CI) record that is stored at the top of the page.<\/li>\n<li>Common prefix compression per column. The longest common prefix is found for each column and the longest column value that contains that prefix is moved to the CI record, and each value in the column is replaced with a number representing how many bytes from the common prefix to use, plus any other bytes after the prefix bytes.<\/li>\n<li>Dictionary compression. Look across all values in all columns and pull out common values into another portion of the CI record. In the column value&#8217;s place is a number representing which element of the dictionary to use.<\/li>\n<li>If the space saved by performing page compression saves 20% or more of the space on the page, the page becomes page compressed, otherwise it remains simply row compressed.<\/li>\n<\/ol>\n<p style=\"text-align: justify;\">You can see some graphics that show this process in the Microsoft doc page <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/relational-databases\/data-compression\/page-compression-implementation?view=sql-server-ver15\" target=\"_blank\" rel=\"noopener\">here<\/a>.<\/p>\n<p style=\"text-align: justify;\">This means a page compressed table or index could have a mixture of row compressed and page compressed pages &#8211; perfectly normal.<\/p>\n<p style=\"text-align: justify;\">What Jon didn&#8217;t realize was that there&#8217;s a way to track step 4, to see whether all the CPU being used to attempt page compression is worthwhile. Some people on Twitter also mentioned that they didn&#8217;t know that, and I&#8217;m not surprised as neither the docs nor the excellent whitepaper <a href=\"https:\/\/docs.microsoft.com\/en-us\/previous-versions\/sql\/sql-server-2008\/dd894051(v=sql.100)\" target=\"_blank\" rel=\"noopener\"><em>Data Compression: Strategy, Capacity Planning and Best Practices<\/em><\/a> mention it.<\/p>\n<p style=\"text-align: justify;\">The DMV\u00a0<em>sys.dm_db_index_operational_stats<\/em> has two columns that \u00a0you can use those too see whether the data in a table or index is suitable for page compression or not: <em>page_compression_attempt_count<\/em> and\u00a0<em>page_compression_success_count<\/em>.<\/p>\n<p style=\"text-align: justify;\">Here&#8217;s a query you can use to figure out the success rate of page compression for indexes, down to the partition level. Note that if you don&#8217;t have any partitioning, you&#8217;ll just see partition ID 1 for each index.<\/p>\n<pre class=\"brush: plain; gutter: false; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nSELECT\r\n    DISTINCT object_name (i.object_id) AS &#x5B;Table],\r\n    i.name AS &#x5B;Index],\r\n    p.partition_number AS &#x5B;Partition],\r\n    page_compression_attempt_count,\r\n    page_compression_success_count,\r\n    page_compression_success_count * 1.0 \/ page_compression_attempt_count\r\n        AS &#x5B;SuccessRate]\r\nFROM\r\n    sys.indexes AS i\r\nINNER JOIN\r\n    sys.partitions AS p\r\nON\r\n    p.object_id = i.object_id\r\nCROSS APPLY\r\n    sys.dm_db_index_operational_stats (\r\n        db_id(), i.object_id, i.index_id, p.partition_number) AS ios\r\nWHERE\r\n    p.data_compression = 2\r\n    AND page_compression_attempt_count &gt; 0\r\nORDER BY\r\n    &#x5B;SuccessRate];\r\n<\/pre>\n<p style=\"text-align: justify;\">What threshold you pick for when to disable page compression for a table, index, or partition is up to you, but at least now you know how to calculate the metric.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>(The Curious Case of\u2026\u00a0used to be part of our bi-weekly\u00a0newsletter\u00a0but we decided to make it a\u00a0regular blog post instead so it can sometimes be more frequent. It covers something interesting one of us encountered when working with a client, doing some testing, or were asked in a random question from the community.) Yesterday I was [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[65,115],"tags":[],"class_list":["post-5229","post","type-post","status-publish","format-standard","hentry","category-partitioning","category-the-curious-case-of"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>The Curious Case of... tracking page compression success rates - 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\/the-curious-case-of-tracking-page-compression-success-rates\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"The Curious Case of... tracking page compression success rates - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"(The Curious Case of\u2026\u00a0used to be part of our bi-weekly\u00a0newsletter\u00a0but we decided to make it a\u00a0regular blog post instead so it can sometimes be more frequent. It covers something interesting one of us encountered when working with a client, doing some testing, or were asked in a random question from the community.) Yesterday I was [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-tracking-page-compression-success-rates\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2022-01-12T18:52:05+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=\"3 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\/the-curious-case-of-tracking-page-compression-success-rates\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-tracking-page-compression-success-rates\/\",\"name\":\"The Curious Case of... tracking page compression success rates - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2022-01-12T18:52:05+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-tracking-page-compression-success-rates\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-tracking-page-compression-success-rates\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-tracking-page-compression-success-rates\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"The Curious Case of&#8230; tracking page compression success rates\"}]},{\"@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":"The Curious Case of... tracking page compression success rates - 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\/the-curious-case-of-tracking-page-compression-success-rates\/","og_locale":"en_US","og_type":"article","og_title":"The Curious Case of... tracking page compression success rates - Paul S. Randal","og_description":"(The Curious Case of\u2026\u00a0used to be part of our bi-weekly\u00a0newsletter\u00a0but we decided to make it a\u00a0regular blog post instead so it can sometimes be more frequent. It covers something interesting one of us encountered when working with a client, doing some testing, or were asked in a random question from the community.) Yesterday I was [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-tracking-page-compression-success-rates\/","og_site_name":"Paul S. Randal","article_published_time":"2022-01-12T18:52:05+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-tracking-page-compression-success-rates\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-tracking-page-compression-success-rates\/","name":"The Curious Case of... tracking page compression success rates - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2022-01-12T18:52:05+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-tracking-page-compression-success-rates\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-tracking-page-compression-success-rates\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-tracking-page-compression-success-rates\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"The Curious Case of&#8230; tracking page compression success rates"}]},{"@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\/5229","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=5229"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/5229\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=5229"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=5229"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=5229"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}