SQL Server Performance

SQL Server and MSA 1000 performance problems

Discussion in 'SQL Server Clustering' started by fssherwani, Nov 21, 2005.

  1. fssherwani New Member

    Hi All
    We have setup SQL server cluster running on Windows 2003 and are using HP MSA 1000(SAN) for hosting our databases. But the performance have been horrible , especaially the Avg. Queue length and Avg. Read Queue length are very high other perf counters like CPU and RAM looks ok , any help ?
  2. satya Moderator

    Post the counters that are collected using PERFMON (SYSMON) and also take help from PROFILER for slow running queries and submit trace to Index Tuning Wizard for any better recommendation on indexes.

    BTW what is the memory setting on SQL Server?
    Any other applications sharing the Win2K3 box?
    How about disks placements?

    Satya SKJ
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. Luis Martin Moderator

    Also did you run reindex and/or update statistics as part of maintenance plan?


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


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



  4. fssherwani New Member

    Satya : Thanks for the reply , this box is running two instances of SQL server , AWE is enabled and each instance have 3 GB of RAM .There is no other application running on this box. Following is the server hardware configuration.
    1. RAM 8 GB + 8 GB of PF

    2. 8 processors

    3. A total of 14 physical drives.
    Array 1 : 2 146 GB Drives with RAID 1+0
    Array 2: 12 146 GB drives with RAID 6, (Note RAID 6 consumes 2 drives , so actual data is written on 10 drives)

    4. For SQL Databases I have created 4 logical drives
    i. Data_drive_Defaultinstance : For hosting DB Data files of Default instance , the disk is configured with 16 kb stripe size on RAID 6.
    ii. Log_drive_Defaultinstance: For hosting DB log of Default instance , the disk is RAID (1+0) with stripe size of 128 KB.
    iii. Data_drive_NamedInstance: RAID 6 , stripe size 16 kb.
    iv. Log_drive_NamedInstance: RAID 1+0 , strip size 128 KB

    Let me know if you need further info
    Regards
    Fahad
  5. Luis Martin Moderator

    I would like to know about reindex and statistics.

    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


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



  6. Haywood New Member

    It's not (my) reccomendation to re-carve up your disk using the O/S tools. If you created one 12 drive array with the HP Array Manager, represent that to the o/s. You're not gaining anything by taking that volumne and splitting it up into more logical volumes with the o/s tools. In fact you may now be taking a hit to the disk because the o/s is involved in your disk read/write operations. Let the controller handle your io operations...

    If each instance is only running 3GB you can disable AWE. Just ensure that you have /3GB set in the boot.ini.
  7. fssherwani New Member

    Luis:
    Yes we do run reindex on the databases as part of maintenance plan , what factors should i be looking for ?

    Satya:
    I collected following perf counters (listing the avg. values)

    MemoryAvailable MBytes : 3622
    MemoryPages/sec: 92.183
    Buffer cache hit ratio: 98.718
    General StatisticsUser Connections :280
    SQL StatisticsBatch Requests/sec : 81.766

    PhysicalDiskAvg. Disk Queue Length :51
    PhysicalDiskAvg. Disk Read Queue Length :44
    PhysicalDiskAvg. Disk Write Queue Length: 6

    Processor(_Total)% Processor Time:20
    SystemProcessor Queue Length : 0

    As you might notice that Disk counters are very high.
    Thanks
  8. Luis Martin Moderator

    Disk are high and pages/sec too.

    I suggest to run Profiler and find long queries (high duration and/or reads) and try to optimize those queries.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


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



  9. satya Moderator

    http://www.sql-server-performance.com/operating_system_tuning_w2003.asp tips on SQL-win2k3.
    IF this is a SQL named instance then ensure NAMED-PIPES protocol is enabled and being used by the clients also. Check whether any issues with WINS or network between application & SQL Servers.

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

    One of the tips in the above link was to turn off auditing on production servers?

    How much overhead does auditing create?

    Isn't this a rather large security hole?

    I could understand making this suggestion if I had a server that was pegging my hardware resources, but to make it as a blanket statement seems short sighted from a security standpoint?

  11. fssherwani New Member

    Satya:
    Our applications are runnig in CITRIX ENVIRONMENT . I have read that TCP gives better performance on slower networks. Anhow the applications are using server alias and i have checked TCP/IP as the protocol.

    Luis : Unfortunately this is a third party appp and i cant do much query tuning , i can suggest what queries are running longer. will creating separate logical drives for all the db's help ?

Share This Page