How to determine SMS thread causing high CPU; An oldie but a goodie!

Article
09/04/2008

You can't call yourself a hardcore SMS admin unless you have been thru this exercise at least once. Apparently this Q was pulled from the MS KB (no idea why) but here it is.

How to Determine SMS Thread Responsible for High CPU Use [sms] Q234508

This article has been retired off of Microsoft’s KB archive. This technique works perfectly for SMS 2003 and several other server applications. I thought posting it here again would be more useful and searchable.

SMS: How to Determine SMS Thread Responsible for High CPU Use [sms]
ID: Q234508 CREATED: 03-JUN-1999 MODIFIED: 30-JUN-2000
winnt:2.0
winnt
PUBLIC | kbSMS200

======================================================================
SUMMARY
======================================================================
If System Management Server 2.0 or 2003 causes high processor utilization on the site server, there is a way to track the SMSEXEC thread responsible in order to begin troubleshooting the problem.

MORE INFORMATION
======================================================================
To determine the SMS thread responsible for high CPU utilization, run Performance Monitor from a remote server, and then add the counters in the following table:

A Running thread is using a processor; a Standby thread is about to use one. A Ready thread wants to use a processor, but is waiting for a processor because none are free. A thread in Transition is waiting for a resource in order to execute, such as waiting for its execution stack to be paged in from disk. A Waiting thread has no use for the processor because it is waiting for a peripheral operation to complete or a resource to become free.

Using Performance Monitor in Report (CTRL+R) view:
Locate the column with the thread(s) using the greatest processor time(s) and note the ID Thread number.

Using Performance Monitor in Chart (CTRL+C) view:
   Locate the line with the thread(s) using the greatest processor time(s), and note the Thread Instance.
   Locate the ID Thread counter with the same instance number. Highlight (right-click) that counter.
   The number in the boxes Last, Ave, Mix, and Max will all be the same. This is the ID Thread number.

Convert the decimal version of the ID Thread (given on the report) to hex using the Calculator (Calc.exe).

To begin troubleshooting the root of your high processor utilization, find the log file in the SMS\logs directory on your site server using the command Findstr /i <HexID>*.log.

SQL Perf

1) Setup Performance Monitor Logs:

For every connection to SQL server, a new SPID (SQL Server Process Identifier) will be created. For each SPID, a new thread of the SQLSERVER.EXE process is created, which is also given a KPID ("KPID" is the kernel-process ID. Under SQL Server for Windows this is the thread ID number, also known as "ID thread," and is assigned by Windows when the thread is created).

A> To identify which threads of a process are causing high CPU utilization, perform the following steps (steps assume Windows 2000/XP):

1. On start menu, select 'Run', and type "Perfmon"
2. Ensure you have "System Monitor" selected. If any counters are added by default, remove them from display.
3. In the toolbar, click the "+" to add counters to your display.
4. (select the proper machine if running perfmon against a remote machine)
5. Under "Performance Object", select 'Thread'
6. Accept the default choice for "Select counters from list", "%ProcessorTime"
7. Under "Select instances from list:", scroll down to the first SQL Server thread, displayed as "sqlservr/0". Click on "sqlsrvr/0", then scroll down to the last SQL Server thread (may be different name), for example "sqlsrvr/9". Hold down the "Shift" keyboard key, and click on the last instance, so that all SQL Server thread instances are selected. You may wish to scroll up and down the list to verify that you have them all selected. When you have all SQL Server thread instances selected, click "Add"
8. Click "Close" on the "Add Counters" dialogue box
9. Click the lightbulb icon on the toolbar to enable highlighting.
10. In its idle state, all threads should use less than 5% processor utilization over time. The counter line for the current thread selected should now be highlighted in the display. If any threads display that they are significantly above 0% processor utilization over time, and do not seem to release the processor, note their "Instance" number. This is referred to as the "Thread ID", and is the thread responsible for increased processor utilization by SQL Server.
11. Minimize the performance monitor console.

B> To identify which component of SMS is responsible for the increased SQL Server processor utilization, perform the following steps (steps assume Windows 2000/XP):

1. On start menu, select 'Run', and type "Perfmon"
2. Ensure you have "System Monitor" selected. If any counters are added by default, remove them from display.
3. In the toolbar, click the "+" to add counters to your display.
4. (select the proper machine if running perfmon against a remote machine)
5. Under "Performance Object", select 'Thread'
6. Under "Select counters from list:", select "ID Thread"
7. Under "Select instances from list:", select all SQL Server Thread IDs noted above, in the previous step. Note, you may have multiple thread ids identified. If this is the case, then select the first thread id, then use "CTRL-Click" to select multiple other threads. Click "Add" when all appropriate thread ids are selected.
8. Click "Close" on the "Add Counters" dialogue box.
9. For each Thread ID, there will be a value specified just under the graph display, such as "Last", "Average", etc. Note the number in "Last". This is the KPID of the thread.
10. Minimize this instance of Performance Monitor.
11. Launch the SQL Query Analyzer, attached to the target instance of SQL Server.
12. Run the following query: select SPID, KPID, status, hostname, dbid, cmd from master..sysprocesses
13. The results pane of the Query Analyzer should populate with the results of the query. Note that for each SPID, there is a KPID. Locate the KPID identified in step 9 above. Note the SPID for each KPID identified in step 9.
14. Run the following query: sp_who2. Each row will contain a distinct SPID. Locate the rows for each SPID identified above. Scroll the results window the right, and note the "ProgramName" column. The data contained in this column will identify BY NAME the component of SMS which has initiated a connection to the SQL Server to perform a work item. You will see “ProgramName"s like "SMS_COLLECTION_EVALUATOR", which are the names of the threads within SMS that would attach to the database.

You have now identified which thread in SMS is causing the SQL server to "peg" the CPU(s) of the SQL Server. To see what the work item is that SMS is issuing to the SQL Server which is causing the performance issue, run the following query in Query Analyzer:

DBCC INPUTBUFFER ( SPID ) for the SPID noted above. The output, under the "Event Info" column will contain the first 255 characters of the TSQL in the work item. This should give you clues as to what the SMS component is attempting to do which is pegging the processor(s) on the SQL Server.

2) Monitor the ENT site to determine rather or not CPU is consumed at 100% for an extended amount of time. It’s normal at times for the CPU to spike to 100% as long as it is not for an extended period (this is subjective depending on your server). The goal here is to create a baseline by monitoring this server.

Reference Articles:

234508 SMS: How to Determine the SMS Thread Responsible for High CPU Utilization
https://support.microsoft.com/?id=234508
117559 INFO: How to Correlate Spid, Kpid, and Thread Instance
https://support.microsoft.com/?id=117559
121555 Compression Performance Tuning in Systems Management Server
https://support.microsoft.com/?id=121555
224587 HOW TO: Troubleshoot Application Performance with SQL Server
https://support.microsoft.com/?id=224587
150934 How to Create a Performance Monitor Log for NT Troubleshooting
https://support.microsoft.com/?id=150934

How to determine SMS thread causing high CPU; An oldie but a goodie!

How to Determine SMS Thread Responsible for High CPU Use [sms] Q234508

Additional resources