<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by Ian Stirk
»
What SQL Statements Are Currently Executing?
40 posts, Page 4 of 4
««
«
1
2
3
4
What SQL Statements Are Currently Executing?
Rate Topic
Display Mode
Topic Options
Author
Message
BudaCli
BudaCli
Posted Tuesday, December 15, 2009 1:14 AM
Ten Centuries
Group: General Forum Members
Last Login: Today @ 7:16 AM
Points: 1,046,
Visits: 575
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
SQLRNNR
SQLRNNR
Posted Tuesday, April 06, 2010 6:16 PM
SSCoach
Group: General Forum Members
Last Login: Today @ 5:03 PM
Points: 18,853,
Visits: 12,438
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 2008
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
ianstirk
ianstirk
Posted Tuesday, April 06, 2010 8:59 PM
SSC Rookie
Group: General Forum Members
Last Login: Today @ 6:41 AM
Points: 47,
Visits: 778
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
SQLRNNR
SQLRNNR
Posted Tuesday, April 06, 2010 9:03 PM
SSCoach
Group: General Forum Members
Last Login: Today @ 5:03 PM
Points: 18,853,
Visits: 12,438
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 2008
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
mEmENT0m0RI
mEmENT0m0RI
Posted Wednesday, April 21, 2010 11:42 AM
Forum Newbie
Group: General Forum Members
Last Login: Friday, February 08, 2013 9:10 AM
Points: 6,
Visits: 157
Good work. Very useful script.
Thanks!
Post #907982
David.Lavers
David.Lavers
Posted Monday, September 27, 2010 4:33 PM
Forum 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
SQLRNNR
SQLRNNR
Posted Monday, September 27, 2010 5:00 PM
SSCoach
Group: General Forum Members
Last Login: Today @ 5:03 PM
Points: 18,853,
Visits: 12,438
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 2008
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
Preet_S
Preet_S
Posted Friday, June 03, 2011 4:21 AM
SSC Veteran
Group: General Forum Members
Last Login: Wednesday, May 01, 2013 5:15 AM
Points: 236,
Visits: 235
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
ianstirk
ianstirk
Posted Friday, June 03, 2011 9:45 AM
SSC Rookie
Group: General Forum Members
Last Login: Today @ 6:41 AM
Points: 47,
Visits: 778
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
logicinside22
logicinside22
Posted Friday, January 20, 2012 9:30 AM
SSC Veteran
Group: General Forum Members
Last Login: Today @ 12:19 PM
Points: 280,
Visits: 1,028
nice script very handy...
Aim to inspire rather than to teach.
SQL Server DBA
Post #1239584
« Prev Topic
|
Next Topic »
40 posts, Page 4 of 4
««
«
1
2
3
4
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
