3

CPU utilization is very high and the SQL Server process is consuming a lot of CPU. We have run few queries and notice that there are a few SPIDs taking a long time to complete and these queries may be causing the high CPU pressure. Is there any way to get queries which are taking a lot of CPU of each SQL Server process is consuming?

I want to put an alert when ever the CPU reaches to 90% above then mail an alert with high consuming queries and to get exact one among all. I got the below process to get the high cpu thread which is using the CPU more but I need it through script to get the information. How to do this by script?

SQL Server 2014
OS: Windows 2012 Server

4

Please look at the three main queries in the following answer (of mine):

SQL Server performance: PREEMPTIVE_OS_DELETESECURITYCONTEXT dominant wait type

The first query looks at currently running queries.

The second two queries (labeled "Query #1" and "Query #2") work in combination to find high-cost queries over a period of time.

To get alerts, look into options such as Solarwinds Free WMI Monitor or Spiceworks Network Monitor.

You need monitoring software of some nature. Those were 2 that I found for Windows that were free. Most of the free ones appear to be for Unix, or are at least Unix-based. You can also look at Splunk and Zabbix. You can use the queries I provided in the linked answer with at least some of those products. But they should do the "alert on high CPU for X minutes" thing.

You could also write your own system that polls on a scheduled, is scheduled, stores data in SQL Server, ages out old data, calculates % variance over a configurable time-frame, and takes action upon that condition being met. This is a lot more work as you now have even more code to maintain (but is still a viable option if the above doesn't work out for you).

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.