To find Seesion ID- for Top 5 SQL Statement consuming more cpu

Question

Post reply

To find Seesion ID- for Top 5 SQL Statement consuming more cpu

SQL Galaxy

One Orange Chip

Points: 29472
More actions
July 17, 2011 at 11:29 pm

#389994

Hi,
SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time],
SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY total_worker_time/execution_count DESC;
As per this script, Is it possible to find session id? please help
Thanks
ananda

Viewing 5 posts - 1 through 4 (of 4 total)

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

Christopher Klein SSCrazy Points: 2648 More actions · Answer 1

Could you add in a join against sys.dm_exec_requests on plan_handle?

I think that would get you access to the session_id

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 2

You could modify the example to get the session_id from the sys.dm_exec_connections table

Edit: I following statement is incorrect.

Then join the most_recent_sql_handle from the sys.dm_exec_connections Table on the

sql_handle in the sys.dm_exec_query_stats Table.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Johan Bijnens SSC Guru Points: 135376 More actions · Answer 3

lists the top 50 statements by input/output usage. This script requires Microsoft SQL Server 2005.

http://gallery.technet.microsoft.com/scriptcenter/f3a4dd6d-c521-4fc6-b047-bfad68f59556

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

chrisfradenburg SSCrazy Eights Points: 9592 More actions · Answer 4

What are looking for the sessionID for? sys.dm_os_query_stats is historical data so there may not be a query running at the current time. If you let us know what you're looking to accomplish we may be able to point you in a different direction.