Objective

We had Column Store Tables created and now have tracking partitioned tables created, as well.

It is now time to see whether we benefit by partitioning the tables.

From our original set of tables, we only created tracking Partition tables for the Row Store Clustered Index and the Column Store Clustered Index.

For brevity sake, we skipped the Column Store Non-Clustered Index.

 

Query

Scenario

Our sample query will retrieve the top two sales within a date range.

SQL

Sample

Here is what the query likes when we target the [Fact].[SaleLargeRowStore] table.

BTW, that table is the unpartitioned Row Store table.

It has a Clustered Index on it’s primary key, Sale Key.

declare @dateBegin datetime
declare @dateEnd   datetime


set @dateBegin = '2013-04-15';
set @dateEnd = '2013-05-15';

; with cteYearCustomer --[Fact].[SaleLargeRowStore]
(
      [CustomerKey]
    , [profit]
)
as
(
    select  
              [CustomerKey] = tblFS.[Customer Key]
            , [profit] = max(tblFS.[profit])

    from  [Fact].[SaleLargeRowStore] tblFS

    where tblFS.[Invoice Date Key] between @dateBegin and @dateEnd

    group by
             tblFS.[Customer Key]

)

select top 2 *

from    cteYearCustomer cteYC

order by
        [profit] desc

Full

dbcc dropcleanbuffers with no_infomsgs
go

use [WideWorldImportersDW]
go

set nocount on
go

set statistics io on
go

declare @dateBegin datetime
declare @dateEnd   datetime


set @dateBegin = '2013-04-15';
set @dateEnd = '2013-05-15';

-- [Fact].[SaleLargeRowStore] 
declare @dateRSCBegin datetime
declare @dateRSCEnd datetime

declare @dateRSCPIndexIsChosenByEngineBegin datetime
declare @dateRSCPIndexIsChosenByEngineEnd datetime

declare @dateRSCPIndexIsClusteredBegin datetime
declare @dateRSCPIndexIsClusteredEnd datetime

declare @dateCSCIndexBegin datetime
declare @dateCSCIndexEnd datetime

declare @dateCSCPIndexBegin datetime
declare @dateCSCPIndexEnd datetime

declare @dateCSNCIndexBegin datetime
declare @dateCSNCIndexEnd datetime


declare @datediffSum bigint 

declare @NUMBER_OF_CHARS_IN_DIVIDER int

set @NUMBER_OF_CHARS_IN_DIVIDER = 180

set @dateRSCBegin = getdate();

print '@@version :- '
		+ cast(@@version as varchar(4000))

print replicate('=', @NUMBER_OF_CHARS_IN_DIVIDER);

print '[Fact].[SaleLargeRowStore]'
print '**************************'

-- [Fact].[SaleLargeRowStore]
; with cteYearCustomer --[Fact].[SaleLargeRowStore]
(
	  [CustomerKey]
	, [profit]
)
as
(
	select  
			  [CustomerKey] = tblFS.[Customer Key]
			, [profit] = max(tblFS.[profit])

	from  [Fact].[SaleLargeRowStore] tblFS

	where tblFS.[Invoice Date Key] between @dateBegin and @dateEnd

	group by
			 tblFS.[Customer Key]

)

select top 2 *

from    cteYearCustomer cteYC

order by
		[profit] desc

set @dateRSCEnd = getdate()

print replicate('=', @NUMBER_OF_CHARS_IN_DIVIDER);



/*
	 [Fact].[SaleLargeRowStoreClusteredPartition]
*/

print '[Fact].[SaleLargeRowStoreClusteredPartition] -- Clustered Index'
print '***************************************************************'

set @dateRSCPIndexIsClusteredBegin = getdate()

; with cteYearCustomer --[Fact].[SaleLargeRowStoreClusteredPartition] --Clustered Index
(
	  [CustomerKey]
	, [profit]
)
as
(
	select  
			  [CustomerKey] = tblFS.[Customer Key]
			, [profit] = max(tblFS.[profit])

	from [Fact].[SaleLargeRowStoreClusteredPartition]  tblFS with ( INDEX = 1)

	where tblFS.[Invoice Date Key] between @dateBegin and @dateEnd

	group by
			 tblFS.[Customer Key]

)

select top 2 *

from    cteYearCustomer cteYC

order by
		[profit] desc

set @dateRSCPIndexIsClusteredEnd = getdate()

print replicate('=', @NUMBER_OF_CHARS_IN_DIVIDER);

exec('dbcc dropcleanbuffers with no_infomsgs')

/*
	 [Fact].[SaleLargeRowStoreClusteredPartition]
*/

print '[Fact].[SaleLargeRowStoreClusteredPartition] -- Chosen by SQL Engine'
print '********************************************************************'

set @dateRSCPIndexIsChosenByEngineBegin = getdate()

; with cteYearCustomer --[Fact].[SaleLargeRowStoreClusteredPartition] -- Chosen by SQL Engine
(
	  [CustomerKey]
	, [profit]
)
as
(
	select  
			  [CustomerKey] = tblFS.[Customer Key]
			, [profit] = max(tblFS.[profit])

	from [Fact].[SaleLargeRowStoreClusteredPartition]  tblFS --with ( INDEX = 1)

	where tblFS.[Invoice Date Key] between @dateBegin and @dateEnd

	group by
			 tblFS.[Customer Key]

)

select top 2 *

from    cteYearCustomer cteYC

order by
		[profit] desc

set @dateRSCPIndexIsChosenByEngineEnd = getdate()

print replicate('=', @NUMBER_OF_CHARS_IN_DIVIDER);



/*
	[Fact].[SaleLargeColumnStoreClustered]
*/
print '[Fact].[SaleLargeColumnStoreClustered]'
print '**************************************'

set @dateCSCIndexBegin = getdate()

; with cteYearCustomer -- [Fact].[SaleLargeColumnStoreClustered]
(
	  [CustomerKey]
	, [profit]
)
as
(
	select  
			  [CustomerKey] = tblFS.[Customer Key]
			, [profit] = max(tblFS.[profit])

	from  [Fact].[SaleLargeColumnStoreClustered] tblFS

	where tblFS.[Invoice Date Key] between @dateBegin and @dateEnd

	group by
			--  tblY.[year]
			 tblFS.[Customer Key]

)

select top 2 *

from    cteYearCustomer cteYC

order by
		[profit] desc

set @dateCSCIndexEnd = getdate()

print replicate('=', @NUMBER_OF_CHARS_IN_DIVIDER);

/*
	[Fact].[SaleLargeColumnStoreClusteredPartition] 
*/

print '[Fact].[SaleLargeColumnStoreClusteredPartition]'
print '**********************************************'

set @dateCSCPIndexBegin = getdate()

; with cteYearCustomer -- [Fact].[SaleLargeColumnStoreClusteredPartition] 
(
	  [CustomerKey]
	, [profit]
)
as
(
	select  
			  [CustomerKey] = tblFS.[Customer Key]
			, [profit] = max(tblFS.[profit])

	from  [Fact].[SaleLargeColumnStoreClusteredPartition] tblFS

	where tblFS.[Invoice Date Key] between @dateBegin and @dateEnd

	group by
			 tblFS.[Customer Key]

)

select top 2 *

from    cteYearCustomer cteYC

order by
		[profit] desc

set @dateCSCPIndexEnd = getdate()

print replicate('=', @NUMBER_OF_CHARS_IN_DIVIDER)

/*
	[Fact].[SaleLargeColumnStoreNonClustered] 
*/

print '[Fact].[SaleLargeColumnStoreNonClustered]'
print '*****************************************'

set @dateCSNCIndexBegin = getdate()

; with cteYearCustomer -- [Fact].[SaleLargeColumnStoreNonClustered 
(
	  [CustomerKey]
	, [profit]
)
as
(
	select  
			  [CustomerKey] = tblFS.[Customer Key]
			, [profit] = max(tblFS.[profit])

	from  [Fact].[SaleLargeColumnStoreNonClustered] tblFS

	where tblFS.[Invoice Date Key] between @dateBegin and @dateEnd

	group by
			 tblFS.[Customer Key]

)

select top 2 *

from    cteYearCustomer cteYC

order by
		[profit] desc

set @dateCSNCIndexEnd = getdate()


print replicate('=', @NUMBER_OF_CHARS_IN_DIVIDER)

set @datediffSum =
			        isNull(datediff(millisecond, @dateRSCBegin, @dateRSCEnd), 0)
				  + isNull(datediff(millisecond, @dateRSCPIndexIsChosenByEngineBegin, @dateRSCPIndexIsChosenByEngineEnd), 0)
				  + isNull(datediff(millisecond, @dateRSCPIndexIsClusteredBegin, @dateRSCPIndexIsClusteredEnd), 0)
				  + isNull(datediff(millisecond, @dateCSCIndexBegin, @dateCSCIndexEnd), 0)
				  + isNull(datediff(millisecond, @dateCSCPIndexBegin, @dateCSCPIndexEnd), 0)
				  + isNull(datediff(millisecond, @dateCSNCIndexBegin, @dateCSNCIndexEnd), 0)


select 

		  [sourceID] = [sourceID]
		,  =  
		, [dateStart] = dateStart
		, [dateEnd] = dateEnd
		, [durationMilliseconds] =  [duration] 
		, [%] = cast([%] as decimal(6, 2))

from   
	(

		select 
				  [sourceID] = 1
				,  = '[Fact].[SaleLargeRowStore]'
				, [dateStart] = @dateRSCBegin
				, [dateEnd] = @dateRSCEnd
				, [duration] = datediff(millisecond, @dateRSCBegin, @dateRSCEnd)
				, [%] = (datediff(millisecond, @dateRSCBegin, @dateRSCEnd)) * 100.00 / @datediffSum


		union all

		select 
				  [sourceID] = 2.1
				,  = '[Fact].[SaleLargeRowStoreClusteredPartition] - Clustered Index'
				, [dateStart] = @dateRSCPIndexIsClusteredBegin
				, [dateEnd] = @dateRSCPIndexIsClusteredEnd
				, [duration] = datediff(millisecond, @dateRSCPIndexIsClusteredBegin, @dateRSCPIndexIsClusteredEnd)
				, [%] = (datediff(millisecond, @dateRSCPIndexIsClusteredBegin, @dateRSCPIndexIsClusteredEnd)) * 100.00 / @datediffSum

		union all

		select 
				  [sourceID] = 2.2
				,  = '[Fact].[SaleLargeRowStoreClusteredPartition] - Chosen by Engine'
				, [dateStart] = @dateRSCPIndexIsChosenByEngineBegin
				, [dateEnd] = @dateRSCPIndexIsChosenByEngineBegin
				, [duration] = datediff(millisecond, @dateRSCPIndexIsChosenByEngineBegin, @dateRSCPIndexIsChosenByEngineEnd)
				, [%] = (datediff(millisecond, @dateRSCPIndexIsChosenByEngineBegin, @dateRSCPIndexIsChosenByEngineEnd)) * 100.00 / @datediffSum

		union all

		select 
				  [sourceID] = 3
				,  = '[Fact].[SaleLargeColumnStoreClustered]'
				, [dateStart] = @dateCSCIndexBegin
				, [dateEnd] = @dateCSCIndexEnd
				, [duration] = datediff(millisecond, @dateCSCIndexBegin, @dateCSCIndexEnd)
				, [%] = (datediff(millisecond, @dateCSCIndexBegin, @dateCSCIndexEnd)) * 100.00 / @datediffSum


		union all

		select 
				  [sourceID] = 4
				,  = '[Fact].[SaleLargeColumnStoreClusteredPartition]'
				, [dateStart] = @dateCSCPIndexBegin
				, [dateEnd] = @dateCSCPIndexEnd
				, [duration] = datediff(millisecond, @dateCSCPIndexBegin, @dateCSCPIndexEnd)
				, [%] = (datediff(millisecond, @dateCSCPIndexBegin, @dateCSCPIndexEnd)) * 100.00 / @datediffSum

		union all

		select 
				  [sourceID] = 5
				,  = '[Fact].[SaleLargeColumnStoreNonClustered]'
				, [dateStart] = @dateCSNCIndexBegin
				, [dateEnd] = @dateCSNCIndexEnd
				, [duration] = datediff(millisecond, @dateCSNCIndexBegin, @dateCSNCIndexEnd)
				, [%] = (datediff(millisecond, @dateCSNCIndexBegin, @dateCSNCIndexEnd)) * 100.00 / @datediffSum
		

	) tblA

order by

	  [sourceID] asc

Query Plan

Query Plan – v2014

Row Store – Unpartition Table ( Fact.SaleLargeRowStore )

Image

Explanation

1. Missing Index
   • Improvement Projection
     • The Query Processor estimates that implementing the following index could improve the query cost by 93.1978%.
   • Index Statement
     • CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>] ON [Fact].[SaleLargeRowStore] ([Invoice Date Key]) INCLUDE ([Customer Key],[Profit])

Row Store – Partition Table ( Fact.SaleLargeRowStoreClusteredPartition ) – Index Chosen By Engine

Image

Explanation

1. Missing Index
   • Improvement Projection
     • The Query Processor estimates that implementing the following index could improve the query cost by 93.1978%.
   • Index Statement
     • CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>] ON [Fact].[SaleLargeRowStore] ([Invoice Date Key]) INCLUDE ([Customer Key],[Profit])

Row Store – Partition Table ( Fact.SaleLargeRowStoreClusteredPartition ) – Index Chosen By Engine

Image

Column Store – Unpartitioned Table ( Fact.SaleLargeColumnStoreClustered )

Image

1. Column Index Scan
   • With a Clustered Column Store Index, the Clustering Index is inclusive of all columns

Column Store – Clustered – Partitioned Table ( Fact.SaleLargeColumnStoreClusteredPartition )

Image

 

Explanation

1. Column Index Scan
   • With a Clustered Column Store Index, the Clustering Index is inclusive of all columns

 

Column Store – Non Clustered – Non-Partitioned Table –  ( Fact.SaleLargeColumnStoreNonClustered)

Image

Explanation

1. Table Scan
   • We do not have a targeted Index

 

Statistics I/O

Image

Statistics – v2014

Statistics I/O – v2016

Tabulated

 

Summary

Here a couple of findings:

1. Vertical partitioning
   • Helpful
     • Especially when queries are date driven
     • Please do some prep work and determine which date columns users often target
   • Not as Helpful
     • In a multi-column Index, not as helpful when the Date Column is not the first column referenced in the Index
2. SQL Server Version 2016/Enterprise Versus 2014/Developer Edition
   • Optimization
     • In v2016, Enterprise Edition
       • Workfile not referenced in Statistics IO
       • Segment Reads and Segments Skipped explicitly Stated
         • Table ‘SaleLargeColumnStoreClustered’. Segment reads 1, segment skipped 0.