October 21, 2003 at 12:02 pm
The configuration of my prod and test servers are the same, both 2 cpu's. The same query takes 10 times as long on prod as on test (!!!). It includes joining 1 table with 3900 rows and 4 more joins to another table (the same table with diffirent aliases) which contains only 400 rows. I discovered a wait state CXPackage on the slow server, but cannot find that much info where this occurs in SQL7. I get the idea that this may have something to do with parallalism, but why on one server and not on the other? Has anybody experienced something similar or any suggestions on what I must look at?
October 22, 2003 at 5:03 am
I've done some more testing and discovered that the problem is definitely with SMP. I added option maxdop 1 after the where clause and the query was much faster!! My question now is: Why does this not happen on the test server which also has 4 CPU's. And also, what is the best way to solve this - there are more than 1 config option to set, but what would be best? And again, why do I get 2 totally diffirent estimated query plans on the 2 servers?
October 22, 2003 at 7:37 am
quote:
I've done some more testing and discovered that the problem is definitely with SMP. I added option maxdop 1 after the where clause and the query was much faster!! My question now is: Why does this not happen on the test server which also has 4 CPU's. And also, what is the best way to solve this - there are more than 1 config option to set, but what would be best? And again, why do I get 2 totally diffirent estimated query plans on the 2 servers?
Is the "cost threshold for parallelism" the same for the two servers? How about the "affinity mask"?
Even if the "cost threshold" is the same, a slower server may choose a different (parallel) plan from that (serial) chosen by a faster server as the threshold is in seconds, and a slower server could estimate more seconds to execute the same query.
IMHO, parallel queries often perform worse than serial queries (as you have observed). We sometimes disable parallel queries entirely on production OLTP systems:
EXEC sp_configure 'show advanced option', '1'
EXEC sp_configure 'max degree of parallelism', '1'
RECONFIGURE
but usually just adjust the threshold up, because we want the system to use parallelism for processes like reindexing:
EXEC sp_configure 'show advanced option', '1'
EXEC sp_configure cost threshold for parallelism', '60'
RECONFIGURE
I believe there may be some issues with the newer Intel processors that have "Hyper-Threading Technology" (HTT), where SQL Server sees each processor as two, and even more inefficiency may result from parallel plans.
--Jonathan
--Jonathan
October 24, 2003 at 1:28 am
Thanks Jonathan. I also came to the conclusion that changing the threshold or maxdop are the only options. But now I have more questions on parallelism and thought it a good idea to start a new thread about this.
October 24, 2003 at 4:35 am
The below is probably unrelated but took a lot of investigation and caused me a fair bit of stress.
A few days ago I had a server that began running slow. I noted Exchange waits and CXPackets coming from one stored proc that was using parallalism. On investigation every perf counter was within bounds but the server was still using a fixed part of the paging file. Stored proc looked ok. A reboot solved nothing. After some time we scripted the proc, dropped it and recreated it. Problem solved and server back to speed. I can only assume that the plans or proc had somehow got corrupt at compile.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply