question

jyoukhanis avatar image
jyoukhanis asked

CPU usage

I have a SQL Server 2014 for Sharepoint Database, I noticed my CPU usage is always around 30%, however running sp_whoisactive, I only have 2-3 queries running. I have 8 cpu's. What could be the issue? My cost of threshold is set to 50 and max degree of parallelism is 1 (best practice for sharepoint to always keep at 1)
cpusharepointcost threshold
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Are you happy that it is the SQLServr.exe that is consuming the CPU?
0 Likes 0 ·

1 Answer

· Write an Answer
sp_lock avatar image
sp_lock answered
**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][1] 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 = "" $SQLInstance = Read-Host -Prompt 'Input your SQL server name\instance' #$Computer = "" $Computer= Read-Host -Prompt 'Input your Server name (press enter if the same)' if (!$variablename) { $Computer= $SQLInstance} [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 0} | 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 [1]: https://sqlscope.wordpress.com/2012/05/07/identifying-high-cpu-sql-server-thread/
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.