CPU usage by DB

I have a SQL server cluster that hosts about 40 db.  The CPU utilization wen
t
from an average of 6% to 30%.  I did not add any more DBs during this
timeframe.  I can see from the running processes that it is sqlservr process
using it.

How can I determine which DBs are using the most CPU?

thanks

time to dust of SQLProfiler and start investigating.

"jason" <jason@discussions.microsoft.com> wrote in message
news:6A92892A-7952-4A9A-9BA1- F89167C26BBC@microso
ft.com...
>I have a SQL server cluster that hosts about 40 db.  The CPU utilization
>went
> from an average of 6% to 30%.  I did not add any more DBs during this
> timeframe.  I can see from the running processes that it is sqlservr
> process
> using it.
>
> How can I determine which DBs are using the most CPU?
>
> thanks

Not in SQL 2000.  No idea what new toys are available for 2005.

Try a PSSDiag to see what all is happening:

http://support.microsoft.com/kb/830232/en-us

--
Kevin Hill
3NF Consulting
www.3nf-inc.com
http://kevin3nf.blogspot.com


"jason" <jason@discussions.microsoft.com> wrote in message
news:6A92892A-7952-4A9A-9BA1- F89167C26BBC@microso
ft.com...
>I have a SQL server cluster that hosts about 40 db.  The CPU utilization
>went
> from an average of 6% to 30%.  I did not add any more DBs during this
> timeframe.  I can see from the running processes that it is sqlservr
> process
> using it.
>
> How can I determine which DBs are using the most CPU?
>
> thanks

Hi Jason,
My understanding of your issue is that:
Your SQL Server cluster hosts about 40 databases. You found that the CPU
utilization went from an average 6% to 30%. You wanted to see which DBs are
busily using CPU.
If I have misunderstood, please let me know.

You may use SQL Profiler and PSSDiag to monitor your SQL Server
performance. However there is a direct way on this issue:
use master
Go
select t2.name as dbname, t1.* from sysprocesses t1 join sysdatabases t2 on
t1.dbid=t2.dbid  order by t1.dbid

Run the SQL statement and pay attention on the columns: lastwaittype, cpu,
physical_io, memusage, status, and cmd.

If you have any other questions or concerns, please feel free to let me
know. It is my pleasure to be of assistance.

Charles Wang
Microsoft Online Community Support

 ====================
 ====================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
 ====================
 ====================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
 ====================
 ====================
==============

Charles thank you for the assistance.

i ran the query. can you tell me more about what these indicators mean.
e.g. i sorted by cpu. does a large number there definitely indicate its
consuming a lot of cpu?
when i read about it in books online, it says that is the cumulitive CPU
time.  to me that means it could have been running for days.  so if i look a
t
the other columns, it show this process logged in oct 10th, over 2 weeks ago
.
so is this large cpu value adding up all the time this process has used on
the cpu for the last 2 weeks?

or is there somewhere in books online that i can learn more about what i am
looking at?



"Charles Wang[MSFT]" wrote:

> Hi Jason,
> My understanding of your issue is that:
> Your SQL Server cluster hosts about 40 databases. You found that the CPU
> utilization went from an average 6% to 30%. You wanted to see which DBs ar
e
> busily using CPU.
> If I have misunderstood, please let me know.
>
> You may use SQL Profiler and PSSDiag to monitor your SQL Server
> performance. However there is a direct way on this issue:
> use master
> Go
> select t2.name as dbname, t1.* from sysprocesses t1 join sysdatabases t2 o
n
> t1.dbid=t2.dbid  order by t1.dbid
>
> Run the SQL statement and pay attention on the columns: lastwaittype, cpu,
> physical_io, memusage, status, and cmd.
>
> If you have any other questions or concerns, please feel free to let me
> know. It is my pleasure to be of assistance.
>
> Charles Wang
> Microsoft Online Community Support
>
>  ====================
 ====================
==============
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
>  ====================
 ====================
==============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>  ====================
 ====================
==============
>
>

Hi Jason,
I am sorry for not saying clearly in my first reply. Yes, it is cumulitive
time, but it is helpful.
First, you can see the status and cmd columns to check the current
executing command and the current status of the process that the database
used. Second, you can know which database is the busiest from the
cumulitive time since it has been started; Also, you can use subtraction to
compute the CPU time cost, physical IO read and write times by running the
query twice during a time frame, then you will know which database is the
busiest during the time frame.

Hope this helpful. For the columns meaning, you can refer to sysprocesses
in SQL Server Books Online.

If you have any other questions or cnocerns, please feel free to let me
know. It is my pleasure to be of assistance.

Charles Wang
Microsoft Online Community Support

 ====================
 ====================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
 ====================
 ====================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
 ====================
 ====================
==============

Hi Jason,

I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.
I will be more than happy to be of assistance.

Have a great day!

Charles Wang
Microsoft Online Community Support