Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dewacorp.alliances
452 Posts |
 Posted - 2007-03-15 : 06:19:50
|
| Hi thereWe have one production database cluster which the configuration is slightly different among other cluster that we have. What I found that the original consultant set up this server to have a Parallelism off (under SQL Configuration > Processor) by do the following:Specify the no of processor to use for parallel execution of queries: 1 processor.Min query plan considering queries for parallel (cost estimate): 5By default are:Specify the no of processor to use for parallel execution of queries: Use all available processors.Min query plan considering queries for parallel (cost estimate): 5So ... what do you think about setting up to 1 processor which is in fact that we have 2 processors Intel Xeon 3.2GHz. Also ... what I don't really get it that it saying that eventhough it set as 1 as processor is actually using the all processor?!?!The reason bring this is up due to we have a box that a Development box that is similar SQL configuration (but only a single box instead of cluster plus single box running parallesim while the production box is not) and when it's running the same query on the same database the query tooks so long on the production box (the one that parallelism is off). BTW ... I've already do INDEXDEFRAG, UPDATE STATISTIC WITH 50% SAMPLE etc etc. The result is quite different 336 seconds and 22 minutes.Any input regarding this ... I am really appreciated.Thanks |
|
|
rlaubert
Yak Posting Veteran
96 Posts |
Posted - 2007-03-16 : 08:04:08
|
| If I remember right, Parallelism only effects a single query where the cost exceeds the settings for the server. SQL will still use multiple CPU based on the needs of the operating system if they are available. The Parellism settings tells SQL to split the execution plan over multiple CPUs if it can and the cost is high enough to warrent the extra processing.As for the difference in execution time, I would check several things. First compare the execution plans for development and production. Make sure they are the same. Could be that production is doing table scans or something (even though the stats are up to date). I would also check the database stats. Are they the same? Are you accessing the production data with development or does development have its on database. Disk fragmentation could also come into play if the two databases are different. Just a couple of things to check.Raymond LaubertMCDBA, MCITP:Administration, MCT |
 |
|
|
|
|
|
Parallelism Issue - SQL2000