November 15, 2018 at 8:59 am
Hello experts,
I've used the steps mentioned in this excellent article to get the general idea of how to map SPID to KPID using Perfmon in an effort to find which SQL SPID might be driving high CPU usage on a SQL Server box.
How to find out how much CPU a SQL Server process is really using
https://www.mssqltips.com/sqlservertip/2454/how-to-find-out-how-much-cpu-a-sql-server-process-is-really-using/
However, I am a bit stumped by this section and hope someone can help me.
Click on Add counters and select the "Thread" object in the drop down.
Select these counters at the same time:
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.
What I wondered as I worked through this part of the article is: SPIDs are dynamic in SQL Server, so while I run the Perfmon trace in question, it will be collecting data for the sqlservr instances I started with.
But over the course of the trace, new SQL SPIDs could start up and existing ones could end. So if the offending SPID is one that doesn't start until after the trace is running, won't the trace miss that SPID?
Thanks for any help on this, including whether I could use Extended Events or some other method to take into account whichever SPIDs happen to start after a trace is already running. I've tried to use wildcards (*) for the sqlservr instances but haven't been able to get them to collect the data.
Thanks again.
- 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
November 15, 2018 at 9:50 am
Yeah. You'll have to correlate in the IDs. A SPID can, and should, be reused, so without the time correlation, this spid, at this time, you can't make a direct comparison.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 15, 2018 at 11:14 am
Grant Fritchey - Thursday, November 15, 2018 9:50 AMYeah. You'll have to correlate in the IDs. A SPID can, and should, be reused, so without the time correlation, this spid, at this time, you can't make a direct comparison.
Hi Grant,
Thanks for your help. I'll have to proceed as recommended in the article and perhaps start the trace closer to the time in question when I see the CPU spike to increase the chances that I get the right SPID.
Thanks again.
- 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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply