I have a few databases with identical schema, lets call:

  • DB_A
  • DB_B

I have a query that when run takes 11 seconds and returns 11K (11000) records on DB_A but when the exact same query is run on the identical DB_B it takes 40 seconds and returns 7K (7000) records.

The schema is identical, but when i run it on DB_B it runs on Degree of Parallelism 1, on DB_A it runs on 16.

I tried setting the cost of threshold to 0 to force parallelism but got the same result.

Why is that? How can the same query running on cloned databases behave so differently?

Any ideas are welcome. I'm using SQL 2017 Standard.

  • 1
    Can you show us , the execution plan ? the query? also you can try with OPTION(MAXDOP X), where x = 1 or 2 .4... and see the estimate cost of your query; + you don't have limitations/restrictions on your workload – Sabin Bio Nov 9 at 6:47
  • Yes I tried that, set it to maxdop 0 and 16, same result, what kind of limitations could I have? Where would o check? – Kludge Nov 9 at 7:12
  • docs.microsoft.com/en-us/sql/t-sql/statements/… , see the part with MAX_DOP = value – Sabin Bio Nov 9 at 10:23
  • 1
    Are statistics up to date on the tables? – Tony Hinkle Nov 9 at 11:58
  • 1
    You should include both the plans in your question – sepupic Nov 11 at 14:38

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.