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, December 15, 2009 1:14 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, November 05, 2013 6:08 AM
Points: 1,076, Visits: 591
Nice article I'd agree that it is my first time seeing CROSS APPLY as well.

What you don't know won't hurt you but what you know will make you plan to know better
Post #834332
Posted Tuesday, April 06, 2010 6:16 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:30 PM
Points: 20,467, Visits: 14,104
Nice and relevant article. I would have liked to have seen it not use a deprecated object though.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #898035
Posted Tuesday, April 06, 2010 8:59 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 3:05 AM
Points: 48, Visits: 833
Hi Jason,

I'm glad you liked the article.

There is an updated version of this script, without the deprecated objects, given in the book I am writing "SQL Server DMVs in Action". You can get the first chapter free here: www.manning.com/stirk

Thanks
Ian
Post #898088
Posted Tuesday, April 06, 2010 9:03 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:30 PM
Points: 20,467, Visits: 14,104
ianstirk (4/6/2010)
Hi Jason,

I'm glad you liked the article.

There is an updated version of this script, without the deprecated objects, given in the book I am writing "SQL Server DMVs in Action". You can get the first chapter free here: www.manning.com/stirk

Thanks
Ian


That's cool.

I took the liberty of creating a script similar in nature that I use in place of sp_who2.

I will be posting that script to the web soon.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #898090
Posted Wednesday, April 21, 2010 11:42 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 01, 2013 12:37 PM
Points: 8, Visits: 158
Good work. Very useful script.

Thanks!
Post #907982
Posted Monday, September 27, 2010 4:33 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 27, 2010 5:01 PM
Points: 1, Visits: 13
When executed on a local machine, why doesn't this stored procedure find itself running?

Post #994102
Posted Monday, September 27, 2010 5:00 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:30 PM
Points: 20,467, Visits: 14,104
David.Lavers (9/27/2010)
When executed on a local machine, why doesn't this stored procedure find itself running?



The script excludes itself from the result set.
where s.is_user_process=1 AND s.session_Id NOT IN (@@SPID)





Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #994114
Posted Friday, June 03, 2011 4:21 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, December 20, 2013 4:45 AM
Points: 236, Visits: 239
ianstirk (4/6/2010)
Hi Jason,

I'm glad you liked the article.

There is an updated version of this script, without the deprecated objects, given in the book I am writing "SQL Server DMVs in Action". You can get the first chapter free here: www.manning.com/stirk

Thanks
Ian



Hi Ian

Will you post the updated script here, please ?

Cheers
Preet
Post #1119302
Posted Friday, June 03, 2011 9:45 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 3:05 AM
Points: 48, Visits: 833
Hi Preet,

below is a version that uses only DMVs/DMFs, from section 5.9.1 of the book (http://www.amazon.com/SQL-Server-DMVs-Action-Management/dp/1935182730)

Thanks
Ian


SELECT
es.session_id, es.host_name, es.login_name
, er.status, DB_NAME(database_id) AS DatabaseName
, SUBSTRING (qt.text,(er.statement_start_offset/2) + 1,
((CASE WHEN er.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE er.statement_end_offset
END - er.statement_start_offset)/2) + 1) AS [Individual Query]
, qt.text AS [Parent Query]
, es.program_name, er.start_time, qp.query_plan
, er.wait_type, er.total_elapsed_time, er.cpu_time, er.logical_reads
, er.blocking_session_id, er.open_transaction_count, er.last_wait_type
, er.percent_complete
FROM sys.dm_exec_requests AS er
INNER JOIN sys.dm_exec_sessions AS es ON es.session_id = er.session_id
CROSS APPLY sys.dm_exec_sql_text( er.sql_handle) AS qt
CROSS APPLY sys.dm_exec_query_plan(er.plan_handle) qp
WHERE es.is_user_process=1
AND es.session_Id NOT IN (@@SPID)
ORDER BY es.session_id
Post #1119526
Posted Friday, January 20, 2012 9:30 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, April 18, 2014 1:05 PM
Points: 304, Visits: 1,171
nice script very handy...

Aim to inspire rather than to teach.
SQL Server DBA
Post #1239584
« Prev Topic | Next Topic »

Add to briefcase «««1234

Permissions Expand / Collapse