data_compression_desc\r\n---------------------------------\r\nPAGE<\/pre>\nProblem solved and fairly easy to handle if you are seeing this problem in a typical OLTP database. In these cases, if you are disabling and reenabling indexes on a regular basis, it should be only a few indexes, (and if you are commonly disabling them first, you may need to question your processes.)<\/p>\n
Just executing the following code to rebuild your index:<\/p>\n
ALTER INDEX Description ON Sales.OrderLines REBUILD;<\/pre>\nEffectively recreates the index, but it does not change the compression of the object’s partition, because a REBUILD doesn’t drop the index in the same way that a DISABLE does. If your physical structure is partitioned, this will be possibly be more obvious to you, in that you can compress only certain partitions. (If you are using the code that follows, you would need to extend it if your tables are partititoned, and it includes protections that stop it from working on partitioned tables).<\/p>\n
Note that the way you change the compression status of the index is to do a rebuild.<\/p>\n
ALTER INDEX Description ON Sales.OrderLines REBUILD \r\n WITH (DATA_COMPRESSION = PAGE);<\/pre>\nThe way we combatted this is in our data warehouse, non-partitioned case was by creating a table of indexes that are compressed and using that information our index rebuild stored procedure. This works fine if every programmer and DBA realizes this method and doesn’t use direct DDL to do this process.<\/p>\n
CREATE SCHEMA Utility;\r\nGO\r\n-- Use to hold the status of compression for an index with disabling it\r\nCREATE TABLE Utility.IndexCompressionMetadata\r\n(\r\n SchemaName sysname NOT NULL,\r\n TableName sysname NOT NULL,\r\n IndexName sysname NOT NULL,\r\n CompressionStyle sysname NOT NULL,\r\n CONSTRAINT PKIndexCompressionMetadata PRIMARY KEY (SchemaName,TableName,IndexName)\r\n)\r\nGO\r\n\r\nCREATE OR ALTER PROCEDURE Utility.Index$Disable\r\n(\r\n @SchemaName sysname,\r\n @TableName sysname,\r\n @IndexName sysname\r\n)\r\nAS\r\n BEGIN\r\n SET NOCOUNT ON;\r\n IF EXISTS (\r\n SELECT *\r\n FROM Utility.IndexCompressionMetadata\r\n WHERE IndexCompressionMetadata.SchemaName = @SchemaName\r\n AND IndexCompressionMetadata.TableName = @TableName\r\n AND IndexCompressionMetadata.IndexName = @IndexName) \r\n THROW 50000,'This Index Already Has Compression Metadata. Fix Manually',1;\r\n IF EXISTS (\r\n SELECT *\r\n FROM sys.indexes \r\n INNER join sys.data_spaces \r\n ON indexes.data_space_id = \r\n data_spaces.data_space_id\r\n WHERE data_spaces.type_desc ='PARTITION_SCHEME'\r\n AND OBJECT_SCHEMA_NAME(indexes.object_id) \r\n = @SchemaName\r\n AND OBJECT_NAME(indexes.object_id) = @TableName\r\n AND Indexes.name = @Indexname\r\n )\r\n THROW 50000,'This Utility may not be used with Partitioned Indexes',1;\r\n \r\n BEGIN TRY;\r\n BEGIN TRANSACTION;\r\n INSERT INTO Utility.IndexCompressionMetadata\r\n (SchemaName, TableName,\r\n IndexName,CompressionStyle)\r\n SELECT @SchemaName, @TableName,@IndexName, \r\n partitions.data_compression_desc\r\n FROM sys.partitions\r\n JOIN sys.indexes\r\n ON indexes.object_id = partitions.object_id\r\n AND indexes.index_id = partitions.index_id\r\n WHERE indexes.object_id = \r\n OBJECT_ID(CONCAT(@SchemaName,'.',@TableName))\r\n AND indexes.name = 'Description';\r\n\r\n DECLARE @query varchar(MAX);\r\n SET @query = CONCAT ('ALTER INDEX ',@IndexName,\r\n ' ON ',@SchemaName,'.',\r\n @TableName,' DISABLE');\r\n --SELECT @query\r\n EXEC (@query);\r\n COMMIT TRANSACTION;\r\n END TRY\r\n BEGIN CATCH\r\n IF @@TRANCOUNT > 0\r\n ROLLBACK;\r\n THROW;\r\n END CATCH;\r\nEND;<\/pre>\nNow to disable the index using:<\/p>\n
EXEC Utility.[Index$Disable] @SchemaName = 'Sales',\r\n @TableName = 'OrderLines',\r\n @IndexName = 'Description';<\/pre>\nNow you can check the compression on the partition, and what you will see is that it doesn’t even exist, because for indexes, the partition is deleted in the disable process.<\/p>\n
SELECT partitions.data_compression_desc\r\nFROM sys.partitions\r\n JOIN sys.indexes\r\n ON indexes.object_id = partitions.object_id\r\n AND indexes.index_id = partitions.index_id\r\nWHERE indexes.object_id = OBJECT_ID('Sales.OrderLines')\r\nAND indexes.name = 'Description';<\/pre>\nYou can also see in the metadata, that the index will have PAGE compression when the index is rebuilt using the tool we are about to create:<\/p>\n
SELECT *\r\nFROM Utility.IndexCompressionMetadata;<\/pre>\nThis returns:<\/p>\n
SchemaName TableName IndexName CompressionStyle\r\n------------- ------------ ------------- -------------------\r\nSales OrderLines Description PAGE<\/pre>\nThen this stored procedure will be used to reenable the index, with the last compression status<\/p>\n
CREATE OR ALTER PROCEDURE Utility.Index$Enable\r\n(\r\n @SchemaName sysname,\r\n @TableName sysname,\r\n @IndexName sysname\r\n)\r\nAS\r\n BEGIN\r\n BEGIN TRY;\r\n BEGIN TRANSACTION;\r\n DECLARE @compressionStyle sysname = \r\n (SELECT IndexCompressionMetadata.CompressionStyle\r\n FROM Utility.IndexCompressionMetadata\r\n WHERE IndexCompressionMetadata.SchemaName = @SchemaName\r\n AND IndexCompressionMetadata.TableName = @TableName\r\n AND IndexCompressionMetadata.IndexName = @IndexName)\r\n \r\n IF @compressionStyle IS NULL\r\n THROW 50000,'This procedure can only be used on indexes that are disabled, and registered with Utility.Index$Disable',1;\r\n\r\n DECLARE @query varchar(MAX);\r\n SET @query = CONCAT ('ALTER INDEX ',@IndexName,' ON ',@SchemaName,'.',@TableName,' REBUILD',\r\n ' WITH (DATA_COMPRESSION = ' + @compressionStyle + ')')\r\n --SELECT @query\r\n EXEC (@query);\r\n\r\n DELETE FROM Utility.IndexCompressionMetadata\r\n WHERE IndexCompressionMetadata.SchemaName = @SchemaName\r\n AND IndexCompressionMetadata.TableName = @TableName\r\n AND IndexCompressionMetadata.IndexName = @IndexName;\r\n\r\n COMMIT TRANSACTION;\r\n END TRY\r\n BEGIN CATCH\r\n IF @@TRANCOUNT > 0\r\n ROLLBACK;\r\n THROW;\r\n END CATCH;\r\nEND;<\/pre>\nExecute the procedure:<\/p>\n
EXEC Utility.Index$Enable @SchemaName = 'Sales',\r\n @TableName = 'OrderLines',\r\n @IndexName = 'Description';<\/pre>\nNow you can check the compression on the partition, and what you will see is that it is not compressed with PAGE level compression.<\/p>\n
Mystery solved. It is funny how much fun finding things like this can be once you get started. So when you find something seems wrong in your configuration, never panic, just start looking at the system catalog views and look for the clues. The fact that the index had no compression information was my first clue that this was going to be a problem with how compression is done with indexes.<\/p>\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
Recently, we started adding compression to some of our larger tables in our data warehouse where columnstore indexes didn\u2019t make sense. (For example, some very large dimension tables, and a few facts where they are not used for large aggregates in the common manner). The changes were checked into source control, we monitored performance, all……<\/p>\n","protected":false},"author":56085,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[],"coauthors":[19684],"class_list":["post-85292","post","type-post","status-publish","format-standard","hentry","category-blogs"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/85292","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/56085"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=85292"}],"version-history":[{"count":2,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/85292\/revisions"}],"predecessor-version":[{"id":85294,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/85292\/revisions\/85294"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=85292"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=85292"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=85292"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=85292"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}