Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
raguyazhin
Posting Yak Master
105 Posts |
 Posted - 2011-06-11 : 05:30:04
|
| hiHow to monitor DISK IO for SQL server 2005?and how do we know IO bottleneck issues in our SQL Servers?--Ragu |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2011-06-12 : 01:35:13
|
| There are different methodologies. It will depend on subsystem , nature of problem. A common approach is to initially use WAITSTATS , and see if there is a high level of ASYNC_IO_COMPLETION and then drill down into Perfmon , and monitor . Counters in Perfmon, such as Average Disk Qiueue Length and Avg.Disk Sec Write or Avg.Disk Sec Read are good. Which one you use will depend on such factors as : a)Are you using a SAN? If you used this approach - and , for example were using Avg.Disk Sec Write and response was consistently greater than 50 ms - then you have a serious bottleneck . It is important to benchmark - so you've got something to compareJack Vamvas--------------------http://www.sqlserver-dba.com |
 |
|
|
Jahanzaib
Posting Yak Master
115 Posts |
Posted - 2011-06-13 : 03:11:36
|
| select DB_NAME(database_id) DB_NAME, di.file_id,df.name,io_stall_read_ms ,num_of_reads,cast(io_stall_read_ms/(1.0+num_of_reads) as numeric(10,1)) as 'avg_read_stall_ms',io_stall_write_ms,num_of_writes ,cast(io_stall_write_ms/(1.0+num_of_writes) as numeric(10,1)) as 'avg_write_stall_ms',io_stall_read_ms + io_stall_write_ms as io_stalls ,num_of_reads + num_of_writes as total_io,cast((io_stall_read_ms+io_stall_write_ms)/(1.0+num_of_reads + num_of_writes) as numeric(10,1)) as 'avg_io_stall_ms'from sys.dm_io_virtual_file_stats(null,null) di inner join sys.database_files df on df.file_id = di.file_idwhere DB_NAME(database_id) = 'your database name'order by avg_io_stall_ms desccheck Files delays of your databaseSELECT ST.TEXT,SP.SPID,WAITTIME,LASTWAITTYPE,CPU,PHYSICAL_IO,STATUS,HOSTNAME,PROGRAM_NAME,CMD,LOGINAME FROM SYS.SYSPROCESSES SPCROSS APPLY SYS.DM_EXEC_SQL_TEXT(SP.SQL_HANDLE) STWHERE STATUS !='SLEEPING'ORDER BY CPU DESCcheck physical IO in a particular query and then optimize itRegards,Syed Jahanzaib Bin HassanMCTS,MCITP,OCA,OCP,OCE,SCJP,IBMCDBAMy Blogwww.aureus-salah.com |
|
|
|
|
|
|
|
|
DISK IO