August 18, 2005 at 11:28 am
Hello all,
I am new to these forums and to SQL Server performance tuning in general. If there is a better place to ask this, or better resources available, I welcome all ideas.
I am collecting stats on Physical Disk - % Disk Time. Thus far (checking every few minutes over a period of a few hours), the average value is 59.16. I have read that anything over 55% is a possible sign of an I/O bottleneck (http://www.sql-server-performance.com/sql_server_performance_audit2.asp).
However, I have two questions:
1. I read that for the % disk time stat to be most accurate, one needs to use the "diskperf -y" option, though you don't want to just leave that on, as it slows everything down by about 3-5%. How much of a difference does that option make? Are stats without it still useful?
2. Our database server stores its physical files on a virtual disk presented to it from a SAN, to which it is attached via fibre. Is this slow enough (compared to a db stored on local drives) that I should add an extra "allowance" to the 55% to account for it? In other words, is 59% perhaps more appropriate since we add the overhead of transfer back and forth from the SAN?
Thanks in advance for any help. I have much to learn.
- Chris McCormick
August 18, 2005 at 1:13 pm
It's been my experience that PhysicalDisk counters are notoriously unreliable and unusable on a SAN. The best results I've found for measuring SAN performance come from utilities put out by the SAN and HBA manufacturers themselves. As you point out, there are a lot of factors (fibre channel, SAN admin software and hardware, etc.) that affect any type of performance metrics between your SQL Server and your SAN to get anything useful out of the PhysicalDisk counters.
AFIK, PhysicalDisk counters are most useful when dealing with local hard drives. Check with your HBA and SAN manufacturers for SAN performance measuring tools specific to your hardware.
August 19, 2005 at 7:48 am
As Mike said, the disk counter is not accurate for SAN, especially the disk time. I sometime got 200%. I found using SQL server function fn_virtualfilestats is much helpful. You can schedule a job to run the function and save the result to a table.
By analysing the result, you can easily figure out any I/O bottleneck.
August 19, 2005 at 11:01 am
My virtue of it's architecture a SAN should outperform internal disk 99% of the time. The SAN manufacturers have the performance monitoring software that you need - probably your SAN Administrator. Experience has taught me that unless the SAN is overloaded (there would be more complainers) or there is insufficuent cache the most likely cuplrit is the application database's use. You may want to check how you are using the SAN disk. From an application perspective:
Are all of your SQL executables on local disk ?
Are databse data (.mdf files) and tranasction logs (.ldf) files on separate SAN LUNs ?
Are the database backups (full/differential and transaction logs) on a separet SAN LUN ?
If you can answer 'yesy' to all of the previous questions then I'd ask the SAN administrator if you are using LUNs or Meta-LUNs. Meta-LUNs are faster.
If all else fails, then its time to contact the manufacturer.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
August 19, 2005 at 5:43 pm
I would be very careful in saying that a SAN should outperform an internal disk 99% of the time. I have worked at several sites where the number of fibre cards attaching the servers to the SAN just couldn't compete with the throughput and performance that a well designed SCSI subsystem can achieve. If I need to spec out a performance system, and I am not looking for the other advantages a SAN setup can provide, I will definitely go with direct attached SCSI. I have found, dollar for dollar, you can achieve much better performance with direct attached vs SAN.
Ian Dundas
Senior IT Analyst - Database
Manitoba Public Insurance Corp.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply