SQL Server Performance

Caching in SQL SERVER 2000 on WIN2K3

Discussion in 'Performance Tuning for Hardware Configurations' started by vshah, Sep 7, 2006.

  1. vshah New Member

    Hi,<br /><br />Thanks in advance for your support.<br />We have recently purchased a Dual Core, Dual processor(XEON) server with 4 GB installed RAM, which we are using exclusively as our SQL SERVER Database server. We have installed SQL SERVER 2000 on this machine and we are now facing serious performance issues.<br /><br />The Server is configured in the following fashion.<br /><br />1) OS is installed in a disk with RAID 1 configuration [36 GB]<br />2) SQL DATA is stored in a disk array of RAID 10 configuration[67 GB]<br />3) SQL LOGS are stored on a disk array of RAID 1 configuration.[135 GB]<br /><br />Our database size is 3.2 GB (MDF file).<br /><br />One of the interesting issues,which we are facing is the following; <br />After we started getting poor performance, I had installed the same Database on a desktop (Pentium IV / 512 MHz machine) and when I use the query analyzer, I can find that the queries are cached as expected. (For example, If I query a table for the first time, I get the result in 6 seconds and in consequent attempts, I am getting the results in 2 secs or even 0 secs)<br /><br />But on my NEW super powered, Dual Core Dual Processor machine dedicated for my database activities,the same "select query on the same table" constantly takes 6 secs, regardless of the number of attempts.<br /><br />If I run a DBCC MEMORYSTATUS I am getting the following values.<img src='http://66.155.212.68/palm/image/DBCCMEMORYSTATUS.jpg' border='0' /> I am not sure whether the following information helps you in suggesting your views.<br /><br /><br />
  2. Luis Martin Moderator

    1) How many processor do you have for paralellism?
    2) How do you set SQL Memory?
    3) Some counters will help: pages/sec, Avg. Disk queue lenght.
    4) Did you run maintenance plan like Reindex or/and Update Statistics?

    BTW:Welcome to the forum.



    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided “AS IS” with no warranties for accuracy.



  3. joechang New Member

    i would run the query with the OPTION (MAXDOP 1) at the end

    also, run the two below, then run the query and post the messages output
    SET STATISTICS IO ON
    SET STATISTICS TIME ON

  4. vshah New Member

    Hi,
    We have two dual core processors availible for paralleism, and we have also reindexed the tables It did not help.
  5. Adriaan New Member

    There are known issues with SQL Server using parallellism when it shouldn't, and your query may be one of them. Look at Joe's suggestions.
  6. vshah New Member

    we got an sql expert today but we dont have any progress on this, guys if you have anything to we can try, please help URGENTLY!!
  7. Adriaan New Member

    Have you tried Joe's suggestion to "run the query with the OPTION (MAXDOP 1) at the end"?

    The point is that parallellism may be the cause of the problem, rather than an improvement. The MAXDOP 1 option disables parallellism for the query.
  8. vg15 New Member

    SQL DATA is stored in a disk array of RAID 10 configuration[67 GB]

    why RAID 10? (mirror with stripping) ?

    why not go with raid 50 (raid 5 stripped) ?

Share This Page