{"id":599,"date":"2016-10-04T04:24:54","date_gmt":"2016-10-04T04:24:54","guid":{"rendered":"http:\/\/oranotes.tuktaroff.com\/?page_id=599"},"modified":"2016-10-04T04:24:54","modified_gmt":"2016-10-04T04:24:54","slug":"cpu-utilization","status":"publish","type":"page","link":"http:\/\/oranotes.tuktaroff.com\/?page_id=599","title":{"rendered":"CPU utilization"},"content":{"rendered":"<p><code><br \/>\n<script type=\"text\/javascript\"> \nfunction copy(what) {\n    window.clipboardData.setData('Text',what.value);\n}\nfunction paste(what) {\n    what.value = window.clipboardData.getData('Text');\n}\n<\/script><\/p>\n<h2>\nServer CPU utilization last 256 min<br \/>\n<\/h2>\n<pre class=\"qcode\">\r\nDECLARE @ts_now bigint = (SELECT cpu_ticks\/(cpu_ticks\/ms_ticks) FROM sys.dm_os_sys_info WITH (NOLOCK)); \r\n\r\nSELECT TOP(256) SQLProcessUtilization AS [SQL Server Process CPU Utilization], \r\n               SystemIdle AS [System Idle Process], \r\n               100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization], \r\n               DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time] \r\nFROM (SELECT record.value('(.\/Record\/@id)[1]', 'int') AS record_id, \r\n\t\t\trecord.value('(.\/Record\/SchedulerMonitorEvent\/SystemHealth\/SystemIdle)[1]', 'int') \r\n\t\t\tAS [SystemIdle], \r\n\t\t\trecord.value('(.\/Record\/SchedulerMonitorEvent\/SystemHealth\/ProcessUtilization)[1]', 'int') \r\n\t\t\tAS [SQLProcessUtilization], [timestamp] \r\n\t  FROM (SELECT [timestamp], CONVERT(xml, record) AS [record] \r\n\t\t\tFROM sys.dm_os_ring_buffers WITH (NOLOCK)\r\n\t\t\tWHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' \r\n\t\t\tAND record LIKE N'%<SystemHealth>%') AS x) AS y \r\nORDER BY record_id DESC OPTION (RECOMPILE);\r\n<\/pre>\n<form action=\"\"><textarea name=\"data\" cols=\"50\" rows=\"10\" style=\"display:none;\"><br \/>\nDECLARE @ts_now bigint = (SELECT cpu_ticks\/(cpu_ticks\/ms_ticks) FROM sys.dm_os_sys_info WITH (NOLOCK)); <\/p>\n<p>SELECT TOP(256) SQLProcessUtilization AS [SQL Server Process CPU Utilization],<br \/>\n               SystemIdle AS [System Idle Process],<br \/>\n               100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization],<br \/>\n               DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time]<br \/>\nFROM (SELECT record.value('(.\/Record\/@id)[1]', 'int') AS record_id,<br \/>\n\t\t\trecord.value('(.\/Record\/SchedulerMonitorEvent\/SystemHealth\/SystemIdle)[1]', 'int')<br \/>\n\t\t\tAS [SystemIdle],<br \/>\n\t\t\trecord.value('(.\/Record\/SchedulerMonitorEvent\/SystemHealth\/ProcessUtilization)[1]', 'int')<br \/>\n\t\t\tAS [SQLProcessUtilization], [timestamp]<br \/>\n\t  FROM (SELECT [timestamp], CONVERT(xml, record) AS [record]<br \/>\n\t\t\tFROM sys.dm_os_ring_buffers WITH (NOLOCK)<br \/>\n\t\t\tWHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'<br \/>\n\t\t\tAND record LIKE N'%<SystemHealth>%') AS x) AS y<br \/>\nORDER BY record_id DESC OPTION (RECOMPILE);<br \/>\n<\/textarea><input type=\"button\" value=\"Clipboard\" onclick=\"copy(this.form.data)\"><\/form>\n<h2>\nMAX and AVG CPU utilization last 256 min<br \/>\n<\/h2>\n<pre class=\"qcode\">\r\nDECLARE @ts_now bigint = (SELECT cpu_ticks\/(cpu_ticks\/ms_ticks) FROM sys.dm_os_sys_info WITH (NOLOCK)); \r\n\r\nSELECT max(SQLProcessUtilization) AS [Max SQL Server Process CPU Utilization], \r\n\t   AVG(SQLProcessUtilization) AS [Avg SQL Server Process CPU Utilization]          \r\nFROM (SELECT record.value('(.\/Record\/@id)[1]', 'int') AS record_id, \r\n\t\t\trecord.value('(.\/Record\/SchedulerMonitorEvent\/SystemHealth\/SystemIdle)[1]', 'int') \r\n\t\t\tAS [SystemIdle], \r\n\t\t\trecord.value('(.\/Record\/SchedulerMonitorEvent\/SystemHealth\/ProcessUtilization)[1]', 'int') \r\n\t\t\tAS [SQLProcessUtilization], [timestamp] \r\n\t  FROM (SELECT [timestamp], CONVERT(xml, record) AS [record] \r\n\t\t\tFROM sys.dm_os_ring_buffers WITH (NOLOCK)\r\n\t\t\tWHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' \r\n\t\t\tAND record LIKE N'%<SystemHealth>%') AS x) AS y \r\nOPTION (RECOMPILE);\r\n<\/pre>\n<form action=\"\"><textarea name=\"data\" cols=\"50\" rows=\"10\" style=\"display:none;\"><br \/>\nDECLARE @ts_now bigint = (SELECT cpu_ticks\/(cpu_ticks\/ms_ticks) FROM sys.dm_os_sys_info WITH (NOLOCK)); <\/p>\n<p>SELECT max(SQLProcessUtilization) AS [Max SQL Server Process CPU Utilization],<br \/>\n\t   AVG(SQLProcessUtilization) AS [Avg SQL Server Process CPU Utilization]<br \/>\nFROM (SELECT record.value('(.\/Record\/@id)[1]', 'int') AS record_id,<br \/>\n\t\t\trecord.value('(.\/Record\/SchedulerMonitorEvent\/SystemHealth\/SystemIdle)[1]', 'int')<br \/>\n\t\t\tAS [SystemIdle],<br \/>\n\t\t\trecord.value('(.\/Record\/SchedulerMonitorEvent\/SystemHealth\/ProcessUtilization)[1]', 'int')<br \/>\n\t\t\tAS [SQLProcessUtilization], [timestamp]<br \/>\n\t  FROM (SELECT [timestamp], CONVERT(xml, record) AS [record]<br \/>\n\t\t\tFROM sys.dm_os_ring_buffers WITH (NOLOCK)<br \/>\n\t\t\tWHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'<br \/>\n\t\t\tAND record LIKE N'%<SystemHealth>%') AS x) AS y<br \/>\nOPTION (RECOMPILE);<br \/>\n<\/textarea><input type=\"button\" value=\"Clipboard\" onclick=\"copy(this.form.data)\"><\/form>\n<h2>\nCPU utilization for each DB<br \/>\n<\/h2>\n<pre class=\"qcode\">\r\nWITH DB_CPU_Stats\r\nAS\r\n(SELECT pa.DatabaseID, DB_Name(pa.DatabaseID) AS [Database Name], SUM(qs.total_worker_time\/1000) AS [CPU_Time_Ms]\r\n FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)\r\n CROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID] \r\n              FROM sys.dm_exec_plan_attributes(qs.plan_handle)\r\n              WHERE attribute = N'dbid') AS pa\r\n GROUP BY DatabaseID)\r\nSELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [CPU Rank],\r\n       [Database Name], [CPU_Time_Ms] AS [CPU Time (ms)], \r\n       CAST([CPU_Time_Ms] * 1.0 \/ SUM([CPU_Time_Ms]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPU Percent]\r\nFROM DB_CPU_Stats\r\nWHERE DatabaseID <> 32767 -- ResourceDB\r\nORDER BY [CPU Rank] OPTION (RECOMPILE);\r\n<\/pre>\n<form action=\"\"><textarea name=\"data\" cols=\"50\" rows=\"10\" style=\"display:none;\"><br \/>\nWITH DB_CPU_Stats<br \/>\nAS<br \/>\n(SELECT pa.DatabaseID, DB_Name(pa.DatabaseID) AS [Database Name], SUM(qs.total_worker_time\/1000) AS [CPU_Time_Ms]<br \/>\n FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)<br \/>\n CROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID]<br \/>\n              FROM sys.dm_exec_plan_attributes(qs.plan_handle)<br \/>\n              WHERE attribute = N'dbid') AS pa<br \/>\n GROUP BY DatabaseID)<br \/>\nSELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [CPU Rank],<br \/>\n       [Database Name], [CPU_Time_Ms] AS [CPU Time (ms)],<br \/>\n       CAST([CPU_Time_Ms] * 1.0 \/ SUM([CPU_Time_Ms]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPU Percent]<br \/>\nFROM DB_CPU_Stats<br \/>\nWHERE DatabaseID <> 32767 -- ResourceDB<br \/>\nORDER BY [CPU Rank] OPTION (RECOMPILE);<br \/>\n<\/textarea><input type=\"button\" value=\"Clipboard\" onclick=\"copy(this.form.data)\"><\/form>\n<p><\/code><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Server CPU utilization last 256 min DECLARE @ts_now bigint = (SELECT cpu_ticks\/(cpu_ticks\/ms_ticks) FROM sys.dm_os_sys_info WITH (NOLOCK)); SELECT TOP(256) SQLProcessUtilization AS [SQL Server Process CPU Utilization], SystemIdle AS [System Idle Process], 100 &#8211; SystemIdle &#8211; SQLProcessUtilization AS [Other Process CPU Utilization], DATEADD(ms, -1 * (@ts_now &#8211; [timestamp]), GETDATE()) AS [Event Time] FROM (SELECT record.value(&#8216;(.\/Record\/@id)[1]&#8217;, &#8216;int&#8217;) AS [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":578,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-599","page","type-page","status-publish","hentry"],"jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/P2NVny-9F","_links":{"self":[{"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/pages\/599","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/pages"}],"about":[{"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=599"}],"version-history":[{"count":1,"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/pages\/599\/revisions"}],"predecessor-version":[{"id":600,"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/pages\/599\/revisions\/600"}],"up":[{"embeddable":true,"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/pages\/578"}],"wp:attachment":[{"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=599"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}