asked
Percentage of CPU each SQL Server process is consuming?
When we look into the database server we see CPU utilization is very high and the SQL Server process is consuming most of the CPU. When we launch SSMS and run sp_who2 and notice that there are a few SPIDs taking a long time to complete and these queries may be causing the high CPU pressure. At the server level we can only see the overall SQL Server process, but within SQL Server we can see each individual query that is running. Is there a way to tell how much percentage of CPU each SQL Server process is consuming? Do we have any script for conclude the above problem? Our environment is SQL Server 2014 and Windows 2012 server. We are checking the total_elapsed_time column to identify the which SPID is running from long time. I am looking for a script to identify which SPID is taking more CPU in percentage. For example we can see spid 400,500,600 and 700 are running on server and they are high consuming cpu queries. I want the below output for them: SPID 400: 20% CPU is using SPID 500: 30% of CPU is using SPID 600: 26% of CPU is using etc. When I googled it I came across power shell code to get CPU threads information and correlated spid's with kpid's. But I am getting errors while checking script in my local machine. Please guide me to get this to be done. Find the link for the same.
http://www.systemcentercentral.com/whats-taking-sql-cpu/
**WARNING. NO WARRANTY COMES WITH THIS SCRIPT. PLEASE TEST!** To use this PoSH script install the ISE (grid display used). It will hit the Windows server and get all threads with PercentProcessorTime >0 (you can tweak this) and then query SQL to find the dm_exec_sql_text with that thread. Sometimes the tread may not be active by the time it hits SQL so the PoSH script will just end. If that is the case the the values in IDThread and run the TSQL from here and replace YourThreadID with the one returned in the script. # set no of processors to average % out and SQL instance to run for $proc = 4 #Read-host {-Prompt "No. Of Processors" } $SQLInstance = "SERVER\INSTANCE" $Computer = "SERVER" [int]$id = get-process -Name "sqlservr" -computername $Computer | select -ExpandProperty id $count = 0 #run until records returned sometime 0 percent found While($threads.count -eq 0) { $threads = gwmi Win32_PerfFormattedData_PerfProc_Thread -ComputerName $Computer | ?{$_.Name -notmatch '_Total' -and $_.IDProcess -ieq $id -and $_.PercentProcessorTime -igt 5} | sort PercentProcessorTime -desc | select-object Name,IDProcess, IDThread,{($_.PercentProcessorTime) / $proc} $count =$count+1 write-host "Attempt :" $count } $threads #set sql statement $sql = "SELECT r.session_id, os_thread_id,sub.[Percent],st.text/*, qp.query_plan, r.status*/ FROM sys.dm_os_threads AS ot JOIN sys.dm_os_tasks AS t ON t.worker_address = ot.worker_address JOIN sys.dm_exec_requests AS r ON t.session_id = r.session_id CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) AS qp INNER JOIN ( " foreach ($thr in $threads) { # build up threads to loop through $sql = -join ($SQL,"SELECT "+$thr.IDThread.ToString() +" as [Thread], " +$thr.'($_.PercentProcessorTime) / $proc' +" as [Percent] UNION ALL ") } #remove last union all $sql = $sql.Substring(0,($sql.Length -10)) #add where clause $sql =-join($sql,") as sub ON sub.Thread = ot.os_thread_id WHERE r.session_id @@spid ") #run sql and display Invoke-Sqlcmd -Query $sql -ServerInstance $SQLInstance -Database "Master" | out-gridview #$sql #clear out variable remove-variable threads,id,sqlinstance,proc,sql,count
Hi, --> I got the percentage of cpu as follows for few sessions, having a confusion as our cpu is showing up 100% but the following spids are showing beyond 100% as total. Can you clarify the same. sessionid 281: 11% sessionid 1845: 98% sessionid 442: 99% sessionid 2121: 98% --> Do we have any alert mechanism for capturing the same and send it through DB mail with output from powershell.
It's working and below is the DBMAIL process from Powershell. $dbMailQuery1 = "execute msdb..sp_send_dbmail @profile_name = 'profilename', @recipients =
'XXXX@mail.com', @subject = 'High CPU queires in percentage', @body_format = 'TEXT', @body = 'Please have a look', @file_attachments = 'c:\desktop\test.csv' " #clear out variable remove-variable threads,id,sqlinstance,proc,sql,count Invoke-Sqlcmd -Query $dbmailquery1 -ServerInstance $SQLInstance -Database "Master"
18 People are following this question.
