SQL Server Function to Measure CPU Usage Per Database

By:   |   Updated: 2019-11-05   |   Comments (4)   |   Related: More > 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

Last Updated: 2019-11-05

get scripts

next tip button

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.

View all my tips
Related Resources

More SQL Server Solutions

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.

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

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

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

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

Tuesday, November 05, 2019 - 7:03:52 AM - Ambrozyhorn Back To Top

Great Eli!


Recommended Reading

How to setup SQL Server alerts and email operator notifications

Reading the SQL Server log files using TSQL

Determining space used for all tables in a SQL Server database

Different techniques to identify blocking in SQL Server

Methods to determine the status of a SQL Server database

get free sql tips
agree to terms

Learn more about SQL Server tools