June 8, 2022 at 10:30 pm
Hey All,
I work at an organization in which a server must have more than 15 minutes of continuous cpu utilization over 90% before a ticket is generated. I don't have control over these thresholds. Often times our NOC escalates these tickets to me and I have no idea which query and user was the issue. It would have to be a simple query, so as not bog down the instance.
Has anyone experimented with setting up a SQL Agent job that would run and collect performance analysis of queries if cpu utilization hit a certain point? I'm just curious if there are any major flaws with this. I'm not even sure how I would trigger it. My initial thought is checking the system health XE session, as I believe there is an attribute for cpu idle time for the SQL server process.
I've not investigated query store, so please tell me if this is an utter waste of time and I should look there.
Thanks in advance!
June 9, 2022 at 11:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
June 11, 2022 at 11:28 am
I suspect the SQL Agent and system health will not be much use for this. Assuming you have the basics covered, like enough memory for Windows to run without paging to disk, I would look at the following:
https://www.brentozar.com/archive/2006/12/dba-101-using-perfmon-for-sql-performance-tuning/
https://www.scarydba.com/2019/06/03/which-query-used-the-most-cpu-implementing-extended-events/
https://www.scarydba.com/2019/12/02/tracking-cpu-use-over-time/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply