Użycie CPU przez bazy danych SQL


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/

Ten wpis został opublikowany w kategorii Bez kategorii. Dodaj zakładkę do bezpośredniego odnośnika.

Dodaj komentarz