3

I have a long running query that behaves differently between two different machines.

I have an 8 core machine running Microsoft SQL Server Developer Edition (64-bit) that does not use parallelism and a 4 core machine running Microsoft SQL Server Enterprise Edition (64-bit) that does. I need the former to run in parallelism. I've adjusted all the available settings in the advanced options with no luck.

The 8 core machine runs the query in 1 hour 21 minutes. The other machine runs it in 2 seconds. Please help!

Thanks, Tyler

4

You should find explanations for parallelism in SQL Server in these two questions:

To enable the use of parallelism in first server you have two options:

  • enable it at query level ( use option OPTION (MAXDOP 8) to enable the query to use all CPUs)

  • enable it at server level (use the system stored procedure sp_configure or Management Studio - advanced server properties.

Questions regarding the issue:

  • do you have statistics updated in both places?
  • did you check the explain plans for the query in both places to see what's the difference?
  • 2
    FYI: By default, MAXDOP and sp_configure are 0 meaning all processors. No changes are needed out of the box. It comes down to whether SQL Server judges the cost high enough and any factors that prevent it (eg stats, table variables) – gbn Jul 13 '11 at 17:43

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.