Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Events          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

SQL Server 2012 Partitioned Index Compression Comparison


By:   |   Read Comments (2)   |   Related Tips: More > Compression

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.

SQL Server 2012 Partitioned Index Compression Comparison


Next, the index with row-level partioning was then dropped and a new non-clustered partitioned index was built
Next Steps


Last Update:






About the author
MSSQLTips author Dallas Snider Dr. Dallas Snider is an Assistant Professor in the Computer Science Department at the University of West Florida and has 18+ years of SQL experience.

View all my tips
Related Resources


 









Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Monday, September 02, 2013 - 5:33:51 AM - vivek kumar Back To Top

I am facing a problem regarding SQL server 2012.

I have a SQL reporting service running on 1 node but not on the other i.e. the other node is not showing the services.

So my question is how can i transfer the Sql report service from 1 node to the other???? 


Wednesday, August 28, 2013 - 2:26:55 AM - hakinchen Back To Top
"The linear relationship between the number of records and size of the index is beneficial when planning for storage."

How can this information be used to plan the storage?


And how does the performance of the storage when compression is used?


Thanks for any answer.

hakinchen


Learn more about SQL Server tools