September 3, 2003 at 11:01 am
Yeah we ran a trace but couldn't see anything
which counters should we be monitoring when running a trace and looking for slow performance ?
September 3, 2003 at 12:57 pm
the default trace should get all SQL events sent to the server. Is this SQL 2000? Did you enter any filters on the last tab?
Steve Jones
September 3, 2003 at 5:19 pm
Manny,
When looking for slow performance, what I often do in the trace (DURING the slow performance period) is:
a) events: monitor just rpc complete and batch complete.
b) columns: maybe add some columns to the defaults (eg. hostname, dbid)
c) filters: add just "CPU>=1" to the defaults.
Let it run for just a few minutes, or several thousand lines (say, up to 10,000), whichever comes first. This should give you lots of barely-useful information.
Save the results to a SQL table (preferably on your workstation or another server).
Run some SQL queries against the results. Try to isolate bad stuff by grouping on say, SUBSTRING(textdata, 1, 30), and get aggregate results of CPU, READS, DURATION. Use aggregates of MAX & SUM & COUNT at a minimum.
If could be a single occasionally-execute query that's dragging performance down (which should be identified by the MAX query).
Or, there could be one fairly-respectable query that's executed zillions of times, which may be identified by the COUNT and SUM queries.
Cheers,
- Mark
Cheers,
- Mark
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply