pageiolatch_sh

Hi,
We've been having problems with our SQL server 2000 regarding running
some queries. The server runs on a dual Xenon 2.8 G with 2Gb of RAM
machine
We run weekly a query on a 2 mil lines table which usually takes no
more than 1-2 hours using SQL query analyzer.
Now apparently with no reason at random points in the progress (no
matter select or update) the query enters sleep mode and shows
PAGEIOLATCH_SH as waittype. From now on it just does nothing for
hours. Other users are able to run other queries in parallel, but this
one query will only start running again in a very long time.
We don't know what's issuing this and it's dragging us down a lot.
Please let us know if any of you encountered this or has a solution.
ThanksWhat might be useful is if you posted the SHOWPLANS
set showplan_all on
go
go
go
set showplan_all off
go
Also, as a guess you could try using the OPTION (MAXDOP 1) , if you
suspect it's a parallelism issue
Jack Vamvas
___________________________________
Advertise your IT vacancies for free at - http://www.ITjobfeed.com
<solutzii@.gmail.com> wrote in message
news:1174910527.642712.269180@.y66g2000hsf.googlegroups.com...
> Hi,
> We've been having problems with our SQL server 2000 regarding running
> some queries. The server runs on a dual Xenon 2.8 G with 2Gb of RAM
> machine
> We run weekly a query on a 2 mil lines table which usually takes no
> more than 1-2 hours using SQL query analyzer.
> Now apparently with no reason at random points in the progress (no
> matter select or update) the query enters sleep mode and shows
> PAGEIOLATCH_SH as waittype. From now on it just does nothing for
> hours. Other users are able to run other queries in parallel, but this
> one query will only start running again in a very long time.
> We don't know what's issuing this and it's dragging us down a lot.
> Please let us know if any of you encountered this or has a solution.
> Thanks
>|||Thanks for your reply,
I'm afraid to set MAXDOP to 1 since there are some procedures that run
each 10 minutes and it might get ugly if those got stuck. Also I
wonder if there is an option to set the priority to run the queries
per user or such.
Here's the output of SHOWPLANS for the statement that got stuck in the
current query. It usualy runs in no longer than 10 mins but now it's
still sleeping on it since about 4 hours:
http://spreadsheets.google.com/pub?...hdsihvL5hvWLoMA|||Please let us know if you find anything in particular that could
negatively affect the server's performance in the following
configuration:
name min value max
value config_value run_value
affinity mask -2147483648 2147483647 0 0
allow updates 0 1 0 0
awe enabled 0 1 0 0
c2 audit mode 0 1 0 0
cost threshold for parallelism 0 32767 5 5
Cross DB Ownership Chaining 0 1 0 0
cursor threshold -1 2147483647 -1 -1
default full-text language 0 2147483647 1033 1033
default language 0 9999 0 0
fill factor (%) 0 100 0 0
index create memory (KB) 704 2147483647 0 0
lightweight pooling 0 1 0 0
locks 5000 2147483647 0 0
max degree of parallelism 0 32 0 0
max server memory (MB) 4 2147483647 2147483647 2147483647
max text repl size (B) 0 2147483647 65536 65536
max worker threads 32 32767 255 255
media retention 0 365 0 0
min memory per query (KB) 512 2147483647 1024 1024
min server memory (MB) 0 2147483647 0 0
nested triggers 0 1 1 1
network packet size (B) 512 65536 4096 4096
open objects 0 2147483647 0 0
priority boost 0 1 1 1
query governor cost limit 0 2147483647 0 0
query wait (s) -1 2147483647 -1 -1
recovery interval (min) 0 32767 3 3
remote access 0 1 1 1
remote login timeout (s) 0 2147483647 20 20
remote proc trans 0 1 0 0
remote query timeout (s) 0 2147483647 600 600
scan for startup procs 0 1 0 0
set working set size 0 1 0 0
show advanced options 0 1 1 1
two digit year cutoff 1753 9999 2049 2049
user connections 0 32767 0 0
user options 0 32767 0 0|||That wait type is usually associated with disk to memory type actions and
generally means your disks are the bottle neck. Do you have a bad drive in
the array?
Andrew J. Kelly SQL MVP
<solutzii@.gmail.com> wrote in message
news:1174933662.357683.212020@.y80g2000hsf.googlegroups.com...
> Please let us know if you find anything in particular that could
> negatively affect the server's performance in the following
> configuration:
> name min value max
> value config_value run_value
> affinity mask -2147483648 2147483647 0 0
> allow updates 0 1 0 0
> awe enabled 0 1 0 0
> c2 audit mode 0 1 0 0
> cost threshold for parallelism 0 32767 5 5
> Cross DB Ownership Chaining 0 1 0 0
> cursor threshold -1 2147483647 -1 -1
> default full-text language 0 2147483647 1033 1033
> default language 0 9999 0 0
> fill factor (%) 0 100 0 0
> index create memory (KB) 704 2147483647 0 0
> lightweight pooling 0 1 0 0
> locks 5000 2147483647 0 0
> max degree of parallelism 0 32 0 0
> max server memory (MB) 4 2147483647 2147483647 2147483647
> max text repl size (B) 0 2147483647 65536 65536
> max worker threads 32 32767 255 255
> media retention 0 365 0 0
> min memory per query (KB) 512 2147483647 1024 1024
> min server memory (MB) 0 2147483647 0 0
> nested triggers 0 1 1 1
> network packet size (B) 512 65536 4096 4096
> open objects 0 2147483647 0 0
> priority boost 0 1 1 1
> query governor cost limit 0 2147483647 0 0
> query wait (s) -1 2147483647 -1 -1
> recovery interval (min) 0 32767 3 3
> remote access 0 1 1 1
> remote login timeout (s) 0 2147483647 20 20
> remote proc trans 0 1 0 0
> remote query timeout (s) 0 2147483647 600 600
> scan for startup procs 0 1 0 0
> set working set size 0 1 0 0
> show advanced options 0 1 1 1
> two digit year cutoff 1753 9999 2049 2049
> user connections 0 32767 0 0
> user options 0 32767 0 0
>|||You may be experiencing a User Mode Scheduler issue. Pretty rare but NASTY
when it arises. Do a search online for that issue. There is a workaround I
have seen floating around somewhere. MS tech support can be called to help
out also.
TheSQLGuru
President
Indicium Resources, Inc.
<solutzii@.gmail.com> wrote in message
news:1174910527.642712.269180@.y66g2000hsf.googlegroups.com...
> Hi,
> We've been having problems with our SQL server 2000 regarding running
> some queries. The server runs on a dual Xenon 2.8 G with 2Gb of RAM
> machine
> We run weekly a query on a 2 mil lines table which usually takes no
> more than 1-2 hours using SQL query analyzer.
> Now apparently with no reason at random points in the progress (no
> matter select or update) the query enters sleep mode and shows
> PAGEIOLATCH_SH as waittype. From now on it just does nothing for
> hours. Other users are able to run other queries in parallel, but this
> one query will only start running again in a very long time.
> We don't know what's issuing this and it's dragging us down a lot.
> Please let us know if any of you encountered this or has a solution.
> Thanks
>|||Priority Boost should almost NEVER be set to one. That is the only thing
that jumped out at me as an 'incorrect' setting. Also, unless you have a
lot of cpu's (as in 8+ cores, not hyperthreaded) and a VERY good I/O
subsystem, I would up the cost threshhold for parallelism to 20-50 (testing
out to see effect.
TheSQLGuru
President
Indicium Resources, Inc.
<solutzii@.gmail.com> wrote in message
news:1174933662.357683.212020@.y80g2000hsf.googlegroups.com...
> Please let us know if you find anything in particular that could
> negatively affect the server's performance in the following
> configuration:
> name min value max
> value config_value run_value
> affinity mask -2147483648 2147483647 0 0
> allow updates 0 1 0 0
> awe enabled 0 1 0 0
> c2 audit mode 0 1 0 0
> cost threshold for parallelism 0 32767 5 5
> Cross DB Ownership Chaining 0 1 0 0
> cursor threshold -1 2147483647 -1 -1
> default full-text language 0 2147483647 1033 1033
> default language 0 9999 0 0
> fill factor (%) 0 100 0 0
> index create memory (KB) 704 2147483647 0 0
> lightweight pooling 0 1 0 0
> locks 5000 2147483647 0 0
> max degree of parallelism 0 32 0 0
> max server memory (MB) 4 2147483647 2147483647 2147483647
> max text repl size (B) 0 2147483647 65536 65536
> max worker threads 32 32767 255 255
> media retention 0 365 0 0
> min memory per query (KB) 512 2147483647 1024 1024
> min server memory (MB) 0 2147483647 0 0
> nested triggers 0 1 1 1
> network packet size (B) 512 65536 4096 4096
> open objects 0 2147483647 0 0
> priority boost 0 1 1 1
> query governor cost limit 0 2147483647 0 0
> query wait (s) -1 2147483647 -1 -1
> recovery interval (min) 0 32767 3 3
> remote access 0 1 1 1
> remote login timeout (s) 0 2147483647 20 20
> remote proc trans 0 1 0 0
> remote query timeout (s) 0 2147483647 600 600
> scan for startup procs 0 1 0 0
> set working set size 0 1 0 0
> show advanced options 0 1 1 1
> two digit year cutoff 1753 9999 2049 2049
> user connections 0 32767 0 0
> user options 0 32767 0 0
>|||Thank you for your suggestions
I am taking the query I was talking above stept by step and it works
like 30x slower than it should be. I am experimenting different
setting on this query and stopping it if it does not complete in about
5 minutes. Running it weekly for months I know for sure that this one
should take no more than 1-2 minutes.
- I have set the priority boost to 0, no noticeable performance change
till now.
- I ran scandisk on this drive that we never had a problem with, and
no errors appeared. I did notice however that there might be a
bottleneck on the hard drive maybe due to some procedures that run on
the server periodically, yet these procedures have always been there.
Though I'm the only one running on the server right now, the update
statement mentioned here is put on PAGEIOLATCH just a second away
since I run it.
The CPU is idle as well as the Hard drive. I will get some more info
on the User Mode Scheduler issue in the meantime. Let me know if you
have any other ideas.
Regards|||I looked all day over the internet trying to find solutions but none
worked. The query finally ran at night and it ran incredibly fast.
Either the OS makes the server put the query on hold at some times or
the Server itself does that. This did not happen until a couple of
weeks ago and the only thing that changed since then is the size of
the weekly table, which crossed 2 mil rows. Can anyone make a
connection to any setting ?|||Are you sure it isn't blocked? What does sp_who2 show when it is waiting?
Andrew J. Kelly SQL MVP
<solutzii@.gmail.com> wrote in message
news:1175108694.916927.56640@.l77g2000hsb.googlegroups.com...
>I looked all day over the internet trying to find solutions but none
> worked. The query finally ran at night and it ran incredibly fast.
> Either the OS makes the server put the query on hold at some times or
> the Server itself does that. This did not happen until a couple of
> weeks ago and the only thing that changed since then is the size of
> the weekly table, which crossed 2 mil rows. Can anyone make a
> connection to any setting ?
>