June 20, 2008 at 8:35 am
Hi All,
I have build a new SQL 2005 std edt 2 node cluster which holds the 30GB DB for Clarify-like application and we have performance issues - users reporting slow responses, freezes... The reason can be in lot of things but Im suspecting a SAN cause of some counters being too high.
Config:
4x3GBCPU 16 Logical CPUs, 16GB RAM,
Local disks C: system, D: swap
2 node windows 2003 cluster
SQL 2005 standard edition
SAN:
MSA 1000 running in active/active mode, 6 DISKS - 5 used +1 spare
2 port HBA, one path configured for disk F other one for disk G
Logical Disks : F:\ DB (3physDISKs RAID5), G:\ TransLogs, BKp (2physDISKs RAID1)
First of all, which counters should I use in perf mon - LogicalDisk or PhysicalDisk?
Here are the results when I copy 30 GB file from disk F: to disk C:
counter name>>LogicalDisk Value for disk F:>>PhysicalDisk Value for disk F:
------------------------------------------------------------------------------------
Avg.Disk queue Length##0.962##0.983
Avg.Disk sec/read##0.002##0.968
Avg.Disk sec/transfer##0.002##0.002
Avg.Disk sec/write##0.002##0.002
Current Disk Queue Length##1##1
Disk Transfer/sec##500.0##500.0
Split IO/Sec##0.000##0.000
As you see Avg.Disk queue Length is realy high but that should be ok as for SAN this counter is not applicable as I read in some Technet articles. What worries me is the Avg.Disk sec/read value for PhysicalDisk counter. I dont understand why it is so big compared to LogicalDisk.
June 20, 2008 at 9:33 am
Logical vs Physical, on the IO stats, is data queried from the in-RAM cache (logical) or from the actual HDD (physical).
The first thing I noticed is that you have your database on a RAID-5 array. For OLTP, that will slow things down, compared to RAID-1/10/01. Might not make a huge difference for you, your mileage may vary, but it usually does make at least some difference.
Other than that, it doesn't look to bad on physical set-up. I'd be more inclined to run traces on the query execution and see if there are badly written queries and/or badly built tables.
Most of the time (vast majority), when a database is slow, it's not the hardware. All it takes is a few gratuitous cursors, a couple of unfiltered large datasets, a few bad index designs, and you end up with timeouts, slow code, crashes, etc.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 20, 2008 at 9:45 am
Is performance worse than the old system?
I tend to agree with GSquared on the setup, though I'd check the cache on the SAN as well. For SQL you really want 100% write. The read cache doesn't help as SQL does a lot of read ahead on it's own.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply