Anomalous heavy reads when heavily loaded

In a large-scale tuning exercise we are repeatedly seeing anomalous
behaviors - small (or large) SPs that run fine on all data values when
run as single transactions, but which repeatably runs with 1000x more
reads in at least one place on a trace.
We are producing the load by reruning a trace via profiler, so we
expect the test box to be maxed out, and it is.
When I say "repeatedly", I have not verified that it's always the same
transactions that are running badly. Yes, of course it *may* be the
overall state of the database at that point in time that "is the
problem", but at least one of these SPs is a fairly simple piece of
business that shouldn't be that sensitive to ANY possible data
configurations.
It has been suggested that it is an optimizer FEATURE that it will
produce different plans when it sees the system is heavily loaded. I
was not aware of that. Can anybody tell me just what it has to see to
decide on this load? I heard it might be CPU, and synthesized some
bogus CPU loads to see if that would make my little SPs misbehave, but
so far that has not worked - the SP (the simplest one of about a dozen
I've seen doing this so far) insists on running in a normal 100 reads
instead of the 1.7m reads it displays occassionally in the trace.
Just looking for anyone who has even seen similar anomalies when
testing under load, I'm not even asking for further diagnostics,
though anything you have I'd like to hear.
Thanks.
Josh
Hi
Have you read the section on parameter sniffing in Ken Henderson's
"The Guru's Guide to SQL Server Architecture and Internals" ISBN
0-201-70047-6
also check out the post http://tinyurl.com/983uf
John
"jxstern" wrote:

> In a large-scale tuning exercise we are repeatedly seeing anomalous
> behaviors - small (or large) SPs that run fine on all data values when
> run as single transactions, but which repeatably runs with 1000x more
> reads in at least one place on a trace.
> We are producing the load by reruning a trace via profiler, so we
> expect the test box to be maxed out, and it is.
> When I say "repeatedly", I have not verified that it's always the same
> transactions that are running badly. Yes, of course it *may* be the
> overall state of the database at that point in time that "is the
> problem", but at least one of these SPs is a fairly simple piece of
> business that shouldn't be that sensitive to ANY possible data
> configurations.
> It has been suggested that it is an optimizer FEATURE that it will
> produce different plans when it sees the system is heavily loaded. I
> was not aware of that. Can anybody tell me just what it has to see to
> decide on this load? I heard it might be CPU, and synthesized some
> bogus CPU loads to see if that would make my little SPs misbehave, but
> so far that has not worked - the SP (the simplest one of about a dozen
> I've seen doing this so far) insists on running in a normal 100 reads
> instead of the 1.7m reads it displays occassionally in the trace.
> Just looking for anyone who has even seen similar anomalies when
> testing under load, I'm not even asking for further diagnostics,
> though anything you have I'd like to hear.
> Thanks.
> Josh
>
|||On Thu, 20 Oct 2005 00:50:03 -0700, John Bell
<jbellnewsposts@.hotmail.com> wrote:
>Have you read the section on parameter sniffing in Ken Henderson's
>"The Guru's Guide to SQL Server Architecture and Internals" ISBN
>0-201-70047-6
>also check out the post http://tinyurl.com/983uf
I'm aware of sniffing, but I don't see how it can be that when the
same parameters run later give (very!) different runtimes.
(benchmark process does not show or validate results).
J.
|||Hi
Have you looked at the locking events in SQL Profiler?
John
"jxstern" wrote:

> On Thu, 20 Oct 2005 00:50:03 -0700, John Bell
> <jbellnewsposts@.hotmail.com> wrote:
> I'm aware of sniffing, but I don't see how it can be that when the
> same parameters run later give (very!) different runtimes.
> (benchmark process does not show or validate results).
> J.
>
|||Have not done that, but I'm curious, could that possibly explain the
situation?
What lock-checking we've done in the way of investigating performance
has shown locking and waits to be very rare, almost impossible it
should correspond to the incidences we're seeing here.
J.
On Thu, 20 Oct 2005 23:27:02 -0700, John Bell
<jbellnewsposts@.hotmail.com> wrote:[vbcol=seagreen]
>Hi
>Have you looked at the locking events in SQL Profiler?
>John
>"jxstern" wrote:
|||Hi
The abnormal number of reads would not be more likely to be a poor query
plan, but if you have ruled out recompiles/parameter sniffing/missing or poor
stats/fragmented indexes, you need to look elsewhere and although it may not
explain the higher number of reads locking/blocking is one of the most common
causes of intermittently slow queries.
John
"JXStern" wrote:

> Have not done that, but I'm curious, could that possibly explain the
> situation?
> What lock-checking we've done in the way of investigating performance
> has shown locking and waits to be very rare, almost impossible it
> should correspond to the incidences we're seeing here.
> J.
>
> On Thu, 20 Oct 2005 23:27:02 -0700, John Bell
> <jbellnewsposts@.hotmail.com> wrote:
>