question

**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/