Columnstore Indexes – part 94 (“Use Partitioning Wisely”)

Continuation from the previous 93 parts, the whole series can be found at https://www.nikoport.com/columnstore/.

This blog post will talk about some of the common problematic practices for the Columnstore Indexes and how to avoid or solve them.
It is not intended as a bashing of the Columnstore Indexes but as a guide of how to detect and resolve them. This is the first blog post in the series of the blog posts about the common problems and solutions.

The focus of this particular blog post is the Partitioning. An enterprise-feature only before the SQL Server 2016 with Service Pack 1, after which it became all-edition one, it has been one of the most beloved feature for any BI & Data Warehousing professionals working with big amounts of data, for managing and loading the data.
Some of the recent investments (specifically for the SQL Server 2016 the TRUNCATE statement supporting on the partition level and all the online rebuilds in the previous editions of the SQL Server) has enabled even wider range of the solutions – with my personal hopes lying on the partition level statistics one fine day. :)
A lot of people are expecting the partitioning to bring some performance improvements by eliminating the partitions, and surely it happens a number of times but there are more quirks about Columnstore Indexes partitioning than one might initially think.

Let’s us start wit the setup first and for the tests, I will use my own generated copy of the TPCH database (1GB version), that I have done with the help of the HammerDB.
As usually, the location of my backup file is C:\Install and I will use the following script to restore and reconfigure the database on SQL Server 2014 & 2016 instances (with an obvious difference that the compatibility level will be set to 120 & 130 accordingly)

/* 
 * This script restores backup of the TPC-H Database from the C:\Install
 */
USE [master]

if exists(select * from sys.databases where name = 'tpch')
begin
	alter database [tpch]
		set SINGLE_USER WITH ROLLBACK IMMEDIATE;
end

RESTORE DATABASE [tpch] 
	FROM  DISK = N'C:\Install\tpch_1gb_new.bak' WITH  FILE = 1, NOUNLOAD,  STATS = 1

alter database [tpch]
    set MULTI_USER;
GO

GO
ALTER DATABASE [tpch] SET COMPATIBILITY_LEVEL = 130
GO

USE [tpch]
GO
EXEC dbo.sp_changedbowner @loginame = N'sa', @map = false
GO

USE [master]
GO
ALTER DATABASE [tpch] MODIFY FILE ( NAME = N'tpch',  FILEGROWTH = 256152KB )
GO
ALTER DATABASE [tpch] MODIFY FILE ( NAME = N'tpch_log', SIZE = 1200152KB , FILEGROWTH = 256000KB )

Let’s convert 2 biggest tables of the TPCH (lineitem & orders) to the columnstore, by creating the copies of the respective tables with Clustered Columnstore Indexes:

USE [tpch]
GO

drop table if exists dbo.lineitem_cci;

-- Data Loding
SELECT [l_shipdate]
      ,[l_orderkey]
      ,[l_discount]
      ,[l_extendedprice]
      ,[l_suppkey]
      ,[l_quantity]
      ,[l_returnflag]
      ,[l_partkey]
      ,[l_linestatus]
      ,[l_tax]
      ,[l_commitdate]
      ,[l_receiptdate]
      ,[l_shipmode]
      ,[l_linenumber]
      ,[l_shipinstruct]
      ,[l_comment]
  into dbo.lineitem_cci
  FROM [dbo].[lineitem];
GO

-- Create Clustered Columnstore Index
create clustered columnstore index cci_lineitem_cci
	on dbo.lineitem_cci;

USE [tpch]
GO

DROP TABLE IF EXISTS dbo.orders_cci;

SELECT [o_orderdate]
      ,[o_orderkey]
      ,[o_custkey]
      ,[o_orderpriority]
      ,[o_shippriority]
      ,[o_clerk]
      ,[o_orderstatus]
      ,[o_totalprice]
      ,[o_comment]
	into dbo.orders_cci
  FROM [dbo].[orders];

create clustered columnstore index cci_orders_cci
	on dbo.orders_cci;
GO

Partitioning

Remember kids – Partitioning is NOT a PERFORMANCE IMPROVEMENT !
It is a data management improvement that might or might not deliver some improvements.
Partitioning your data wrongly with Columnstore Indexes will be a huge killer for your performance.
The issue here is when you are dealing with partitioning in Columnstore Indexes (especially with the Clustered Columnstore ones), you need to watch out for the size of the row groups and if they are too small (times less than 1048576 rows), your performance will suffer greatly.
For showing the result, I will create a copy the dbo.lineitem table where I shall partition it by day, making on average the size of each of the row groups to be equal to 2375 rows.
The following script will define the partition for each of the dates between 1st of January 1992 and 1st of January of 1999:

DECLARE @bigString NVARCHAR(MAX) = '',
		@partFunction NVARCHAR(MAX);	

;WITH cte AS (
	SELECT CAST( '1 Jan 1992' AS DATE ) testDate
	UNION ALL
	SELECT DATEADD( day, 1, testDate )
		FROM cte
		WHERE testDate < '31 Dec 1998'
)
SELECT @bigString += ',' + QUOTENAME( CONVERT ( VARCHAR, testDate, 106 ), '''' )
	FROM cte
	OPTION ( MAXRECURSION 5000 )


SELECT @partFunction = 'CREATE PARTITION FUNCTION fn_DailyPartition (DATE) AS RANGE RIGHT FOR VALUES ( ' + cast(STUFF( @bigString, 1, 1, '' )as nvarchar(max)) + ' )'

EXEC sp_Executesql @partFunction

CREATE PARTITION SCHEME ps_DailyPartScheme 
	AS PARTITION fn_DailyPartition 
		ALL TO ( [PRIMARY] );

Now let us create a partitioned copy of the dbo.lineitem table, for having less space on my disk drive I went with loading data into an existing Columnstore Index and compressing it with ALTER INDEX REORGANIZE (COMPRESS_ALL_ROW_GROUPS = ON):

-- Data Loding
SELECT [l_shipdate]
      ,[l_orderkey]
      ,[l_discount]
      ,[l_extendedprice]
      ,[l_suppkey]
      ,[l_quantity]
      ,[l_returnflag]
      ,[l_partkey]
      ,[l_linestatus]
      ,[l_tax]
      ,[l_commitdate]
      ,[l_receiptdate]
      ,[l_shipmode]
      ,[l_linenumber]
      ,[l_shipinstruct]
      ,[l_comment]
  into dbo.lineitem_cci_part
  FROM [dbo].[lineitem]
  where 1 = 0;
GO


-- Create Clustered  Index
create clustered index cci_lineitem_cci_part
	on dbo.lineitem_cci_part ( [l_shipdate] )
		WITH (DATA_COMPRESSION = PAGE)
			ON ps_DailyPartScheme( [l_shipdate] ); 

-- Create Clustered Columnstore Index
create clustered columnstore index cci_lineitem_cci_part
	on dbo.lineitem_cci_part
		WITH (DROP_EXISTING = ON)
			ON ps_DailyPartScheme( [l_shipdate] );

insert into dbo.lineitem_cci_part (l_shipdate, l_orderkey, l_discount, l_extendedprice, l_suppkey, l_quantity, l_returnflag, l_partkey, l_linestatus, l_tax, l_commitdate, l_receiptdate, l_shipmode, l_linenumber, l_shipinstruct, l_comment)
SELECT [l_shipdate]
      ,[l_orderkey]
      ,[l_discount]
      ,[l_extendedprice]
      ,[l_suppkey]
      ,[l_quantity]
      ,[l_returnflag]
      ,[l_partkey]
      ,[l_linestatus]
      ,[l_tax]
      ,[l_commitdate]
      ,[l_receiptdate]
      ,[l_shipmode]
      ,[l_linenumber]
      ,[l_shipinstruct]
      ,[l_comment]
  FROM [dbo].[lineitem]

alter index cci_lineitem_cci_part
	on dbo.lineitem_cci_part
	reorganize with (COMPRESS_ALL_ROW_GROUPS = ON);

Now I am ready to test a couple of simple queries, let's start by simply showing the total discounts from our sales:

set statistics time, io on

select SUM(l_discount)
	from dbo.lineitem_cci

select SUM(l_discount)
	from dbo.lineitem_cci_part

Needless to say that looking at the execution plans you notice that the actual execution plan shows 10 times difference between them, even though both tables contain the very same data!
The query cost for the partitioned table is staggering - it is around 10 times bigger (~8.8) vs (~0.81) for the first query.
The execution times reflect in part this situation: 12 ms vs 91 ms. Non-partitioned table performs almost 9 times faster overall and the spent CPU time is reflecting it: 15 ms vs 94 ms. Remember, that both tables are Columnstore Indexes based ! Partitioning your table in a wrong way will contain a huge penalty that might not be directly detectable through the execution plan of the complex queries. Well, you might want to use the CISL, just saying :)

For better understanding of the impact here is the script for the same table but partitioned per month:

DECLARE @bigString NVARCHAR(MAX) = '',
		@partFunction NVARCHAR(MAX);	


;WITH cte AS (
	SELECT CAST( '1 Jan 1992' AS DATE ) testDate
	UNION ALL
	SELECT DATEADD( month, 1, testDate )
		FROM cte
		WHERE testDate < '31 Dec 1998'
)
SELECT @bigString += ',' + QUOTENAME( CONVERT ( VARCHAR, testDate, 106 ), '''' )
	FROM cte
	OPTION ( MAXRECURSION 5000 )


SELECT @partFunction = 'CREATE PARTITION FUNCTION fn_MonthlyPartition (DATE) AS RANGE RIGHT FOR VALUES ( ' + cast(STUFF( @bigString, 1, 1, '' )as nvarchar(max)) + ' )'
print @partFunction;
EXEC sp_Executesql @partFunction


CREATE PARTITION SCHEME ps_MonthlyPartScheme 
	AS PARTITION fn_MonthlyPartition 
		ALL TO ( [PRIMARY] );

drop table if exists dbo.lineitem_cci_part_month

-- Data Loding
SELECT [l_shipdate]
      ,[l_orderkey]
      ,[l_discount]
      ,[l_extendedprice]
      ,[l_suppkey]
      ,[l_quantity]
      ,[l_returnflag]
      ,[l_partkey]
      ,[l_linestatus]
      ,[l_tax]
      ,[l_commitdate]
      ,[l_receiptdate]
      ,[l_shipmode]
      ,[l_linenumber]
      ,[l_shipinstruct]
      ,[l_comment]
  into dbo.lineitem_cci_part_month
  FROM [dbo].[lineitem]
  where 1 = 0;
GO


-- Create Clustered  Index
create clustered index cci_lineitem_cci_part_month
	on dbo.lineitem_cci_part_month ( [l_shipdate] )
		WITH (DATA_COMPRESSION = PAGE)
			ON ps_MonthlyPartScheme( [l_shipdate] ); 

-- Create Clustered Columnstore Index
create clustered columnstore index cci_lineitem_cci_part_month
	on dbo.lineitem_cci_part_month
		WITH (DROP_EXISTING = ON)
			ON ps_MonthlyPartScheme( [l_shipdate] );

insert into dbo.lineitem_cci_part_month (l_shipdate, l_orderkey, l_discount, l_extendedprice, l_suppkey, l_quantity, l_returnflag, l_partkey, l_linestatus, l_tax, l_commitdate, l_receiptdate, l_shipmode, l_linenumber, l_shipinstruct, l_comment)
SELECT [l_shipdate]
      ,[l_orderkey]
      ,[l_discount]
      ,[l_extendedprice]
      ,[l_suppkey]
      ,[l_quantity]
      ,[l_returnflag]
      ,[l_partkey]
      ,[l_linestatus]
      ,[l_tax]
      ,[l_commitdate]
      ,[l_receiptdate]
      ,[l_shipmode]
      ,[l_linenumber]
      ,[l_shipinstruct]
      ,[l_comment]
  FROM [dbo].[lineitem_cci]

alter index cci_lineitem_cci_part_month
	on dbo.lineitem_cci_part_month
	reorganize with (COMPRESS_ALL_ROW_GROUPS = ON);

Now, let's compare all 3 results of the Total Sales Discounts:

set statistics time, io on

select SUM(l_discount)
	from dbo.lineitem_cci

select SUM(l_discount)
	from dbo.lineitem_cci_part

select SUM(l_discount)
	from dbo.lineitem_cci_part_month

The query execution time of the monthly partitioned table is extremely close to the non-partitioned one, as one would expect given the number of rows that every row group contains. It took just 15 ms on the average to run the query, when comparing to a non-partitioned table that required 11 ms on the average in my tests in my VM. The CPU time for each of the queries is appears to be equal or indistinguishably similar, being around 15-16 ms in both cases.

The difference for the execution times lies within the number of partitions, but mostly based on the average size of the Row Group. As you should know, the perfect size for a Row Group would be a 1048576 rows, and an average size for a Row Group for a daily partitioned table is just 2375 rows, while for the monthly-partitioned table this number increases around 35 times to 72270 rows.

From a different angle you will notice that the actual sizes of the table containing the very same amount of data occupies quite a different amount of space - it takes more than double amount of space ~463MB for a daily partitioned table (with 2828 partitions) when comparing with the original table 227MB. The monthly partitioned table containing just 83 partitions occupies around 257MB which represents less than 15% increase in the size when compared with the original table. A bi-monthly partitioned in this case would be overall more beneficial, though not a critical improvement.

Let us consider the another query, surely just because one single query runs slowly you should not be judging the whole small size partitioning story when using Columnstore Indexes.
I have selected the query 21 from the TPCH performance test and here is the comparison between the non-partitioned and the daily-partitioned tables (notice that they are being used 3 times in the same query):

set statistics time on

set nocount on

select
	s_name,
	count(*) as numwait
from
	supplier,
	lineitem_cci l1,
	orders,
	nation
where
	s_suppkey = l1.l_suppkey
	and o_orderkey = l1.l_orderkey
	and o_orderstatus = 'F'
	and l1.l_receiptdate > l1.l_commitdate
	and exists (
		select
			*
		from
			lineitem_cci l2
		where
			l2.l_orderkey = l1.l_orderkey
			and l2.l_suppkey <> l1.l_suppkey
	)
	and not exists (
		select
			*
		from
			lineitem_cci l3
		where
			l3.l_orderkey = l1.l_orderkey
			and l3.l_suppkey <> l1.l_suppkey
			and l3.l_receiptdate > l3.l_commitdate
	)
	and s_nationkey = n_nationkey
	and n_name = 'GERMANY'
group by
	s_name
order by
	numwait desc,
	s_name;


select
	s_name,
	count(*) as numwait
from
	supplier,
	lineitem_cci_part l1,
	orders,
	nation
where
	s_suppkey = l1.l_suppkey
	and o_orderkey = l1.l_orderkey
	and o_orderstatus = 'F'
	and l1.l_receiptdate > l1.l_commitdate
	and exists (
		select
			*
		from
			lineitem_cci_part l2
		where
			l2.l_orderkey = l1.l_orderkey
			and l2.l_suppkey <> l1.l_suppkey
	)
	and not exists (
		select
			*
		from
			lineitem_cci_part l3
		where
			l3.l_orderkey = l1.l_orderkey
			and l3.l_suppkey <> l1.l_suppkey
			and l3.l_receiptdate > l3.l_commitdate
	)
	and s_nationkey = n_nationkey
	and n_name = 'GERMANY'
group by
	s_name
order by
	numwait desc,
	s_name;

After running both queries a couple of times I have caught the following average execution times:
--CPU time = 861 ms, elapsed time = 293 ms.
--CPU time = 1906 ms, elapsed time = 790 ms.

We are talking here about more than double of the actual elapsed execution time difference. This is taking in count that I am running a pretty fast SSD for this VM, and if you are running a traditional storage or in production under heavy load, the execution time should differ in more times.
Interestingly if I am running the same query for the monthly partitioned table, the actual execution time becomes even faster than the non-partitioned table, which is not that surprising given that we are not extracting the content of the whole table, but rather smaller chunks of around 340.000 rows and the bigger Row Groups here are of a less advantage.

Just because you can partitioned your table with thousands of partitions (up to 15.000 to be more precise), it does not mean that you should use the highest available number. Consider merging partitions (and watch out for some of the Columnstore indexes quirks there) if the average row group size is too small, but mostly think very precise about defining it when creating partitioning for the Columnstore Indexes. Research & analyse your data and monitor its developments.

to be continued with Columnstore Indexes – part 95 ("Basic Query Patterns")