PAGEIOLATCH_SH wait time is 80%

Hi,
One of my sql job contain 80% read & 20% insert /updates.
This job also contains number of cursor operations. It use to run for 2Hrs
or less and now it runs for 8hrs.
When I check the process wait time. The PAGEIOLATCH_SH is 80% and
PAGEIOLATCH_EX is 10%.
I am not sure what is causing this high PAGEIOLATCH_SH wait time?
Any thoughts on what could be the issue here?
Regards,
Sarav.Maybe http://sqldev.net/articles/WaitTypes.htm can help you.
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-2005 All rights reserved.
"sarav" <orumurai@.yahoo.cmom> wrote in message
news:ugiqcPlAFHA.2584@.TK2MSFTNGP09.phx.gbl...
> Hi,
>
> One of my sql job contain 80% read & 20% insert /updates.
> This job also contains number of cursor operations. It use to run for 2Hrs
> or less and now it runs for 8hrs.
>
> When I check the process wait time. The PAGEIOLATCH_SH is 80% and
> PAGEIOLATCH_EX is 10%.
>
> I am not sure what is causing this high PAGEIOLATCH_SH wait time?
>
> Any thoughts on what could be the issue here?
>
> Regards,
> Sarav.
>|||I'm going to venture a guess here, but I'd bet that your use of cursors is
at the root of your problem. Resource utilization by cursors has a way of
growing in non-linear ways. Some of the gurus here might be able to offer
set based solutions if you can succinctly describe the process.
Bob Castleman
SuccessWare Software
"sarav" <orumurai@.yahoo.cmom> wrote in message
news:ugiqcPlAFHA.2584@.TK2MSFTNGP09.phx.gbl...
> Hi,
>
> One of my sql job contain 80% read & 20% insert /updates.
> This job also contains number of cursor operations. It use to run for 2Hrs
> or less and now it runs for 8hrs.
>
> When I check the process wait time. The PAGEIOLATCH_SH is 80% and
> PAGEIOLATCH_EX is 10%.
>
> I am not sure what is causing this high PAGEIOLATCH_SH wait time?
>
> Any thoughts on what could be the issue here?
>
> Regards,
> Sarav.
>|||Hi Sarav
The process wait time % split seems normal to me given your estimate of 80%
read / 20% insert / updates. The PAGEIOLATCH_SH represents the shared locks
taken under the 80% read operations and thePAGEIOLATCH_EX represents the
exclusive locks required to perform the insert / updates.
Perhaps the post re cursors has some merit. I'd also be looking at general
resource availability (memory / cache stats / lock blocks) and whether the
sql job is being blocked by other processes.
HTH
Regards,
Greg Linwood
SQL Server MVP
"sarav" <orumurai@.yahoo.cmom> wrote in message
news:ugiqcPlAFHA.2584@.TK2MSFTNGP09.phx.gbl...
> Hi,
>
> One of my sql job contain 80% read & 20% insert /updates.
> This job also contains number of cursor operations. It use to run for 2Hrs
> or less and now it runs for 8hrs.
>
> When I check the process wait time. The PAGEIOLATCH_SH is 80% and
> PAGEIOLATCH_EX is 10%.
>
> I am not sure what is causing this high PAGEIOLATCH_SH wait time?
>
> Any thoughts on what could be the issue here?
>
> Regards,
> Sarav.
>|||That means you have a heck of a lot of disk access going on. I would ensure
the operations are well tuned first.
--
Andrew J. Kelly SQL MVP
"sarav" <orumurai@.yahoo.cmom> wrote in message
news:ugiqcPlAFHA.2584@.TK2MSFTNGP09.phx.gbl...
> Hi,
>
> One of my sql job contain 80% read & 20% insert /updates.
> This job also contains number of cursor operations. It use to run for 2Hrs
> or less and now it runs for 8hrs.
>
> When I check the process wait time. The PAGEIOLATCH_SH is 80% and
> PAGEIOLATCH_EX is 10%.
>
> I am not sure what is causing this high PAGEIOLATCH_SH wait time?
>
> Any thoughts on what could be the issue here?
>
> Regards,
> Sarav.
>