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