6

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.

0
1

TMWFR

This was a documentation error. I blogged about the results of many tests comparing parallel queries against partitioned tables here:

That resulted in the documentation getting updated to reflect that there is no difference

NUTS

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.