MS SQL Data Warehousing - waittype=LATCH_EX with waitresource=PARALLEL_PAGE

Author

waittype=LATCH_EX with waitresource=PARALLEL_PAGE_SUPPLIER

Jéjé

2005-07-18, 11:23 am

Hi,

I have some issues with a server which is connected to a slow SAN.
the iometer utility says:
SAN= 260io/sec - 8mb/s (32kb block read/write)
Local C:=500io/sec - 15mb/s (32kb block read/write)

To try to improve my loading proces, I have added some files on the local C.
So my staging area database use 2 files 1 on the SAN the second on the C:
in some loading steps there is 50% of improvement while there is a little
loss in other steps.

with this change I see some waittype LATCH_EX with the
waitresource=PARALLE
L_PAGE_SUPPLIER information.

I have a big update statement on a 2millions rows table, and the waittype is
PAGEIOLATCH_SH

does it because 1 disk has a better throughput then the second?
Can I ask SQL Server to read/write 2 blocks on the local drive and only 1
block on the SAN?

I have no access to the configuration of the SAN. (but we ask the
administrator to do something!!!)

thanks for your comments.

Neil MacMurchy

2005-07-18, 1:23 pm

did you hear "Jéjé" < willgart_A_@hotmail_
A_.com> say in
news:eEqMmS7iFHA.1044@tk2msftngp13.phx.gbl:

> Hi,
>
> I have some issues with a server which is connected to a slow SAN.
> the iometer utility says:
> SAN= 260io/sec - 8mb/s (32kb block read/write)
> Local C:=500io/sec - 15mb/s (32kb block read/write)
>
> To try to improve my loading proces, I have added some files on the
> local C. So my staging area database use 2 files 1 on the SAN the
> second on the C: in some loading steps there is 50% of improvement
> while there is a little loss in other steps.
>
> with this change I see some waittype LATCH_EX with the
> waitresource=PARALLE
L_PAGE_SUPPLIER information.
>
> I have a big update statement on a 2millions rows table, and the
> waittype is PAGEIOLATCH_SH
>
> does it because 1 disk has a better throughput then the second?
> Can I ask SQL Server to read/write 2 blocks on the local drive and
> only 1 block on the SAN?
>
> I have no access to the configuration of the SAN. (but we ask the
> administrator to do something!!!)
>
> thanks for your comments.
>
>
>

Brian Moran covered it off in SQL ServerMagazine in July 2004:

http://www.windowsitpro.com/Article...ArticleID=42902
&DisplayTab=Article

latches are lightwieght locks (BOL covers latches - badly) and if you have
quite a few nonzero waittime pageiolatch_sh SPIDs you may have a disk
bottleneck (identifies connections waiting for SQL to read a page from disk
into RAM)

other considerations here are what level of RAID are you using and are the
files in separate filegroups (could cause more harm than good). might be
well worth your while to pick up Kalen Delaney's "Inside SQL server 2000"
(chapter 4 specifically has some usefull info for you)

</end commercial> ;)

--
Neil MacMurchy

http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs

Jéjé

2005-07-18, 8:23 pm

well...

I don't know the Raid level of the SAN. I wait for the info.
the local drive is a raid 1 (mirror).

for my test when I split the files on the SAN + Local I'm using the same
filegroup (the primary) and 2 files (1 for each drive)

I'll try and benchmark the way to use 2 files groups and share my tables
between these 2 groups.

"Neil MacMurchy" <neilmcse@nospamf.gmail.com> wrote in message
news:Xns969784DC1B6D
Aneilmcsehotmailcom@
207.46.248.16...
> did you hear "Jéjé" < willgart_A_@hotmail_
A_.com> say in
> news:eEqMmS7iFHA.1044@tk2msftngp13.phx.gbl:
>
>
> Brian Moran covered it off in SQL ServerMagazine in July 2004:
>
> http://www.windowsitpro.com/Article...ArticleID=42902
> &DisplayTab=Article
>
> latches are lightwieght locks (BOL covers latches - badly) and if you have
> quite a few nonzero waittime pageiolatch_sh SPIDs you may have a disk
> bottleneck (identifies connections waiting for SQL to read a page from
> disk
> into RAM)
>
> other considerations here are what level of RAID are you using and are the
> files in separate filegroups (could cause more harm than good). might be
> well worth your while to pick up Kalen Delaney's "Inside SQL server 2000"
> (chapter 4 specifically has some usefull info for you)
>
> </end commercial> ;)
>
> --
> Neil MacMurchy
>
> http://spaces.msn.com/members/neilmacmurchy
> http://spaces.msn.com/members/mctblogs
>
>

Sponsored Links