0

Consider the queries below. The only difference is 1000 vs 2000 rows returned.

SELECT TOP 1000 lwa.Message INTO #foo
FROM dbo.LogWidgetsAPI lwa (NOLOCK)
ORDER BY lwa.TimeStamp

vs

SELECT TOP 2000 lwa.Message INTO #foo
FROM dbo.LogWidgetsAPI lwa (NOLOCK)
ORDER BY lwa.TimeStamp

However, 1000 rows are returned in under a second, while the query with 2000 rows takes significantly longer.

The query plan for the first query is reasonably simple:

enter image description here

but the 2nd query is using parallelization: enter image description here

What would using just 1k rows more force parallelization?

P.S. The table contains over 6 million records and TimeStamp column is indexed.

Improve this question
5
  • What is the cost threshold for parallelism setting, and what are the costs for both plans?
    – Jacob H
    Commented Aug 20, 2019 at 21:32
  • Because your cost threshold for parallelism is low?
    – S3S
    Commented Aug 20, 2019 at 21:32
  • @JacobH For query with 1000 rows the Estimated Subtree Cost is 6.63. For query with 2000 rows, it's 9.03. The Cost Threshold for Parallelism is 12 and Max Degree of Parallelism is 4.
    – AngryHacker
    Commented Aug 20, 2019 at 22:19
  • As a simple test, try adding OPTION (MAXDOP 1) to the end of your second query, and see what your timing looks like. When you get right down to it, there are many things that can slow down a query with more rows, from insufficient free memory to insufficient free CPUs to cover your parallelism.
    – Laughing Vergil
    Commented Aug 20, 2019 at 22:38
  • @LaughingVergil I did. That brings the performance back to the level of the 1st query.
    – AngryHacker
    Commented Aug 20, 2019 at 23:30

1 Answer 1

Reset to default
1

As per other comments, it sounds like the cost threshold for parallelism is too low, and is making queries go parallel even when the process to do so/ gather the streams makes your process take longer.

Try setting that to 50, or experimenting with different numbers.

Also, worth a read: https://www.brentozar.com/archive/2017/03/why-cost-threshold-for-parallelism-shouldnt-be-set-to-5/

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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