locked
Monitoring Disk IO RRS feed

  • Question

  • hi
    How to monitor DISK IO for SQL server 2005?
    and how do we know IO bottleneck issues in our SQL Servers?

    Ragu Thangavel
    Saturday, June 11, 2011 9:32 AM

Answers

  • select 
          pending_disk_io_count 
    from 
          sys.dm_os_schedulers
    --Look at pending I/O requests by file
    SELECT DB_NAME(mf.database_id) AS [Database], mf.physical_name, 
    r.io_pending, r.io_pending_ms_ticks, r.io_type, fs.num_of_reads, fs.num_of_writes
    FROM sys.dm_io_pending_io_requests AS r
    INNER JOIN sys.dm_io_virtual_file_stats(null,null) AS fs
    ON r.io_handle = fs.file_handle 
    INNER JOIN sys.master_files AS mf
    ON fs.database_id = mf.database_id
    AND fs.file_id = mf.file_id
    ORDER BY r.io_pending, r.io_pending_ms_ticks DESC;
    You can use the following DMV query to find
     currently pending I/O requests. 
    You can execute this query periodically to check the health of I/O subsystem and 
    to isolate physical disk(s) that are involved in the I/O bottlenecks.
    select  
        database_id,  
        file_id,  
        io_stall, 
        io_pending_ms_ticks, 
        scheduler_address  
    from    sys.dm_io_virtual_file_stats(NULL, NULL)t1, 
            sys.dm_io_pending_io_requests as t2 
    where    t1.file_handle = t2.io_handle
    A sample output is as follows. It shows that on a given database,
     there are three pending I/Os at this moment. 
    You can use the database_id and file_id to find the physical disk the
     files are mapped to. The io_pending_ms_ticks represent the total time individual 
    I/Os are waiting in the pending queue

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, June 12, 2011 7:00 AM
    Answerer

All replies

  • 1. Look for the waittype in sysprocesses table and see if you have *IO* waits. Please consider waittime also when you look at the output.
    2. Have a look at SQL ERRORLOG if you see any warning about "IO taking longer than 15 seconds"
    3. Capture perfmon (start>run>perfmon) and have a look at physical disk counter. Specailly look at Avg Disk sec/read, Avg Disk sec/write, Avg Disk sec/transfer. Ideally value should be as low as possible. .010 (10 ms per operaion) is best value. anything beyond 50, throw the server.


    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter
    Saturday, June 11, 2011 10:01 AM
  • select 
          pending_disk_io_count 
    from 
          sys.dm_os_schedulers
    --Look at pending I/O requests by file
    SELECT DB_NAME(mf.database_id) AS [Database], mf.physical_name, 
    r.io_pending, r.io_pending_ms_ticks, r.io_type, fs.num_of_reads, fs.num_of_writes
    FROM sys.dm_io_pending_io_requests AS r
    INNER JOIN sys.dm_io_virtual_file_stats(null,null) AS fs
    ON r.io_handle = fs.file_handle 
    INNER JOIN sys.master_files AS mf
    ON fs.database_id = mf.database_id
    AND fs.file_id = mf.file_id
    ORDER BY r.io_pending, r.io_pending_ms_ticks DESC;
    You can use the following DMV query to find
     currently pending I/O requests. 
    You can execute this query periodically to check the health of I/O subsystem and 
    to isolate physical disk(s) that are involved in the I/O bottlenecks.
    select  
        database_id,  
        file_id,  
        io_stall, 
        io_pending_ms_ticks, 
        scheduler_address  
    from    sys.dm_io_virtual_file_stats(NULL, NULL)t1, 
            sys.dm_io_pending_io_requests as t2 
    where    t1.file_handle = t2.io_handle
    A sample output is as follows. It shows that on a given database,
     there are three pending I/Os at this moment. 
    You can use the database_id and file_id to find the physical disk the
     files are mapped to. The io_pending_ms_ticks represent the total time individual 
    I/Os are waiting in the pending queue

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, June 12, 2011 7:00 AM
    Answerer