Re: Per databse CPU usage

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Adam Machanic (amachanic_at_hotmail._removetoemail_.com)
Date: 01/03/05 

Date: Mon, 3 Jan 2005 14:27:57 -0500

You might want to use a server-side trace instead of Profiler if you're
going to run the trace over a long period of time. This article is a pretty
good intro, and the techniques also apply to extracting data from trace
tables saved via Profiler:

http://www.dbazine.com/larsen6.shtml 

-- 
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Maxime Ducharme" <mducharme@cybergeneration.com> wrote in message
news:TVgCd.30250$nN6.4259@edtnps84...
>
> Hi,
>     we have a single server which hosts ~150 different databases
> with an average of 60 user connections.
>
> We are experiencing CPU performance issues, some requests just
> take all the CPU for 10 to 20 seconds.
>
> I'm currently trying to track down these heavy requests to determine
> which databses are using CPU the most, and I need help.
>
> I used SQL Profiler to extract requests, CPU time used, I/O reads/writes,
> ...
>
> An idea I had was to read data from the profiler, and keep a cumulative
> CPU time usage seperated by databases.
>
> At the end of a day, I should know which database used CPU the most.
>
> Is it possible to extract profiler's data easily ?
>
> Any better idea ?
>
> Thanks in advance and happy new year ! :)
>
> Maxime Ducharme
>
>

Relevant Pages

  • Re: Best way to read from directory with many files
    ... These files are ETL files which contain trace information. ... The database is on a different server. ... If you're CPU bound, then you can either partition the work ahead of time, ... Or you can have a central data structure that each thread consumes ...
    (microsoft.public.dotnet.framework)
  • Re: Running profiler on busy 8-cpu Sql Server
    ... I always start with a light trace and add more detail as I go along to ... SQL Server MVP ... > I think it's also worth saying that you should introduce Profiler ... > SQL Server MVP ...
    (microsoft.public.sqlserver.server)
  • Re: URGENT deadlock question
    ... > Profiler to view a trace, SQL Server dumps rowsets across the network to ... > SQL Server doesn't feel like it can keep up with all the activity. ... > server-side trace to a trace file guarantees you'll see everything. ...
    (microsoft.public.sqlserver.server)
  • Re: URGENT deadlock question
    ... >> network to the Profiler client, but there is no guarantee you'll see ... >> everything if SQL Server doesn't feel like it can keep up with all ... Just make sure the trace file is created on a local ...
    (microsoft.public.sqlserver.server)
  • Re: SQL 7 Traces
    ... Profiler 2K can generate the xp calls for a defined trace in 7 format. ... Tibor Karaszi, SQL Server MVP ...
    (microsoft.public.sqlserver.programming)