none
SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file RRS feed

  • 问题

  • SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [E:\Data\templog.ldf] in database [tempdb] (2).  The OS file handle is 0x0000000000000CE4.  The offset of the latest long I/O is: 0x00000015f46600

    后台是连接的SAN,SAN那边配置以及其FIrmware是OK的。

    看起来是过度的使用了tempdb。

    我要如何查看到底是哪些SQL引起tempdb非常大的IO呢?



    If you haven't all the things you want,be grateful for the things you don't have that you didn't want.

    2013年8月1日 3:16

答案

  • This is storage related issue, double check SAN link and windows event logs.
    2013年8月1日 4:36
  • 查一下硬件有没有问题。如果查询IO是用最多的SQL用:

    SELECT TOP 5
        (total_logical_reads/execution_count) AS avg_logical_reads,
        (total_logical_writes/execution_count) AS avg_logical_writes,
        (total_physical_reads/execution_count) AS avg_phys_reads,
        execution_count,
        statement_start_offset as stmt_start_offset,
        (SELECT SUBSTRING(text, statement_start_offset/2 + 1,
            (CASE WHEN statement_end_offset = -1
                THEN LEN(CONVERT(nvarchar(MAX),text)) * 2
                    ELSE statement_end_offset
                END - statement_start_offset)/2)
         FROM sys.dm_exec_sql_text(sql_handle)) AS query_text,
          (SELECT query_plan from sys.dm_exec_query_plan(plan_handle)) as query_plan
    FROM sys.dm_exec_query_stats 
    ORDER BY (total_logical_reads + total_logical_writes)/execution_count DESC


    Please Mark As Answer if it is helpful.

    2013年8月1日 4:43
  • --可以通过下面的查询了解哪个文件经常要做读(num_of_reads/num_of_bytes_read),经常要做写
    --(num_of_writes/num_of_bytes_written),经常读写要等待(io_stall_write_ms/io_stall_read_ms/io_stall)
    SELECT
    db.name AS databasename,
    f.fileid AS fileid,
    f.filename AS FILENAME,
    i.num_of_reads AS numofread,
    i.num_of_bytes_read AS numofbyteread,
    i.io_stall_read_ms AS ioinstallread,
    i.num_of_writes AS numofwrite,
    i.num_of_bytes_written AS numofbytewrite,
    i.io_stall_write_ms AS iostallwrite,
    i.io_stall AS iostall,
    i.size_on_disk_bytes AS sizeondiskbyte
    from sys.databases db INNER JOIN
    sys.sysaltfiles f ON db.database_id=f.dbid
    INNER JOIN sys.dm_io_virtual_file_stats(NULL,null) i
    ON i.database_id=f.dbid AND i.file_id=f.fileid
    
    ----------------------------------------------------------------------------------------------------
    SELECT
    db.name AS databasename,
    f.fileid AS fileid,
    f.filename AS FILENAME,
    i.num_of_reads AS numofread,
    i.num_of_bytes_read AS numofbyteread,
    i.io_stall_read_ms AS ioinstallread,
    i.num_of_writes AS numofwrite,
    i.num_of_bytes_written AS numofbytewrite,
    i.io_stall_write_ms AS iostallwrite,
    i.io_stall AS iostall,
    i.size_on_disk_bytes AS sizeondiskbyte
    from sys.databases db INNER JOIN
    sys.sysaltfiles f ON db.database_id=f.dbid
    INNER JOIN sys.dm_io_virtual_file_stats(DB_ID('gposdb'),null) i
    ON i.database_id=f.dbid AND i.file_id=f.fileid
    
    
    --动态管理视图sys.dm_io_pending_io_requests
    --当前SQL中每个处于挂起状态的I/O请求
    SELECT
    database_id AS dbid,
    file_id AS fileid,
    io_stall AS iostall,
    io_pending_ms_ticks AS iopendingmstick,
    scheduler_address AS scheduleraddress
    from sys.dm_io_virtual_file_stats(NULL,null) as t1,
    sys.dm_io_pending_io_requests AS t2
    WHERE t1.file_handle=t2.io_handle

    2013年8月1日 13:53

全部回复

  • This is storage related issue, double check SAN link and windows event logs.
    2013年8月1日 4:36
  • 查一下硬件有没有问题。如果查询IO是用最多的SQL用:

    SELECT TOP 5
        (total_logical_reads/execution_count) AS avg_logical_reads,
        (total_logical_writes/execution_count) AS avg_logical_writes,
        (total_physical_reads/execution_count) AS avg_phys_reads,
        execution_count,
        statement_start_offset as stmt_start_offset,
        (SELECT SUBSTRING(text, statement_start_offset/2 + 1,
            (CASE WHEN statement_end_offset = -1
                THEN LEN(CONVERT(nvarchar(MAX),text)) * 2
                    ELSE statement_end_offset
                END - statement_start_offset)/2)
         FROM sys.dm_exec_sql_text(sql_handle)) AS query_text,
          (SELECT query_plan from sys.dm_exec_query_plan(plan_handle)) as query_plan
    FROM sys.dm_exec_query_stats 
    ORDER BY (total_logical_reads + total_logical_writes)/execution_count DESC


    Please Mark As Answer if it is helpful.

    2013年8月1日 4:43
  • --可以通过下面的查询了解哪个文件经常要做读(num_of_reads/num_of_bytes_read),经常要做写
    --(num_of_writes/num_of_bytes_written),经常读写要等待(io_stall_write_ms/io_stall_read_ms/io_stall)
    SELECT
    db.name AS databasename,
    f.fileid AS fileid,
    f.filename AS FILENAME,
    i.num_of_reads AS numofread,
    i.num_of_bytes_read AS numofbyteread,
    i.io_stall_read_ms AS ioinstallread,
    i.num_of_writes AS numofwrite,
    i.num_of_bytes_written AS numofbytewrite,
    i.io_stall_write_ms AS iostallwrite,
    i.io_stall AS iostall,
    i.size_on_disk_bytes AS sizeondiskbyte
    from sys.databases db INNER JOIN
    sys.sysaltfiles f ON db.database_id=f.dbid
    INNER JOIN sys.dm_io_virtual_file_stats(NULL,null) i
    ON i.database_id=f.dbid AND i.file_id=f.fileid
    
    ----------------------------------------------------------------------------------------------------
    SELECT
    db.name AS databasename,
    f.fileid AS fileid,
    f.filename AS FILENAME,
    i.num_of_reads AS numofread,
    i.num_of_bytes_read AS numofbyteread,
    i.io_stall_read_ms AS ioinstallread,
    i.num_of_writes AS numofwrite,
    i.num_of_bytes_written AS numofbytewrite,
    i.io_stall_write_ms AS iostallwrite,
    i.io_stall AS iostall,
    i.size_on_disk_bytes AS sizeondiskbyte
    from sys.databases db INNER JOIN
    sys.sysaltfiles f ON db.database_id=f.dbid
    INNER JOIN sys.dm_io_virtual_file_stats(DB_ID('gposdb'),null) i
    ON i.database_id=f.dbid AND i.file_id=f.fileid
    
    
    --动态管理视图sys.dm_io_pending_io_requests
    --当前SQL中每个处于挂起状态的I/O请求
    SELECT
    database_id AS dbid,
    file_id AS fileid,
    io_stall AS iostall,
    io_pending_ms_ticks AS iopendingmstick,
    scheduler_address AS scheduleraddress
    from sys.dm_io_virtual_file_stats(NULL,null) as t1,
    sys.dm_io_pending_io_requests AS t2
    WHERE t1.file_handle=t2.io_handle

    2013年8月1日 13:53
  • 是极少数情况出现,还是定期性出现?

    通常不是负荷过大,检查一下磁盘是否有坏道。。。


    Try SQL Server 2008 QQ:315054403 dgdba@hotmail.com

    2013年8月2日 0:38