Performance Tuning Help needed

This is a Sql 2000 server running on Win 2k. This is a 1
CPU box connected to a SAN.
I ran a profiler trace for 3 hours and collected all the
data. Some of the queries show a large amount of reads.
When I run those queries individually the # of reads is
fairly smaller. For example the trace file shows 36503
reads and the individual query plan shows 91 CPU reads.
Whats going on ? Is the profiler tracking all I/O activity
at that time ? I mean, is it tracking all tempdb usage and
any other I/O activity at this time. What would explain
this large drop in I/O ?
THanks"JackA" <anonymous@.discussions.microsoft.com> wrote in message
news:061701c3a972$5d6cc080$a301280a@.phx.gbl...
> Whats going on ? Is the profiler tracking all I/O activity
> at that time ? I mean, is it tracking all tempdb usage and
> any other I/O activity at this time. What would explain
> this large drop in I/O ?
I'm new to SQL Server so take what I say with a grain of salt.. But does
the profiler keep track of how many executions for each SQL statement?
Executed once the number of reads may be small but if it was executed 1000
times during that timeframe I would expect the number to be large.|||i assume you ran the query with the same parameters?
if so, then this large a difference in reads might
indicate the trace captured that query with a different
plan. if the choice of parameters can results in large
differences in rows involved, then depending on what
parameters were used when the query was last compiled,
different executes could get different plans.
a smaller difference on the order of several hundred or
even more than 1k might indicate compile or recompile.
>--Original Message--
>This is a Sql 2000 server running on Win 2k. This is a 1
>CPU box connected to a SAN.
>I ran a profiler trace for 3 hours and collected all the
>data. Some of the queries show a large amount of reads.
>When I run those queries individually the # of reads is
>fairly smaller. For example the trace file shows 36503
>reads and the individual query plan shows 91 CPU reads.
>Whats going on ? Is the profiler tracking all I/O
activity
>at that time ? I mean, is it tracking all tempdb usage
and
>any other I/O activity at this time. What would explain
>this large drop in I/O ?
>THanks
>.
>|||Jack,
are you comparing Profiler output to Query Analyzer query plan?
If so... the difference is "normal".
Trust the Profiler, if it shows 36000 reads, that's really happening
in the DB.
peksi
"JackA" <anonymous@.discussions.microsoft.com> wrote in message
news:061701c3a972$5d6cc080$a301280a@.phx.gbl...
> This is a Sql 2000 server running on Win 2k. This is a 1
> CPU box connected to a SAN.
> I ran a profiler trace for 3 hours and collected all the
> data. Some of the queries show a large amount of reads.
> When I run those queries individually the # of reads is
> fairly smaller. For example the trace file shows 36503
> reads and the individual query plan shows 91 CPU reads.
> Whats going on ? Is the profiler tracking all I/O activity
> at that time ? I mean, is it tracking all tempdb usage and
> any other I/O activity at this time. What would explain
> this large drop in I/O ?
> THanks|||Hi Jack
The profiler shows what's happening at run time, which can be different from
what happens when you run the query for a few reasons, eg:
(a) The level & type of SQL Server activity is different when you run the
query from when the same query was captured by the profiler.
(b) The security context from which you ran the query may be different from
the query picked up by profiler.
(c) General resource levels on the server may be different.
Capturing the execution plans & comparing them may reveal some explanation
of what accounts for the different levels of reads, but this only takes you
part way to resolving the actual problem.
HTH
Regards,
Greg Linwood
SQL Server MVP
"JackA" <anonymous@.discussions.microsoft.com> wrote in message
news:061701c3a972$5d6cc080$a301280a@.phx.gbl...
> This is a Sql 2000 server running on Win 2k. This is a 1
> CPU box connected to a SAN.
> I ran a profiler trace for 3 hours and collected all the
> data. Some of the queries show a large amount of reads.
> When I run those queries individually the # of reads is
> fairly smaller. For example the trace file shows 36503
> reads and the individual query plan shows 91 CPU reads.
> Whats going on ? Is the profiler tracking all I/O activity
> at that time ? I mean, is it tracking all tempdb usage and
> any other I/O activity at this time. What would explain
> this large drop in I/O ?
> THankssql

performance monitor lifetime

from what i've read so far, the performance monitor's data is not
instantaneous, rather collected historically since the last reboot. is
this correct? i've also read (though i can't remember where), that
there was some win2k/service pack combination that allowed for
instantaneous monitoring. i've got a feeling the monitors values are
getting skewed by past (good) performance and would like to see
instantaneous values (w/out having to reboot). thanks,
arthurThere's no one setting that control all counters. You have to read the counter definition about how
it is measured. The ones that you are probably thinking of is Buffer Cache counters that now
averages from about 2000 to 3000 last samples (changed in sp3).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"arthur" <alangham@.gmail.com> wrote in message
news:1107372326.151669.237260@.f14g2000cwb.googlegroups.com...
> from what i've read so far, the performance monitor's data is not
> instantaneous, rather collected historically since the last reboot. is
> this correct? i've also read (though i can't remember where), that
> there was some win2k/service pack combination that allowed for
> instantaneous monitoring. i've got a feeling the monitors values are
> getting skewed by past (good) performance and would like to see
> instantaneous values (w/out having to reboot). thanks,
> arthur
>|||arthur wrote:
> from what i've read so far, the performance monitor's data is not
> instantaneous, rather collected historically since the last reboot. is
> this correct? i've also read (though i can't remember where), that
> there was some win2k/service pack combination that allowed for
> instantaneous monitoring. i've got a feeling the monitors values are
> getting skewed by past (good) performance and would like to see
> instantaneous values (w/out having to reboot). thanks,
> arthur
Performance monitor values vary by counter type. You can read about them
in MSDN. If you are dealing with a counter that does not display an
instantaneous value, all you need to do is compare it to the previous
value. That's essentially what sp_monitor does (as does perfmon). That
is, it keeps track of previous values and capture date/time in order to
perform the necessary calculations. Rebooting the server won't help
because once you do, you're back to have to calculate each time.
--
David Gugick
Imceda Software
www.imceda.com

performance monitor lifetime

from what i've read so far, the performance monitor's data is not
instantaneous, rather collected historically since the last reboot. is
this correct? i've also read (though i can't remember where), that
there was some win2k/service pack combination that allowed for
instantaneous monitoring. i've got a feeling the monitors values are
getting skewed by past (good) performance and would like to see
instantaneous values (w/out having to reboot). thanks,
arthur
There's no one setting that control all counters. You have to read the counter definition about how
it is measured. The ones that you are probably thinking of is Buffer Cache counters that now
averages from about 2000 to 3000 last samples (changed in sp3).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"arthur" <alangham@.gmail.com> wrote in message
news:1107372326.151669.237260@.f14g2000cwb.googlegr oups.com...
> from what i've read so far, the performance monitor's data is not
> instantaneous, rather collected historically since the last reboot. is
> this correct? i've also read (though i can't remember where), that
> there was some win2k/service pack combination that allowed for
> instantaneous monitoring. i've got a feeling the monitors values are
> getting skewed by past (good) performance and would like to see
> instantaneous values (w/out having to reboot). thanks,
> arthur
>
|||arthur wrote:
> from what i've read so far, the performance monitor's data is not
> instantaneous, rather collected historically since the last reboot. is
> this correct? i've also read (though i can't remember where), that
> there was some win2k/service pack combination that allowed for
> instantaneous monitoring. i've got a feeling the monitors values are
> getting skewed by past (good) performance and would like to see
> instantaneous values (w/out having to reboot). thanks,
> arthur
Performance monitor values vary by counter type. You can read about them
in MSDN. If you are dealing with a counter that does not display an
instantaneous value, all you need to do is compare it to the previous
value. That's essentially what sp_monitor does (as does perfmon). That
is, it keeps track of previous values and capture date/time in order to
perform the necessary calculations. Rebooting the server won't help
because once you do, you're back to have to calculate each time.
David Gugick
Imceda Software
www.imceda.com

performance monitor lifetime

from what i've read so far, the performance monitor's data is not
instantaneous, rather collected historically since the last reboot. is
this correct? i've also read (though i can't remember where), that
there was some win2k/service pack combination that allowed for
instantaneous monitoring. i've got a feeling the monitors values are
getting skewed by past (good) performance and would like to see
instantaneous values (w/out having to reboot). thanks,
arthurThere's no one setting that control all counters. You have to read the count
er definition about how
it is measured. The ones that you are probably thinking of is Buffer Cache c
ounters that now
averages from about 2000 to 3000 last samples (changed in sp3).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"arthur" <alangham@.gmail.com> wrote in message
news:1107372326.151669.237260@.f14g2000cwb.googlegroups.com...
> from what i've read so far, the performance monitor's data is not
> instantaneous, rather collected historically since the last reboot. is
> this correct? i've also read (though i can't remember where), that
> there was some win2k/service pack combination that allowed for
> instantaneous monitoring. i've got a feeling the monitors values are
> getting skewed by past (good) performance and would like to see
> instantaneous values (w/out having to reboot). thanks,
> arthur
>|||arthur wrote:
> from what i've read so far, the performance monitor's data is not
> instantaneous, rather collected historically since the last reboot. is
> this correct? i've also read (though i can't remember where), that
> there was some win2k/service pack combination that allowed for
> instantaneous monitoring. i've got a feeling the monitors values are
> getting skewed by past (good) performance and would like to see
> instantaneous values (w/out having to reboot). thanks,
> arthur
Performance monitor values vary by counter type. You can read about them
in MSDN. If you are dealing with a counter that does not display an
instantaneous value, all you need to do is compare it to the previous
value. That's essentially what sp_monitor does (as does perfmon). That
is, it keeps track of previous values and capture date/time in order to
perform the necessary calculations. Rebooting the server won't help
because once you do, you're back to have to calculate each time.
David Gugick
Imceda Software
www.imceda.com