May 25, 2011 at 8:01 pm
SQL Server has encountered 111 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file path in database [name]
May 25, 2011 at 8:39 pm
can any one please tell me what could be the best resolution for this issue?i want to reduce the occurences
May 30, 2011 at 5:02 am
Its mean your IO system is busy and delays provide me result of this query
use [your database name]
Go
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_id
where DB_NAME(database_id) = 'your database'
order by avg_io_stall_ms desc
change the both parameter as your database name , how many drives you have, are you using SAN,NAS etc
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
September 7, 2011 at 6:05 am
I have the same issue.
I ran your query; what does it show?
Here is result:
DB_PROD 3 DB_PROD2 189381747532498435.650747388122057641.6240129135654556036.7
DB_PROD 1 DB_PROD1 90084640 322148328.03276864396798033.9122853283418946329.3
DB_PROD 4 DB_PROD1_log2 59 5 9.8 0 0 0.0 59 5 9.8
DB_PROD 2 DB_PROD1_log 1409263 228671 6.2 1527955 4395289 0.3 2937218 4623960 0.6
Our server has 24 processors, 17 drives, it's a active/passive cluster, running on SAN.
Thanks
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply