SPID of top CPU consuming queries

Question

SPID of top CPU consuming queries

coolchaitu

SSChampion

Points: 10334
More actions
June 5, 2017 at 6:03 pm

#402138

Good Morning Experts,
I am finding top CPU queries using SSMS(Reports->Standard Reports->Performance-Top Queries by Total CPU Time) .This is giving me the queries, but not the SPID of the query. I want to know SPID of the query. How can I get the SPID?

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

the items are from cache, and are not the currently running as of this moment queries; the users could have disconnected long ago. you also get an execution count, so you can see how many times it was executed, so it's not per user/spid.

all those items related to performance are what is in the cache, and were slow, or used the most CPU, etc according to the DMV's

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 2

If you want to see what's currently happening, look to the Activity - All Sessions or Activity - Top Sessions reports. They're querying sys.dm_exec_requests (and other Dynamic Management Views) to retrieve active information. You can also learn how to query the DMVs yourself to gather this data. It's a very useful skill set to have.

"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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Why do you want the session_id?

The point of that report is to identify queries that need tuning to reduce their CPU usage.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

coolchaitu SSChampion Points: 10334 More actions · Answer 4

GilaMonster - Wednesday, June 7, 2017 6:52 AM
Why do you want the session_id?
The point of that report is to identify queries that need tuning to reduce their CPU usage.

Hi Gail,
I want the SPID so that I can get to know the database name,login, program name etc.

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 5

coolchaitu - Wednesday, June 7, 2017 7:01 AM
Hi Gail,
I want the SPID so that I can get to know the database name,login, program name etc.

The query itself is going to show you which database is being used, so you can get that name from there.

Otherwise, as Lowell has already pointed out, that data is aggregated performance metrics taken from the queries that are currently in cache. It won't show individual calls including connection information.

"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

coolchaitu SSChampion Points: 10334 More actions · Answer 6

GilaMonster - Wednesday, June 7, 2017 6:52 AM
Why do you want the session_id?
The point of that report is to identify queries that need tuning to reduce their CPU usage.

Hi Gail,
I am looking for queries that are consuming high CPU. I am getting confused. We have sysprocesses table, sys.dm_exec_requests DMV, sys.dm_exec_query_stats DMV that has cpu info. Which one to use? Which one returns correct information?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 7

See this. You'll want to order by total_worker_time.

John

coolchaitu SSChampion Points: 10334 More actions · Answer 8

John Mitchell-245523 - Tuesday, June 13, 2017 7:08 AM
See this. You'll want to order by total_worker_time.
John

Shouldnt it be total_worker_time/execution_count?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 9

Depends whether you're interested in how long a single execution of a query takes, or in its total load on the processor over time. What's worse, the report that takes 10 minutes but only runs monthly, or the stored procedure that completes in half a second but runs 100 times a minute?

John

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

coolchaitu - Tuesday, June 13, 2017 7:16 AM
John Mitchell-245523 - Tuesday, June 13, 2017 7:08 AM
See this. You'll want to order by total_worker_time.
John
Shouldnt it be total_worker_time/execution_count?

No. The average CPU usage is not going to tell you what queries are consuming the most CPU.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Eric M Russell SSC Guru Points: 125561 More actions · Answer 11

The DMV sys.dm_exec_requests will return one row for each active session / request. The column [total_elapsed_time] contains the total milliseconds since the request started, and by constrast the column [cpu_time] is the total milliseconds of CPU processing time consumed by the request. You can refer to [cpu_time] as an indicator for which currently running SPIDs are consuming the most CPU. For single threaded executions, CPU time will typically be less than elapsed time due to time spent in various wait states (ie: CXPACKET or blocking). For multi-threaded executions, CPU time may be greater than elapsed time, because there are 2 or more CPU doing work in parallel.

You can also join sys.dm_exec_sessions on [session_id] to get login name, client IP address, etc.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

coolchaitu SSChampion Points: 10334 More actions · Answer 12

John Mitchell-245523 - Tuesday, June 13, 2017 7:26 AM
Depends whether you're interested in how long a single execution of a query takes, or in its total load on the processor over time. What's worse, the report that takes 10 minutes but only runs monthly, or the stored procedure that completes in half a second but runs 100 times a minute?
John

Got it John. Thanks a lot.