{"id":1449,"date":"2020-09-01T08:08:00","date_gmt":"2020-09-01T13:08:00","guid":{"rendered":"http:\/\/www.sqlnuggets.com\/?p=1449"},"modified":"2020-08-24T13:11:11","modified_gmt":"2020-08-24T18:11:11","slug":"disabling-an-index-drops-compression","status":"publish","type":"post","link":"https:\/\/sqlnuggets.com\/disabling-an-index-drops-compression\/","title":{"rendered":"Disabling An Index Drops Compression"},"content":{"rendered":"\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"320\" src=\"https:\/\/sqlnuggets.com\/wp-content\/uploads\/2020\/08\/puzzle-1765847_1920-1024x320.jpg\" alt=\"\" class=\"wp-image-1458\" srcset=\"https:\/\/sqlnuggets.com\/wp-content\/uploads\/2020\/08\/puzzle-1765847_1920-1024x320.jpg 1024w, https:\/\/sqlnuggets.com\/wp-content\/uploads\/2020\/08\/puzzle-1765847_1920-300x94.jpg 300w, https:\/\/sqlnuggets.com\/wp-content\/uploads\/2020\/08\/puzzle-1765847_1920-768x240.jpg 768w, https:\/\/sqlnuggets.com\/wp-content\/uploads\/2020\/08\/puzzle-1765847_1920-1536x480.jpg 1536w, https:\/\/sqlnuggets.com\/wp-content\/uploads\/2020\/08\/puzzle-1765847_1920.jpg 1920w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p><em>This post is more of an FYI, and to remind myself in case I run into it again down the road.  <\/em><\/p>\n\n\n\n<p>Here at work we have a very large, very intensive data load that disables and rebuilds indexes as part of the process.  We recently added compression to many of the tables and indexes in the database because it was growing quite large (around 28TB at the time).  After adding compression, we got the database size down to somewhere around 17TB.<\/p>\n\n\n\n<p>So you can imagine our surprise when the DB size jumped back up to over 30TB after the last data load!  In trying to figure out what happened I discovered that most of the data compression was gone.  After some investigation one of the developers discovered that when the indexes were disabled and rebuilt, they were rebuilt without compression.  The reason for that is because when you disable an index, the index&#8217;s current compression setting gets changed to &#8220;none&#8221; in sys.partitions, and that is the value SQL Server will use when rebuilding the index.<\/p>\n\n\n\n<p>Here is an example:  Create an empty table (we don&#8217;t need data for this test) and add a nonclustered index to it, specifying page compression.<\/p>\n\n\n<pre class=\"lang:tsql decode:true \">--create an empty table for our test\nCREATE TABLE [dbo].[Compression_Test](\n\t[ID] [int] IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL,\n\t[Col2] [varchar](100) NOT NULL,\n\t[Col3] [int] NOT NULL,\n\t[Col4] [int] NULL,\n\t[Col5] [int] NULL,\n\t[Col6] [varchar](255) NULL,\n\t[Col7] [varchar](255) NULL\n )\nGO\n\n--add a compressed nonclustered index\nCREATE NONCLUSTERED INDEX [IX_Test_Compression] ON [dbo].[Compression_Test] (Col2,Col3) WITH (DATA_COMPRESSION=PAGE);\n<\/pre>\n\n\n<p>Now, check the compression of our newly created index and it should be listed as &#8220;page&#8221;.<\/p>\n\n\n<pre class=\"lang:tsql decode:true \">--check the compression of our index\nSELECT name, i.type_desc,p.data_compression,p.data_compression_desc FROM sys.indexes i\nLEFT JOIN sys.partitions p ON p.object_id = i.object_id\nWHERE i.name = 'IX_Test_Compression'\nAND i.index_id = 2<\/pre>\n\n\n<p>Now, disable the index and check the compression again:<\/p>\n\n\n<pre class=\"lang:tsql decode:true \">--disable the index\nALTER INDEX [IX_Test_Compression] ON [dbo].[Compression_Test] DISABLE;\n\n--recheck the compression of our index\nSELECT name, i.type_desc,p.data_compression,p.data_compression_desc \nFROM sys.indexes i\nLEFT JOIN sys.partitions p ON p.object_id = i.object_id\nWHERE i.name = 'IX_Test_Compression'\nAND i.index_id = 2<\/pre>\n<p><\/p>\n\n\n<p>This time you should notice that the compression setting is set to &#8220;None&#8221;.  If you were to rebuild the index now, it would be rebuilt without compression.  In order to ensure that compression is enabled, you have to explicitly specify it in your rebuild statement<\/p>\n\n\n<pre class=\"lang:tsql decode:true \">ALTER INDEX [IX_Test_Compression] ON [dbo].[Compression_Test] REBUILD WITH (DATA_COMPRESSION=PAGE);<\/pre>\n<p>I have tested this on SQL Server 2016 and SQL Server 2019, and get the same results on each.<\/p>\n<p>I&#8217;m not sure if this should be considered a feature or a bug.&nbsp; In my mind, when re-enabling a disabled index it should retain these settings, and the index be restored as it was when it was disabled.&nbsp;&nbsp;<\/p>\n\n\n<p>Luckily, in our case, we had <a href=\"https:\/\/github.com\/ericcobb\/SQL-Server-Metrics-Pack\" target=\"_blank\" rel=\"noreferrer noopener\">SQL Server Metrics Pack<\/a> running on the server, so we were able to <a href=\"https:\/\/sqlnuggets.com\/blog\/tag\/sql-metrics-pack\/\">recover the scripts<\/a> for all of the indexes that had been previously compressed.<br><\/p>\n","protected":false},"excerpt":{"rendered":"<p>This post is more of an FYI, and to remind myself in case I run into it again down the road. Here at work we have a very large, very &#8230;<\/p>\n","protected":false},"author":2,"featured_media":1458,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[68,28,24],"tags":[26,58],"class_list":["post-1449","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-configurations","category-db-engine","category-indexes","tag-index-basics","tag-sql-metrics-pack"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.5 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Disabling An Index Drops Compression - SQL Nuggets<\/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.sqlnuggets.com\/disabling-an-index-drops-compression\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Disabling An Index Drops Compression - SQL Nuggets\" \/>\n<meta property=\"og:description\" content=\"This post is more of an FYI, and to remind myself in case I run into it again down the road. Here at work we have a very large, very ...\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlnuggets.com\/disabling-an-index-drops-compression\/\" \/>\n<meta property=\"og:site_name\" content=\"SQL Nuggets\" \/>\n<meta property=\"article:published_time\" content=\"2020-09-01T13:08:00+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.sqlnuggets.com\/wp-content\/uploads\/2020\/08\/puzzle-1765847_1920.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"1920\" \/>\n\t<meta property=\"og:image:height\" content=\"600\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Eric Cobb\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@cfgears\" \/>\n<meta name=\"twitter:site\" content=\"@sqlnugg\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Eric Cobb\" \/>\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\":\"Article\",\"@id\":\"https:\\\/\\\/www.sqlnuggets.com\\\/disabling-an-index-drops-compression\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.sqlnuggets.com\\\/disabling-an-index-drops-compression\\\/\"},\"author\":{\"name\":\"Eric Cobb\",\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/#\\\/schema\\\/person\\\/210536254addbc1b9d2d95dc1448b38a\"},\"headline\":\"Disabling An Index Drops Compression\",\"datePublished\":\"2020-09-01T13:08:00+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.sqlnuggets.com\\\/disabling-an-index-drops-compression\\\/\"},\"wordCount\":366,\"commentCount\":1,\"publisher\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/#organization\"},\"image\":{\"@id\":\"https:\\\/\\\/www.sqlnuggets.com\\\/disabling-an-index-drops-compression\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/sqlnuggets.com\\\/wp-content\\\/uploads\\\/2020\\\/08\\\/puzzle-1765847_1920.jpg\",\"keywords\":[\"Index Basics\",\"SQL Metrics Pack\"],\"articleSection\":[\"Configurations\",\"DB Engine\",\"Indexes\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.sqlnuggets.com\\\/disabling-an-index-drops-compression\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.sqlnuggets.com\\\/disabling-an-index-drops-compression\\\/\",\"url\":\"https:\\\/\\\/www.sqlnuggets.com\\\/disabling-an-index-drops-compression\\\/\",\"name\":\"Disabling An Index Drops Compression - SQL Nuggets\",\"isPartOf\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.sqlnuggets.com\\\/disabling-an-index-drops-compression\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.sqlnuggets.com\\\/disabling-an-index-drops-compression\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/sqlnuggets.com\\\/wp-content\\\/uploads\\\/2020\\\/08\\\/puzzle-1765847_1920.jpg\",\"datePublished\":\"2020-09-01T13:08:00+00:00\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.sqlnuggets.com\\\/disabling-an-index-drops-compression\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.sqlnuggets.com\\\/disabling-an-index-drops-compression\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.sqlnuggets.com\\\/disabling-an-index-drops-compression\\\/#primaryimage\",\"url\":\"https:\\\/\\\/sqlnuggets.com\\\/wp-content\\\/uploads\\\/2020\\\/08\\\/puzzle-1765847_1920.jpg\",\"contentUrl\":\"https:\\\/\\\/sqlnuggets.com\\\/wp-content\\\/uploads\\\/2020\\\/08\\\/puzzle-1765847_1920.jpg\",\"width\":1920,\"height\":600},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.sqlnuggets.com\\\/disabling-an-index-drops-compression\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"http:\\\/\\\/www.sqlnuggets.com\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Disabling An Index Drops Compression\"}]},{\"@type\":\"WebSite\",\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/#website\",\"url\":\"http:\\\/\\\/www.sqlnuggets.com\\\/\",\"name\":\"SQL Nuggets\",\"description\":\"Nuggets Of SQL Server Knowledge\",\"publisher\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"http:\\\/\\\/www.sqlnuggets.com\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/#organization\",\"name\":\"SQL Nuggets\",\"url\":\"http:\\\/\\\/www.sqlnuggets.com\\\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/#\\\/schema\\\/logo\\\/image\\\/\",\"url\":\"https:\\\/\\\/sqlnuggets.com\\\/wp-content\\\/uploads\\\/2023\\\/11\\\/website-logo.jpg\",\"contentUrl\":\"https:\\\/\\\/sqlnuggets.com\\\/wp-content\\\/uploads\\\/2023\\\/11\\\/website-logo.jpg\",\"width\":320,\"height\":54,\"caption\":\"SQL Nuggets\"},\"image\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/#\\\/schema\\\/logo\\\/image\\\/\"},\"sameAs\":[\"https:\\\/\\\/x.com\\\/sqlnugg\",\"https:\\\/\\\/www.linkedin.com\\\/in\\\/ericcobb\\\/\"]},{\"@type\":\"Person\",\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/#\\\/schema\\\/person\\\/210536254addbc1b9d2d95dc1448b38a\",\"name\":\"Eric Cobb\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/3ca1fc0c7054a668e048f09d412cd4ebf89833c4630fbbfccca78a0678a6bdc2?s=96&d=mm&r=pg\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/3ca1fc0c7054a668e048f09d412cd4ebf89833c4630fbbfccca78a0678a6bdc2?s=96&d=mm&r=pg\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/3ca1fc0c7054a668e048f09d412cd4ebf89833c4630fbbfccca78a0678a6bdc2?s=96&d=mm&r=pg\",\"caption\":\"Eric Cobb\"},\"sameAs\":[\"https:\\\/\\\/x.com\\\/cfgears\"],\"url\":\"https:\\\/\\\/sqlnuggets.com\\\/author\\\/eric-cobb\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Disabling An Index Drops Compression - SQL Nuggets","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.sqlnuggets.com\/disabling-an-index-drops-compression\/","og_locale":"en_US","og_type":"article","og_title":"Disabling An Index Drops Compression - SQL Nuggets","og_description":"This post is more of an FYI, and to remind myself in case I run into it again down the road. Here at work we have a very large, very ...","og_url":"https:\/\/www.sqlnuggets.com\/disabling-an-index-drops-compression\/","og_site_name":"SQL Nuggets","article_published_time":"2020-09-01T13:08:00+00:00","og_image":[{"width":1920,"height":600,"url":"https:\/\/www.sqlnuggets.com\/wp-content\/uploads\/2020\/08\/puzzle-1765847_1920.jpg","type":"image\/jpeg"}],"author":"Eric Cobb","twitter_card":"summary_large_image","twitter_creator":"@cfgears","twitter_site":"@sqlnugg","twitter_misc":{"Written by":"Eric Cobb","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.sqlnuggets.com\/disabling-an-index-drops-compression\/#article","isPartOf":{"@id":"https:\/\/www.sqlnuggets.com\/disabling-an-index-drops-compression\/"},"author":{"name":"Eric Cobb","@id":"http:\/\/www.sqlnuggets.com\/#\/schema\/person\/210536254addbc1b9d2d95dc1448b38a"},"headline":"Disabling An Index Drops Compression","datePublished":"2020-09-01T13:08:00+00:00","mainEntityOfPage":{"@id":"https:\/\/www.sqlnuggets.com\/disabling-an-index-drops-compression\/"},"wordCount":366,"commentCount":1,"publisher":{"@id":"http:\/\/www.sqlnuggets.com\/#organization"},"image":{"@id":"https:\/\/www.sqlnuggets.com\/disabling-an-index-drops-compression\/#primaryimage"},"thumbnailUrl":"https:\/\/sqlnuggets.com\/wp-content\/uploads\/2020\/08\/puzzle-1765847_1920.jpg","keywords":["Index Basics","SQL Metrics Pack"],"articleSection":["Configurations","DB Engine","Indexes"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.sqlnuggets.com\/disabling-an-index-drops-compression\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.sqlnuggets.com\/disabling-an-index-drops-compression\/","url":"https:\/\/www.sqlnuggets.com\/disabling-an-index-drops-compression\/","name":"Disabling An Index Drops Compression - SQL Nuggets","isPartOf":{"@id":"http:\/\/www.sqlnuggets.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.sqlnuggets.com\/disabling-an-index-drops-compression\/#primaryimage"},"image":{"@id":"https:\/\/www.sqlnuggets.com\/disabling-an-index-drops-compression\/#primaryimage"},"thumbnailUrl":"https:\/\/sqlnuggets.com\/wp-content\/uploads\/2020\/08\/puzzle-1765847_1920.jpg","datePublished":"2020-09-01T13:08:00+00:00","breadcrumb":{"@id":"https:\/\/www.sqlnuggets.com\/disabling-an-index-drops-compression\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlnuggets.com\/disabling-an-index-drops-compression\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlnuggets.com\/disabling-an-index-drops-compression\/#primaryimage","url":"https:\/\/sqlnuggets.com\/wp-content\/uploads\/2020\/08\/puzzle-1765847_1920.jpg","contentUrl":"https:\/\/sqlnuggets.com\/wp-content\/uploads\/2020\/08\/puzzle-1765847_1920.jpg","width":1920,"height":600},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlnuggets.com\/disabling-an-index-drops-compression\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"http:\/\/www.sqlnuggets.com\/"},{"@type":"ListItem","position":2,"name":"Disabling An Index Drops Compression"}]},{"@type":"WebSite","@id":"http:\/\/www.sqlnuggets.com\/#website","url":"http:\/\/www.sqlnuggets.com\/","name":"SQL Nuggets","description":"Nuggets Of SQL Server Knowledge","publisher":{"@id":"http:\/\/www.sqlnuggets.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"http:\/\/www.sqlnuggets.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"http:\/\/www.sqlnuggets.com\/#organization","name":"SQL Nuggets","url":"http:\/\/www.sqlnuggets.com\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"http:\/\/www.sqlnuggets.com\/#\/schema\/logo\/image\/","url":"https:\/\/sqlnuggets.com\/wp-content\/uploads\/2023\/11\/website-logo.jpg","contentUrl":"https:\/\/sqlnuggets.com\/wp-content\/uploads\/2023\/11\/website-logo.jpg","width":320,"height":54,"caption":"SQL Nuggets"},"image":{"@id":"http:\/\/www.sqlnuggets.com\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/x.com\/sqlnugg","https:\/\/www.linkedin.com\/in\/ericcobb\/"]},{"@type":"Person","@id":"http:\/\/www.sqlnuggets.com\/#\/schema\/person\/210536254addbc1b9d2d95dc1448b38a","name":"Eric Cobb","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/3ca1fc0c7054a668e048f09d412cd4ebf89833c4630fbbfccca78a0678a6bdc2?s=96&d=mm&r=pg","url":"https:\/\/secure.gravatar.com\/avatar\/3ca1fc0c7054a668e048f09d412cd4ebf89833c4630fbbfccca78a0678a6bdc2?s=96&d=mm&r=pg","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/3ca1fc0c7054a668e048f09d412cd4ebf89833c4630fbbfccca78a0678a6bdc2?s=96&d=mm&r=pg","caption":"Eric Cobb"},"sameAs":["https:\/\/x.com\/cfgears"],"url":"https:\/\/sqlnuggets.com\/author\/eric-cobb\/"}]}},"jetpack_publicize_connections":[],"jetpack_featured_media_url":"https:\/\/sqlnuggets.com\/wp-content\/uploads\/2020\/08\/puzzle-1765847_1920.jpg","jetpack_shortlink":"https:\/\/wp.me\/pdyDvE-nn","jetpack_sharing_enabled":true,"jetpack-related-posts":[{"id":1107,"url":"https:\/\/sqlnuggets.com\/sql-server-metrics-pack-update-september-2019\/","url_meta":{"origin":1449,"position":0},"title":"SQL Server Metrics Pack Update &#8211; September 2019","author":"Eric Cobb","date":"September 20, 2019","format":false,"excerpt":"This morning I updated the Index Metrics piece of SQL Server Metrics Pack. This update mainly introduced some new functionality, with a few minor bug fixes. The two big enhancements are the capability to generate and store CREATE INDEX scripts and support for Columnstore indexes. Creating index scripts seems pretty\u2026","rel":"","context":"In &quot;Indexes&quot;","block_context":{"text":"Indexes","link":"https:\/\/sqlnuggets.com\/category\/indexes\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/07\/binary-797263_1280-e1502824772680.jpg?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/07\/binary-797263_1280-e1502824772680.jpg?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/07\/binary-797263_1280-e1502824772680.jpg?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/07\/binary-797263_1280-e1502824772680.jpg?resize=700%2C400&ssl=1 2x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/07\/binary-797263_1280-e1502824772680.jpg?resize=1050%2C600&ssl=1 3x"},"classes":[]},{"id":813,"url":"https:\/\/sqlnuggets.com\/sql-scripts-how-to-find-missing-indexes\/","url_meta":{"origin":1449,"position":1},"title":"SQL Scripts: How To Find Missing Indexes","author":"Eric Cobb","date":"June 5, 2018","format":false,"excerpt":"One of the really cool things that SQL Server does is keep up with index statistics behind the scenes.\u00a0 It knows how an index has been used, how often it is used, or if it is even being used at all.\u00a0 But SQL Server doesn't just monitor the indexes that\u2026","rel":"","context":"In &quot;Indexes&quot;","block_context":{"text":"Indexes","link":"https:\/\/sqlnuggets.com\/category\/indexes\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2018\/06\/lost-cat-tree-sign-fun-159868.jpeg?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2018\/06\/lost-cat-tree-sign-fun-159868.jpeg?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2018\/06\/lost-cat-tree-sign-fun-159868.jpeg?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2018\/06\/lost-cat-tree-sign-fun-159868.jpeg?resize=700%2C400&ssl=1 2x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2018\/06\/lost-cat-tree-sign-fun-159868.jpeg?resize=1050%2C600&ssl=1 3x"},"classes":[]},{"id":209,"url":"https:\/\/sqlnuggets.com\/index-basics-nonclustered-indexes-part-1\/","url_meta":{"origin":1449,"position":2},"title":"Index Basics: Nonclustered Indexes, Part 1","author":"Eric Cobb","date":"February 18, 2016","format":false,"excerpt":"In SQL Server, an index is used to\u00a0speed the retrieval of data\u00a0from a\u00a0table. Indexes are built from one or more columns in the table, and SQL Server uses these Indexes to look up and retrieve data quickly and efficiently. \u00a0In order to effectively use Indexes in SQL Server, it's important\u2026","rel":"","context":"In &quot;Indexes&quot;","block_context":{"text":"Indexes","link":"https:\/\/sqlnuggets.com\/category\/indexes\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":790,"url":"https:\/\/sqlnuggets.com\/sql-scripts-how-to-find-filtered-indexes\/","url_meta":{"origin":1449,"position":3},"title":"SQL Scripts: How To Find Filtered Indexes","author":"Eric Cobb","date":"May 9, 2018","format":false,"excerpt":"Filtered indexes are one of those neat little SQL Server features that a lot of people don't seem to know about, or if they do they don't use them very much.\u00a0 While I agree that putting a filter on every index probably isn't a good idea, there are cases where\u2026","rel":"","context":"In &quot;Indexes&quot;","block_context":{"text":"Indexes","link":"https:\/\/sqlnuggets.com\/category\/indexes\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/06\/board-electronics-computer-data-processing-50711-e1498160567831.jpeg?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/06\/board-electronics-computer-data-processing-50711-e1498160567831.jpeg?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/06\/board-electronics-computer-data-processing-50711-e1498160567831.jpeg?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/06\/board-electronics-computer-data-processing-50711-e1498160567831.jpeg?resize=700%2C400&ssl=1 2x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/06\/board-electronics-computer-data-processing-50711-e1498160567831.jpeg?resize=1050%2C600&ssl=1 3x"},"classes":[]},{"id":554,"url":"https:\/\/sqlnuggets.com\/index-basics-hidden-costs-associated-indexes\/","url_meta":{"origin":1449,"position":4},"title":"Index Basics: Hidden Costs Associated With Indexes","author":"Eric Cobb","date":"September 26, 2017","format":false,"excerpt":"I have a confession. When I was a developer, I didn't understand indexes in SQL Server. I rarely added them to my tables, and when I did there was not really any logic behind my decision, other than it was usually just because I thought a column might be queried\u2026","rel":"","context":"In &quot;Indexes&quot;","block_context":{"text":"Indexes","link":"https:\/\/sqlnuggets.com\/category\/indexes\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/08\/mother-board-electronics-computer-board-39290-e1506443567415.jpeg?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/08\/mother-board-electronics-computer-board-39290-e1506443567415.jpeg?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/08\/mother-board-electronics-computer-board-39290-e1506443567415.jpeg?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/08\/mother-board-electronics-computer-board-39290-e1506443567415.jpeg?resize=700%2C400&ssl=1 2x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/08\/mother-board-electronics-computer-board-39290-e1506443567415.jpeg?resize=1050%2C600&ssl=1 3x"},"classes":[]},{"id":468,"url":"https:\/\/sqlnuggets.com\/sql-scripts-find-index-usage\/","url_meta":{"origin":1449,"position":5},"title":"SQL Scripts: How To Find Index Usage","author":"Eric Cobb","date":"July 11, 2017","format":false,"excerpt":"This post is part of an ongoing\u00a0SQL Scripts\u00a0series, in which I list useful queries that I use in my day to day database administration. If you are interested in code that will Persist And Aggregate Index Statistics\u00a0for your server, take a look at my\u00a0SQL Server Metrics Pack\u00a0project that is available\u2026","rel":"","context":"In &quot;Indexes&quot;","block_context":{"text":"Indexes","link":"https:\/\/sqlnuggets.com\/category\/indexes\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/06\/board-electronics-computer-data-processing-50711-e1498160567831.jpeg?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/06\/board-electronics-computer-data-processing-50711-e1498160567831.jpeg?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/06\/board-electronics-computer-data-processing-50711-e1498160567831.jpeg?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/06\/board-electronics-computer-data-processing-50711-e1498160567831.jpeg?resize=700%2C400&ssl=1 2x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/06\/board-electronics-computer-data-processing-50711-e1498160567831.jpeg?resize=1050%2C600&ssl=1 3x"},"classes":[]}],"_links":{"self":[{"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/posts\/1449","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/comments?post=1449"}],"version-history":[{"count":16,"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/posts\/1449\/revisions"}],"predecessor-version":[{"id":1466,"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/posts\/1449\/revisions\/1466"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/media\/1458"}],"wp:attachment":[{"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/media?parent=1449"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/categories?post=1449"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/tags?post=1449"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}