locked
DISK IO Performance Issues RRS feed

  • Question

  • Hi All,

    Lately, I am having issues with the DISK performing very slow. The problem is intermittent and happens randomly. Below are the <g class="gr_ gr_72 gr-alert gr_spell gr_run_anim ContextualSpelling ins-del multiReplace" data-gr-id="72" id="72">perfmon</g> counters I have checked to identify the issue. 

    - % Disk Time Over 100% ( Varied Duration)

    - Average Disk Queue Length is around 3.

    -Average Disk Read/sec (50ms)

    -Average Disk Write/sec (15 ms)

    Any help here is highly appreciated.

    Regards,


    -<g class="gr_ gr_79 gr-alert gr_spell gr_run_anim ContextualSpelling ins-del multiReplace" data-gr-id="79" id="79">kccrga</g> http://dbatrend.blogspot.com.au/


    • Edited by kccrga Sunday, November 20, 2016 6:57 AM
    Sunday, November 20, 2016 6:56 AM

All replies

  • Dear KCCRGA

    i think you can find  clear related  disk resources utilization idea from system log or performance monitor

    for  more  pls check https://technet.microsoft.com/en-us/library/cc749115(v=ws.11).aspx

    Regards

    Saajid

     

    Sunday, November 20, 2016 10:42 AM
  • All disks under load will become overwhelmed. You need to ensure that your disk subsystem can handle your workload.

    Your disks apparently are good for most of your workload, but are unable to keep up with the demands at some point in time. You need to be able to  ensure that they have not deteriorated or that something has changed in your workload that they are unable to keep up with.

    Run perfmon counters over a week to determine what their latency is:

    Collect Avg. Disk sec/Read and Avg. Disk sec/Write at a minimum. If you can run the pal counters for SQL Server you can get from pal.codeplex.com. They will generate graphs and charts for you so you can zero in on the problem.

    Now I bet that there has been some code change or a bad execution plan which is causing your problem. You need to look at high IO consumers. This is a good start to do this:

    SELECT DB_NAME(fs.database_id) AS [Database Name] , mf.physical_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) AS fs INNER JOIN sys.master_files AS mf WITH ( NOLOCK ) ON fs.database_id = mf.database_id AND fs.[file_id] = mf.[file_id] ORDER BY avg_io_stall_ms DESC OPTION ( RECOMPILE );

    Courtesy of Glenn Berry.

     

    Sunday, November 20, 2016 12:27 PM
  • > The problem is intermittent and happens randomly.

    That is the sign of a "killer" query.

    Try to identify the query which causes the overload.

    SQL SERVER – Find Most Expensive Queries Using DMV

    How to Identify Slow Running Queries with SQL Profiler



    Kalman Toth Database & OLAP/AI Architect SQL Server 2016 Database Design
    New Book : Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016


    Sunday, November 20, 2016 2:02 PM
  • There is 2 primary counters you may look at if you think you have disk performance issue.

    From Performance Monitor (perfmon.exe)

    "\LogicalDisk(*)\Avg. Disk sec/Read"
    "\LogicalDisk(*)\Avg. Disk sec/Write"

    Both have the same thresholds:

    < 15ms --> Ok
    Between 15ms and 25ms --> Warning
    > 25ms --> Critical

    The perfmon counter "\LogicalDisk(*)\Avg. Disk Queue Length" could be use to confirm disk IO issue if  the 2 primary counters are over the thresholds.  But it also depends on how many spindles you have.  If you are using a SAN, it could be hard to know this.

    You could also use PAL http://pal.codeplex.com to generate a Perfmon template and then use the same tool to generate a report on your current server.

    hth


    This posting is provided AS IS without warranty of any kind

    Sunday, November 20, 2016 2:38 PM