October 24, 2003 at 1:18 am
I have problems with a sqlserver 7 machine with 4 processors and queries running very slowly because of bad queryplans. On the test machine with also 4 cpu's and much less activity, the same query runs much faster because the queryplan is calculated for serial processing where on production, it uses parallelism.
My question(s):
How does sqlerver decide when to allocate more threads to a process?
How can I calculate a 'cost threshold for parallelism' so that i can increase this configuration value?
If I set 'max degree of parallelism' to one, does SQL still in a way bennefit from the 4 cps's when processing queries?
October 24, 2003 at 8:38 am
quote:
I have problems with a sqlserver 7 machine with 4 processors and queries running very slowly because of bad queryplans. On the test machine with also 4 cpu's and much less activity, the same query runs much faster because the queryplan is calculated for serial processing where on production, it uses parallelism.My question(s):
How does sqlerver decide when to allocate more threads to a process?
Covered fairly well in BOL. There is also this unhelpful article:
http://support.microsoft.com/default.aspx?id=329204
quote:
How can I calculate a 'cost threshold for parallelism' so that i can increase this configuration value?
It's like indexes: you have to test the change in the gestalt. A longer threshold is apt to speed some queries and slow others. The only way to tune this for specific queries is, as you know, with MAXDOP. With two servers having different loads and hardware, the MAXDOP values for best performance may be different as well.
quote:
If I set 'max degree of parallelism' to one, does SQL still in a way bennefit from the 4 cps's when processing queries?
If you have more than one concurrent user, it benefits you greatly. Each user process thread will be scheduled to the next free processor. There's an important distinction between parallel processing and parallel query plans. If you do set this to '1' on an OLTP server, don't forget to change it back to '0' when performing index maintenance.
--Jonathan
--Jonathan
October 29, 2003 at 7:19 am
Hi, We were using parallelism on all our SQL 7.0 servers until we were live for about a year. I think (and this is only my own opinion) that there are more cons than pros to using parallelism in 7.0. A lot of times the query plan would go off on two processors and ..well forgot to come back. Sometimes the query plan was actually slower on two processors than it was on one. I think many of the problems with it are due to the query optimizer on 7.0 is some what flaky. Do a search on the MS KB for parallelism, there are many issues with it. I would recommend turning it off on 7.0 servers. I think that would save you time. alternately you could identify the queries that cause you the most problems and explicitly run them with MAXDOP(1) to confine them to one processor. Like I said, it's only my opinion though.
...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply