1

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:

enter image description here

2

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.

  • It's a possible alternative, I did it yesterday, however, i'm looking for a T-SQL solution. Now I want to sniff Activity Monitor and determine which functions it uses to draw its charts. It may be the simplest solution. And PerformanceCounter could be used without EXTERNAL_ACCESS or UNSAFE, becuase they exists in whitelist assembly. – Alex Zhukovskiy Oct 12 '16 at 9:35
  • For example I find out dm_io_virtual_file_stats, which provide some required information. – Alex Zhukovskiy Oct 12 '16 at 13:49
  • @AlexZhukovskiy Glad you got the SQLCLR option working. Just FYI: EXTERNAL_ACCESS and UNSAFE have nothing to do with whether or not the Assembly containing the class is in the "supported" list or not. They pertain to what the code is doing. The "supported" list is the set of libraries that have been tested and verified to work, and are guaranteed to continue working through future .NET Framework updates. Libraries not on the list are not guaranteed to work in the future and so might change to mixed mode -- managed and unmanaged code -- which can't be loaded into SQL Server. – Solomon Rutzky Oct 12 '16 at 19:18
0

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)
  • Thank you for an answer, but it's not appliacble in my case: I should show actual values on-demant. I mean this value is updated once per minute while server could be requested for actual values once per second. – Alex Zhukovskiy Oct 11 '16 at 14:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.