August 18, 2010 at 1:30 am
Hi,
We have SQL Server 2005 EE x64 with SP3 on Windows 2003 EE x64 with SP2
16 GB of RAM. Max memory is set to 12 GB and 4 GB is left for OS.
Lock pages in memory is enabled.
We have all drives with RAID 10 and the storage is on SAN.
I gathered values for the counters Avg.sec/read and Avg.sec/write and they are >25 many times
Please see the attached document.
Here what is the difference between the below two:
1. PhysicalDisk(_Total)\Avg. Disk sec/Read,PhysicalDisk(_Total)\Avg. Disk sec/Write
2. PhysicalDisk(0 D:)\Avg. Disk sec/Read PhysicalDisk(0 D:)\Avg. Disk sec/Write
Do we need to consider the values for PhysicalDisk(_Total)\Avg or PhysicalDisk(0 D:)\Avg (not total)?
Here I noticed that even though the PhysicalDisk(_Total)\Avg. Disk sec/Read or writes is very less, the Avg.sec/read and Avg.sec/write is very HIGH.
So what need to be consider the PhysicalDisk(_Total) or just for each individual disk?
I need to work with SAN admin to report the IO issue and want to go with proof:-)
Thank you
August 18, 2010 at 10:21 am
Notes on Disk subsystem troubleshooting, the spike in usage is not a concern.
You should look at the average over a fixed time, aka when your systems were the most busy.
If you see any time the system sustained the high disk i/o over lets say 5 min then you should be concerned.
I personally look at following disk counters:
% Disk Read Time
% Disk Write Time
Avg. Disk Bytes/Transfer
Avg. Disk Read Queue Length
Avg. Disk Write Queue Length
Avg. Disk sec/Read
Avg. Disk sec/Write
Disk Read Bytes/sec
Disk Write Byes/Sec
I never look at total because it doesn't really show where your contention issue is. The % values are a little unreal when it comes to SAN, but it still gives you idea of what windows thinks of disk usage. If they ares staying at 100%, you have an issue. But that value alone doesn't say much, look at the bytes transferred to see what is the total through put?
But your values for seconds should be less then 5ms on average. For it to stay that high either you I/O system cannot keep up with the through put or other issues need to be resolved on you database. How is your database performance in executing queries? What do each of your disk represent? D, E, T, Z, Q, Y? For your avg. disk sec/write and read to be in seconds instead of ms, there is got to be performance issues. How are your server and SAN communicating?
How often do you do maintenance on your databases? How large are you databases? Is this a SQL Dedicated server or shared?
What are the wait stats for PAGEIOLATCH and PAGELATCH?
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
August 18, 2010 at 12:15 pm
What do each of your disk represent? D, E, T, Z, Q, Y? For your avg. disk sec/write and read to be in seconds instead of ms, there is got to be performance issues
Thanks Mohit,
D - Primary data files
E - Log files
T - TempDB
Z - Backups
Q - Quorum
Y - msdtc
Here, why the Avg.sec/reads & Avg.sec/write should be measured in Seconds instead of ms?
As Microsoft best practices says:
Avg.sec/reads & Avg.sec/write < 5 ms is very good
Avg.sec/reads & Avg.sec/write < 10 ms is good
Avg.sec/reads & Avg.sec/write > 25 ms then we have real problem with Disk IO.
Here, I gathered the Avg.sec/reads & Avg.sec/write counter values and then multiplied the values by 1000 to get them in ms.
Thanks
August 20, 2010 at 9:46 am
Sorry for late reply.
Well its not as scary as it looked, heh I didn't know you already multiplied by 1000, so I was worried a bit heh.
Drive D seems to have high activity after 5PM, taking a guess backups kicked off? Other then that it is a bit on high side but it can be because of indexes, or bad sql being executed. How many databases reside on that server?
Drive E Pretty normal.
Drive T Seems like your write performance on drive T is bad right now, how many tempdb files you have. Might need to look at creating more tempdb files please look at Paul's article at (http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-%281230%29-tempdb-should-always-have-one-data-file-per-processor-core.aspx).
Other Drives I didn't look at, but drive Z: behavior is expected :).
Cheers.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply