Dual CPU Machine and SQL 7.0

We have a complex query that runs in 3-5 seconds on a single CPU machine. When we install the query on a Dual CPU machine, Optimizer selects a totally different execution plan that causes table scans and takes about 45 - 360 seconds to execute.
Any ideas on what to do?
ThanksWhen was the last time you updated statistics, re-built indexes and re-compiled your sps on the dual box?|||We have performed several analyses and traces on the system. We tore apart the Stored Procedure (and ended up re-assembling it the same way).

We dropped and rebuilt all indexes. Update Stats and recompile runs daily on all DBs.

We determined that on dual cpu machines, SQL Optimizer decides to use a bad Execution Path that includes Table Scans (thus the 30-40 minutes).

The data is the same across all the machines, as are the indexes, primary keys, security permissions etc.

We've eliminated flags/settings differences within SQL itself.

We eliminated the raid configuration as on single-cpu machines it runs on any raid config and won't run on dual-cpu any raid or non raid config.

When we pop out one of the CPUs, then the stored procedure runs in 3-5 seconds.|||I'm not quit sure that it's a because of a dual processor. Our development machine and UAT machine are single processors. Our Production and Reporting servers are dual processors and they created the same query plan as those on development and UAT. There is this one instance, about a month ago where a production stored procedure created a different query plan from development, UAT and the Reporting server. But there was one thing different and that was the Production server's indexes where created as CONTRAINTS, whereas on the other machines they where created with CREATE INDEX. The reason Production had to change was because transactional replication requires keys to be defined via PRIMARY KEY constraints. So to rectify the problem I coded tables in the FROM statement in the order of query optimization and adding the FORCE ORDER option.
This cleared up the problem. I never use HINTS or any of those FORCE options before, this is my first time. I'm not one to tamper with query plans, however in this one instance it helped.|||Thanks for the idea, but we have already tried the FORCE and hints options and Optimizer still overrides them. The data is the same across all the machines, as are the indexes, primary keys, security permissions etc.|||Dumb question here but is your hardware and os configured for one or two cpus?

I talked to one of our surver guys and he said you can't just reomve a cpu, reboot and expect NT to run properly.

In fact based on what you said I am wondering if NT was installed for one processor while the hardware is configured for 2 cpus!|||Thanks for the suggestion. We tried 2 different dual-cpu machines.

When configuring the machines both were wiped and WIN 2K SP2+ and SQL 7.0 SP3 were re-installed for a 2-cpu environment.

One machine (Dev) is AN HP LH4R w/Dual Pentium II Xeon 400 mhz CPUs. 2Ghz mem w/HP Netraid controller running a Raid 5 array.

The other machine (Production) is a IX Systems Tyan Motherboard Thunder model LES2510 with 2 Pentium III 1Ghz CPUs. Symbios SCSI Controller hooked to Infotran Raid Controller Model 3102 running Raid 5 array.

When the machines were taken back to 1 CPU, WIN 2K and SQL were again wiped and re-installed.|||Another "Is it Plugged in?" Question...

Are the settings correct for Paralellism in the 'Properties'
under your server in EM... Minimum query plan threshold setting...
defaults at 5 seconds (cost estimate)... for multi-CPU machines|||Hi all,

If the machine can be restarted - then boot to a single processor mode and run the tests...

just a shot...

take care
tony|||We have an sql application that runs perfecly on single processor machines. we recently installed on a dual processor capable server with only one Xeon 1.8 Ghz processor, windows2000 server. Suprisingly we find the sql performance has degraded dramatically( when compared to running it on 1 Ghz, single processpor)
I went through a number of message postings and detrmined that a number of other people have seen performace degradation or at least no increase in performance. Here are the links, the common thread is all of them have xeon processors

Wondering if this is a setup issue on the server or ....

http://216.239.33.100/search?q=cache:GokRMOJ5XtQC:dbforums.com/t363432.html+CONFIGURING+DUAL+PROCESSOR+SQL&hl=en&ie=UTF-8

http://webforums.sybase.com/nntp/nd000049.nsf/85255e6f0052055e85255d7f005ed8bc/dac80f4be40abe7352192d6d624343b1?OpenDocument

http://www.sqlmag.com/Forums/messageview.cfm?catid=5&threadid=4383

http://www.sqlmag.com/Forums/messageview.cfm?catid=5&threadid=5970

http://www.sqlmag.com/Forums/messageview.cfm?catid=5&threadid=4466

http://www.sqlmag.com/Forums/messageview.cfm?catid=22&threadid=3358

http://www.winnetmag.com/Forums/Application/Thread.cfm?CFID=19669352&CFTOKEN=65475496&CFApp=70&Thread_ID=88250|||We still haven't fixed the problem. We're currently trying different tests with setting the parallelism. I'll investigate the latest suggestions and incorporate them into our tests.

Thanks for all your help.