Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite

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

share|improve this question
what version of SQL Server? Also, what is the O/S for each machine? – SQLRockstar Jul 13 '11 at 14:19
Both are running MSSQL 2008. The 8 core machine is Windows 7; the other is Windows Server 2003. Thanks! – Tyler Jul 13 '11 at 15:00

1 Answer

up vote 4 down vote accepted

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?
share|improve this answer
Updating the statistics worked (EXEC sp_updatestats)! Thank you very much for your help. – Tyler Jul 13 '11 at 15:10
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
@gbn: I know that, but I supposed that it was disabled (that's why I said about how to enable it..). – Marian Jul 13 '11 at 19:24
@gbn: LOL, I realize now that you didn't speak to me :-). Sorry. Sometimes beer does affect IT guys...though I didn't believe that is possible. – Marian Jul 13 '11 at 20:10

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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