March 20, 2019 at 11:17 am
Once you set up your Resource Governor, and have configured a few pools/workload etcs, and one of the Pools are Max Out on CPU.
Is there a way to set up an alert for this Pool to ensure that you are not max out?
I did find a script I can run but that would mean that I run this every few minutes to confirm.
Thank you.
If found a sql script
SELECT session_id AS SPID,
( SELECT login_name
FROM sys.dm_exec_sessions
WHERE session_id = a.session_id
) AS Login_Name,
( SELECT [HOST_NAME]
FROM sys.dm_exec_sessions
WHERE session_id = a.session_id
) AS [Host_Name],
( SELECT cpu_time
FROM sys.dm_exec_sessions
WHERE session_id = a.session_id
) AS cpu_time,
( SELECT reads
FROM sys.dm_exec_sessions
WHERE session_id = a.session_id
) AS reads,
( SELECT writes
FROM sys.dm_exec_sessions
WHERE session_id = a.session_id
) AS writes,
(
SELECT TOP 1 [text]
FROM sys.dm_exec_cached_plans P
CROSS APPLY sys.dm_exec_sql_text(a.plan_handle)
) AS SQL_Query,
( SELECT name
FROM sys.dm_resource_governor_workload_groups
WHERE group_id = a.group_id
) AS Group_Name,
request_time,
CAST(DATEDIFF(mi, request_time, GETDATE()) AS VARCHAR(3)) + ' Minute(s)' AS Run_Time_Minutes,
( requested_memory_kb / 1024 ) AS requested_memory_mb,
required_memory_kb,
max_used_memory_kb,
( ideal_memory_kb / 1024 ) AS ideal_memory_mb,
( ( ideal_memory_kb - requested_memory_kb ) / 1024 ) AS memory_required_mb
FROM sys.dm_exec_query_memory_grants a
March 20, 2019 at 2:17 pm
TRACEY-320982 - Wednesday, March 20, 2019 11:17 AMOnce you set up your Resource Governor, and have configured a few pools/workload etcs, and one of the Pools are Max Out on CPU.
Is there a way to set up an alert for this Pool to ensure that you are not max out?I did find a script I can run but that would mean that I run this every few minutes to confirm.
Thank you.
That script gives you cpu time and that's it for anything related to cpu. It's more about memory statistics with the groups.
What you alert or monitor in some respects depends upon what cpu settings (cap cpu, max cpu, etc) you have for the group. And it still gets a bit convoluted due to the differences with some of those, especially with max cpu being able to exceed the max when there is no contention at the time of the request. It's all not really worked out yet and MS has continued to change how these work. In the unlikely event you want to read about this and look for a wait stats that might be better to monitor, you can read details in this post:
T-SQL Tuesday #83: Resource Governor CAP_CPU_PERCENT: It just runs faster?
You may want to monitor resource_governor_idle waits if you are using cap_cpu_percent as that a decent indicator of having that set too low (or some other related issues). You can find decent information on that wait type in this blog:
What is RESOURCE_GOVERNOR_IDLE and why you should not ignore it completely
If you are just setting the max cpu percent, the dmv sys.dm_resource_governor_workload_groups tracks the number of cpu limit violations. Like many other statistics it is also since server restart. If you wanted to reset resource governor related statistics, you need to execute: ALTER RESOURCE GOVERNOR RESET STATISTICS
There are also performance monitor events that fire depending on what you set with the CPUs. Take a look at SQLServer:Workload Group Stats and SQLServer:Resource Pool Stats. I think there is one event from sys.messages that fires depending on what was set with the CPUs. I can't remember offhand which one. But you can always write queries and send a notification. Or create a message with log and raise that error when you hit whatever conditions you really end up wanting to capture. You just put those queries in a job and run them however often you need to.
You really want to test resource governor pools a lot before you ever put it in production. That first link has a CPU intensive query you can use for testing. So I'd play with that, see what happens, look at some of the DMVs and performance counters to decide what you want to do and how to monitor and go from there. For me, I've found that's the best way to set things up. If you have some particular queries or groups that you have limits for, you really want to test those out, make sure they are close to what is actually needed and come up with some plans for monitoring if they are behaving as you intended.
This article goes through some monitoring with the DMVs and has explanations of the different columns for those DMVs:
Using DMVs to Adjust SQL Server Resource Governor Settings
None of that really answers your question directly but hopefully that information helps explain things some.
Sue
March 20, 2019 at 2:23 pm
yep.. figure out the rest
USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'resource_pool_CPU',
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=0,
@performance_condition=N'Resource Pool Stats|CPU usage %||>|90',
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
March 26, 2019 at 1:04 pm
Thank you SSCGrur
Mr SSCertifiable,
Wow, I can actually set up the Alert and do this so I get it alerted in the SQL Logs for all my Pools I create. That is exactly what I want to do.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply