Get CPU & Memory Usage from a SQL Query | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Get CPU & Memory Usage from a SQL Query

Hi, I’m able to get the list of process running on a SQL Server but I’m wondering if there is a way to get the CPU & Memory Usage on the server by SQL Server … The goal is to determine whether I can do some large request or not … Thanks !
One way is to use PERFMON (SYSMON) to get the values for the above counters alongwith others for further assessment. To investigate potential memory bottleneck, you can use this query: SELECT cntr_value/1024 as ‘MBs used’
from master.dbo.sysperfinfo
where object_name = ‘SQLServer:Memory Manager’ and
counter_name = ‘Total Server Memory (KB)’ For other counters
SELECT ‘Procedure
Cache Allocated’,
CONVERT(int,((CONVERT(numeric(10,2),cntr_value)
* 8192)/1024)/1024)
as ‘MBs’
from master.dbo.sysperfinfo
where object_name = ‘SQLServer:Buffer Manager’ and
counter_name = ‘Procedure cache pages’
UNION
SELECT ‘Buffer Cache database pages’,
CONVERT(int,((CONVERT(numeric(10,2),cntr_value)
* 8192)/1024)/1024)
as ‘MBs’
from master.dbo.sysperfinfo
where object_name = ‘SQLServer:Buffer Manager’ and
counter_name = ‘Database pages’
UNION
SELECT ‘Free pages’,
CONVERT(int,((CONVERT(numeric(10,2), cntr_value)
* 8192)/1024)/1024)
as ‘MBs’
from master.dbo.sysperfinfo
where object_name = ‘SQLServer:Buffer Manager’ and
counter_name = ‘Free pages’
HTH
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Thanks ! To be honest, i’m not really sure to understant what the result give me ‘Total Server Memory (KB)’ is the memory that is allowed to the server ‘ProcedureCache Allocated’ is what is really using ‘Buffer Cache database pages’ ?? ‘Free pages’ : memory that is able to use …
THen ascertain the values collected from PERFMON(SYSMON) for CPU, memory, physical disk, SQL memory and there is a section alone about collecting the PERFMON in this website. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
]]>