Partition Level Truncation in SQL Server 2016.

Prior to SQL 2016 to clear out a partition, you have to switch data to an auxiliary table (with identical schema) and truncate the auxiliary table. SQL Server 2016 allows you to perform truncation at partition level, eliminating the overhead involved in maintaining auxiliary table.

The syntax is very simple -

TRUNCATE TABLE dbo.FactProductInventoryPartitioned WITH (PARTITIONS (6,7,8) )

This truncates partitions 6,7 & 8 from the fact table.

This greatly simplifies sliding window implementation for DataWarehouse Loads.

Get More Out of Clustered Column Stored Indexes

To get best performance out of CCI the query should be running Batch mode and Segment Elimination is done in efficient manner.   

Batch Mode Execution

In batch mode execution data is processed in batches of 1000 rows (by CPU). In traditional row mode the data is processed one row at a time.

To verify that query is running in batch mode, look at the execution plan and check for “Actual Execution Mode”, it should indicate “Batch”.  To enable batch mode in SQL 2014, Parallelism should be enabled on the server.

Efficient Segment Elimination

To encourage segment elimination, presort the data and then build the clustered column store index. Presort should be done on column subject to heaviest join or aggregate operation.

To presort, create clustered index on the presort column. Once clustered index is created, create clustered column stored index with Drop_Existing=ON Option.

create clustered index cix_FactInternetSalesCCI on  [dbo].FactInternetSalesCCI(OrderDateKey)

create clustered columnstore index cix_FactInternetSalesCCI on FactInternetSalesCCI with (Drop_Existing = ON)