sql record counts: diagnosing

Showing posts with label diagnosing. Show all posts

Friday, March 9, 2012

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...
> > 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|||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:
>> "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 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:
> 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:
> >> "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
> >

Diagnosing Performance Problem?

I have a database server that has is acting very sluggish. Using the performance monitor, I can see that disk use is steadily at 100% usage.

The system is so overburdened that attempting to do even basic tasks (open folders, look in Enterprise Manager, get screen repaints) has become impractical.

Even if I could get to the Enterprise Manager "Process Info" display, what would I look for to tell me what is wrong?From a remote PC, use Profiler to monitor all queries with over say 1000 reads. Copy those queries out to Query Analyzer, and see if you can get some indexes on the underlying tables to speed them up.

Another thing to check is how much memory is in the box? SQL Server will default to taking all it wants, but if you have only 64MB in your box, then you are in for a world-o-hurt.|||Originally posted by MCrowley
From a remote PC, use Profiler to monitor all queries with over say 1000 reads.

How do I do this? What is Profiler?

thanks for the feedback!|||Originally posted by RogerWilco
How do I do this? What is Profiler?

thanks for the feedback!

Found it. This is definitely helping.

Thankfully, we just hired a real DBA who starts Thursday.

Thanks again!

Diagnosing Memory Use

I've noticed high memory consumption on some of our workstations that eventually cause unacceptable performance problems. Through Task Manager, I see that SQL Server is consuming the bulk of this memory.

A coworker has implemented a system to periodically restart SQL Server (using netstop/netstart). This does hold down memory usage but I'd like to avoid doing this.

I'd like to diagnose SQL Server and find exactly what it is doing that consumes so much memory.

I am aware of three tools to diagnose this:
- Performance Monitor
- Enterprise Manager "Current Activity"
- SQL Server Profiler

Which is the best tool to do this and how would I approach this scenario?The bottleneck may not be the memory consumption. The Task Manager is showing you the max memory sql is allowed to allocate, not the actual memory sql is currently using. You may have something else hosted your system.|||Originally posted by joejcheng
The bottleneck may not be the memory consumption. The Task Manager is showing you the max memory sql is allowed to allocate, not the actual memory sql is currently using. You may have something else hosted your system.

Task Manager shows both Mem Usage (actively paged in) and VM Size (reserved but not necessarily paged in) for the sqlservr.exe process. Those are 651MB/670MB respectively. Using Performance Monitor I see that the disk use is high, and the "Pages/sec" is high.

Shortly after doing a netstop/netstart restart of SQL Server Mem Usage/VM Size = 36MB/44MB respectively and performance is great. Our application has already reconnected to SQL Server and is operating at full speed.

This really has to be SQL Server related.|||Just to make sure, the users all have SQL or MSDE installed on their workstations or laptops, right? You can cap the amount of memory that SQL Server uses, but that will probably increase the amount of disk I/O and may cause even more problems. Anyway, try running this from a command line

osql /S(local) /E /Q"sp_configure 'max server memory (MB)', 256"
osql /S(local) /E /Q"reconfigure with override"

You may need to modify that a bit with the proper workstation\instancename, but it will keep SQL Server from hogging memory. Good luck.|||Originally posted by MCrowley
Just to make sure, the users all have SQL or MSDE installed on their workstations or laptops, right? You can cap the amount of memory that SQL Server uses, but that will probably increase the amount of disk I/O and may cause even more problems. Anyway, try running this from a command line

osql /S(local) /E /Q"sp_configure 'max server memory (MB)', 256"
osql /S(local) /E /Q"reconfigure with override"

You may need to modify that a bit with the proper workstation\instancename, but it will keep SQL Server from hogging memory. Good luck.

Thanks for the feedback!

Actually, there are no users just an internal app that runs 24/7.

We could cut SQL Server's memory allotment but as you said, that will increase disk use which is the main problem. What I want to know is why it is using the memory that it is. Once I identify that, I can redesign to use less resources. I suspect a design problem rather than simply a lack of resources.|||Ahh. Then your problem seems to be that your database has grown to more than probably 500 MB. More likely to more than 1GB. SQL Server (and Oracle, Informix, DB2, etc.) will store information in memory for faster access. You can cut down on the amount of I/O you need to perform by reducing redundancy in the data, or applying indexes on the tables that are queried most often.

The first method may be beyond help, as reconfiguring the database is near impossible without a total re-write of the application. As for the second, open Profiler in the SQL program group, and connect to your server. Under Events, remove Security Audit and Sessions. On the filters page, add a limit under Reads of greater than or equal say 100 reads. 1 read is only a logical I/O rather than a physical one, but you should be able to figure out quickly which queries are your problem children. Good luck.|||Hi,

I had similar doubts and i would like you to see the following link, which is a discussion on the same forum
http://www.dbforums.com/showthread.php?threadid=966886&referrerid=47114

regards,
henry

Diagnosing Memory Problems?

I've noticed high memory consumption on some of our workstations that
eventually cause unacceptable performance problems. Through Task
Manager, I see that SQL Server (sqlsrvr.exe) is consuming the bulk of
this memory.
We've found that restarting SQL Server (using netstop/netstart)
dramatically reduces memory usage but it gradually creeps back to an
unacceptable level and becomes a problem (high pages/sec thrashing). We
are setting up a system that will periodically restart SQL Server. This
is a stopgap solution.
I'd like to identify what in SQL Server is using this memory and why.
Ideally, I would redesign my applications so that this wouldn't happen.
I suspect some kind of design flaw or usage error and I want to identify
it. Adding physical RAM isn't an option since this is running on a 70
workstation farm.
I don't want to simply limit SQL Server's memory allotment since this
leads to increased disk use which is the main problem.
I am aware of three tools to diagnose this:
- Performance Monitor
- Enterprise Manager "Current Activity"
- SQL Server Profiler
Which is the best tool to do this and how would I approach this scenario?
Thank You!SQL Server and most other high-end database products are
essentially disk caching engines (that provide
transactions and management etc)
so under most circumstances, its suppose to use all
available memory for most effective disk caching
it will also release memory to other apps if the available
system memory falls below 4-6MB (i do wish MS would
increase this setting to 16-32MB)
however, if you are not using it as a dedicated DB server,
use Ent Manager to restrict the max memory to some desired
amount, 64-128M is good for a development system.
don't trying to figure out which queries are using memory,
unless you want to reduce the amount of data.
>--Original Message--
>I've noticed high memory consumption on some of our
workstations that
>eventually cause unacceptable performance problems.
Through Task
>Manager, I see that SQL Server (sqlsrvr.exe) is consuming
the bulk of
>this memory.
>We've found that restarting SQL Server (using
netstop/netstart)
>dramatically reduces memory usage but it gradually creeps
back to an
>unacceptable level and becomes a problem (high pages/sec
thrashing). We
>are setting up a system that will periodically restart
SQL Server. This
>is a stopgap solution.
>I'd like to identify what in SQL Server is using this
memory and why.
>Ideally, I would redesign my applications so that this
wouldn't happen.
>I suspect some kind of design flaw or usage error and I
want to identify
>it. Adding physical RAM isn't an option since this is
running on a 70
>workstation farm.
>I don't want to simply limit SQL Server's memory
allotment since this
>leads to increased disk use which is the main problem.
>I am aware of three tools to diagnose this:
>- Performance Monitor
>- Enterprise Manager "Current Activity"
>- SQL Server Profiler
>Which is the best tool to do this and how would I
approach this scenario?
>Thank You!
>.
>|||Excellent insight. Thank you!
joe chang wrote:
> SQL Server and most other high-end database products are
> essentially disk caching engines (that provide
> transactions and management etc)
> so under most circumstances, its suppose to use all
> available memory for most effective disk caching
> it will also release memory to other apps if the available
> system memory falls below 4-6MB (i do wish MS would
> increase this setting to 16-32MB)
> however, if you are not using it as a dedicated DB server,
> use Ent Manager to restrict the max memory to some desired
> amount, 64-128M is good for a development system.
> don't trying to figure out which queries are using memory,
> unless you want to reduce the amount of data.|||I am consistantly getting Physical Memory Usage High errors. My Buffer Cache is running at 99% and the Server Memory is at 87%. Any thoughts on what's causing this?

Diagnosing Memory Problems?

I am consistantly getting Physical Memory Usage High errors. My Buffer Cache
is running at 99% and the Server Memory is at 87%. Any thoughts on what's c
ausing this?I am consistantly getting Physical Memory Usage High errors. My Buffer Cache
is running at 99% and the Server Memory is at 87%. Any thoughts on what's c
ausing this?

Friday, March 9, 2012

Diagnosing source of SQL Server activity in high-volume system

Diagnosing source of SQL Server activity in high-volume system

Diagnosing Performance Problem?

Diagnosing Memory Use

Diagnosing Memory Problems?

Diagnosing Memory Problems?

sql record counts

Blog Archive

About Me