right way to perfromance tune?

I captured a trace from a production box. I've mad some changes with
Indexes, Stats, etc. so now I want to replay. Im mostly interested in
lowering Duration and CPU at this point. If I replay this trace on a box
thats not as powerful as Production, is this even an accurate assessment? Is
there a better way?
SQL2K SP3
TIA, ChrisROn Mon, 15 Nov 2004 15:07:08 -0800, "ChrisR" <bla@.noemail.com> wrote:
>I captured a trace from a production box. I've mad some changes with
>Indexes, Stats, etc. so now I want to replay. Im mostly interested in
>lowering Duration and CPU at this point. If I replay this trace on a box
>thats not as powerful as Production, is this even an accurate assessment? I
s
>there a better way?
Even on a very different box, AS LONG AS THE DATA IS THE SAME, eg,
full production size, then at least the logical reads should be
comparable. That's so the statistics and execution plans are the
same, not to mention getting the same results!
CPU and duration will not be comparable.
Joshua Stern|||On Mon, 15 Nov 2004 18:58:23 -0800, jxstern wrote:

>On Mon, 15 Nov 2004 15:07:08 -0800, "ChrisR" <bla@.noemail.com> wrote:
>Even on a very different box, AS LONG AS THE DATA IS THE SAME, eg,
>full production size, then at least the logical reads should be
>comparable. That's so the statistics and execution plans are the
>same, not to mention getting the same results!
>CPU and duration will not be comparable.
>Joshua Stern
Hi Joshua,
I don't think that this is correct. Completely different execution plans
can be chosen if there are more or less processors available. AFAIK, the
amount of available memory will also influence the choice.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Tue, 16 Nov 2004 10:27:48 +0100, Hugo Kornelis
<hugo@.pe_NO_rFact.in_SPAM_fo> wrote:
>I don't think that this is correct. Completely different execution plans
>can be chosen if there are more or less processors available. AFAIK, the
>amount of available memory will also influence the choice.
I didn't know that, and would like to hear more, but even so I'd bet
you could depend on logical read counts to give you a pretty good
indication of whether your database structure and queries are lined up
well, plus or minus, oh, maybe 20%.
I wonder if a parallel algorithm might do *more* logical reads, hmm.
J.|||"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:pc5kp05cqkv13jj688b3pnjjvvid6vg9vk@.
4ax.com...
> On Tue, 16 Nov 2004 10:27:48 +0100, Hugo Kornelis
> <hugo@.pe_NO_rFact.in_SPAM_fo> wrote:
> I didn't know that, and would like to hear more, but even so I'd bet
> you could depend on logical read counts to give you a pretty good
> indication of whether your database structure and queries are lined up
> well, plus or minus, oh, maybe 20%.
>
I doubt that. Imagine your prod server has 2 gig of RAM and your test
server has 512K. The amount of memory available for caching will be
radically different. In that case your logical reads may be far lower and
your physical reads would be far higher.

> I wonder if a parallel algorithm might do *more* logical reads, hmm.
> J.
>|||On Tue, 16 Nov 2004 07:03:31 -0800, JXStern wrote:

>On Tue, 16 Nov 2004 10:27:48 +0100, Hugo Kornelis
><hugo@.pe_NO_rFact.in_SPAM_fo> wrote:
>I didn't know that, and would like to hear more, but even so I'd bet
>you could depend on logical read counts to give you a pretty good
>indication of whether your database structure and queries are lined up
>well, plus or minus, oh, maybe 20%.
Hi J,
I wouldn't rely on it. I don't know the details (where's Kalen when you
need her? <g> ), but I can easily imagine that the optimizer would choose
an execution plan using hash joins and sorting on a high-memory machine,
but prefer looping on a low-memory machine. Operations like sorting and
hashing tend to be relatively cheap if it's all in memory, but very
expensive if the amount of data is more than fits in memory.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Tue, 16 Nov 2004 15:21:33 GMT, "Greg D. Moore \(Strider\)"
<mooregr_deleteth1s@.greenms.com> wrote:
>I doubt that. Imagine your prod server has 2 gig of RAM and your test
>server has 512K. The amount of memory available for caching will be
>radically different. In that case your logical reads may be far lower and
>your physical reads would be far higher.
For a given execution plan (and server and data, etc), aren't the
logical reads constant no matter what the physical reads?
J.|||jxstern wrote:
> On Tue, 16 Nov 2004 15:21:33 GMT, "Greg D. Moore \(Strider\)"
> <mooregr_deleteth1s@.greenms.com> wrote:
> For a given execution plan (and server and data, etc), aren't the
> logical reads constant no matter what the physical reads?
> J.
If the same query plan is used (you can't compare the count of a hash
with a loop join), and the databases have the same free space (caused by
updates and deletes), the same index depth and the same number of pages,
then the logical read count should be the same. As you can see, a lot
should match...
Gert-Jan|||On Tue, 16 Nov 2004 23:23:17 +0100, Gert-Jan Strik
<sorry@.toomuchspamalready.nl> wrote:
>If the same query plan is used (you can't compare the count of a hash
>with a loop join), and the databases have the same free space (caused by
>updates and deletes), the same index depth and the same number of pages,
>then the logical read count should be the same. As you can see, a lot
>should match...
Do logical reads really know about free space?
I'm talking about logical reads from the profiler. Oh, well, yeah, I
guess it would. But EVEN SO I'm betting you could get a pretty fair
correspondence.
Most tuning I do is in search of 10x or 100x gains from tracking down
missing indexes and really bad code. I think you can see changes of
that magnitude even through the noise. If your tuning goal is a 10%
improvement, then sure, the noise will prevent any real comparison
across environments.
But in any case, if you take the bad code off production, put it in
development and get a baseline, you can measure newly developed
changes pretty well and guestimate them back up to production based on
the ratio.
J.