Access to performance counters from T-SQL procedure

I want to write a stored procedure, which is returning CPU utilisation, Memory utilisation and Count of rows in table T1. So it should look like:

SELECT cpu, memory, (SELECT COUNT(*) FROM T1) as T1RowCount
FROM perfmon

But the problem is that I can’t access cpu and memory from script: I looked at sys.dm_os_ring_buffers, sys.dm_os_performance_counters and others, but they don’t provide actual information (for example, every counter in Resource Pool Stats group always returns 0).

Another workaround is configuring PerfMon to write directly in SQL table and then just fetch them in this stored procedure, but it has two minuses: it’s complex and it requires external tool configuration when I really want to collect required info using vanilla T-SQL.

I have a program which is reporting its health (CPU usage, Number of allocated objects of type T and so on). But I currently do not provide such information about SQL Server which may be installed on another machine so I want to. But for unknown reason Resource Pool Stats object just do not provide any information: counters are just empty.

Rowcount tell me how many elements are not processed. Rows are added by producer and are deleted by consumer. So if this number is 0 then producer is not working. If this number is growing then something happened with consumer… There are several cases when this information may be useful.

What am I currently getting from counters is… nothing:

This should be possible via SQLCLR. The PerformanceCounter class is in in System.dll, which is one of the Supported .NET Framework Libraries. The Assembly containing the custom .NET code will need to be set to at least EXTERNAL_ACCESS.

I will try this myself later and update this answer with the results.

This will give you the CPU history for the last 4 hours. It is from a Microsoft DMV and it is very handy to go back in time and see History.

--CPU for the last 4.x hours
declare @ts_now bigint   
select @ts_now = ms_ticks 
from sys.dm_os_sys_info
select 
  record_id
  , dateadd (ms, (y.[timestamp] -@ts_now), GETDATE()) as EventTime
  , SystemIdle Idle
  , SQLProcessUtilization [SQL]
  , 100 - SystemIdle - SQLProcessUtilization as OtherProcessUtilization
  , 0 + SQLProcessUtilization +(100 - SystemIdle - SQLProcessUtilization) as Total_CPU_usage
from (
  select
    record.value('(./Record/@id)[1]', 'int') as record_id
    ,record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') as SystemIdle
    ,record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]'
    ,'int') as SQLProcessUtilization
    ,timestamp   
  from (   
  select timestamp, convert(xml, record) as record   
  from sys.dm_os_ring_buffers   
  where ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'   
  and record like '%<SystemHealth>%'
  ) as x   
) as y   
order by record_id desc;

This will give you memory:

SELECT 'Memory Grants' as What, mg.granted_memory_kb, mg.session_id, t.text, qp.query_plan 
FROM sys.dm_exec_query_memory_grants AS mg
CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(mg.plan_handle) AS qp
ORDER BY mg.granted_memory_kb DESC OPTION (MAXDOP 1)