Finding CPU usage by database on a server

We often have many databases on a single SQL Server instance. Is there a
simple way to determine resource usage (mainly CPU) on a per database basis?
For example, we have databases A through Z. I want to be able to determine
that database A is taking up 5% of the CPU, while Y is taking up 95%.
Anybody have any experience along these lines?
Thanks in advanceKevin Lavelle wrote:
> We often have many databases on a single SQL Server instance. Is
> there a simple way to determine resource usage (mainly CPU) on a per
> database basis?
> For example, we have databases A through Z. I want to be able to
> determine that database A is taking up 5% of the CPU, while Y is
> taking up 95%. Anybody have any experience along these lines?
> Thanks in advance
If your users tend be in a single database for the duration of their
connections, the easiest way is to capture the Disconnect event from
Profiler which aggregates all the CPU, Duration, Reads, and Writes for
the connection. A simple query can total everything up by database
(Database ID).
If that's not the case, and users tend to switch databases a lot, the
second easiest way is to capture the SQL:BatchCompleted and
RPC:Completed events (leave TextData out and capture only performance
related columns and the DatabaseID). You can then total up the results
by SQL execution and group by database id.
In either case, using a server-side trace is better. But for the first
option, using Profiler may be sufficient.
David Gugick
Imceda Software
www.imceda.com|||It is not easily achievable. You may profile the SQL server and look at the
CPU column based on grouping of databases. But that is very crude and rough
estimate.
"Kevin Lavelle" <kevinlavelle@.community.nospam> wrote in message
news:BA3B738C-3745-41D5-9CA6-4C264367BEFF@.microsoft.com...
> We often have many databases on a single SQL Server instance. Is there a
> simple way to determine resource usage (mainly CPU) on a per database
> basis?
> For example, we have databases A through Z. I want to be able to
> determine
> that database A is taking up 5% of the CPU, while Y is taking up 95%.
> Anybody have any experience along these lines?
> Thanks in advance|||Hi Kevin,
One of our products within the Quest Central suite, Performance Analysis, does exactly this. Plus, it also gives you performance information on a variety of other dimensions, such as application, tables, filegroups, etc. Check it out at http://www.quest.com/quest_central_for_sql_server/index.asp.
There's a free trial version available on the website.
HTH,
-Kevin Kline
Quest Software (www.quest.com)
SQL Server MVP
I support PASS, the Professional Association for SQL Server. (www.sqlpass.org)
> We often have many databases on a single SQL Server instance. Is
> there a simple way to determine resource usage (mainly CPU) on a per
> database basis?
> For example, we have databases A through Z. I want to be able to
> determine that database A is taking up 5% of the CPU, while Y is
> taking up 95%. Anybody have any experience along these lines?
> Thanks in advance
>