Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2002
    Posts
    14

    Angry Unanswered: 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?

    Thanks

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    When was the last time you updated statistics, re-built indexes and re-compiled your sps on the dual box?
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Feb 2002
    Posts
    14
    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.

  4. #4
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    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.
    MCDBA

  5. #5
    Join Date
    Feb 2002
    Posts
    14
    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.

  6. #6
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    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!
    Last edited by Paul Young; 04-26-02 at 16:33.
    Paul Young
    (Knowledge is power! Get some!)

  7. #7
    Join Date
    Feb 2002
    Posts
    14
    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.

  8. #8
    Join Date
    May 2002
    Posts
    1
    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

  9. #9
    Join Date
    May 2002
    Posts
    6

    an additional try here...

    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

  10. #10
    Join Date
    Jun 2002
    Posts
    3

    SQL PERFROMANCE DEEGRADATION WITH XEON DUAL PROCESSOR

    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...hl=en&ie=UTF-8

    http://webforums.sybase.com/nntp/nd0...1?OpenDocument

    http://www.sqlmag.com/Forums/message...&threadid=4383

    http://www.sqlmag.com/Forums/message...&threadid=5970

    http://www.sqlmag.com/Forums/message...&threadid=4466

    http://www.sqlmag.com/Forums/message...&threadid=3358

    http://www.winnetmag.com/Forums/Appl...hread_ID=88250

  11. #11
    Join Date
    Feb 2002
    Posts
    14

    Thanks

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •