July 18, 2010 at 1:00 pm
I have too much disk activity on one of my server but i have to prove that to my Network Admin with some exhibits so that he can take some action either changing SAN configurations or RAID levels. What would be the best idea ? I dont want him to blame on sql server for no reason.
My goal is to prove that Avg Disk IO is more than 2 for longer times as it is accepted that more that 2 can last for some time may be 10min but more than that is not acceptable. we see more disk activity on reads.
Also I would like to know how can i capture records from win 2003 perf mon for the history activity (15days)
July 18, 2010 at 2:40 pm
You want to capture windows perfmon stats for the past 15 days? You need to have a utility running that captures those stats and stores them somewhere. There are tools such as spotlight and idera diagnostic manager that can do that for you.
That is going to be your best option to prove to the SAN admin that something is misconfigured.
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
July 19, 2010 at 7:05 am
Do you have a baseline of when the systems was performing well?
The following is from "Professional SQL Server 2005 Performance Tuning", WROX, Page 50.
Trap these counters to confirm disk bottlenecks:
Avg Disk Queue Length: Consistently above 2 is out of tolerance
Avg. Disk sec/Read and Write: (multiply this value by 1000 for ms) 20 ms or greater is out of tolerance
SQLServer: Access Methods Full scans/sec: > 1 per second could indicate an index issue
SQLServer: Access Methods Page Splits/sec: many page splits can affect performance. This can be due to indexes and fill factors.
Also take a look at the Disks/Controller contention. Your SAN admin should have a tool to look at this.
July 19, 2010 at 7:31 am
It's also not that easy; personally, I'd look at the MB/s throughput as well. The IO queue can be as high as it likes; if you're getting a throughput rate close to your theoretical maximum, then you're doing as well as the current setup can.
Disk queue; I believe I've seen references that 1 per spindle involved is reasonable (perhaps http://dbaspot.com/forums/ms-sqlserver/140091-re-perfmon-physical-disk-counters.html); give it 25 spindles, and a disk read queue length of 25 is reasonable, one outstanding read IO per spindle. Unless, of course, one has evidence that adding more disks does not allow more reads at the same time.
Avg sec/IO I'd propose as a more important metric, if you're looking at things that are only slightly above the 'normal' spec.
Unfortunately, this is an area where knowledge of the entire actual setup is important.
July 19, 2010 at 7:52 am
1) Having done this type of work for over a decade now, I can tell you that it has been my experience that SAN admins RARELY listen to in-house DBAs, regardless of how experienced said DBAs are. And nothing personal but the questions you ask and the way you phrase them make me think you are not up to the task of confidently telling a SAN admin that his stuff is the cause of SQL Server's poor performance. Get a professional in for a few hours and you will have all the information you need to know what is really at the root of your perf problems.
2) If you can't do item 1, then take regular snapshots of the sys.dm_io_virtual_file_stats DMV into a table with an identity column and date column on it as well. Then you can do all sorts of spiffy time-series analyses of said data. avg ms/read and avg ms/write are really what matter here.
3) There are MANY reasons why SQL server can be slow, and IO is just one of them (and IO itself has MANY potential underlying causes). I will recommend again that you get a qualified performance guru onboard to give your system a review.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 19, 2010 at 10:16 am
I have a similar issue.
I have a SQL 2005 Standard 32-bit server and our Idera DM reports that disk C:, E: (user db logs) and T: (temdb) are more or less 100% busy most of the time.
We have already done some work to resolve this; D: drive was changed to RAID 1+0 (E: already was) and T: was created as RAID 1+0 for tempdb. There is no SAN here, its all local.
But we cant figure whats using the drives... E: and T: maybe, but why C:?
Does anyone know of a tool (like the XTM from Windows 7) that has a disk monitor that can tell me what process is using the disk?
Thanks in advance.
Adam Zacks-------------------------------------------Be Nice, Or Leave
July 19, 2010 at 10:38 am
Before going down the path of insisting that your IO subsystem is inadequate you should be very, very sure that the cause of excessive IO is not poorly optimized queries. For one thing, query performance is an activity that you can do something about without needing someone else to approve on it.
Take the 10 worst performing queries and analyze them. Are they doing table or clustered index scans on large tables? This would certainly result in excessive IO.
I always assume that the problem is poorly written queries, which are MY problem and I can do something about the problem personally. Then I will look at other causes for slowness.
Todd Fifield
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply