locked
Buffer i/o very high RRS feed

  • Question

  • Hi,

    I have issue with my database. it keeps up and down now. when i check the buffer i/o waits is very high..can please suggest me what caused this buffer io issue and how to check and fix.

    Thanks.


    kneeah
    Thursday, July 7, 2011 1:20 AM

Answers

  • I have issue with my database. it keeps up and down now. when i check the buffer i/o waits is very high..can please suggest me what caused this buffer io issue and how to check and fix.

    I'm going to guess that some of your code and data model needs work, you are doing a lot of scans of big tables.

    Look at your long-running queries, look at your missing indexes report, and the problems will probably become clear to you, along with the solutions!

    Josh

     

    • Proposed as answer by WeiLin Qiao Monday, July 11, 2011 8:33 AM
    • Marked as answer by kneeah Tuesday, August 16, 2011 8:15 AM
    Friday, July 8, 2011 3:01 PM

All replies

  • wahts the RAM on this server.

    Generally low ram will cause buffer cache hit ratio and buffer i/o waits.

    thanks


    Thanks, Satish Kumar.
    Thursday, July 7, 2011 2:02 AM
  • RAM size : 2.54Gb

    Current available: 856Mb


    kneeah
    Thursday, July 7, 2011 2:54 AM
  • Could you please tell us how you check the buffer I/O waits and if you could share some data/logs ?
    Harsh Chawla Personal Blog:- http://blogs.msdn.com/batala Team Blog:- http://blogs.msdn.com/b/sqlserverfaq/
    Thursday, July 7, 2011 8:27 AM
  • Hi,

    I check the wait from the activity monitor on ssms.


    kneeah
    Friday, July 8, 2011 2:17 AM
  • How do you find an issue, ups and down is ok, unless anybody complains.

    is query on that database is having issue? anyone says their application is slow? if not let it be.

    btw: having RAM of ~3gb is not a big one. what is the size of your DB

    use sp_spaceused and send the output.

    also how many other application are running on this server/is it dedicated to sql server?

    HTH

    Vinay

     


    Vinay Thakur http://rdbmsexperts.com/Blogs http://vinay-thakur.spaces.live.com/ http://twitter.com/ThakurVinay
    Friday, July 8, 2011 11:14 AM
  • What is the size of TEMPDB in this case?

     

    For more information about one of the possible causes of BUF latch contention, click the following article number to view the article in the Microsoft Knowledge Base: 328551  (http://support.microsoft.com/kb/328551/ ) FIX: Concurrency enhancements for the tempdb database.

    Also my blog http://sqlserver-qa.net/blogs/t-sql/archive/2009/12/21/6165.aspx post.


    Satya SKJ, SQL Server MVP [Knowledge Sharing Network - www.sqlserver-qa.net]
    Author of SQL Server 2008 R2 Administration cookbook.
    Follow me @sqlmaster.
    • Proposed as answer by WeiLin Qiao Monday, July 11, 2011 8:33 AM
    Friday, July 8, 2011 12:24 PM
  • I have issue with my database. it keeps up and down now. when i check the buffer i/o waits is very high..can please suggest me what caused this buffer io issue and how to check and fix.

    I'm going to guess that some of your code and data model needs work, you are doing a lot of scans of big tables.

    Look at your long-running queries, look at your missing indexes report, and the problems will probably become clear to you, along with the solutions!

    Josh

     

    • Proposed as answer by WeiLin Qiao Monday, July 11, 2011 8:33 AM
    • Marked as answer by kneeah Tuesday, August 16, 2011 8:15 AM
    Friday, July 8, 2011 3:01 PM
  • please post sys.dm_exec_query_statistics and sys.dm_os_buffer_descriptors DMV output
    Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    Friday, July 8, 2011 3:11 PM