Is there a way to find which database is using how much of total CPU

Posted on 2011-10-31
Medium Priority
Last Modified: 2012-05-12
hi all,
Is there a way to find particularly on database to database like how much of CPU is a particular database using?

Question by:msdba
LVL 28

Accepted Solution

strickdd earned 1500 total points
ID: 37058542
This won't tell you a percent, but you should be able to get an idea of the load for each DB:

--This first thing to check if CPU is at 100% is to look for parallel queries:
-- Tasks running in parallel (filtering out MARS requests below):

* from sys.dm_os_tasks as t 
where t.session_id in (
select t1.session_id 
from sys.dm_os_tasks as t1 
group by t1.session_id 
having count(*) > 1 
and min(t1.request_id) = max(t1.request_id));

-- Requests running in parallel:
select *
from sys.dm_exec_requests as r 
join (
select t1.session_id, min(t1.request_id)
from sys.dm_os_tasks as t1 
group by t1.session_id 
having count(*) > 1 
and min(t1.request_id) = max(t1.request_id)
) as t(session_id, request_id)
on r.session_id = t.session_id 
and r.request_id = t.request_id;

Open in new window


Author Closing Comment

ID: 37084738

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question