Problem
What are the differences in space consumption for the SQL Server partitioned index compression options? What compression options are available in SQL Server? Check out this tip to learn more.
Solution
In this tip, we will walk through a simple demonstration of the differences in the size of a partitioned index when we use data compression at the row-level, page-level and no compression at all. To begin, in a database named Partitioning, four partitions were created for four separate files, in four separate file groups, on four separate storage devices by the T-SQL code presented here.
ALTER DATABASE Partitioning ADD FILEGROUP fg1_mssqltips;
ALTER DATABASE Partitioning ADD FILEGROUP fg2_mssqltips;
ALTER DATABASE Partitioning ADD FILEGROUP fg3_mssqltips;
ALTER DATABASE Partitioning ADD FILEGROUP fg4_mssqltips;
----===================================================================
ALTER DATABASE Partitioning
ADD FILE
(
NAME = file1_mssqltips,
FILENAME = 'W:\SQL_DATA\file1_mssqltips.ndf',
SIZE = 50MB, MAXSIZE = 3000MB, FILEGROWTH = 50MB
)
TO FILEGROUP fg1_mssqltips;
----===================================================================
ALTER DATABASE Partitioning
ADD FILE
(
NAME = file2_mssqltips,
FILENAME = 'X:\SQL_DATA\file2_mssqltips.ndf',
SIZE = 50MB, MAXSIZE = 3000MB, FILEGROWTH = 50MB
)
TO FILEGROUP fg2_mssqltips;
----===================================================================
ALTER DATABASE Partitioning
ADD FILE
(
NAME = file3_mssqltips,
FILENAME = 'Y:\SQL_DATA\file3_mssqltips.ndf',
SIZE = 50MB, MAXSIZE = 3000MB, FILEGROWTH = 50MB
)
TO FILEGROUP fg3_mssqltips;
----===================================================================
ALTER DATABASE Partitioning
ADD FILE
(
NAME = file4_mssqltips,
FILENAME = 'Z:\SQL_DATA\file4_mssqltips.ndf',
SIZE = 50MB, MAXSIZE = 3000MB, FILEGROWTH = 50MB
)
TO FILEGROUP fg4_mssqltips;
The next step was to create the partition function named fnPartitionMSSQLTips as shown in the T-SQL shown below.
CREATE PARTITION FUNCTION fnPartitionMSSQLTips (int) AS RANGE LEFT FOR VALUES (1000000, 2000000, 3000000) ;
A partition scheme named schemePartitionMSSQLTips was then created to assign the partition function to the file groups created above.
CREATE PARTITION SCHEME schemePartitionMSSQLTips AS PARTITION fnPartitionMSSQLTips TO (fg1_mssqltips, fg2_mssqltips, fg3_mssqltips, fg4_mssqltips);
Then, a partitioned table named tblMSSQLTipsPartition was created using the schemePartitionMSSQLTips partitioning scheme from the above T-SQL.
CREATE TABLE dbo.tblMSSQLTipsPartition ( colPK int identity (1,1) PRIMARY KEY, colNvarchar01 nvarchar(256), colNvarchar02 nvarchar(256), colNvarchar03 nvarchar(256), colNvarchar04 nvarchar(256), colDate1 date ) ON schemePartitionMSSQLTips (colPK);
Next, 4 rows of data were loaded into the table and this data was distributed evenly across all partitions. After loading the table, a non-clustered index was built using the schemePartitionMSSQLTips partitioning scheme and page-level data compression.
CREATE NONCLUSTERED INDEX IX_tblMSSQLTipsPartition_colPK ON dbo.tblMSSQLTipsPartition (colPK) WITH ( DATA_COMPRESSION = PAGE ON PARTITIONS (1,2,3,4) ) ON schemePartitionMSSQLTips (colPK)
The size of the index was then calculated using the T-SQL below and recorded.
SELECT idx.name AS NameOfIndex, SUM(stat.used_page_count) * 8 AS SizeOfIndexInKB
FROM sys.dm_db_partition_stats AS stat
INNER JOIN sys.indexes AS idx
ON stat.object_id = idx.object_id AND stat.index_id = idx.index_id
WHERE stat.object_id = object_id('dbo.tblMSSQLTipsPartition')
GROUP BY idx.name
ORDER BY idx.name
The index with page-level partioning was then dropped and a new non-clustered partitioned index was built using the schemePartitionMSSQLTips partitioning scheme and row-level data compression. The size of the index was then calculated and recorded.
IF EXISTS (SELECT name FROM sys.indexes
WHERE name = N'IX_tblMSSQLTipsPartition_colPK'
AND object_id = OBJECT_ID(N'dbo.tblMSSQLTipsPartition'))
DROP INDEX IX_tblMSSQLTipsPartition_colPK ON dbo.tblMSSQLTipsPartition;
CREATE NONCLUSTERED INDEX IX_tblMSSQLTipsPartition_colPK
ON dbo.tblMSSQLTipsPartition (colPK)
WITH
(
DATA_COMPRESSION = ROW ON PARTITIONS (1,2,3,4)
)
ON schemePartitionMSSQLTips (colPK)
Next, the index with row-level partioning was then dropped and a new non-clustered partitioned index was built using the schemePartitionMSSQLTips partitioning scheme and no data compression. The size of the index was then calculated and recorded.
IF EXISTS (SELECT name FROM sys.indexes
WHERE name = N'IX_tblMSSQLTipsPartition_colPK'
AND object_id = OBJECT_ID(N'dbo.tblMSSQLTipsPartition'))
DROP INDEX IX_tblMSSQLTipsPartition_colPK ON dbo.tblMSSQLTipsPartition;
CREATE NONCLUSTERED INDEX IX_tblMSSQLTipsPartition_colPK
ON dbo.tblMSSQLTipsPartition (colPK)
WITH
(
DATA_COMPRESSION = NONE ON PARTITIONS (1,2,3,4)
)
ON schemePartitionMSSQLTips (colPK)
The process of rebuilding the indexes with the differing compression options was repeated with one, two, three and four million records in the table distributed evenly across all four partitions. The results are shown in the table and graph below. The linear relationship between the number of records and size of the index is beneficial when planning for storage. It is also interesting to see in this example how there isn’t much difference in the size between the page-level compressed and row-level compressed indexes.
Next Steps
- Please note that the above results will vary with differing configurations and are only used as a general guideline.
- Please refer to the following tips for further assistance with SQL Server Indexing:
