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
Commands completed successfully.
Obciążenie CPU dla pojedynczej bazy
USE master
GO
SELECT dbo.udf_Get_DB_Cpu_Pct ('AdventureWorks2012') as usagepct
GO
usagepct --------------------------------------- 8.909 (1 row affected)
Procentowo dla wszystkich baz w instancji
USE master
GO
SELECT d.name,dbo.udf_Get_DB_Cpu_Pct (d.name) as usagepct
FROM sysdatabases d
ORDER BY usagepct desc
GO
name usagepct ------------------------------------------- master 79.462 AdventureWorksLT2012 10.666 AdventureWorks2012 7.886 msdb 0.076 SSISDB 0.000 tempdb 0.000 model 0.000 Northwind 0.000 TSQL2012 0.000 Nauka_CSharp 0.000 AdventureWorksDW2012 0.000 Nauka_SSIS 0.000 test 0.000 BULKDB 0.000 (14 rows affected)
I inne podobne zapytanie
WITH DB_CPU_Stats
AS
(
SELECT DatabaseID, DB_Name(DatabaseID) AS [DatabaseName],
SUM(total_worker_time) AS [CPU_Time_Ms]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY (
SELECT CONVERT(int, value) AS [DatabaseID]
FROM sys.dm_exec_plan_attributes(qs.plan_handle)
WHERE attribute = N'dbid') AS F_DB
GROUP BY DatabaseID
)
SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [row_num],
DatabaseName,
[CPU_Time_Ms],
CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPUPercent]
FROM DB_CPU_Stats
--WHERE DatabaseID > 4 -- system databases
--AND DatabaseID <> 32767 -- ResourceDB
ORDER BY row_num OPTION (RECOMPILE);
row_num DatabaseName CPU_Time_Ms CPUPercent -------------------- ------------------------------------------------------------- 1 master 230357 87.47 2 AdventureWorksLT2012 18869 7.17 3 AdventureWorks2012 13480 5.12 4 msdb 643 0.24 (4 rows affected)
I jeszcze jedno
DECLARE @total INT
SELECT @total=sum(cpu) FROM sys.sysprocesses sp (NOLOCK)
join sys.sysdatabases sb (NOLOCK) ON sp.dbid = sb.dbid
SELECT sb.name 'database', @total 'system cpu', SUM(cpu) 'database cpu', CONVERT(DECIMAL(4,1), CONVERT(DECIMAL(17,2),SUM(cpu)) / CONVERT(DECIMAL(17,2),@total)*100) '%'
FROM sys.sysprocesses sp (NOLOCK)
JOIN sys.sysdatabases sb (NOLOCK) ON sp.dbid = sb.dbid
--WHERE sp.status = 'runnable'
GROUP BY sb.name
ORDER BY CONVERT(DECIMAL(4,1), CONVERT(DECIMAL(17,2),SUM(cpu)) / CONVERT(DECIMAL(17,2),@total)*100) desc
database system cpu database cpu % --------------------------------------------------------------------- master 968 624 64.5 AdventureWorksLT2012 968 220 22.7 msdb 968 124 12.8 (3 rows affected)
Źródło : https://www.mssqltips.com/sqlservertip/6195/sql-server-function-to-measure-cpu-usage-per-database/
