SQL Server has encountered 1 occurrence(s) of IO requests taking longer than 15 seconds

Question

SQL Server has encountered 1 occurrence(s) of IO requests taking longer than 15 seconds

Krasavita

SSC-Insane

Points: 23138
More actions
June 1, 2010 at 8:00 am

#94915

Guys, I got an error in SQL logs, anyone know what this means and what I need to do:
SQL Server has encountered 1 occurrence(s) of IO requests taking longer than 15 seconds to complete on file [E:\MSSQL\Data\Koka_CatalogScratch_Log.LDF] in database [Koka_CatalogScratch] (11). The OS file handle is 0x000005AC. The offset of the latest long IO is: 0x00000006c91a00
Thank you

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

george sibbald SSC Guru Points: 104210 More actions · Answer 1

this suggests your I/O subsystem is stressed if this is a a common occurrence.

This could be because the server is just not powerful enough to cope with the SQL load imposed on it or the disk system is not tuned properly (particularly if its a SAN).

so ask the system or storage guys to check out the disks and HBA settings and from your end look at things that affect IO, how the databases are laid out, when housekeeping jobs are run (tend to be heavy on IO) and that the IO required by SQL is minimised (so look at indexing strategy)

---------------------------------------------------------------------

dhechle SSC Eights! Points: 922 More actions · Answer 2

I had this error recently due to a failed disk in the SAN that was part of the RAID group the database existed on. Disk was swapped out and errors went away.

Krasavita SSC-Insane Points: 23138 More actions · Answer 3

My housekepping jobs are run on a different date. Example:this happend on Monday and optimizatation runs on thursday and integrity on wenesday. How would I check indexing strategy?

george sibbald SSC Guru Points: 104210 More actions · Answer 4

Krasavita (6/1/2010)
How would I check indexing strategy?

Thats a big question. you need to look for queries that are heavy on IO, they could be table scanning because an index is missing or because the way the query is written prevents the use of an index. Run profiler during your busy period and put the results through the database tuning advisor as a starting point. If you can run profiler whilst this error happens and identify queries running at the time all the better.

Use the DMVs to identify heavy IO queries -

--run the following query to get the top 10 costliest queries in terms of IO:

SELECT TOP 10

[Average IO] = (total_logical_reads + total_logical_writes) / qs.execution_count

,[Total IO] = (total_logical_reads + total_logical_writes)

,[Execution count] = qs.execution_count

,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,

(CASE WHEN qs.statement_end_offset = -1

THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2

ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)

,[Parent Query] = qt.text

,DatabaseName = DB_NAME(qt.dbid)

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt

ORDER BY [Average IO] DESC;

There are dmvs to help identify index usage (sys.dm_db_index_usage_stats ) and missing indexes, you can even use the standard reports in SSMS.

---------------------------------------------------------------------