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 trace

Does anyone know how to track the actual execution plan the server uses? I
have one query designed for a frequently run report that normally takes about
3 seconds to return. However, the Profiler logs show me that in certain
cases, it took more than 3 minutes to return and it cost a huge amount of CPU
time. However, when I test it manually, it always returns in less than 3
seconds and nothing wrong in the execution plan. I am wondering that at
runtime, the server might decide to choose a different plan, especially when
the report is called by multiple users at the same time. If I can capture the
execution plan at runtime, I might be able to explain why the performance can
vary so dramatically, and find a way to tune it.
Another thing, not sure if it is related or not. I am using table hint to
direct the server to use one specific index in order to speed it up. Because
I noticed if I don't, server sometimes choose a cluster index scan which is
too slow and costly. So, if I use table hint in the query and several
instances of it are called concurrently, is there any performance concerns?
You can run a trace with a filter for that particular sp and include the
showplan event. That way you can see each time it is called what it is
actually using for a plan. The only real performance concern with a hint is
that you never let SQL Server determine what the best way may be.
Conditions and data can change and what may be OK today may not be tomorrow.
If you need to specify a hint it is possible that the query can be rewritten
so that the optimizer always chooses the right plan.
Andrew J. Kelly SQL MVP
"Joseph" <Joseph@.discussions.microsoft.com> wrote in message
news:CD9BE668-ADFD-4960-BAF9-CF31A026B074@.microsoft.com...
> Does anyone know how to track the actual execution plan the server uses? I
> have one query designed for a frequently run report that normally takes
> about
> 3 seconds to return. However, the Profiler logs show me that in certain
> cases, it took more than 3 minutes to return and it cost a huge amount of
> CPU
> time. However, when I test it manually, it always returns in less than 3
> seconds and nothing wrong in the execution plan. I am wondering that at
> runtime, the server might decide to choose a different plan, especially
> when
> the report is called by multiple users at the same time. If I can capture
> the
> execution plan at runtime, I might be able to explain why the performance
> can
> vary so dramatically, and find a way to tune it.
> Another thing, not sure if it is related or not. I am using table hint to
> direct the server to use one specific index in order to speed it up.
> Because
> I noticed if I don't, server sometimes choose a cluster index scan which
> is
> too slow and costly. So, if I use table hint in the query and several
> instances of it are called concurrently, is there any performance
> concerns?
>
|||Thanks, Andrew, for the tip. However, when I include the 'Execution Plan'
event, it does not matter if I set up a filter for that specific SP or not,
it shows all execution plans for all queries running on the server. I tried
to include 'Show Plan Text', but the capture the text data does not show any
details. Any idea?
"Andrew J. Kelly" wrote:

> You can run a trace with a filter for that particular sp and include the
> showplan event. That way you can see each time it is called what it is
> actually using for a plan. The only real performance concern with a hint is
> that you never let SQL Server determine what the best way may be.
> Conditions and data can change and what may be OK today may not be tomorrow.
> If you need to specify a hint it is possible that the query can be rewritten
> so that the optimizer always chooses the right plan.
> --
> Andrew J. Kelly SQL MVP
>
> "Joseph" <Joseph@.discussions.microsoft.com> wrote in message
> news:CD9BE668-ADFD-4960-BAF9-CF31A026B074@.microsoft.com...
>
>
|||Joseph wrote:
> Thanks, Andrew, for the tip. However, when I include the 'Execution
> Plan' event, it does not matter if I set up a filter for that
> specific SP or not, it shows all execution plans for all queries
> running on the server. I tried to include 'Show Plan Text', but the
> capture the text data does not show any details. Any idea?
>
Execution Plan requires only TextData. All other execucution plan events
require the BinaryData column as well. To filter on the procedure, use
the ObjectID. ObjectName does not filter on SP-related events. In any
case, any event you select for the trace that does not map to the
ObjectID column shows up. Try and stick to the
SP:Starting/Completed/StmtStarting/StmtCompleted events to avoid
excessive trace output.
David Gugick
Quest Software
www.imceda.com
www.quest.com

performance trace

Does anyone know how to track the actual execution plan the server uses? I
have one query designed for a frequently run report that normally takes about
3 seconds to return. However, the Profiler logs show me that in certain
cases, it took more than 3 minutes to return and it cost a huge amount of CPU
time. However, when I test it manually, it always returns in less than 3
seconds and nothing wrong in the execution plan. I am wondering that at
runtime, the server might decide to choose a different plan, especially when
the report is called by multiple users at the same time. If I can capture the
execution plan at runtime, I might be able to explain why the performance can
vary so dramatically, and find a way to tune it.
Another thing, not sure if it is related or not. I am using table hint to
direct the server to use one specific index in order to speed it up. Because
I noticed if I don't, server sometimes choose a cluster index scan which is
too slow and costly. So, if I use table hint in the query and several
instances of it are called concurrently, is there any performance concerns?You can run a trace with a filter for that particular sp and include the
showplan event. That way you can see each time it is called what it is
actually using for a plan. The only real performance concern with a hint is
that you never let SQL Server determine what the best way may be.
Conditions and data can change and what may be OK today may not be tomorrow.
If you need to specify a hint it is possible that the query can be rewritten
so that the optimizer always chooses the right plan.
--
Andrew J. Kelly SQL MVP
"Joseph" <Joseph@.discussions.microsoft.com> wrote in message
news:CD9BE668-ADFD-4960-BAF9-CF31A026B074@.microsoft.com...
> Does anyone know how to track the actual execution plan the server uses? I
> have one query designed for a frequently run report that normally takes
> about
> 3 seconds to return. However, the Profiler logs show me that in certain
> cases, it took more than 3 minutes to return and it cost a huge amount of
> CPU
> time. However, when I test it manually, it always returns in less than 3
> seconds and nothing wrong in the execution plan. I am wondering that at
> runtime, the server might decide to choose a different plan, especially
> when
> the report is called by multiple users at the same time. If I can capture
> the
> execution plan at runtime, I might be able to explain why the performance
> can
> vary so dramatically, and find a way to tune it.
> Another thing, not sure if it is related or not. I am using table hint to
> direct the server to use one specific index in order to speed it up.
> Because
> I noticed if I don't, server sometimes choose a cluster index scan which
> is
> too slow and costly. So, if I use table hint in the query and several
> instances of it are called concurrently, is there any performance
> concerns?
>|||Thanks, Andrew, for the tip. However, when I include the 'Execution Plan'
event, it does not matter if I set up a filter for that specific SP or not,
it shows all execution plans for all queries running on the server. I tried
to include 'Show Plan Text', but the capture the text data does not show any
details. Any idea?
"Andrew J. Kelly" wrote:
> You can run a trace with a filter for that particular sp and include the
> showplan event. That way you can see each time it is called what it is
> actually using for a plan. The only real performance concern with a hint is
> that you never let SQL Server determine what the best way may be.
> Conditions and data can change and what may be OK today may not be tomorrow.
> If you need to specify a hint it is possible that the query can be rewritten
> so that the optimizer always chooses the right plan.
> --
> Andrew J. Kelly SQL MVP
>
> "Joseph" <Joseph@.discussions.microsoft.com> wrote in message
> news:CD9BE668-ADFD-4960-BAF9-CF31A026B074@.microsoft.com...
> > Does anyone know how to track the actual execution plan the server uses? I
> > have one query designed for a frequently run report that normally takes
> > about
> > 3 seconds to return. However, the Profiler logs show me that in certain
> > cases, it took more than 3 minutes to return and it cost a huge amount of
> > CPU
> > time. However, when I test it manually, it always returns in less than 3
> > seconds and nothing wrong in the execution plan. I am wondering that at
> > runtime, the server might decide to choose a different plan, especially
> > when
> > the report is called by multiple users at the same time. If I can capture
> > the
> > execution plan at runtime, I might be able to explain why the performance
> > can
> > vary so dramatically, and find a way to tune it.
> >
> > Another thing, not sure if it is related or not. I am using table hint to
> > direct the server to use one specific index in order to speed it up.
> > Because
> > I noticed if I don't, server sometimes choose a cluster index scan which
> > is
> > too slow and costly. So, if I use table hint in the query and several
> > instances of it are called concurrently, is there any performance
> > concerns?
> >
> >
>
>|||Joseph wrote:
> Thanks, Andrew, for the tip. However, when I include the 'Execution
> Plan' event, it does not matter if I set up a filter for that
> specific SP or not, it shows all execution plans for all queries
> running on the server. I tried to include 'Show Plan Text', but the
> capture the text data does not show any details. Any idea?
>
Execution Plan requires only TextData. All other execucution plan events
require the BinaryData column as well. To filter on the procedure, use
the ObjectID. ObjectName does not filter on SP-related events. In any
case, any event you select for the trace that does not map to the
ObjectID column shows up. Try and stick to the
SP:Starting/Completed/StmtStarting/StmtCompleted events to avoid
excessive trace output.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com

performance trace

Does anyone know how to track the actual execution plan the server uses? I
have one query designed for a frequently run report that normally takes abou
t
3 seconds to return. However, the Profiler logs show me that in certain
cases, it took more than 3 minutes to return and it cost a huge amount of CP
U
time. However, when I test it manually, it always returns in less than 3
seconds and nothing wrong in the execution plan. I am wondering that at
runtime, the server might decide to choose a different plan, especially when
the report is called by multiple users at the same time. If I can capture th
e
execution plan at runtime, I might be able to explain why the performance ca
n
vary so dramatically, and find a way to tune it.
Another thing, not sure if it is related or not. I am using table hint to
direct the server to use one specific index in order to speed it up. Because
I noticed if I don't, server sometimes choose a cluster index scan which is
too slow and costly. So, if I use table hint in the query and several
instances of it are called concurrently, is there any performance concerns?You can run a trace with a filter for that particular sp and include the
showplan event. That way you can see each time it is called what it is
actually using for a plan. The only real performance concern with a hint is
that you never let SQL Server determine what the best way may be.
Conditions and data can change and what may be OK today may not be tomorrow.
If you need to specify a hint it is possible that the query can be rewritten
so that the optimizer always chooses the right plan.
Andrew J. Kelly SQL MVP
"Joseph" <Joseph@.discussions.microsoft.com> wrote in message
news:CD9BE668-ADFD-4960-BAF9-CF31A026B074@.microsoft.com...
> Does anyone know how to track the actual execution plan the server uses? I
> have one query designed for a frequently run report that normally takes
> about
> 3 seconds to return. However, the Profiler logs show me that in certain
> cases, it took more than 3 minutes to return and it cost a huge amount of
> CPU
> time. However, when I test it manually, it always returns in less than 3
> seconds and nothing wrong in the execution plan. I am wondering that at
> runtime, the server might decide to choose a different plan, especially
> when
> the report is called by multiple users at the same time. If I can capture
> the
> execution plan at runtime, I might be able to explain why the performance
> can
> vary so dramatically, and find a way to tune it.
> Another thing, not sure if it is related or not. I am using table hint to
> direct the server to use one specific index in order to speed it up.
> Because
> I noticed if I don't, server sometimes choose a cluster index scan which
> is
> too slow and costly. So, if I use table hint in the query and several
> instances of it are called concurrently, is there any performance
> concerns?
>|||Thanks, Andrew, for the tip. However, when I include the 'Execution Plan'
event, it does not matter if I set up a filter for that specific SP or not,
it shows all execution plans for all queries running on the server. I tried
to include 'Show Plan Text', but the capture the text data does not show any
details. Any idea?
"Andrew J. Kelly" wrote:

> You can run a trace with a filter for that particular sp and include the
> showplan event. That way you can see each time it is called what it is
> actually using for a plan. The only real performance concern with a hint
is
> that you never let SQL Server determine what the best way may be.
> Conditions and data can change and what may be OK today may not be tomorro
w.
> If you need to specify a hint it is possible that the query can be rewritt
en
> so that the optimizer always chooses the right plan.
> --
> Andrew J. Kelly SQL MVP
>
> "Joseph" <Joseph@.discussions.microsoft.com> wrote in message
> news:CD9BE668-ADFD-4960-BAF9-CF31A026B074@.microsoft.com...
>
>|||Joseph wrote:
> Thanks, Andrew, for the tip. However, when I include the 'Execution
> Plan' event, it does not matter if I set up a filter for that
> specific SP or not, it shows all execution plans for all queries
> running on the server. I tried to include 'Show Plan Text', but the
> capture the text data does not show any details. Any idea?
>
Execution Plan requires only TextData. All other execucution plan events
require the BinaryData column as well. To filter on the procedure, use
the ObjectID. ObjectName does not filter on SP-related events. In any
case, any event you select for the trace that does not map to the
ObjectID column shows up. Try and stick to the
SP:Starting/Completed/StmtStarting/StmtCompleted events to avoid
excessive trace output.
David Gugick
Quest Software
www.imceda.com
www.quest.com

Performance Question

Hello,
The database we have is pretty CPU intensive.We have a trace running on the
server 24*7 although it doesn't have lot of events in trace and we run this
through stored procedures and not through GUI.Does anybody think trace is
going to affect the performance and the drive the CPU little more up.
Thanks
Yes, but it is impossible to say how much. Key for tracing without affecting performance too much is to use a
server side trace (define through stored procedures not GUI), have the output file local to the SQL Server
(not a network drive) and minimize the events.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"chinn" <chinn@.discussions.microsoft.com> wrote in message
news:8D5D15B2-D664-489A-AD46-56C353BF5F2E@.microsoft.com...
> Hello,
> The database we have is pretty CPU intensive.We have a trace running on the
> server 24*7 although it doesn't have lot of events in trace and we run this
> through stored procedures and not through GUI.Does anybody think trace is
> going to affect the performance and the drive the CPU little more up.
> Thanks

Performance Question

Hello,
The database we have is pretty CPU intensive.We have a trace running on the
server 24*7 although it doesn't have lot of events in trace and we run this
through stored procedures and not through GUI.Does anybody think trace is
going to affect the performance and the drive the CPU little more up.
ThanksYes, but it is impossible to say how much. Key for tracing without affecting
performance too much is to use a
server side trace (define through stored procedures not GUI), have the outpu
t file local to the SQL Server
(not a network drive) and minimize the events.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"chinn" <chinn@.discussions.microsoft.com> wrote in message
news:8D5D15B2-D664-489A-AD46-56C353BF5F2E@.microsoft.com...
> Hello,
> The database we have is pretty CPU intensive.We have a trace running on th
e
> server 24*7 although it doesn't have lot of events in trace and we run th
is
> through stored procedures and not through GUI.Does anybody think trace is
> going to affect the performance and the drive the CPU little more up.
> Thanks