June 20, 2008 at 6:32 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 2:44 pm
It has been my experience that you can't relay on System Monitor counters for SANS. Instead, you need to use the performance monitoring tools provided by your SAN vendor to see really what is happening on your SAN. It is very common to see SQL Server performance issues on newly built SANS, as many SAN designers/engineers aren't expert on how to optimize SANS for SQL Server.
See these for more help:
http://blogs.msdn.com/sqlcat/archive/2005/10/11/479887.aspx
http://sqlcat.com/top10lists/archive/2007/11/21/storage-top-10-best-practices.aspx
jsusanik (6/20/2008)
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.
Brad M. McGehee
DBA
June 20, 2008 at 4:52 pm
ok, this is one the approach. I had come across this myself.
Check to see if there are hypothetical indexes in Sql server. if there are Many hypothetical indexes, try deleting them and your performance should improve... This should be one of the steps among the several you will perform for Performance analysis...
Maninder
www.dbanation.com
November 20, 2009 at 12:45 am
IMO these are very interesting articles on this Disk related subject ...
- Playing with Disk Alignment
http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/11/19/playing-with-disk-alignment.aspx
- SAN Performance Tuning with SQLIO
http://sqlserverpedia.com/wiki/SAN_Performance_Tuning_with_SQLIO
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 25, 2009 at 12:07 pm
In addition to the Disk alignment (typically more of an issue on disks formatted by MS OS installs), is to verify the Allocation Unit size (4kb v. 8kb v. 64kb etc)/ cluster size of your disks. Larger Allocation Units are typically better for databases.
http://msdn.microsoft.com/en-us/library/dd758814.aspx
http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1262122,00.html
http://sqlcommunity.com/Blogs/tabid/70/EntryId/43/Disk-Alignment-for-SQL-Server.aspx
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 26, 2009 at 9:58 am
There are SOOOOO many things that can cause performance problems. Have you checked for:
1) file IO stalls (virtual file stats DMV)
2) wait stats analysis?
3) blocking?
4) done query tuning?
5) index need analysis?
I recommend you get a performance tuning professional in to give your database system a review and teach you how to do the same.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply