Diagnosing source of SQL Server activity in high-volume system

Hi everyone,
We are running a SQL Server 2000 instance which is getting hit with
hundreds of queries a minute. The vast majority of these are very
short-lived, low overhead queries. Some of them are highly resouce
intensive (< 1%). My problem is that lately, our SQL Server CPU
utilization has climbed way up and I can't begin to figure out what is
causing it... the high volume of quick queries, or the low volume of
slow queries.
I'm fairly skilled at SQL Server performance tuning, but this has got
me stumped. How can I isolate the category of queries are causing all
of the activity? I've tried using profiler, but it displays each of the
queries individually ... there's no way to group activity together by
meaningful categories (that I'm aware of).
Can anyone point me in the right direction?
Thanks!"Rich" <rich@.adgooroo.com> wrote in message
news:1156887265.027415.184190@.p79g2000cwp.googlegroups.com...
> Hi everyone,
> We are running a SQL Server 2000 instance which is getting hit with
> hundreds of queries a minute. The vast majority of these are very
> short-lived, low overhead queries. Some of them are highly resouce
> intensive (< 1%). My problem is that lately, our SQL Server CPU
> utilization has climbed way up and I can't begin to figure out what is
> causing it... the high volume of quick queries, or the low volume of
> slow queries.
> I'm fairly skilled at SQL Server performance tuning, but this has got
> me stumped. How can I isolate the category of queries are causing all
> of the activity? I've tried using profiler, but it displays each of the
> queries individually ... there's no way to group activity together by
> meaningful categories (that I'm aware of).
> Can anyone point me in the right direction?
>
The basic technique here is to use profiler or a server trace to gather
execution statistics for individual queries over a window of time. Then
load the results into a table and analyze them. For instance, grouping by
query text (or truncated or scrubbed text) and then summing the IO and CPU
statistics. This will isolate the queries driving the CPU use.
David|||Hi David,
Great suggestion! I didn't know you could export this information from
Profiler into another format.
One more question. Can you suggest which performance monitors I should
select in profiler to get just the query CPU and IO information for
completed queries? Or better yet, do you know of any online tutorials
with this information?
Thanks!
-Rich
David Browne wrote:
> "Rich" <rich@.adgooroo.com> wrote in message
> news:1156887265.027415.184190@.p79g2000cwp.googlegroups.com...
> The basic technique here is to use profiler or a server trace to gather
> execution statistics for individual queries over a window of time. Then
> load the results into a table and analyze them. For instance, grouping b
y
> query text (or truncated or scrubbed text) and then summing the IO and CPU
> statistics. This will isolate the queries driving the CPU use.
> David|||Expanding on David's comments:
Run the profiler trace remotely and let your profiler trace store data in a
table on a different SQL Server.
One of the things that I will do is capture data over a length of time and
then I can calculate the 'effect' of a stored procedure by multiplying
number of times used per time segment (hour, etc.) times the duration,
perhaps also weighted for I/O.
Often, I have been able to determine that making the effort to shave a 10
milliseconds off of a high usage frequency stored procedure has more impact
than trying to take seconds (or even minutes) off of long running procedures
that are not used very often.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Rich" <rich@.adgooroo.com> wrote in message
news:1156888113.125756.277830@.p79g2000cwp.googlegroups.com...
> Hi David,
> Great suggestion! I didn't know you could export this information from
> Profiler into another format.
> One more question. Can you suggest which performance monitors I should
> select in profiler to get just the query CPU and IO information for
> completed queries? Or better yet, do you know of any online tutorials
> with this information?
> Thanks!
> -Rich
>
> David Browne wrote:
>|||Hi everyone,
Thanks for your responses. This technique worked amazingly well for us!
In about 30 minutes of work, we were able to diagnose the problem and
shaved CPU usage from 44% down to an average 14%. We also got a nice
little reduction in disk I/O as well.
I ran profiler and saved everything to a local table. Then ran the
following query to group things together:
select substring(textdata, 1, 24), count(rowNumber) as transactions,
sum(Duration) as Duration, sum(CPU) as CPU, sum(Reads) as Reads,
Sum(Writes) as Writes
from profilerresults
group by substring(textdata, 1, 24)
order by sum(CPU) desc
It turns out there are actually three different sets of queries which
are all combining to cause the problem, but the top offender was
responsible for 50% of the CPU utilization in all queries.
Thanks!!
-Rich
Arnie Rowland wrote:[vbcol=seagreen]
> Expanding on David's comments:
> Run the profiler trace remotely and let your profiler trace store data in
a
> table on a different SQL Server.
> One of the things that I will do is capture data over a length of time and
> then I can calculate the 'effect' of a stored procedure by multiplying
> number of times used per time segment (hour, etc.) times the duration,
> perhaps also weighted for I/O.
> Often, I have been able to determine that making the effort to shave a 10
> milliseconds off of a high usage frequency stored procedure has more impac
t
> than trying to take seconds (or even minutes) off of long running procedur
es
> that are not used very often.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Rich" <rich@.adgooroo.com> wrote in message
> news:1156888113.125756.277830@.p79g2000cwp.googlegroups.com...