6WFtBjKW

1-----------------------------------------------------------------------
2-- Create test data
3-----------------------------------------------------------------------
4
5SELECT @@VERSION
6--Microsoft SQL Server 2017 (RTM-CU3-GDR) (KB4052987) - 14.0.3015.40 (X64) 
7--	Dec 22 2017 16:13:22 
8--	Copyright (C) 2017 Microsoft Corporation
9--	Enterprise Evaluation Edition (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ) (Hypervisor)
10
11USE tempdb /* server: GRANGER */
12GO
13
14SET STATISTICS TIME, IO ON
15GO
16
17DROP TABLE IF EXISTS test_columnstore;
18GO
19
20-- Create a test_columnstore table with 100MM rows of pseudorandom data
21;WITH E1(N) AS (
22    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 
23    UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
24)
25, E2(N) AS (SELECT 1 FROM E1 a CROSS JOIN E1 b)
26, E4(N) AS (SELECT 1 FROM E2 a CROSS JOIN E2 b)
27, E8(N) AS (SELECT 1 FROM E4 a CROSS JOIN E4 b)
28SELECT (ABS(CAST(CAST(NEWID() AS VARBINARY) AS BIGINT)) % 5) + 1 AS col1
29                , ABS(CAST(CAST(NEWID() AS VARBINARY) AS BIGINT)) * RAND() AS col2
30				, ABS(CAST(CAST(NEWID() AS VARBINARY) AS BIGINT)) * RAND() AS col3
31				, ABS(CAST(CAST(NEWID() AS VARBINARY) AS BIGINT)) * RAND() AS col4
32INTO dbo.test_columnstore
33FROM E8
34OPTION (MAXDOP 8)
35-- CPU time = 117859 ms,  elapsed time = 13988 ms.
36GO
37CREATE CLUSTERED COLUMNSTORE INDEX cs_test_columnstore ON dbo.test_columnstore WITH (MAXDOP = 8)
38-- CPU time = 163296 ms,  elapsed time = 29429 ms.
39GO
40
41-----------------------------------------------------------------------
42-- Test query
43	-- This query must perform a hash aggregate on 100 million rows,
44	-- and then a batch mode sort on 99 million rows. It would likely
45	-- be more efficient to perform a single batch mode "Distinct Sort",
46	-- removing the need for the entire Hash Match operator.
47-----------------------------------------------------------------------
48
49-- Overall:			CPU time = 90982 ms,  elapsed time = 7944 ms.
50-- Hash Aggregate:	CPU time = 16299 ms,  elapsed time = 1434 ms.
51SELECT AVG(1.0*rn)
52FROM (
53	SELECT ROW_NUMBER() OVER (ORDER BY col1, col2) AS rn
54	FROM (
55		SELECT DISTINCT col1, col2
56		FROM test_columnstore
57	) y
58) x 
59GO
60
61-----------------------------------------------------------------------
62-- Test query: OPTION (ORDER GROUP)
63	-- If we remove the option of a hash aggregate to implement the DISTINCT
64	-- calculation, SQL Server now chooses a "Distinct Sort", but the query
65	-- suffers because the far less efficient row mode Sort operator is used.
66	-- In addition, SQL Server chains two Sort operators together (one batch
67	-- mode and one row mode), which should not be necessary. A single batch
68	-- mode "Distinct Sort" should be sufficient.
69-----------------------------------------------------------------------
70
71-- Overall:				CPU time = 381325 ms,	elapsed time = 45686 ms.
72-- Distinct Sort:		CPU time = 296047 ms,	elapsed time = 36051 ms.
73-- Repartition Streams:	CPU time = 14560 ms,	elapsed time = 11850 ms.
74SELECT AVG(1.0*rn)
75FROM (
76	SELECT ROW_NUMBER() OVER (ORDER BY col1, col2) AS rn
77	FROM (
78		SELECT DISTINCT col1, col2
79		FROM test_columnstore
80	) y
81) x 
82OPTION (ORDER GROUP)
83GO