CPU & Memory usage

Question

CPU & Memory usage

sqlnaive

SSCoach

Points: 17437
More actions
March 12, 2012 at 1:45 am

#258531

Hello, Is it possible to get the CPU Usage and Memory usage for the SQL server in following way using a query:
---------------------------------------------------
TotalCPU CPUUsage TotalMem MemUsage
---------------------------------------------------

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 1

The short answer would be YES, it is possible. All the necessary information can be queried from various DMV's like sys.dm_os_sys_memory, sys.dm_os_sys_info, sys.dm_os_performance_counters or sys.dm_os_ring_buffers.

I don't have a nice ready-to-use script available for you but here are some starters:

-- CPU usage by current SQL Instance over the last hour

DECLARE @ts_now BIGINT

SELECT @ts_now = cpu_ticks / CONVERT(FLOAT, (cpu_ticks / ms_ticks))

FROM sys.dm_os_sys_info

SELECT top (60)

DATEADD(ms, -1 * (@ts_now - [TIMESTAMP]), GETDATE()) AS MonitorTime,

SQLProcessUtilization,

SystemIdle,

100 - SystemIdle - SQLProcessUtilization AS OtherProcessUtilization

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

-- Memory Configuration on the Server Operating System

SELECTphysical_memory_in_bytes/1024 as [Physical Memory_MB],

virtual_memory_in_bytes/1024 as [Virtual Memory MB]

FROM sys.dm_os_sys_info

-- Or alternatively

SELECT [total_physical_memory_kb]/1024 as [Physical Memory_MB],

[available_physical_memory_kb]/1024 as [Available_Memory_MB]

FROM sys.dm_os_sys_memory

-- Total Memory used by SQL Server instance from Perf Mon

SELECT cntr_value/1024 as Mem_MB

FROM sys.dm_os_performance_counters

WHERE counter_name = 'Total Server Memory (KB)'

[font="Verdana"]Markus Bohse[/font]

sqlnaive SSCoach Points: 17437 More actions · Answer 2

Thanks. Is there something same for memory utilization monitoring ? I tried googling and didn't found anything.

Ant-Green SSC Guru Points: 113445 More actions · Answer 3

the bottom part of the script which marcus provided does the memory utilisation checking

calvo SSChampion Points: 12930 More actions · Answer 4

Glenn Berry[/url] has a great script for performance numbers. Check it out!

______________________________________________________________________________________________
Forum posting etiquette.[/url] Get your answers faster.

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 5

sqlnaive (3/13/2012)
Thanks. Is there something same for memory utilization monitoring ? I tried googling and didn't found anything.

Did you have a look at the DMV I told you? It's all in there.

For even more detailed info check DBCC MEMORYSTATUS

Here's a good link on how to use it: http://blogs.msdn.com/b/psssql/archive/2009/05/15/how-it-works-dbcc-memorystatus-locked-pages-allocated-and-singlepageallocator-values.aspx

[font="Verdana"]Markus Bohse[/font]

dave hants Default port Points: 1485 More actions · Answer 6

Hi MarkusB,

I'm running your script for the cpu ticks, it's great thanks.

However it returns only null values in the output (the timestamp is fine....) like this:

2012-03-21 14:18:54.977NULLNULLNULL

2012-03-21 14:17:54.977NULLNULLNULL

2012-03-21 14:16:54.980NULLNULLNULL

If I look manually at the xml records then they typically show something like 96% idle, for example like this:

</SystemHealth>

</SchedluerMonitorEvent>

</Record>

But the values don't seem to be getting through to the select statement above.

I'm running on SQL Server 2005.

Is there something silly I'm missing??

Any tips appreciated.

Regards, Dave.

dave hants Default port Points: 1485 More actions · Answer 7

Ok got it!

Looks like the xml records have a typo on our system -

should be

!!!

Hope this helps anyone else in the same boat.....

Rgds, Dave