August 26, 2009 at 5:25 pm
Hi,
We have A/P cluster set up for SQL Server 2005.All the Drives are on SAN including OS Drive. I want to monitor the SQL Server performance to take the base line performance readings.
If the Storage is on SAN, then the Perfmon counter readings are incorrect!!!!
Is that true? If yes, what is the correct way to gather the SQL Server statistics like DISK IO, Memory, CPU??
August 26, 2009 at 7:06 pm
Mani you have brought up a good point, i haven't thought of this before. It will be interesting to see feedback from other users, meanwhile i'll just start my research. :hehe:
August 26, 2009 at 11:20 pm
I haven't ever heard that. Is that your opinion or are you asking for clarification of something you're read? If the former, why do you think that, if the latter, can you give a reference?
Some of the disk counters are near-impossible to interpret properly with a SAN (disk queue length for eg), but that doesn't mean they're wrong.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 27, 2009 at 1:23 am
Hi Gail,
My statement might be wrong. But I read about only Avg Disk Queue length, that we can not measure exactly when the storage is on SAN from one of your answers http://www.sqlservercentral.com/Forums/Topic659090-146-1.aspx.
For our clustered servers, write cache is enabled for all the drives and I'm getting high avg.disk queue length values from perfmon counters.
If my statement is wrong please correct it so that every one will be aware of the reality.
SSC is really very helpful.
thank you
August 27, 2009 at 4:35 am
Ok, the Disk queue length reported by perfmon is correct. There's no question there. The problem is the traditional interpretation of that counter is that if it exceeds 2 per spindle there's a problem. That's fine for direct attached storage, but with a SAN, there's so much between the server and the disks (switch, fibre cables, SAN controller, cache) that it's very hard to say whether a value is good or bad now. Personally, with a SAN I stay away from that counter and I'll use other disk counters.
There's a good podcast on runasradio about the various counters, I would strongly suggest that you download that podcast and listen to it.
http://runasradio.com/default.aspx?showNum=81
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 27, 2009 at 6:52 am
Agreed - it's not that the counters are wrong - it's that things change about how to interpret them.
Usually with a SAN you end up having to take into account other counters (average queued IOPS, avg time to clear the iops queuem etc...). Some of the utilities from the SAN vendor (allowing you to monitor what the local SAN card queueing looks like) might be useful as well.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 27, 2009 at 6:59 am
Personally I mostly use % idle time, avg sec/read and avg sec/write. Doesn't matter what the underlying storage is, if the idle time is low and the sec/read or sec/write are high, there's a problem.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 27, 2009 at 11:13 am
Thank you,
Personally I mostly use % idle time, avg sec/read and avg sec/write. Doesn't matter what the underlying storage is, if the idle time is low and the sec/read or sec/write are high, there's a problem
What will be the ideal, good values should we need have for:
%idle time
avg sec/read
avg sec/write
August 27, 2009 at 11:23 am
Did you listen to the podcast I recommended?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 27, 2009 at 11:25 am
Sorry..I missed the last lines to read from your previous post.
thank you
September 1, 2009 at 10:44 am
Is write caching a good thing on san disks?
September 1, 2009 at 3:21 pm
jasonmorris (9/1/2009)
Is write caching a good thing on san disks?
Answer is, it depends.
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
September 2, 2009 at 7:10 am
There's heaps of info about this, but here's a start....
Average Disk/sec Read & Average Disk/sec Write:
On well-tuned I/O subsystems, ideal values would be:
1–5 ms for Log (ideally 1 ms on arrays with cache)
4–20 ms for Data on OLTP systems (ideally 10 ms or less)
30 ms or less on DSS (decision support system) type.
The % Idle Time counter is a bit controversial & misunderstood. Like disk queue length, it can be & often is misused. Be prudent in using & interpreting values from this counter!
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply