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: