PAGEIOLATCH_SH occupies a long time.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

George Shui - 28 Sep 2008 07:07 GMT

Hi experts,

I have run a sql in SQL Server 2005. I run this sql very fast (about 1~3
seconds) in other db servers, but in one db server, it runs very slow, even
it cannot complete in 15 minutes.

I have checked the db activity, I found this sql is suppened because of
PAGEIOLATCH_SH.

Using Performance monitor, I found the Disk Avg Queue Length is almost 100%.

Also the Disk/Page is also high.

Attached is the sql.

select *
from (select a.RequestXml from table1
inner join table2 on table1.RequestID=table2.RequestID
where table2.RCode='1') t
where t.PGRequestXml like '%abc%'

BTW, if I run
(select a.RequestXml from table1
inner join table2 on table1.RequestID=table2.RequestID
where table2.RCode='1')
independently, it runs very fast and the result is an empty table.

Any suggestion is more than welcome.

Thanks & Regard
George.

Reply to this Message

John Bell - 28 Sep 2008 11:05 GMT

> Hi experts,
>
[quoted text clipped - 29 lines]
> Thanks & Regard
> George.

Hi

You may want to post DDL, I would assume that column a is a CLR datatype?

John

Reply to this Message

George Shui - 29 Sep 2008 00:49 GMT

That column is text.

> Hi
>
> You may want to post DDL, I would assume that column a is a CLR datatype?
>
> John

Reply to this Message

Aaron Bertrand [SQL Server MVP] - 29 Sep 2008 01:19 GMT

Why would you use TEXT in SQL Server 2005? If it is valid XML, why not use
XML? Otherwise, why not use VARCHAR(MAX)? Anyway PageIOLatch_SH is usually
indicative of either poorly performing I/O subsystem (due to hardware) or
pressure from other resources (e.g. memory) that lead to it. Have you
looked at any performance monitor counters other than disk queue length?
What is the difference in configuration between this server and the ones
where this issue doesn't occur? Does this server have the same amount of
physical memory, or less? How is max memory configured? Are both systems
set up for AWE/PAE? What is the amount of RAM installed, and how much is
SQL Server allowed to use? Are they using the same type of disk, or is the
problem system using SAN, NAS, local disk...? Lots and lots of questions,
but without treating them you won't get too many answers, sorry.

On 9/28/08 7:49 PM, in article
2DA391EC-010E-4AB5-B8D0-C70B87D062E4@microsoft.com, "George Shui"
<GeorgeShui@discussions.microsoft.com> wrote:

> That column is text.
>
[quoted text clipped - 3 lines]
>>
>> John

Reply to this Message

TheSQLGuru - 30 Sep 2008 19:09 GMT

As usual, Aaron is spot-on. RAM and IO.

Signature

Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net

> Why would you use TEXT in SQL Server 2005? If it is valid XML, why not
> use
[quoted text clipped - 24 lines]
>>>
>>> John

Reply to this Message

John Bell - 29 Sep 2008 07:45 GMT

> That column is text.
>
[quoted text clipped - 3 lines]
>>
>> John

Hi

So there is not really any need for a derived table?

Disk Queue Length is a numeric value so 100% is not a valid value. You don't
say if it a sustained peak or just a peak. Disk Queue length is not always a
reliable value depending on your disc subsystem, but it does seem you do
have a I/O problem see
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx. The
first thing I would do is look at what discs you have and what is on them.

John

Reply to this Message