SQL Server Function to Measure CPU Usage Per Database

By:   |   Comments (8)   |   Related: > Monitoring


There comes a time when a DBA needs to know how much CPU resources each SQL Server database is using. This comes in handy in cases where you need to investigate high CPU usage on your SQL Servers or when you are just trying to understand the CPU heavy consumers on your server.  There is no specific report in SQL Server Management Studio (SSMS) that gets you this information, so the T-SQL code presented will provide this information.


My solution involves creating a T-SQL scalar function in the SQL Server master database, called dbo.udf_Get_DB_Cpu_Pct that can take the database name as input and outputs the CPU usage percentage of the given database as a number between 0 and 100.  There is also an example below that shows this for all databases.

This will enable the investigator (the DBA) to pinpoint the most CPU time consuming databases and assuming that each database relates to an application, it will identify the heavy database CPU aligned application.

The function uses a query with the following two dynamic views where the first is actually a view and the other is a table-valued function. The query uses the CROSS APPLY operator that works with the Database ID field that exists in both dynamic views with a GROUP BY clause on the Database ID fields and the aggregate SUM function on the total_worker_time column from the dm_exec_query_stats view.

The dynamic views that are used:

  • sys.dm_exec_query_stats
    • This DMV returns aggregate performance statistics for cached query plans in SQL Server.
    • The view contains one row per query statement within the cached plan, and the lifetime of the rows is connected to the plan itself. When a plan is removed from the cache, the corresponding rows are eliminated from this view. Source - MSDN.
  • sys.dm_exec_plan_attributes
    • This DMF returns one row per plan attribute for the plan specified by the plan handle. This table-valued function to get details about a particular plan, such as the cache key values or the number of current simultaneous executions of the plan. Source - MSDN.

Important note: the DMVs can only report on the data that's available in the plan cache.

Here is the T-SQL code for the scalar user function:

USE master

-- ===========================================================
-- Author:      Eli Leiba
-- Create date: 19-09-2019
-- Description: Get the CPU usage percentage for the given database.
--              Result should be a decimal between 0 and 100
-- ===========================================================
CREATE FUNCTION dbo.udf_Get_DB_Cpu_Pct (@dbName sysname)
RETURNS decimal (6, 3)AS
   DECLARE @pct decimal (6, 3) = 0

   SELECT @pct = T.[CPUTimeAsPercentage]
        CONVERT (DECIMAL (6, 3), [CPUTimeInMiliSeconds] * 1.0 / 
        SUM ([CPUTimeInMiliSeconds]) OVER () * 100.0) AS [CPUTimeAsPercentage]
          DB_Name(dm_execplanattr.DatabaseID) AS [Database],
          SUM (dm_execquerystats.total_worker_time) AS CPUTimeInMiliSeconds
       FROM sys.dm_exec_query_stats dm_execquerystats
            CONVERT (INT, value) AS [DatabaseID]
         FROM sys.dm_exec_plan_attributes(dm_execquerystats.plan_handle)
         WHERE attribute = N'dbid'
        ) dm_execplanattr
       GROUP BY dm_execplanattr.DatabaseID
      ) AS CPUPerDb
    )  AS T
   WHERE T.[Database] = @dbName

   RETURN @pct

Sample Execution

Report database CPU percentage for the master database:

USE master
SELECT dbo.udf_Get_DB_Cpu_Pct ('master')

And the results are on my server:

cpu usage for one database

Report results for all databases in descending CPU usage order:

USE master
SELECT d.name,dbo.udf_Get_DB_Cpu_Pct (d.name) as usagepct
FROM sysdatabases d
ORDER BY usagepct desc

And the results are on my server:

cpu usage for each database
Next Steps
  • You can create and compile this simple scalar user function in your master database and use it as a simple T-SQL tool for information identifying the top CPU usage databases.
  • The function was tested with SQL Server 2014 and SQL Server 2017

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Eli Leiba Eli Leiba is a senior application DBA, a teacher and a senior database consultant with 19 years of RDBMS experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Wednesday, March 13, 2024 - 7:32:27 AM - Joe McBratnie Back To Top (92065)
Nice article. The codes is a tad better than mine. I use to to explain the cost to the data owners as part of a review. Also it's on a report for management with 24 snapshot of cpu load. Btw I spread the cost of system database (master, tempdb, msdb….) to the user database. I have added in CPUTimeAsPercentageas its own column. Next, I will work on adding in memory the database is using to improve the costing model. Again thanks

Thursday, July 27, 2023 - 5:51:29 AM - Andre Lucas Back To Top (91434)
thanks, a question. is this actual cpu usage per database or since start sql instance ?

Thursday, July 29, 2021 - 6:43:28 AM - Miroslav Makhruk Back To Top (89070)
Hi Eli,

Great function.

I have a question - are the values generated by the function are actual CPU usage percentages or they are relative to SQL Server process?
E.g. If SQL server process, as seen in Task Manager, uses 50% of server's CPU and the query shows that some database uses 50% of CPU time - does it apply to acual server CPU usage or it means that it uses 50% of SQL server process CPU usage (which in this case means it only uses 25% of actual server's CPU)?
If so then it may be confusing. For example if SQL server process consumes 5% of CPU and the query shows some DB uses 100% of CPU then one can think his/her DB is problematic because it uses 100% of CPU while it's OK and actually only uses 5%.

Thank you in advance.

Wednesday, December 30, 2020 - 2:04:04 PM - Camilo Castaño Cardona Back To Top (87982)
Thanks a lot, I had been seek one script, that help me with this information for days. I used this, in sql server 2008 and it worked

Friday, November 22, 2019 - 7:20:19 AM - Scott Back To Top (83166)

Here is an alternative query that does not require anything to be installed in Master based on your post:

WITH cte as (
, SUM (SDEQS.total_worker_time) AS CPUTimeInMiliSeconds
FROM sys.dm_exec_query_stats SDEQS
(SELECT CAST(value as int) AS [DatabaseID]
FROM sys.dm_exec_plan_attributes(SDEQS.plan_handle)
WHERE attribute = N'dbid') SDEPA
SELECT COALESCE(DB_NAME(DatabaseID), '{Overhead Processes}') as [Database]
, CAST ([CPUTimeInMiliSeconds] * 1.0 / SUM ([CPUTimeInMiliSeconds]) OVER () * 100.0 as DECIMAL(6, 3)) AS [CPUTime%]
FROM cte
CAST ([CPUTimeInMiliSeconds] * 1.0 / SUM ([CPUTimeInMiliSeconds]) OVER () * 100.0 as DECIMAL(6, 3)) DESC;

Wednesday, November 20, 2019 - 1:44:23 PM - Salam Back To Top (83147)

Useful and nice script, I tested it on sql it works fine as well I did not create it in the master DB but I do a call master..sysdatabases
Thanks for this tip

Monday, November 11, 2019 - 7:46:27 PM - Graham Okely Back To Top (83063)

Thanks Eli, can your function be adjusted to display percentage for user accounts and the database?

Tuesday, November 5, 2019 - 7:03:52 AM - Ambrozyhorn Back To Top (82983)

Great Eli!

get free sql tips
agree to terms