I want to write a stored procedure, which is returning
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
memory from script: I looked at
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
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)', 'int') as record_id ,record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)', 'int') as SystemIdle ,record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)' ,'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)