Recently, we started adding compression to some of our larger tables in our data warehouse where columnstore indexes didn’t 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 seemed good. Then next time we were doing a code review/compare source control with our development instances, we discovered changes that we were not expecting. Indexes were no longer compressed in some cases.
So I pulled out my Sherlock Holmes hat (which I guarantee you doesn’t exist, and if it did it would have Mickey ears on it), and started sleuthing. To recreate the crime, I will create the following index in the WideWorldImporters database, just a simple index with page level creation turned on:
CREATE INDEX Description ON Sales.OrderLines(Description)
WITH (DATA_COMPRESSION = PAGE);
So now, the index is compressed, right? If you are wondering what the answer is to this question, the answer is yes, clearly, but there is a slight problem with that logic. Is the index really compressed? Check the metadata of the index:
SELECT *
FROM sys.indexes
WHERE indexes.object_id = OBJECT_ID('Sales.OrderLines')
AND indexes.name = 'Description';
The only mention of compression is “compression_delay”, which still might give you the feeling that compression is a part of the index metadata. What happened in our case was that in our processes to truncate and reload a table, we were disabling one or more indexes, loading the data, then rebuilding the index, using a process such as:
ALTER INDEX Description ON Sales.OrderLines DISABLE; --Load the data ALTER INDEX Description ON Sales.OrderLines REBUILD;
Now the index should be right back to where it started, right? If you query sys.indexes, everything will look the same, but remember how sys.indexes had nothing about compression? The compression is no longer in effect, as you can see in sys.partitions.
SELECT partitions.data_compression_desc
FROM sys.partitions
JOIN sys.indexes
ON indexes.object_id = partitions.object_id
AND indexes.index_id = partitions.index_id
WHERE indexes.object_id = OBJECT_ID('Sales.OrderLines')
AND indexes.name = 'Description';
This returns:
data_compression_desc --------------------------------- NONE
Partitions are where compression information is stored, because partitions (even if the table/index is not technically partitioned it has one partition), are compressed. Compression delay is part of a columnstore index, which is always compressed, and how long it waits to move the changes from the delta store to a columnstore segment is controlled by the delay. For more on that, see Niko Neugebaur’s blog here:):
The way you have to rebuild the index if you wish to keep it compressed after a disable, is to specify the data compression again:
ALTER INDEX Description ON Sales.OrderLines DISABLE;
--Load the data
ALTER INDEX Description ON Sales.OrderLines REBUILD
WITH (DATA_COMPRESSION = PAGE);
Checking again in sys.partitions you have:
data_compression_desc --------------------------------- PAGE
Problem 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.)
Just executing the following code to rebuild your index:
ALTER INDEX Description ON Sales.OrderLines REBUILD;
Effectively 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).
Note that the way you change the compression status of the index is to do a rebuild.
ALTER INDEX Description ON Sales.OrderLines REBUILD
WITH (DATA_COMPRESSION = PAGE);
The 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.
CREATE SCHEMA Utility;
GO
-- Use to hold the status of compression for an index with disabling it
CREATE TABLE Utility.IndexCompressionMetadata
(
SchemaName sysname NOT NULL,
TableName sysname NOT NULL,
IndexName sysname NOT NULL,
CompressionStyle sysname NOT NULL,
CONSTRAINT PKIndexCompressionMetadata PRIMARY KEY (SchemaName,TableName,IndexName)
)
GO
CREATE OR ALTER PROCEDURE Utility.Index$Disable
(
@SchemaName sysname,
@TableName sysname,
@IndexName sysname
)
AS
BEGIN
SET NOCOUNT ON;
IF EXISTS (
SELECT *
FROM Utility.IndexCompressionMetadata
WHERE IndexCompressionMetadata.SchemaName = @SchemaName
AND IndexCompressionMetadata.TableName = @TableName
AND IndexCompressionMetadata.IndexName = @IndexName)
THROW 50000,'This Index Already Has Compression Metadata. Fix Manually',1;
IF EXISTS (
SELECT *
FROM sys.indexes
INNER join sys.data_spaces
ON indexes.data_space_id =
data_spaces.data_space_id
WHERE data_spaces.type_desc ='PARTITION_SCHEME'
AND OBJECT_SCHEMA_NAME(indexes.object_id)
= @SchemaName
AND OBJECT_NAME(indexes.object_id) = @TableName
AND Indexes.name = @Indexname
)
THROW 50000,'This Utility may not be used with Partitioned Indexes',1;
BEGIN TRY;
BEGIN TRANSACTION;
INSERT INTO Utility.IndexCompressionMetadata
(SchemaName, TableName,
IndexName,CompressionStyle)
SELECT @SchemaName, @TableName,@IndexName,
partitions.data_compression_desc
FROM sys.partitions
JOIN sys.indexes
ON indexes.object_id = partitions.object_id
AND indexes.index_id = partitions.index_id
WHERE indexes.object_id =
OBJECT_ID(CONCAT(@SchemaName,'.',@TableName))
AND indexes.name = 'Description';
DECLARE @query varchar(MAX);
SET @query = CONCAT ('ALTER INDEX ',@IndexName,
' ON ',@SchemaName,'.',
@TableName,' DISABLE');
--SELECT @query
EXEC (@query);
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK;
THROW;
END CATCH;
END;
Now to disable the index using:
EXEC Utility.[Index$Disable] @SchemaName = 'Sales',
@TableName = 'OrderLines',
@IndexName = 'Description';
Now 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.
SELECT partitions.data_compression_desc
FROM sys.partitions
JOIN sys.indexes
ON indexes.object_id = partitions.object_id
AND indexes.index_id = partitions.index_id
WHERE indexes.object_id = OBJECT_ID('Sales.OrderLines')
AND indexes.name = 'Description';
You 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:
SELECT * FROM Utility.IndexCompressionMetadata;
This returns:
SchemaName TableName IndexName CompressionStyle ------------- ------------ ------------- ------------------- Sales OrderLines Description PAGE
Then this stored procedure will be used to reenable the index, with the last compression status
CREATE OR ALTER PROCEDURE Utility.Index$Enable
(
@SchemaName sysname,
@TableName sysname,
@IndexName sysname
)
AS
BEGIN
BEGIN TRY;
BEGIN TRANSACTION;
DECLARE @compressionStyle sysname =
(SELECT IndexCompressionMetadata.CompressionStyle
FROM Utility.IndexCompressionMetadata
WHERE IndexCompressionMetadata.SchemaName = @SchemaName
AND IndexCompressionMetadata.TableName = @TableName
AND IndexCompressionMetadata.IndexName = @IndexName)
IF @compressionStyle IS NULL
THROW 50000,'This procedure can only be used on indexes that are disabled, and registered with Utility.Index$Disable',1;
DECLARE @query varchar(MAX);
SET @query = CONCAT ('ALTER INDEX ',@IndexName,' ON ',@SchemaName,'.',@TableName,' REBUILD',
' WITH (DATA_COMPRESSION = ' + @compressionStyle + ')')
--SELECT @query
EXEC (@query);
DELETE FROM Utility.IndexCompressionMetadata
WHERE IndexCompressionMetadata.SchemaName = @SchemaName
AND IndexCompressionMetadata.TableName = @TableName
AND IndexCompressionMetadata.IndexName = @IndexName;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK;
THROW;
END CATCH;
END;
Execute the procedure:
EXEC Utility.Index$Enable @SchemaName = 'Sales',
@TableName = 'OrderLines',
@IndexName = 'Description';
Now you can check the compression on the partition, and what you will see is that it is not compressed with PAGE level compression.
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.
The post The Case of the Vanishing Index Compression appeared first on Simple Talk.
from Simple Talk https://ift.tt/2nd3rsI
via
No comments:
Post a Comment