4

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

Paul White
  • 94,921
  • 30
  • 437
  • 687
Narendra
  • 165
  • 1
  • 10

1 Answers1

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).

Community
  • 1
Solomon Rutzky
  • 70,048
  • 8
  • 160
  • 306