SQL Server CPU usage per Different Objects

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

In this post, I am sharing a script find the usage of CPU for all different SQL Server Objects.

Using this script, DBA can monitor the CPU usages.

Below is a script:

SELECT

DB_NAME(st.dbid) AS DatabaseName

,OBJECT_SCHEMA_NAME(st.objectid,dbid) AS SchemaName

,cp.objtype AS ObjectType

,OBJECT_NAME(st.objectid,dbid) AS Objects

,MAX(cp.usecounts)AS Total_Execution_count

,SUM(qs.total_worker_time) AS Total_CPU_Time

,SUM(qs.total_worker_time) / (max(cp.usecounts) * 1.0) AS Avg_CPU_Time

FROM sys.dm_exec_cached_plans cp

INNER JOIN sys.dm_exec_query_stats qs

ON cp.plan_handle = qs.plan_handle

CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st

WHERE DB_NAME(st.dbid) IS NOT NULL

GROUP BY DB_NAME(st.dbid),OBJECT_SCHEMA_NAME(objectid,st.dbid),cp.objtype,OBJECT_NAME(objectid,st.dbid)

ORDER BY sum(qs.total_worker_time) desc

Jun 21, 2015Anvesh Patel

Comments: 4

Amarnath Sankaranarayanan

August 13, 2015 at 7:50 am

I would like to find(using SQL DMVs) whether a database is actually in use and if so then how much of that servers resources it is taking… and need some ideas on how we can properly collect usage statistics on all of these databases across all of these servers and then create a simple report to understand resources usage.
The purpose of this report is to determine what are the critical counters that we need and what is the simplest and least impact method to collect them.
We don’t need minute to minute, I’m only looking for summary information once per day or so.
Can you please advise on this?

Anvesh Patel
August 13, 2015 at 9:46 am
Yes, You can find all database which are currently in use.
Please use this script:

SELECT request_session_id,resource_database_id FROM sys.dm_tran_locks WHERE resource_database_id = DB_ID('database_name')

After execute this, if you get records for your database then your database is in use.
And just remove filter so you can get all list of databases which is in use.
You have to join this dbid with sql query written in post.

Thank You.

sdorttuii plmnr

December 10, 2015 at 4:17 pm

As soon as I noticed this web site I went on reddit to share some of the love with them.

gill

March 31, 2016 at 9:50 am

Major thanks for the blog.Much thanks again. Great.

SQL Server CPU usage per Different Objects

Anvesh Patel

About Me!

About DBRND !

Recent Comments !

Follow Me !