· 8 years ago · Apr 23, 2018, 08:44 PM
1/*************************************************************************
2 This sample targets DataWarehouse workload. We recommend using clustered
3 columnstore index for large tables (million+) rows and traditional rowstore
4 for tables of size < 1 million rows. The examples here are based on star schema
5 consisting of FACT and DIMENSION tables.The examples hi-light the both the
6 storage savings and performance enhancements available in SQL Server 2016.
7
8 We have two fact tables FactResellerSalesXL_CCI and FactResellerSalesXL_PageCompressed.
9 They are identical except one table is based on clustered columnstore index
10 and other table is regular rowstore table with PAGE compression
11
12NOTE: As a pre-requisite download and restore the AdventureworksDW database from codeplex
13*************************************************************************
14*/
15Use AdventureworksDW
16go
17
18
19/*************************************************************************************
20STEP 1 - Space comparison between CCI and PAGE compressed table
21**********************************************************************************/
22-- How about space? Data space is much smaller. One key point to note is that PAGE compression can
23-- compress 2-4x. The difference is less as we have a Primary Key on the table that creates a Unique Non-clustered index
24-- The actual data compression savings will depend upon the data and the schema
25sp_spaceused 'FactResellerSalesXL_CCI'
26GO
27sp_spaceused 'FactResellerSalesXL_PageCompressed'
28GO
29
30-- Validate that both tables have the same amount of rows
31SELECT count(*) as CCITableCount
32FROM FactResellerSalesXL_CCI
33GO
34SELECT count(*) as PageCompressedTableCount
35FROM FactResellerSalesXL_PageCompressed
36GO
37
38-- You can query the following DMV to show that most data in CCI is compressed
39SELECT *
40FROM sys.dm_db_column_store_row_group_physical_stats
41WHERE object_id = object_id('FactResellerSalesXL_CCI')
42
43
44
45/*********************************************************************
46Step 2 -- Overview
47-- Page Compressed BTree table v/s Columnstore table performance differences
48-- Enable actual Query Plan in order to see Plan differences when Executing
49*/
50USE AdventureworksDW
51GO
52
53-- Ensure Database is in 130 compatibility mode
54ALTER DATABASE AdventureworksDW SET compatibility_level = 130
55GO
56DBCC DROPCLEANBUFFERS
57GO
58
59-- Execute a typical query that joins the Fact Table with dimension tables
60-- Note this query will run on the Page Compressed table, Note down the time
61SET STATISTICS IO ON
62SET STATISTICS TIME ON
63GO
64
65SELECT c.CalendarYear
66 ,b.SalesTerritoryRegion
67 ,FirstName + ' ' + LastName AS FullName
68 ,count(SalesOrderNumber) AS NumSales
69 ,sum(SalesAmount) AS TotalSalesAmt
70 ,Avg(SalesAmount) AS AvgSalesAmt
71 ,count(DISTINCT SalesOrderNumber) AS NumOrders
72 ,count(DISTINCT ResellerKey) AS NumResellers
73FROM FactResellerSalesXL_PageCompressed a
74INNER JOIN DimSalesTerritory b ON b.SalesTerritoryKey = a.SalesTerritoryKey
75INNER JOIN DimEmployee d ON d.Employeekey = a.EmployeeKey
76INNER JOIN DimDate c ON c.DateKey = a.OrderDateKey
77WHERE b.SalesTerritoryKey = 3
78 AND c.FullDateAlternateKey BETWEEN '1/1/2006' AND '1/1/2010'
79GROUP BY b.SalesTerritoryRegion,d.EmployeeKey,d.FirstName,d.LastName,c.CalendarYear
80GO
81
82SET STATISTICS IO OFF
83SET STATISTICS TIME OFF
84GO
85
86
87
88
89
90-- This is the same Prior query on a table with a Clustered Columnstore index CCI
91-- The comparison numbers are even more dramatic the larger the table is, this is a 11 million row table only.
92SET STATISTICS IO ON
93SET STATISTICS TIME ON
94GO
95
96SELECT b.SalesTerritoryRegion
97 ,FirstName + ' ' + LastName AS FullName
98 ,count(SalesOrderNumber) AS NumSales
99 ,sum(SalesAmount) AS TotalSalesAmt
100 ,Avg(SalesAmount) AS AvgSalesAmt
101 ,count(DISTINCT SalesOrderNumber) AS NumOrders
102 ,count(DISTINCT ResellerKey) AS NumResellers
103FROM FactResellerSalesXL_CCI a
104INNER JOIN DimSalesTerritory b ON b.SalesTerritoryKey = a.SalesTerritoryKey
105INNER JOIN DimEmployee d ON d.Employeekey = a.EmployeeKey
106INNER JOIN DimDate c ON c.DateKey = a.OrderDateKey
107WHERE b.SalesTerritoryKey = 3
108 AND c.FullDateAlternateKey BETWEEN '1/1/2006' AND '1/1/2010'
109GROUP BY b.SalesTerritoryRegion,d.EmployeeKey,d.FirstName,d.LastName,c.CalendarYear
110GO
111
112SET STATISTICS IO OFF
113SET STATISTICS TIME OFF
114GO
115
116
117
118
119/*************************************************************************************
120STEP 3 - Constraints
121*********************************************************************************
122*/
123-- SQL 2014 did not support foreign Key constraints..
124-- Validate there are constraints on this table
125SELECT * FROM sys.indexes WHERE object_id = object_id('FactResellerSalesXL_CCI')
126SELECT * FROM sys.foreign_keys WHERE parent_object_id = object_id('FactResellerSalesXL_CCI')
127
128-- Now make change to violate constraint and it should fail.
129BEGIN TRAN
130DECLARE @SalesOrderNumber NVARCHAR(25)
131
132SET @SalesOrderNumber = (
133 SELECT TOP 1 SalesOrderNumber
134 FROM FactResellerSalesXL_CCI
135 )
136
137UPDATE FactResellerSalesXL_CCI
138SET ProductKey = - 999
139WHERE SalesOrderNumber = @SalesOrderNumber
140ROLLBACK
141
142
143
144/*************************************************************************************
145STEP 4 - Segment Elimination Diagnostics
146
147-- How about just a normal where clause on the OrderDateKey
148-- Can we see Segments eliminated in the stats IO output
149-- Look at the messages Tab and you should get what Segments are being eliminated
150-- Table 'FactResellerSales_CCI'. Segment reads 1, segment skipped 11.
151-- Can I get the same from the Plan? You have to look at the XML Plan
152
153 <RunTimeInformation>
154 <RunTimeCountersPerThread Thread="0" ActualRows="77313" Batches="158" ActualEndOfScans="0"
155 ActualExecutions="1" ActualExecutionMode="Batch" SegmentReads="1" SegmentSkips="11" />
156 </RunTimeInformation>
157 *****************************************************************************************
158*/
159SET STATISTICS IO ON
160GO
161SELECT OrderDateKey FROM FactResellerSalesXL_CCI
162WHERE OrderDateKey > 20141201
163GO
164
165SET STATISTICS IO OFF
166GO
167
168
169/*************************************************************************************
170STEP 5 - Batch Mode improvements under compatibility mode 130
171**********************************************************************************/
172-- Enable Actual QUery Plans for this exercise
173-- Hover your mouse over the SORT operator, you will see that the SORT is in batch mode.
174-- See the "ACTUAL EXECUTION MODE".. A few operators that were in Row mode in SQL 2014 are now in BATCH mode.
175
176SET STATISTICS IO ON
177SET STATISTICS TIME ON
178GO
179
180SELECT ProductKey
181 ,count(ProductKey)
182FROM FactResellerSalesXL_CCI
183GROUP BY ProductKey
184ORDER BY ProductKey
185
186SET STATISTICS IO OFF
187SET STATISTICS TIME OFF
188GO
189
190
191/*****************************************************************************
192-- Batch Mode for serial ( Serial plan aka maxdop 1 in 2014 was in row mode)
193-- Again hover over the SCAN node in the query plan and you will see that it is
194-- in BATCH mode
195***************************************************************************/
196SET STATISTICS IO ON
197SET STATISTICS TIME ON
198GO
199
200SELECT ProductKey,sum(TotalProductCost)
201FROM FactResellerSalesXL_CCI
202GROUP BY ProductKey
203OPTION (MAXDOP 1)
204GO
205
206
207-- set Database in 120 compatibility mode
208ALTER DATABASE AdventureworksDW SET compatibility_level = 120
209GO
210-- you will see that this query now runs in rowmode (much slower)
211SELECT ProductKey,sum(TotalProductCost)
212FROM FactResellerSalesXL_CCI
213GROUP BY ProductKey
214OPTION (MAXDOP 1)
215GO
216
217-- Ensure Database is back in 130 compatibility mode
218ALTER DATABASE AdventureworksDW SET compatibility_level = 130
219GO
220SET STATISTICS IO OFF
221SET STATISTICS TIME OFF
222GO
223
224
225
226
227/*****************************************************************************
228 Batch mode for Windows aggregates
229******************************************************************************/
230-- you will see that this query runs with in BATCH mode
231SELECT ProductKey,OrderDateKey
232 ,LEAD(OrderQuantity, 1, 0) OVER (ORDER BY OrderDateKey) AS NextQuota
233FROM FactResellerSalesXL_CCI
234WHERE orderdatekey IN ( 20060301,20060601)
235
236-- set Database in 120 compatibility mode
237ALTER DATABASE AdventureworksDW SET compatibility_level = 120
238GO
239
240-- you will see that this query now runs in rowmode (much slower)
241SELECT ProductKey ,OrderDateKey
242 ,LEAD(OrderQuantity, 1, 0) OVER (ORDER BY OrderDateKey) AS NextQuota
243FROM FactResellerSalesXL_CCI
244WHERE orderdatekey IN ( 20060301,20060601)
245
246-- Ensure Database is back in 130 compatibility mode
247ALTER DATABASE AdventureworksDW SET compatibility_level = 130
248GO
249
250-- Multiple distinct batch mode ( was row mode in SQL 2014)
251-- In SQL 2014 the hash match would be ROW mode, and all upstream operators would be row mode.
252SET STATISTICS IO ON
253SET STATISTICS TIME ON
254GO
255
256SELECT ProductKey
257 ,COUNT(DISTINCT rs.EmployeeKey) AS NumEmployees
258 ,COUNT(DISTINCT rs.ResellerKey) AS NumResellers
259FROM dbo.FactResellerSalesXL_CCI AS rs
260WHERE rs.SalesTerritoryKey >= 8
261GROUP BY ProductKey
262ORDER BY ProductKey;
263GO
264
265SET STATISTICS IO OFF
266SET STATISTICS TIME OFF
267GO
268
269
270
271/*************************************************************************************
272STEP 6 - Aggregate Pushdown
273**********************************************************************************/
274
275SET STATISTICS IO ON
276SET STATISTICS TIME ON
277
278-- Scalar aggregate pushdown without groupby is implemented.
279-- Look at the Rows coming out of the SCAN, invariably in the past, 11 million rows would flow out and be filtered to 1 row
280SELECT sum(TotalProductCost)
281FROM FactResellerSalesXL_CCI
282
283-- Aggregate Pushdown for a groupby supported as well
284SELECT ProductKey
285 ,count(*)
286FROM FactResellerSalesXL_CCI
287GROUP BY ProductKey
288
289SET STATISTICS IO OFF
290SET STATISTICS TIME OFF
291GO
292
293
294
295
296
297/*************************************************************************************
298STEP 7 - String Predicate Pushdown
299**********************************************************************************/
300SET STATISTICS IO ON
301SET STATISTICS TIME ON
302GO
303DBCC DROPCLEANBUFFERS
304GO
305
306-- Look at the Query Plan, you will see the Predicate pushed to the SCAN
307-- In SQL 2014, this would be a SCAN followed by a Filter
308-- Look at the Properties of the SCAN, you will see a Predicate: [AdventureworksDW2014].[dbo].[FactResellerSalesXL_CCI].[CustomerPONumber]=[@1]
309SELECT CustomerPONumber
310FROM FactResellerSalesXL_CCI
311WHERE CustomerPONumber = N'PO1022545'
312
313-- set Database in 120 compatibility mode
314ALTER DATABASE AdventureworksDW SET compatibility_level = 120
315GO
316DBCC DROPCLEANBUFFERS
317GO
318
319-- you will see that this query now runs in rowmode (much slower)
320SELECT CustomerPONumber
321FROM FactResellerSalesXL_CCI
322WHERE CustomerPONumber = N'PO1022545'
323
324SET STATISTICS IO OFF
325SET STATISTICS TIME OFF
326GO
327
328-- Ensure Database is back in 130 compatibility mode
329ALTER DATABASE AdventureworksDW SET compatibility_level = 130
330GO
331
332
333@@@@@@@@@@@@@@@@@@@@@@@@@
334
335
336/**NOTE: As a pre-requisite download and restore the AdventureworksDW database from codeplex
337*************************************************************************
338*/
339Use AdventureworksDW
340go
341
342/*************************************************************************************
343STEP 8 - Non-Clustered Indexes ( btree) on top of Clustered columnstore index
344**********************************************************************************/
345-- What abount narrow lookups
346-- See missing index for this Plan
347-- Also notice no segments were eliminated . There is an optimization where you see a PROBE BITMAP filter pushed to the SCAN
348
349DBCC DROPCLEANBUFFERS
350GO
351SET STATISTICS TIME ON
352SET STATISTICS IO ON
353GO
354
355SELECT OrderDate
356 ,SalesAmount
357FROM FactResellerSalesXL_CCI a
358INNER JOIN DimReseller b ON a.ResellerKey = b.ResellerKey
359WHERE b.ResellerName = 'Wheels Inc.'
360GO
361
362SET STATISTICS TIME OFF
363SET STATISTICS IO OFF
364GO
365
366
367/* Now create the missing index */
368CREATE NONCLUSTERED INDEX IndFactResellerSalesXL_CCI_NCI ON [dbo].[FactResellerSalesXL_CCI] ([ResellerKey])
369INCLUDE ([SalesAmount],[OrderDate])
370
371-- After NCI creation what does the plan look like?
372-- Notice the Join has changed from Hash join to a nested loop join
373-- Also the inner branch of the nested loop is a seek
374-- And IO done is far less and time should be less too in particular CPU time
375DBCC DROPCLEANBUFFERS
376GO
377SET STATISTICS TIME ON
378SET STATISTICS IO ON
379GO
380
381SELECT OrderDate
382 ,SalesAmount
383FROM FactResellerSalesXL_CCI a
384INNER JOIN DimReseller b ON a.ResellerKey = b.ResellerKey
385WHERE b.ResellerName = 'Wheels Inc.'
386
387SET STATISTICS TIME OFF
388SET STATISTICS IO OFF
389GO
390
391-- drop the index just created
392--Drop index [FactResellerSales_CCI].IndFactResellerSales_CCI_NCI
393DROP INDEX IF EXISTS [FactResellerSales_CCI].IndFactResellerSales_CCI_NCI;
394
395/*************************************************************************************
396STEP 9 - Read Committed Snapshot Isolation
397**********************************************************************************/
398-- RCSI now supported now, which means you can have CCI on AlwaysOn secondaries
399-- Note this may be blocked if you have other connections opened
400ALTER DATABASE AdventureworksDW SET READ_COMMITTED_SNAPSHOT ON
401GO
402ALTER DATABASE AdventureworksDW SET ALLOW_SNAPSHOT_ISOLATION ON
403GO
404
405
406-- Now run a Snapshot isolation transaction
407SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
408GO
409BEGIN TRAN
410SELECT TOP 10 * FROM FactResellerSalesXL_CCI
411COMMIT
412GO
413SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
414GO
415
416-- Revert back if needed
417ALTER DATABASE AdventureworksDW SET READ_COMMITTED_SNAPSHOT OFF
418GO
419ALTER DATABASE AdventureworksDW SET ALLOW_SNAPSHOT_ISOLATION OFF
420GO
421
422
423
424/*************************************************************************************
425STEP 10 - Diagnostics and DMVs
426**********************************************************************************/
427-- New DMV
428-- See in particular the trim_reason_description which will give you why the rowgroup was closed ( if at all ) before the 1 million mark
429-- In this case all of them have 1 million except one, which has the trim_reason_description as REORG
430SELECT * FROM sys.dm_db_column_store_row_group_physical_stats
431
432
433-- Do we see NCI metadata?
434-- Should appear like any Non clustered index.
435SELECT * FROM sys.dm_db_index_operational_stats(NULL, NULL, NULL, NULL)
436WHERE database_id = db_id() AND object_id = object_id('FactResellerSalesXL_CCI')
437
438
439-- Metadata for Columnstore indexes
440-- This is a new DMV that gives you Usage data like index operational stats for columnstores
441-- You can see which rowgroups are scanned how often, locking on rowgroups, latching on row groups
442-- Also io_latch waits on rowgroups that indicate Io bottleneck
443SELECT * FROM sys.dm_db_column_store_row_group_operational_stats
444
445
446/*************************************************************************************
447STEP 11 - Parallel Insert select in compat mode 130
448**********************************************************************************/
449-- Demo parallel Insert from staging table
450DROP TABLE IF EXISTS [FactResellerSalesXL_CCI_temp];
451
452-- create ccitest_temp and create a columnstore index
453SELECT * INTO FactResellerSalesXL_CCI_temp FROM FactResellerSalesXL_CCI WHERE 1 = 2
454CREATE CLUSTERED columnstore INDEX cci_temp ON FactResellerSalesXL_CCI_temp
455
456
457-- Note this inserts rows in parallel
458-- You need the TABLOCK hint
459INSERT INTO FactResellerSalesXL_CCI_temp WITH (TABLOCK)
460SELECT TOP 400000 * FROM FactResellerSalesXL_CCI
461
462
463-- check the rowgroups created.
464-- you will notice each thread creates its own delta rowgroup. The compressed rowgproup will only be created
465-- if number of rows inserted by a thread is > 100k
466SELECT * FROM sys.dm_db_column_store_row_group_physical_stats
467WHERE object_id = object_id('FactResellerSalesXL_CCI_temp')
468
469
470
471/*************************************************************************************
472STEP 12 - Supportability - Index REORGANIZE and MERGE
473**********************************************************************************/
474-- Force the compression of all rowgroups
475ALTER INDEX cci_temp on FactResellerSalesXL_CCI_temp REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON)
476
477-- Note that all delta RGs were compressed
478-- Also note, that this DMV provides much richer set of information such as why a rowgroup was compressed
479SELECT * FROM sys.dm_db_column_store_row_group_physical_stats
480WHERE object_id = object_id('FactResellerSalesXL_CCI_temp')
481
482
483-- Now delete a subset of rows
484DELETE FactResellerSalesXL_CCI_temp WHERE productkey % 2 = 0
485
486-- run the following DMV to note the deleted rows
487SELECT * FROM sys.dm_db_column_store_row_group_physical_stats
488WHERE object_id = object_id('FactResellerSalesXL_CCI_temp')
489
490
491-- we will now run REORG command to defragement the columnstore index by removing deleted rows
492ALTER INDEX cci_temp on FactResellerSalesXL_CCI_temp REORGANIZE
493
494-- This will MERGE smaller rowgroups into larger rowgroups and also reclaim space from the deleted rows
495-- http://blogs.msdn.com/b/sqlcat/archive/2015/08/17/sql-2016-columnstore-row-group-merge-policy-and-index-maintenance-improvements.aspx
496ALTER INDEX cci_temp on FactResellerSalesXL_CCI_temp REORGANIZE
497
498-- Validate the new rowgroups that were merged
499-- Note only look at the COMPRESSED rowgroup.
500SELECT * FROM sys.dm_db_column_store_row_group_physical_stats
501WHERE object_id = object_id('FactResellerSalesXL_CCI_temp')
502
503
504
505
506
507@@@@@@@@@@@@@@@@@@
508
509/*
510***************************************************************************************
511Step 1:Memory-Optimized Tables with ColumnStore
512***************************************************************************************
513*/
514-- Set your current database, run these 2 lines
515Use AdventureWorks
516Go
517/*
518*************************************************************************************
519Step 2:CREATE Memory Optimized TABLE
520*************************************************************************************
521*/
522CREATE TABLE Account_memorytoptimized (
523 accountkey int NOT NULL ,
524 Accountdescription nvarchar (50),
525 accounttype nvarchar(50),
526 unitsold int,
527 CONSTRAINT[pk_account_hk] PRIMARY KEY NONCLUSTERED HASH(accountkey) with (BUCKET_COUNT=10000000))
528 WITH (MEMORY_OPTIMIZED = ON,DURABILITY=SCHEMA_AND_DATA );
529GO
530
531/*
532**************************************************************************************
533Step 3: Add clustered colummnstore index
534**************************************************************************************
535*/
536
537Alter table Account_memorytoptimized add index tk_account_cci clustered columnstore
538
539/*
540***************************************************************************************
541Step 4: Look at the index definition
542***************************************************************************************
543*/
544
545Select name, index_id, type_desc, compression_delay from sys.indexes where object_id=object_id('Account_memorytoptimized')
546
547Set nocount on
548Go
549
550/*
551******************************************************************************************
552Step 5: Insert 5 millions rows into the memory optimized table
553*******************************************************************************************
554*/
555-- insert into the main table load 5 million rows
556declare @outerloop int = 0
557declare @i int = 0
558while (@outerloop < 5000000)
559begin
560Select @i = 0
561begin tran
562while (@i < 2000)
563begin
564insert Account_memorytoptimized values (@i+@outerloop, 'test1', 'test2',@i)
565set @i += 1;
566end
567commit
568set @outerloop = @outerloop + @i
569set @i = 0
570end
571go
572
573/*
574**************************************************************************************************
575Step 6: Select query: verify that we have inserted these 5 millions row
576**************************************************************************************************
577*/
578Select count(*) from Account_memorytoptimized
579
580/*
581
582
583
584***************************************************************************************************
585Step 7:Simple query with columnstore index
586***************************************************************************************************
587*/
588
589
590SET STATISTICS TIME ON
591Select avg(convert(bigint, unitsold)) from Account_memorytoptimized
592
593/*
594***********************************************************************************************************
595Step 8: Same query but this time without the columnstore index
596***********************************************************************************************************
597*/
598
599Select avg(convert(bigint, unitsold)) from Account_memorytoptimized with (index= pk_account_hk)
600
601/*
602***************************************************************************************************
603
604@@@@@@@@@@@@@@@@@@@@@@@@@
605
606/*
607***************************************************************************************************
608Step 1: Non-Clustered Columnstore Indexes
609***************************************************************************************************
610*/
611-- Set your current database, run these 2 lines
612Use AdventureWorks
613Go
614
615/*
616**************************************************************************************************
617Step 2: Create Table
618**************************************************************************************************
619*/
620
621--Creating the table with required column for analytics
622Create table orders (
623AccountKey int not null,
624customername nvarchar (50),
625OrderNumber bigint,
626PurchasePrice decimal (9,2),
627OrderStatus smallint not NULL,
628OrderStatusDesc nvarchar (50))
629
630/*
631***************************************************************************************************
632Step 3: Create Clustered Indexes
633***************************************************************************************************
634*/
635
636--Create a clustered index on ‘OrderStatus’
637Create clustered index orders_ci on orders(OrderStatus)
638
639/*
640***************************************************************************************************
641Step 4: LoadData
642***************************************************************************************************
643*/
644
645-- insert into the main table load 1.5 million rows
646declare @outerloop int = 0
647declare @i int = 0
648declare @purchaseprice decimal (9,2)
649declare @customername nvarchar (50)
650declare @accountkey int
651declare @orderstatus smallint
652declare @orderstatusdesc nvarchar(50)
653declare @ordernumber bigint
654while (@outerloop < 1500000)
655begin
656Select @i = 0
657begin tran
658while (@i < 2000)
659begin
660set @ordernumber = @outerloop + @i
661set @purchaseprice = rand() * 1000.0
662set @accountkey = convert (int, RAND ()*1000)
663set @orderstatus = convert (smallint, RAND()*100)
664if (@orderstatus >= 5) set @orderstatus = 5
665
666set @orderstatusdesc =
667case @orderstatus
668WHEN 0 THEN 'Order Started'
669WHEN 1 THEN 'Order Closed'
670WHEN 2 THEN 'Order Paid'
671WHEN 3 THEN 'Order Fullfillment'
672WHEN 4 THEN 'Order Shipped'
673WHEN 5 THEN 'Order Received'
674END
675insert orders values (@accountkey,
676(convert(varchar(6), @accountkey) + 'firstname'),@ordernumber, @purchaseprice,@orderstatus, @orderstatusdesc)
677
678set @i += 1;
679end
680commit
681
682set @outerloop = @outerloop + 2000
683set @i = 0
684end
685go
686
687/*
688****************************************************************************************************
689Step 5: Create NCCI Index
690****************************************************************************************************
691*/
692
693--create NCCI Index
694CREATE NONCLUSTERED COLUMNSTORE INDEX orders_ncci ON orders (accountkey, customername, purchaseprice, orderstatus, orderstatusdesc)
695
696/*
697**********************************************************************************************************
698Step 6: Additional Rows
699***********************************************************************************************************
700*/
701
702
703--insert additional 200k rows
704declare @outerloop int = 3000000
705declare @i int = 0
706declare @purchaseprice decimal (9,2)
707declare @customername nvarchar (50)
708declare @accountkey int
709declare @orderstatus smallint
710declare @orderstatusdesc nvarchar(50)
711declare @ordernumber bigint
712while (@outerloop < 3200000)
713begin
714Select @i = 0
715begin tran
716while (@i < 2000)
717begin
718set @ordernumber = @outerloop + @i
719set @purchaseprice = rand() * 1000.0
720set @accountkey = convert (int, RAND ()*1000)
721set @orderstatus = convert (smallint, RAND()*5)
722set @orderstatusdesc =
723case @orderstatus
724WHEN 0 THEN 'Order Started'
725WHEN 1 THEN 'Order Closed'
726WHEN 2 THEN 'Order Paid'
727WHEN 3 THEN 'Order Fullfillment'
728WHEN 4 THEN 'Order Shipped'
729WHEN 5 THEN 'Order Received'
730END
731insert orders values (@accountkey,(convert(varchar(6), @accountkey) + 'firstname'),
732@ordernumber, @purchaseprice, @orderstatus, @orderstatusdesc)
733set @i += 1;
734end
735commit
736set @outerloop = @outerloop + 2000
737set @i = 0
738end
739go
740
741
742/*
743***********************************************************************************************************
744Step 7: Rowgroup
745***********************************************************************************************************
746*/
747-- look at the row group
748Select object_name(object_id), index_id, row_group_id, delta_store_hobt_id, state_desc, total_rows, trim_reason_desc, transition_to_compressed_state_desc
749from sys.dm_db_column_store_row_group_physical_stats
750where object_id=object_id('orders')
751
752/*
753*************************************************************************************************************
754Step 8: NCCI Index
755*************************************************************************************************************
756*/
757
758-- run the query using NCCI
759DBCC DROPCLEANBUFFERS
760SET STATISTICS IO ON;
761SET STATISTICS TIME ON;
762select max (PurchasePrice) from orders
763
764
765/*
766***************************************************************************************************************
767Step 9: without using NCCI
768***************************************************************************************************************
769*/
770-- run the query without using NCCI
771DBCC DROPCLEANBUFFERS
772Go
773SET STATISTICS IO ON;
774SET STATISTICS TIME ON;
775select max (PurchasePrice) from orders
776option (IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX)
777
778/*
779********************************************************************************************************
780Step 10: complex query using NCCI
781********************************************************************************************************
782*/
783
784-- a more complex query using NCCI
785SET STATISTICS IO ON;
786SET STATISTICS TIME ON;
787select top 5 customername, sum (PurchasePrice), Avg (PurchasePrice)
788from orders
789where purchaseprice > 90.0 and OrderStatus=5
790group by customername
791
792/*
793***********************************************************************************************************
794Step 11: more complex query without NCCI
795***********************************************************************************************************
796*/
797
798--a more complex query without NCCI
799SET STATISTICS IO ON;
800SET STATISTICS TIME ON;
801select top 5 customername, sum (PurchasePrice), Avg (PurchasePrice)
802from orders
803where purchaseprice > 90.0 and OrderStatus = 5
804group by customername
805option (IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX)
806
807/*
808***********************************************************************************************************
809step 12: Drop existing NCCI
810***********************************************************************************************************
811*/
812
813--Drop existing NCCI Index
814DROP Index orders_ncci on Orders
815
816/*
817***********************************************************************************************************
818step 13:columnstore index with a filtered condition
819***********************************************************************************************************
820*/
821
822--Create the columnstore index with a filtered condition
823CREATE NONCLUSTERED COLUMNSTORE INDEX orders_ncci ON orders (accountkey, customername, purchaseprice, orderstatus)
824where orderstatus = 5
825
826/*
827**************************************************************************************************************
828step 14:rows both from newly created NCCI index and from 'hot' rows
829**************************************************************************************************************
830*/
831
832SELECT top 5 customername, sum (PurchasePrice)
833FROM orders
834WHERE purchaseprice > 100.0
835Group By customername
836
837
838/*
839
840*************************************************************************************************************
841step 15: create a sample table and then creating nonclustered columnstore index
842*************************************************************************************************************
843*/
844
845-- Create a sample table
846create table t_compressdelay (
847 accountkey int not null,
848 accountdescription nvarchar (50) not null,
849 accounttype nvarchar(50),
850 accountCodeAlternatekey int)
851
852-- after it has been marked closed
853CREATE NONCLUSTERED COLUMNSTORE index t_colstor_cci on t_compressdelay (accountkey, accountdescription, accounttype)
854 WITH (DATA_COMPRESSION= COLUMNSTORE, COMPRESSION_DELAY = 100);