I am reviewing the differences between Standard and Enterprise editions of SQL Server and can't reproduce the differences advertised in this demo that explains the differences - the performance I observe when running a query across Standard and Enterprise are comparable, and queries run in parallel across partitioned tables in the execution plan.
I have verified that:
- The queries produce identical query plans
- The queries produce similar runtime with
set statistics time on
It seems that in SQL Server 2016 this difference is not reproducible. Is there something else this feature affects - maybe I am not testing for the right thing but the queries are comparable to that in the demo.
This is the script I have used to test:
-- MAXDOP is 10
-- structure of table
--Column type
--testData.PKcolumn1 bigint
--testData.PKcolumn2 int
--date datetime
--testData.PKcolumn3 bigint
--metric1 float
--metric2 float
--metric3 float
--metric4 float
--index_description index_keys
--clustered, unique, primary key located on ps_testData categoryId, transactionId, date
--pf_testData/ps_testData is a range right datetime partition scheme, fanout 368
GO
-- Actual partition count: 368
-- all 10 threads participate in the scan, 12MM to 13MM rows / thread
-- each partition likely read by a single thread
SELECT COUNT(*), MIN(date), MAX(date)
FROM testData
GO
-- Actual partitions with data count: 31
-- all 10 threads participate in the scan, 3MM to 4MM rows / thread
-- each partition likely read by a single thread
SELECT COUNT(*), MIN(date), MAX(date)
FROM testData
WHERE date >= '2020-01-01' AND date < '2020-02-01'
GO
-- Actual partitions with data count: 7
-- all 10 threads participate in the scan, with a range of 0.5MM to 1MM rows per thread
-- some partitions were read by multiple threads
SELECT COUNT(*), MIN(date), MAX(date)
FROM testData
WHERE date >= '2020-01-01' AND date < '2020-01-08'
GO
-- Actual partitions with data count: 1
-- all 10 threads participate in the scan, 60K to 100K rows / thread (all threads read from a single partition)
SELECT COUNT(*), MIN(date), MAX(date)
FROM testData
WHERE date >= '2020-01-01' AND date < '2020-01-02'
GO
n.b. This query has a group by clause - both show parallelism across partitions and within the same partition.
