SQL Server and MSA 1000 performance problems | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

SQL Server and MSA 1000 performance problems

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 ?
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.
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.
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
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.
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.
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
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.
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.
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?
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 ?
]]>