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: Monday, November 3, 2014 4:55 AM
Points: 1,083, Visits: 593
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 6, 2010 6:16 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 12:45 PM
Points: 17,947, Visits: 15,941
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, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #898035
Posted Tuesday, April 6, 2010 8:59 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 10:07 PM
Points: 49, Visits: 849
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 6, 2010 9:03 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 12:45 PM
Points: 17,947, Visits: 15,941
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, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
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, November 17, 2014 1:29 PM
Points: 8, Visits: 160
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


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 12:45 PM
Points: 17,947, Visits: 15,941
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, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #994114
Posted Friday, June 3, 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 3, 2011 9:45 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 10:07 PM
Points: 49, Visits: 849
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: Monday, November 17, 2014 2:08 PM
Points: 321, Visits: 1,290
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