SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


What SQL Statements Are Currently Executing?


What SQL Statements Are Currently Executing?

Author
Message
CR8NK
CR8NK
SSC-Enthusiastic
SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)

Group: General Forum Members
Points: 192 Visits: 227
Great code, thanks!

I would also propose creating a view for this instead of a proc. It fits in with the DMVs provided by SQL and allows for easier joining to other system data and extensibility (ORDER BY, Filtering by DB, etc.)

thanks,

Creighton
Anipaul
Anipaul
SSCrazy Eights
SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)

Group: General Forum Members
Points: 9551 Visits: 1407
Very useful and relevant article ...Smile



Troy Gatchell-386101
Troy Gatchell-386101
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 38
If you want to use this query going forward you should join on sys.dm_exec_sessions instead of sys.sysprocesses. This is a backwards compatibility view and is scheduled for deprecation. I wrote a similar article about 2 years ago for SSWUG (Custom Scripts to get DMV results fast). It shows you how to pull the query plan for currently executing requests as well for further debug.

For more info use: sys.dm_exec_query_stats which has a plan_handle to do a cross apply on sys.dm_exec_query_plan to get the XML format of the execution plan.

You can look all this up in books online.
Babu-563807
Babu-563807
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 26
It really helped me.

Thank you very much Smile

Regards, Babu
chandresh_kumar
chandresh_kumar
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 6
Very interesting and knowledge increasing article.

Thanks.

Chandresh Kumar Chhatlani
3 PA 46
Prabhat Nagar
Sector-5, Hiran Magari
UDAIPUR (Rajasthan) - India
313002
http://chandreshkumar.wetpaint.com
Rod
Rod
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6785 Visits: 2165
I've just read this article, copied the code and created the SP in my master database. Using the code I created the SP dba_WhatSQLIsExecuting, and ran it. However, nothing showed up. Is it because I ran it, in the master database? Do I have to be in one of our user databases in order for something to show up?

Kindest Regards,Rod
Connect with me on LinkedIn.
kevriley
kevriley
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4216 Visits: 2634
No it's not because you ran it in the master db - it will report activity across all databases.

It could be that nothing was actually running at that point. Try running over and over to see if you get anything.

Alternatively remove the line that ignores the current statement - then at least you should see yourself.
Steven Hanley
Steven Hanley
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 113
If I'm wondering what's running, I usually want a little extra information about those queries. Here's a very similiar version with some of the extra information I like to see:

set transaction isolation level read uncommitted

select s.[host_name]
, s.login_name
-- , s.is_user_process
, s.program_name
, r.command
, r.status
, s.session_id
, r.blocking_session_id
-- , r.request_id
, datediff( ms, r.start_time, getDate() ) as age_ms
, r.total_elapsed_time
, r.cpu_time
, r.percent_complete
, r.row_count
, r.granted_query_memory
, r.logical_reads
, txt.[text] as query_text
from sys.dm_exec_requests as r
join sys.dm_exec_sessions as s on (s.session_id=r.session_id)
cross apply sys.dm_exec_sql_text( r.sql_handle ) as txt
where s.is_user_process=1 AND s.session_Id NOT IN (@@SPID)
order by datediff( ms, r.start_time, getDate()) desc
Rod
Rod
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6785 Visits: 2165
kevriley (12/11/2009)
No it's not because you ran it in the master db - it will report activity across all databases.

It could be that nothing was actually running at that point. Try running over and over to see if you get anything.

Alternatively remove the line that ignores the current statement - then at least you should see yourself.


OK, I did as you suggested, and at first didn't see any activity. But it is Friday, not all of our users are here, and so I decided to get into one of our apps and run a Crystal Report there that I know is a hog. Sure enough, that SP started showing me the line being executed, what database, etc. Cool. I like it!

Kindest Regards,Rod
Connect with me on LinkedIn.
JStiney
JStiney
SSC-Enthusiastic
SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)

Group: General Forum Members
Points: 129 Visits: 446
Thanks for the script. I can see that it will be useful.

This is the kind of script that I will create as a .sql file in a regular network directory for sql scripts. That way you can execute it on any one of your servers just by pulling it up in Management Studio and you can easily change it as needed and either save the changes or not. If you make it a stored proc or a view, then you are limited to one server or you have to create and maintain it in many places.



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search