locked
Suddenly database IO(40 MB/sec) going very high RRS feed

  • Question

  • Hello Everyone,

    One of our organization SQL server  database IO(40 MB/sec) going very high without any changes in database, past few days everything working fine but now getting in problem and wait type getting IO_COMPLETION in every running task..

    getting any problem if 2 network card bridging or timing at OS level...

    its very tedious job to identify.

    Plz suggest me to resolve my problem.....

    thanks in advance

    Wednesday, December 10, 2014 7:49 AM

Answers

All replies

  • Hello,

    Are these read or write operations or both? Have you checked the currently running SQL statements? What about other performance counter like Page Life Expectancy (PLE) Counter


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, December 10, 2014 8:31 AM
  • check this blog - http://www.sqlskills.com/blogs/paul/causes-io_completion-write_completion-sql-server-wait-types/

    IO_COMPLETION

    • Reading log blocks from the transaction log (during any operation that causes the log to be read from disk – e.g. recovery)
    • Reading allocation bitmaps from disk (e.g. GAM, SGAM, PFS pages) during many operations (e.g. recovery, DB startup, restore)
    • Writing intermediate sort buffers to disk (these are called ‘Bobs’)
    • Reading and writing merge results from/to disk during a merge join
    • Reading and writing eager spools to disk
    • Reading VLF headers from the transaction log

    Do you have log files in seperate disks? Check the disks where log files are placed and check the log files properties. Check if any of the above operations are happening more recently.


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    Wednesday, December 10, 2014 9:09 AM
  • hello Olaf,

    both read and write operations.

    everything is fine, but its happen suddenly..

    PLE is more than 300..

    but one counter increase instantly i.e. paging file is  7, paging file set according to server RAM...

    how its possible to increase paging file... not operation perform through the network like copying and pasting a file on disk, than how it possible to increase a paging file...


    • Edited by Baraiya Kirit Wednesday, December 10, 2014 11:32 AM changing content
    Wednesday, December 10, 2014 11:31 AM
  • PLE above 300 does not means its good value PLE of 400 on 40 G RAM could be very bad value. Plus if you have NUMA system avoid looking at PLE it would show incorrect value instaed look at PLE for each NUMA node.

    IO_Completion can have couple of reasons my bet is queries are doing scans which ideally should have done seek and thus causing issue. You need to find out root cause. Ask storage team to check disk speed as well


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP


    Wednesday, December 10, 2014 11:47 AM
  • PLE is 98000...

    buffer cache hit ratio is 100....

    What to do next if buffer cache hit ratio is high....

    Thursday, December 11, 2014 10:29 AM
  • Brent Ozar has a great procedure to run exactly when it happens to see what is going on

    http://www.brentozar.com/askbrent/


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, December 11, 2014 10:37 AM
    Answerer