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

PAGEIOLATCH WAIT

Can someone help me understand the various causes of
pageio latch waits or point me in the direction of a good
resource on this subject (BOL info is not detailed
enough)? There are no other users on the system - and my
process is experiencing pageio latch waits. I'm guessing
that this issue is related to the disk subsystem, but
don't know for sure.superboy, you are right most times I have experienced pageiolatch issues hav
e been disk sub-system driver issues,
raid controller issue and usually anything related to H/W(disk) configuratio
n/problems|||See http://SQLDev.Net/misc/waittypes.htm
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2004 All rights reserved.
"superboy" <anonymous@.discussions.microsoft.com> wrote in message
news:d5b601c3ef20$380b9160$a601280a@.phx.gbl...
> Can someone help me understand the various causes of
> pageio latch waits or point me in the direction of a good
> resource on this subject (BOL info is not detailed
> enough)? There are no other users on the system - and my
> process is experiencing pageio latch waits. I'm guessing
> that this issue is related to the disk subsystem, but
> don't know for sure.

PAGEIOLATCH WAIT

Can someone help me understand the various causes of
pageio latch waits or point me in the direction of a good
resource on this subject (BOL info is not detailed
enough)? There are no other users on the system - and my
process is experiencing pageio latch waits. I'm guessing
that this issue is related to the disk subsystem, but
don't know for sure.superboy, you are right most times I have experienced pageiolatch issues have been disk sub-system driver issues
raid controller issue and usually anything related to H/W(disk) configuration/problems|||See http://SQLDev.Net/misc/waittypes.htm
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2004 All rights reserved.
"superboy" <anonymous@.discussions.microsoft.com> wrote in message
news:d5b601c3ef20$380b9160$a601280a@.phx.gbl...
> Can someone help me understand the various causes of
> pageio latch waits or point me in the direction of a good
> resource on this subject (BOL info is not detailed
> enough)? There are no other users on the system - and my
> process is experiencing pageio latch waits. I'm guessing
> that this issue is related to the disk subsystem, but
> don't know for sure.

Pageiolatch ??

Hello all,

I have an aggregation job that was taking about 2h on a 20 mil records database. Since some 5 milion new records were inserted 2 days ago, the job is now taking more than 10h. I updated statistics, recompiled all the stored procedures and defraged all the indexes. Still 10h. I deleted those 5 mil records, still 10h. The database file is not fragmented, as it is the only file on that drive. All the data is local, nothing is transferred over the network. Nothing else is running on that machine, just SQL Server. I can see the job having long waiting times, reason being PAGEIOLATCH_UP and PAGEIOLATCH_SH.

Is there something else I can do to bring the job back to 2h?

Thanks a lot! Any idea will be appreciated!Have you tried rebuilding the indexes? I have found that when SQL Server indexes get heavy insert/delete action without rebuilding, they can sometimes give unexpectly big performance issues. (Defraging them did not make a lot of difference)|||Yes, tried it ... no luck :-(

Thanks!

Originally posted by dbabren
Have you tried rebuilding the indexes? I have found that when SQL Server indexes get heavy insert/delete action without rebuilding, they can sometimes give unexpectly big performance issues. (Defraging them did not make a lot of difference)

Pageiolatch

Can anybody point me in the direction of some information on pageiolatch's?
Can't seem to find much info on them.
thanks
GavThere's not much public info on them right now. MS is working on a white
paper that describes this in more detail.
This type of latch indicates a wait on disk to memory transfers. You can
find out what object is being waited on by looking at the waitresource
column in master..sysprocesses. You'll need to use the dbcc page command to
figure out what the actual object is. The value in waitresource will be of a
format like this:
1:1:140
That's DBID:FileID:PageId.
I don't have the dbcc page command docs handy but you should be able to
google it easy enough.
DBCC PAGE will allow you to figure out what objectId owns the page so you'll
know which object was waiting on the pageiolatch to complete.
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Gav" <gavin.metcalfe@.portakabinnospam.com> wrote in message
news:bpcsdp$6k9$1@.sparta.btinternet.com...
> Can anybody point me in the direction of some information on
pageiolatch's?
> Can't seem to find much info on them.
> thanks
> Gav
>