Perfmon capture of sqlservr counters

• webrunner

  SSC-Dedicated

  Points: 31731

  More actions

  November 5, 2018 at 3:25 pm

  #382201

  Hello experts,

  I've been trying to set up a Performance Monitor trace to capture the counters mentioned in the following article. I'm trying to track down which SPID/KPID combo is causing a CPU spike on one of our SQL Servers.
  

  Select these counters at the same time:

  % Processor Time

  % Processor Time

  ID Thread

  ID Thread

  Thread State

  Thread State

  Thread Wait Reason

  Thread Wait Reason

  In the right pane, you will see multiple instances from multiple applications, which are running on that server. Since we are looking for "sqlservr" select all of the instances that begin with "sqlservr" from the list box as shown below and click Add. You can add other counters as needed and will see below that I added a few additional counters.

  In the right pane, you will see multiple instances from multiple applications, which are running on that server. Since we are looking for "sqlservr" select all of the instances that begin with "sqlservr" from the list box as shown below and click Add. You can add other counters as needed and will see below that I added a few additional counters.

  
  https://www.mssqltips.com/sqlservertip/2454/how-to-find-out-how-much-cpu-a-sql-server-process-is-really-using/

  Trouble is, as there are usually 100+ connections on our production SQL Server, it takes me forever to manually add all of the "sqlservr" instances in question (,,,sqlservr/0 etc.). So I have tried to use Logman with a file of counters using the asterisk wildcard instead but can't get it to work. Below is the Logman command I used, followed by the contents of the counters file.

  logman create counter Thread_Log -b 11/05/2018 16:40:00 -e 11/05/2018 16:50:00 -si 00:15 -cf "E:\Traces\counters03.txt" -f csv -o "E:\Traces\Thread_Trace.csv" -a

  counters03.txt contents:

  \Process(sqlservr/*)\% Processor Time
  \Process(sqlservr/*)\ID Thread
  \Process(sqlservr/*)\Thread State
  \Process(sqlservr/*)\Thread Wait Reason

  The result was a file with the time captured  but no values for the counter columns.

  Thanks for any help! Also, if there is a better way to capture these counters using Extended Events, does anyone have a link to a beginner or "accidental DBA" tutorial along those lines?

  - webrunner

  -------------------
  A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
  Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

• goher2000

  SSCertifiable

  Points: 5479

  More actions

  November 6, 2018 at 8:04 am

  #2012544

  I use this to identify, modify the threshold as you wish
  ------
  # set no of processors to average % out and SQL instance to run for
  $proc = 4 #Read-host {-Prompt "No. Of Processors" }
  $SQLInstance = "mssql-p01"
  $Computer = "mssql-p01"
  $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 ")
  #Write-Host $sql
  #$notify = -join("EXEC msdb.dbo.sp_send_dbmail @profile_name = 'mssql-p01', @recipients = 'someone@somewhere.com', @subject = 'SQL Server High CPU session', @attach_query_result_as_file = 1 ,@query = '",$sql)
  $notify = -join("EXEC msdb.dbo.sp_send_dbmail @profile_name = 'mssql-p01', @recipients = 'someone@somewhere.com', @subject = 'SQL Server High CPU session' ,@query = '",$sql)
  $notify = -join($notify,"'")
  write-host $notify
  #run sql and display
    Invoke-Sqlcmd -Query $notify -ServerInstance $SQLInstance -Database "Master"
  #| out-gridview -wait

  #$sql

  #clear out variable
  remove-variable threads,id,sqlinstance,proc,sql,count