Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234»»

What SQL Statements Are Currently Executing? Expand / Collapse
Author
Message
Posted Tuesday, October 28, 2008 4:28 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 31, 2014 12:57 PM
Points: 174, Visits: 209
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
Post #593294
Posted Tuesday, October 28, 2008 11:08 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:47 AM
Points: 5,448, Visits: 1,401
Very useful and relevant article ...:)


Post #593383
Posted Wednesday, October 29, 2008 9:18 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 20, 2012 4:42 PM
Points: 5, 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.
Post #594067
Posted Wednesday, November 5, 2008 3:23 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, April 7, 2013 10:38 PM
Points: 13, Visits: 26
It really helped me.

Thank you very much :)

Regards, Babu
Post #597173
Posted Friday, November 28, 2008 11:45 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 28, 2008 11:45 PM
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
Post #610651
Posted Friday, December 11, 2009 8:49 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Sunday, November 9, 2014 7:49 AM
Points: 738, Visits: 1,306
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.
Post #832990
Posted Friday, December 11, 2009 9:11 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 4:35 AM
Points: 2,716, Visits: 2,480
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.
Post #833012
Posted Friday, December 11, 2009 12:08 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 8, 2014 5:31 PM
Points: 12, Visits: 108
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
Post #833135
Posted Friday, December 11, 2009 1:30 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Sunday, November 9, 2014 7:49 AM
Points: 738, Visits: 1,306
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.
Post #833178
Posted Friday, December 11, 2009 2:51 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, November 2, 2012 7:05 AM
Points: 75, 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.



Post #833231
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse