March 17, 2010 at 10:33 pm
Hi,
We have 3 node a/a/p cluster setup for SQL Server 2005 enterprise edition with SP3 having 16 GB Memory on all the 3 nodes and the storage is on SAN
I did setup Perfmon counters and gathered the values in order to benchmark the performance. Could you please tell me the threshold values for below counters
1) PhysicalDisk(_Total)\% Disk Time--> I got values like 0.871132183, 0.110411253.
Do I need to multiply this value by 100? Because this value should NOT >90%
And from the link http://msdn.microsoft.com/en-us/library/ms175903%28SQL.90%29.aspx
If you are using a RAID device, the % Disk Time counter can indicate a value greater than 100 percent. If it does, use the PhysicalDisk: Avg. Disk Queue Length counter to determine how many system requests, on average, are waiting for disk access
Here, we are using RAID 10 SAN drive, so % Disk Time counter can have >100% (which is>90% threshold & it's near impossible to interpret Avg. Disk Queue Length, if the storage is SAN. So how to deal with these 2 counters? )
2)Paging File(\??\C:\pagefile.sys)\% Usage
3)Paging File(\??\C:\pagefile.sys)\% Usage Peak
4)PhysicalDisk(_Total)\Avg. Disk Read Queue Length
5)PhysicalDisk(_Total)\Avg. Disk Write Queue Length
as it near impossible to get Avg. Disk Queue Length for the drives on SAN, I will try to get values for this from SAN Admin.
Are the Avg. Disk Read Queue Length, Avg. Disk Write Queue Length from perfmon OK to anlyze or need to get from SAN admin for these values too?
What are the Perfmon counter's values are near impossible to interpret, if the storage is SAN?
How to find number of spindles that make up the physical disk of RAID 10?
Thanks
March 30, 2010 at 10:16 am
Could you please shed some light on this?
March 31, 2010 at 7:39 am
1) average disk queue length is useless measure with almost all modern IO subsystems, especially SANS. avg disk sec/read and /write are the best measures to use.
2) Your us of Total counters is also meaningless. You could have umpteen drives available but only 1 of them serving up SQL Server data and the others will skew your results. Use specific drives that server up SQL data/log files.
3) sql server has it's own file IO stall system. See sys.dm_io_virtual_file_stats in BOL
4) Your best bet for getting to the root of performance problems when you are a newbie at it is to hire a professional tuner to review your system while also mentoring you on how to effectively monitor/tune.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply