SQL Server Processor Setup
-
We are taking over an application from a third party vendor and they have configured the processor tab of SQL Server in the following manner:
SQL Server can use all available processors but only one processor can be used for parellel execution of queries.
I am trying to find some information that would determine why you would choose this configuration. Does anyone have any thoughts?
Thanks!!
-
This was removed by the editor as SPAM
-
Run sp_configure proc and check out for Max Degree of Parallelism. For further info on this , Books Online is a good resource.
-
Run sp_configure proc and check out for Max Degree of Parallelism. For further info on this , Books Online is a good resource.
-
The most likely reason for this would be them having trouble with parallelism in their queries. Instead of troubleshooting the issue, they just shut it off completely.
Derrick Leggett
Mean Old DBA
When life gives you a lemon, fire the DBA. -
If this is an On Line Transaction Processing System (OLTP), then setting the configuration parameter of "max degree of parallelism" to 1 to disable parallelism is not unreasonable.
For a SQL statement, alternative execution plans are generated by the SQL Optimizer to determine the least cost execution plan. For example, when joining two tables, there are three options for the execution plan: Nested Loop, Hash or Merge (See Books OnLine for more details). Similarly, alternative execution plans both with and without parallelism are generated and examined. But for OLTP type systems, the SQL is such that you know in advance that the parallelism would not be one of the least cost execution plans. By turning off parallelism, SQL Optimizer would not generate these alternative execution plans that involve parallelism, resulting in less time and CPU spent by the SQL Optimizer determining the optimal plan. This can result in more thruput.
If you look at the TPC OLTP Benchmarks performed by a hardware vendor and Microsoft, you will see that "max degree of parallelism" has been set to 1, disabling parallelism.
Reference:
http://www.tpc.org/results/FDR/TPCC/hp_superdome_win64_030828.pdf
SQL = Scarcely Qualifies as a Language
-
The TPC benchmarks are significantly different than managing a live production system. I wouldn't make a blanket statement about shutting off parallelism across the board. In many cases, there is a more optimal plan obtained by using parallel processing. You can shut if off and claim you get more transaction across the board. I think you would be hard to prove it though. Just because they did it for the benchmarks doesn't mean it's a rule to follow.
Derrick Leggett
Mean Old DBA
When life gives you a lemon, fire the DBA. -
if this is not a sql2000 sp4 system ...
There have been issues with parallelism with sp3(a), and it took a while until there was a hotfix to overcome the buggy things. The workaround suggested has been to shut it off (parallelism).
After upgrade to sp4, this would be one of the this to re-examine.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply