Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2005
    Posts
    183

    Unanswered: Pageiolatch, my nightmare returns

    I bet every SQL-Server guy in this forums has fought pageiolatch issues from one time to another. Either bad indexing, overloaded disks / controllers etc. I know I have, with a great variety of systems and solutions.

    Anyways.... I simply have to ask for any of your experiences, as I'm currently stuck with a Navision client, that ocassionally stalls on pageiolatch on even simple queries.

    User selects a customer account, asks to see his / her details, scrolls up and down a bit, and whammo, stall, and pageiolatch appears on the server.

    Something tells me that this might be an ODBC driver issue rather than actual sql-server issues. Fetching the initial data isn't the issue, but scroling back and forth in the recorsset it seems to cause the cursor to freeze or at least lose track or stall in the progress of paging through the records.

    All of the above sounds very unclear, I know, I'm just trying tro track down or center on the problem, so that I might find a solution. So if you have any experiences with clients, cursors and odbc driver version for sql-server, that really make a lot of trouble, let me know.

    Cheers, Trin
    IT squid: networks, servers, firewalls, routers and I dabble a little with SQL-server and Access as well....

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Trinsan
    I bet every SQL-Server guy in this forums has fought pageiolatch issues from one time to another.
    You lose. I've been working with SQL Server nearly 10 years and consulted for dozens of clients, and I've never dealt with this issue.

    Did I read the magic word "cursors"? The root of many performance problems.

    Post your code, please.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Aug 2004
    Location
    Pune,India
    Posts
    94
    Also, it could be some bad code that's unnecessarily keeping the cursor open for no apparant reason. DO check the frequency of "select" statements running on the DB and on some particular table which fetches the data for above operation.

    I've faced somewhat similar problem while somebody had written a bad Select query on a heavily used table.

    Hope this helps.
    In GOD we believe. Everything else we Test!

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Can't say as I have run up against PAGEIOLATCH problems myself. A couple NETWORK_IO (and when were you planning on committing that transaction?!?), and plenty of tablescan problems. It could be that PAGEIOLATCHes are repreentative of tablescanning: http://www.sqldev.net/misc/waittypes.htm This reference lists "cache contention" as a cause. How are your Page Life Expectancy and Buffer Cache Hit Ratio counters in perfmon?

  5. #5
    Join Date
    Oct 2005
    Posts
    183
    Quote Originally Posted by blindman
    You lose. I've been working with SQL Server nearly 10 years and consulted for dozens of clients, and I've never dealt with this issue.

    Did I read the magic word "cursors"? The root of many performance problems.

    Post your code, please.
    I don't have access to the code, as the client is Navision. (commonly known Microsoft Business Solutions Attain)...

    I was wondering, if its possible to trace the sql-code being sent via odbc to the server from the client? or would that be useless with cursor based clients?
    IT squid: networks, servers, firewalls, routers and I dabble a little with SQL-server and Access as well....

  6. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    do you know how to use profiler?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I think he means in profiler he is seeing a lot of sp_cursorexecute, sp_prepare, sp_prepexec, etc. These are a true pain in the neck to hunt down. So far, I have only been able to do it manually in the trace. It is easier, if you can narrow it down to one spid.

  8. #8
    Join Date
    Oct 2005
    Posts
    183
    Wel, I only know how to log ODBC communications, but I really need a good tool to browse through the output?!?

    I don't knw how to use the Profiler tool that comes with SQL-Server.. sadly.
    IT squid: networks, servers, firewalls, routers and I dabble a little with SQL-server and Access as well....

  9. #9
    Join Date
    Aug 2007
    Posts
    1

    status?

    Hello Trinsan,

    I never saw any resolution to your issues. Did you ever optimize your ERP environment? You're obviously on Navision SQL option, and if using sql2005, there are lots of DMV views to help analyze indexes. I've found that cache hit ratios were not related to the PageioLatches in our cases, however the size of the tables and fill factors and re-indexing seemed to help things temporarily. There is nav v4 sp3 update 5 that just came out in aug2007, which optimizes some queries at the SQL level. However I would be interested to know what you had done...

    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •