snippetsqlMinor
How to find High CPU queries
Viewed 0 times
howquerieshighfindcpu
Problem
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
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
Solution
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).
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).
Context
StackExchange Database Administrators Q#139156, answer score: 4
Revisions (0)
No revisions yet.