IO performance check for SQL Server

The one-month-old was entangled in an information section chief because their SQL Server, R2, was always performing poorly on regular writes. I think so, but people around me don't seem to think so. When I express my concern about the networked storage that hangs on a switch, this chief always disagrees.

Until the information provided by performance monitoring on the window's management tools was utilized, they were focused back on the store.

Check for three things:

1.Average disk Sec/read, average per read time, less than 10ms represents a good performance, between 10ms~20ms performance can be acceptable, if greater than 20ms, indicating that there is an I/O problem;
2.Average disk sec/write, average per write time, less than 10ms represents a good performance, between 10ms~20ms performance can be acceptable, if greater than 20ms, indicating that there is an I/O problem.
3.Average Disk Queue Length, the number of disks to wait for IO, preferably within 1.5 and twice times the number of spindles (spindles), if above this value, it is generally indicated that the IO subsystem has some problems, but now widespread use of RAID or disk virtualization, It is difficult to determine the problem from this counter if you do not have a detailed understanding of the specific I/O subsystem configuration.

Plus a similar number of large files on the SQL, all the files stored in the H-disk (network storage) Read and write time is very long, the problem should be clear

Select Db_name (vfs.database_id) as ' Database NAME ',
Smf.physical_name,
case if Smf.type = 1 Then ' log_file ' ELSE ' data_file ' END as Databasefile_type,
Vfs.io_stall_read_ms/vfs.num_of_reads as ' Avg read Trans/ms ',
Vfs.io_stall_write_ms/vfs.num_of_writes as ' Avg write Trans/ms '
From Sys.dm_io_virtual_file_stats ( -1,-1) VFS
JOIN Sys.master_files as SMF
On vfs.database_id = smf.database_id
and vfs.file_id = smf.file_id
where Num_of_reads>0 and num_of_writes>0

Here are the pages I studied

http://blog.csdn.net/leamonjxl/article/details/6790832

http://blog.csdn.net/dba_huangzj/article/details/7773744

IO performance check for SQL Server