I have a problem with a production SQL box.
The problem is that once or twice a day the box is getting its processor
allocated for 100% for 10+ seconds.
How can I find out what command or something causes that?

Thanks

Well task manager will tell you if it is SQL Server or something else. If it
is SQL then you can run a trace to see what is using lots of CPU cycles.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"Mark Goldin" <mgoldin (AT) UFANDD (DOT) LOCAL> wrote

If you take quick snapshots of sysprocesses when the CPU usage is high, and
find the incremental cpu usage between the consecutive snapshots for each
spid for the same session, you can find the sessions that are the largest CPU
consumers at the time, and that can usually help you determine what SQL
statements are contributing to the the high CPU usage.

Linchi

"Mark Goldin" wrote:

Linchi Shea (LinchiShea (AT) discussions (DOT) microsoft.com) writes:
And if you instead use beta_lockinfo, which includes a CPU column, you
can also see what that process is up to.

beta_lockinfo is available on my website:
http://www.sommarskog.se/sqlutil/beta_lockinfo.html


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

Excellent! Just a comment on your comments on the performance impact of the
tool itself. What I found is that if you can get to the server in time to
collect data when the problem is already there, you are probably okay to do
all these joins on the DMVs. But often (1) you don't know when a performance
problem may appear and (2) when it occurs, it may disappear before you get
there, so you may have to collect data continuously or at a fairly high
frequency (say once every few seconds). In that case, a monitoring tool that
does heavy joins every time it collects perf data can easily become a top
load contributor, kind of defeating the purpose.

One solution is to collect perf data lightly (in terms of its perf impact)
but regularly, and shift heavy processing to the reporting/query time. And if
you store the data somewher else, you can afford to run fancy joins to look
for the info you want.

Linchi

"Erland Sommarskog" wrote:

In my experience, performance problems that are shorter than your tolerance
for querying the DMVs (depending on how hard you are querying them), are not
really problems. The Resource Governor works in the same way... It only
checks for long-running queries so often; anything shorter is ignored.

I do agree with this. The key is to collect the bare minimum on a routine
schedule. An extension would be to add code that dynamically determines
that a problem is creeping up and at that point take the more detailed info
for a few cycles.

How do I properly read a column "cpu"? I see some very big numbers there.

Thanks

"Erland Sommarskog" <esquel (AT) sommarskog (DOT) se> wrote

That really depends on the nature of the app in question. Frankly,
performance problems that are long running (or relatively long running) are
easy to troubleshoot, precisely because they can easily caught.

The tough performance problems are those that come and go, but they are
really problems. For instance, I have been called to find out why there are
10-20 second gaps in certain appl flow (like why don't we see trades coming
in between 10:31:10 and 10:32:25?).

If you have to query a bunch of DMVs and do fancy joins once every few
seconds, you'd quickly find your monitoring query consuming a lot of CPU
cycles.

Linchi

"Aaron Bertrand [SQL Server MVP]" wrote:

I understand about the same spid but I am not sure about:
<how many CPU milliseconds are consumed by that spid in that time period
What is 'that time period'?

Thanks

"Linchi Shea" <LinchiShea (AT) discussions (DOT) microsoft.com> wrote

Whatever time period you make it to be. So, if you are sampling these DMVs
once every 10 seconds, it would be the incremental for that 10 seconds. You
need to determine how often you sample them. Ultimately, it depends on the
nature of the app and what you want to use the sampled data for.

For instance, if you are collecting the perf data for long term trending
and/or capacity planning purposes, you probably cna live with sample
intervals that are in minutes.

Linchi

"Mark Goldin" wrote: