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, TrinI 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.|||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.|||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?|||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?|||do you know how to use profiler?|||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.|||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.|||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