SQL Server Performance

SQL server - Slow performance on a dual CPU box

Discussion in 'Performance Tuning for Hardware Configurations' started by sharatjain, Sep 1, 2005.

  1. sharatjain New Member

    Hi,

    I am new to this forum and am not sure if I am posting this in the right place.

    My problem is as follows:

    I have a ASP / MSSQL project which runs fine on the develpment server (P4 / I gb RAM / single CPU/ Win 2003 standard)

    The same application when ported on the production server slows down immensely. Certain reports which do a fair bit of number crunching take upto 4 minutes on the development server but can take more than 15 minutes on the production server

    The production server is a dual P3 with 1 GB RAM also running Win 2003 std. MSSQL on both machines was installed from the same physical media

    I searched this forum and found articles on parallelism. I have check the production server and found that parallelism is turned ON.

    The production server does not have any other application running. Apart from Windows 2003 std and MSSQL, both servers run IIS6.

    Running a performance monitor on the development server shows and average CPU load of around 40% with spikes going up to 100%. However, on the production server, the CPU load is always in excess of 90% and is often at 100%.

    Any help and advice will be highly appreciated.

    Thanks,

    Sharat Jain
  2. bertcord New Member

    You say both machines have IIS installed. Does the prod server support a live website? Maybe you shoud use the better hardware in production rather then in dev.

    Bert
  3. sharatjain New Member

    Both servers - development as well as production are running IIS to support an ASP application which is accessed by users over the Intranet

    Better Hardware...I am not sure what you mean. The development server is a single processor P 4, whereas the production server is a dual processor P 3


    regards,

    Sharat
  4. satya Moderator

    When you say both are identical, is that applicable to TEMPDB as well?
    How about memory settings on SQL server?

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  5. bertcord New Member

    my bad, I missed the Single CPU vs. Dual CPU part <img src='/community/emoticons/emotion-5.gif' alt=';)' /><br /><br />are you sure it is SQL running at the high cpu or do you have other process running?<br /><br />Are the servers set the same? What about statistics, are teh settigns the same for both servers?<br /><br />Bert
  6. satya Moderator

    -- Anti-virus software can affect SQL Server performance:http://support.microsoft.com/?kbid=309422 and also defragmentation will have negative effect on performance, so keep in regular reversal of index defragmentation.

    Also check the Connection Timeout under the website Properties->Connections.


    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  7. sharatjain New Member

    Here is what I found on this site:

    "http://www.sql-server-performance.com/case_studies.asp

    A developer came to me with a stored procedure that was taking forever (over 30 minutes) to run. The SP had a UNION ALL as part of the code, and what was strange is that if you ran each separate query (on either side of the UNION), they ran very fast. But when run as a whole, the query ran forever.

    My first instinct was to look at the execution plan of each SELECT statement individually, and then of the entire statement, looking for any obvious issues. There weren't any as the execution plan looked good.

    One thing I did notice was that parallelism was used by SQL Server to run the query. The server has 4 CPUs on it. This was true whether the two separate queries were run individually, of if they were run together as part of a UNION.

    Past experience has told me that parallelism and SQL Server don't always go well together. So my next step was to add this hint to the bottom of the SP:

    OPTION (MAXDOP 1)

    This query hint is used to tell the Query Optimizer to not use parallelism in this particular query. The number "1" in the hint specifies that only one CPU is to be used to run the query.

    After adding the hint, the query went from taking over 30 minutes to run to less than 2 seconds. Obviously, this query raised a parallelism bug (related to the UNION) which was causing the performance issue, but the hint turned off the parallelism bug, fixing the problem. [7.0] Added 3-27-2003"



    Do you think I am facing a parallelism bug?

    Is there a fix for this?

    Regards,

    Sharat

  8. ranjitjain New Member

    hi Sharat,
    I dont think there is any parallelism issue but you can give it a try.

    Try reinstalling the latest servicepack and then restarting the SQL Server and would be better if you can manage to restart the server too and ther testfire your queries.
  9. satya Moderator

    You can try specifying one processer under SQL server properties in order to see any parallelism is affecting this issue, as defined above you can also deploy latest service pack on SQL.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  10. sharatjain New Member

    ok, Here is an update...

    Installed SP3 on the production server and performance has improved dramtically.

    However, users still claim that the development server was a shade faster.

    More importnantly....I noticed a *Key* difference. We were running SQL Enterprise Edition on the development server and are only running a standard edition on the production server.

    Question now is: Will switching to Enterprise Edition on the production server help improve performance?

    Thanks,

    Sharat
  11. satya Moderator

    Yes EE has got few advantages on SE in comparison and if the budget permits there is no harm in upgrading edition of SQL SErver. But make sure you've tested in development with similar data volume and user load, otherwise it makes no difference.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  12. mmarovic Active Member

    I agree with Satya. On top of that, you can keep the same budget, but IMO it makes much more sense to have SE on dev server and EE in production.

Share This Page