April 14, 2016 at 5:16 am
Hello I am trying to build my own SQL monitor reports. Currently do not have funds to buy a third party app.
I would like to have a SQL Agent job that captures the CPU and RAM at that moment and dump the information into a baseline table so I can make a report off it the data.
Can anyone please point me in the right direction for this?
I like this Query but not sure how to modify it to get it into a table.
DECLARE @ts_now BIGINT
SELECT @ts_now = cpu_ticks / CONVERT(FLOAT, cpu_ticks_in_ms) FROM sys.dm_os_sys_info
SELECT record_id,
DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS EventTime,
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 '% %') AS x
) AS y
ORDER BY record_id DESC
April 14, 2016 at 6:14 am
I have moved my article on SQL performance dashboards over to codeplex - check it out
April 14, 2016 at 7:03 am
This is perfect!!!! Thank you so much for sharing this. Great Article and reports.
I cant thank you enough.
April 14, 2016 at 7:38 am
If you want to monitor SQL Server or diagnose MANY issues, you MUST get Glenn Berry's SQL Server Diagnostics Scripts.
here is a spiffy query from there to see CPU history for the last 256 minutes:
-- Get CPU Utilization History for last 256 minutes (in one minute intervals) (Query 35) (CPU Utilization History)
-- This version works with SQL Server 2012
DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks) FROM sys.dm_os_sys_info WITH (NOLOCK));
SELECT TOP(256) SQLProcessUtilization AS [SQL Server Process CPU Utilization],
SystemIdle AS [System Idle Process],
100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization],
DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time]
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 WITH (NOLOCK)
WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
AND record LIKE N'%<SystemHealth>%') AS x) AS y
ORDER BY record_id DESC OPTION (RECOMPILE);
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 14, 2016 at 9:55 am
DISREGARD- I found my problem.
Hello, BLOB_EATER I am trying to make this work me my environment. But I am having problems getting it to work. But I am getting this error
The report I am trying to run is the Performance_dashboard_main_CMS.
From the Server Name dropdown. I do not see my Server that I want to monitor. I ran the Script on that server and it is added to the CMS in a server group.
Can you please help?
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy