5

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.

3
  • 1
    Your sample queries are probably not a good test case for this, as they aggregate over the entire table, reducing the usefulness of partition parallelism. – mustaccio Sep 1 '20 at 17:09
  • @Joe I have added query plans for a case similar to "Actual partitions with data count: 7" as requested. – ruskind Sep 16 '20 at 16:48
  • @mustaccio i have added query plans for a case where I group by the partitioning column – ruskind Sep 16 '20 at 16:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.