By: Eli Leiba | 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:
- 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.
- 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 GO -- =========================================================== -- 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 BEGIN DECLARE @pct decimal (6, 3) = 0 SELECT @pct = T.[CPUTimeAsPercentage] FROM (SELECT [Database], CONVERT (DECIMAL (6, 3), [CPUTimeInMiliSeconds] * 1.0 / SUM ([CPUTimeInMiliSeconds]) OVER () * 100.0) AS [CPUTimeAsPercentage] FROM (SELECT dm_execplanattr.DatabaseID, DB_Name(dm_execplanattr.DatabaseID) AS [Database], SUM (dm_execquerystats.total_worker_time) AS CPUTimeInMiliSeconds FROM sys.dm_exec_query_stats dm_execquerystats CROSS APPLY (SELECT 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 END GO
Report database CPU percentage for the master database:
USE master GO SELECT dbo.udf_Get_DB_Cpu_Pct ('master') GO
And the results are on my server:
Report results for all databases in descending CPU usage order:
USE master GO SELECT d.name,dbo.udf_Get_DB_Cpu_Pct (d.name) as usagepct FROM sysdatabases d ORDER BY usagepct desc GO
And the results are on my server:
- 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
About the author
View all my tips