SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Ian Stirk / What SQL Statements Are Currently Executing? / Latest Posts

RE: What SQL Statements Are Currently Executing?

logicinside22 — Fri, 20 Jan 2012 09:30:54 GMT

nice script very handy...

RE: What SQL Statements Are Currently Executing?

ianstirk — Fri, 03 Jun 2011 09:45:47 GMT

Hi Preet,below is a version that uses only DMVs/DMFs, from section 5.9.1 of the book ([url=http://www.amazon.com/SQL-Server-DMVs-Action-Management/dp/1935182730]http://www.amazon.com/SQL-Server-DMVs-Action-Management/dp/1935182730[/url])ThanksIanSELECT 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_completeFROM sys.dm_exec_requests AS erINNER JOIN sys.dm_exec_sessions AS es ON es.session_id = er.session_idCROSS APPLY sys.dm_exec_sql_text( er.sql_handle) AS qtCROSS APPLY sys.dm_exec_query_plan(er.plan_handle) qpWHERE es.is_user_process=1 AND es.session_Id NOT IN (@@SPID)ORDER BY es.session_id

RE: What SQL Statements Are Currently Executing?

Preet_S — Fri, 03 Jun 2011 04:21:10 GMT

[quote][b]ianstirk (4/6/2010)[/b][hr]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/stirkThanksIan[/quote]Hi Ian Will you post the updated script here, please ?CheersPreet

RE: What SQL Statements Are Currently Executing?

SQLRNNR — Mon, 27 Sep 2010 17:00:26 GMT

[quote][b]David.Lavers (9/27/2010)[/b][hr]When executed on a local machine, why doesn't this stored procedure find itself running?[/quote]The script excludes itself from the result set.[code="sql"]where s.is_user_process=1 AND s.session_Id NOT IN (@@SPID)[/code]

RE: What SQL Statements Are Currently Executing?

David.Lavers — Mon, 27 Sep 2010 16:33:52 GMT

When executed on a local machine, why doesn't this stored procedure find itself running?

RE: What SQL Statements Are Currently Executing?

mEmENT0m0RI — Wed, 21 Apr 2010 11:42:02 GMT

Good work. Very useful script.Thanks!

RE: What SQL Statements Are Currently Executing?

SQLRNNR — Tue, 06 Apr 2010 21:03:56 GMT

[quote][b]ianstirk (4/6/2010)[/b][hr]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/stirkThanksIan[/quote]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.

RE: What SQL Statements Are Currently Executing?

ianstirk — Tue, 06 Apr 2010 20:59:12 GMT

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/stirkThanksIan

RE: What SQL Statements Are Currently Executing?

SQLRNNR — Tue, 06 Apr 2010 18:16:53 GMT

Nice and relevant article. I would have liked to have seen it not use a deprecated object though.

RE: What SQL Statements Are Currently Executing?

BudaCli — Tue, 15 Dec 2009 01:14:53 GMT

Nice article I'd agree that it is my first time seeing CROSS APPLY as well.

RE: What SQL Statements Are Currently Executing?

JStiney — Fri, 11 Dec 2009 14:51:09 GMT

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.

RE: What SQL Statements Are Currently Executing?

Rod at work — Fri, 11 Dec 2009 13:30:44 GMT

[quote][b]kevriley (12/11/2009)[/b][hr]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.[/quote]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 [b]like[/b] it!

RE: What SQL Statements Are Currently Executing?

Steven Hanley — Fri, 11 Dec 2009 12:08:14 GMT

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 uncommittedselect 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_textfrom sys.dm_exec_requests as rjoin sys.dm_exec_sessions as s on (s.session_id=r.session_id)cross apply sys.dm_exec_sql_text( r.sql_handle ) as txtwhere s.is_user_process=1 AND s.session_Id NOT IN (@@SPID)order by datediff( ms, r.start_time, getDate()) desc

RE: What SQL Statements Are Currently Executing?

kevriley — Fri, 11 Dec 2009 09:11:54 GMT

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.

RE: What SQL Statements Are Currently Executing?

Rod at work — Fri, 11 Dec 2009 08:49:37 GMT

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?

RE: What SQL Statements Are Currently Executing?

chandresh_kumar — Fri, 28 Nov 2008 23:45:53 GMT

Very interesting and knowledge increasing article.Thanks.Chandresh Kumar Chhatlani3 PA 46Prabhat NagarSector-5, Hiran MagariUDAIPUR (Rajasthan) - India313002http://chandreshkumar.wetpaint.com

RE: What SQL Statements Are Currently Executing?

Babu-563807 — Wed, 05 Nov 2008 03:23:51 GMT

It really helped me.Thank you very much :)Regards, Babu

RE: What SQL Statements Are Currently Executing?

Troy Gatchell-386101 — Wed, 29 Oct 2008 21:18:27 GMT

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.

RE: What SQL Statements Are Currently Executing?

Anipaul — Tue, 28 Oct 2008 23:08:07 GMT

Very useful and relevant article ...:)

RE: What SQL Statements Are Currently Executing?

CR8NK — Tue, 28 Oct 2008 16:28:11 GMT

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

RE: What SQL Statements Are Currently Executing?

Adam Gojdas — Tue, 28 Oct 2008 14:17:01 GMT

I am planning on using a bit of a modified version that will filter to a specific DB. As a developer I am in need of just seeing what is on the DB I am working on. Here is a crack at a revision to the code to allow this.[code]IF OBJECT_ID(N'[dbo].[dba_WhatSQLIsExecuting]') IS NULL BEGIN EXEC ('CREATE PROCEDURE [dbo].[dba_WhatSQLIsExecuting] AS BEGIN SELECT ''STUB'' END');END;GOALTER PROC [dbo].[dba_WhatSQLIsExecuting] --Inputs @specificDB nvarchar(128) = NULLAS/*--------------------------------------------------------------------Purpose: Shows what individual SQL statements are currently executing.----------------------------------------------------------------------Parameters: None.Revision History: 24/07/2008 Ian_Stirk@yahoo.com Initial version 28/10/2008 added filter for a specific DBExample Usage: To get data from all DBs on that instance 1. exec YourServerName.master.dbo.dba_WhatSQLIsExecuting To get data from a specific DB 2. DECLARE @thisDB nvarchar(128); SET @thisDB = DB_NAME(); exec YourServerName.master.dbo.dba_WhatSQLIsExecuting @thisDB ---------------------------------------------------------------------*/BEGIN DECLARE @thisSPID int; SET @thisSPID = @@SPID; -- Do not lock anything, and do not get held up by any locks. SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED -- What SQL Statements Are Currently Running? SELECT session_Id [SPID] ,ecid [ecid] ,DB_NAME(sp.dbid) [Database] ,nt_username [User] ,er.status [Status] ,wait_type [Wait] ,SUBSTRING (qt.text, er.statement_start_offset/2, (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) [Individual Query] ,qt.text [Parent Query] ,program_name [Program] ,Hostname ,nt_domain ,start_time FROM sys.dm_exec_requests [er] INNER JOIN sys.sysprocesses [sp] ON er.session_id = sp.spid CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) [qt] WHERE session_Id > 50 -- Ignore system spids. AND session_Id NOT IN (@thisSPID) -- Ignore this current statement. --when a specific DB is supplied filter for it, ohterwise get all DBs AND CASE WHEN @specificDB IS NULL THEN N'' ELSE DB_NAME(sp.dbid) END = CASE WHEN @specificDB IS NULL THEN N'' ELSE @specificDB END ORDER BY session_Id, ecidEND[/code]

RE: What SQL Statements Are Currently Executing?

ianstirk — Tue, 28 Oct 2008 10:48:28 GMT

Hi,I’m glad you liked the article, it’s quite interesting to see what is happening with SQL Server and SQL.I hadn’t really thought about creating a view, I typically create utilities as procedures by default, but maybe the advantages of permissioning and sorting make a view more appropriate…ThanksIan

RE: What SQL Statements Are Currently Executing?

timothyawiseman — Tue, 28 Oct 2008 10:26:25 GMT

Very nice article and very useful query. Just out of curiosity, why did you decide to make it a procedure instead of a view?Naturally you would lose that order by, but since you may wish to see it ordered by other things such as User or Status anyway it seems the view would be more flexible.

RE: What SQL Statements Are Currently Executing?

Raj Gujar — Tue, 28 Oct 2008 09:54:52 GMT

This was a good one , Helped me quite a bit.

RE: What SQL Statements Are Currently Executing?

Adam Gojdas — Tue, 28 Oct 2008 09:39:51 GMT

You will need 'VIEW SERVER STATE' permission to run the query that is in the proc.

RE: What SQL Statements Are Currently Executing?

Jamie Pick — Tue, 28 Oct 2008 08:52:17 GMT

This is great. It will be very useful in nailing down those sneaky, long running processes that you can't get a peek at with the standard tools. Thanks!

RE: What SQL Statements Are Currently Executing?

o-3463522 — Tue, 28 Oct 2008 07:40:11 GMT

How to change the compatibility mode?

RE: What SQL Statements Are Currently Executing?

Royce Powers — Tue, 28 Oct 2008 06:41:14 GMT

That explains it! Thanks!

RE: What SQL Statements Are Currently Executing?

dhayes-797480 — Tue, 28 Oct 2008 06:40:15 GMT

I got that same message when trying this script on a SQL2000 server. This script is only for SQL2005.

RE: What SQL Statements Are Currently Executing?

chinn — Tue, 28 Oct 2008 06:36:49 GMT

This is very Useful..Thanks Very Much!

RE: What SQL Statements Are Currently Executing?

Royce Powers — Tue, 28 Oct 2008 06:31:57 GMT

Msg 170, Level 15, State 1, Procedure dba_WhatSQLIsExecuting, Line 26Line 26: Incorrect syntax near 'MAX'.-- IRADBA Wannabe

RE: What SQL Statements Are Currently Executing?

dhayes-797480 — Tue, 28 Oct 2008 06:09:31 GMT

Thank You Kev. I'm not new to SQL, I'm just not a SQL DBA. I work with Sql every day, just don't normally call Stored procedures. Primarily do backups, and Database creations.Thanks again for the info.Don

RE: What SQL Statements Are Currently Executing?

kevriley — Tue, 28 Oct 2008 05:59:25 GMT

dhayes,I guess you are new to SQL :)The first time you ran the script, you were creating a stored procedure, teh scond time errored, as you found out, because the proc already exists!To run the proc useexec dba_WhatSQLIsExecutingensuring you are in the right database.....Kev

RE: What SQL Statements Are Currently Executing?

dhayes-797480 — Tue, 28 Oct 2008 05:56:16 GMT

I ran this in a new query window and it ran successfully. I did not get an output. How do I view the results???? If I run it again I get the following message. Msg 2714, Level 16, State 3, Procedure dba_WhatSQLIsExecuting, Line 17There is already an object named 'dba_WhatSQLIsExecuting' in the database.

RE: What SQL Statements Are Currently Executing?

Mark Underhill — Tue, 28 Oct 2008 03:36:16 GMT

Kev,Thanks - the server was 2005 but the compatibility level was still 2000. It worked find after that change.Mark

RE: What SQL Statements Are Currently Executing?

kevriley — Tue, 28 Oct 2008 03:21:31 GMT

Mark,are you sure you are running this against a SQL2005 instance?Kev

RE: What SQL Statements Are Currently Executing?

Mark Underhill — Tue, 28 Oct 2008 02:45:18 GMT

I get this error when creating the SP: Msg 102, Level 15, State 1, Procedure dba_WhatSQLIsExecuting, Line 42 Incorrect syntax near '.'.Line 42 is the 'CROSS APPLY' line.Can anyone help?

RE: What SQL Statements Are Currently Executing?

Gerhard Schmeusser — Tue, 28 Oct 2008 01:02:11 GMT

Really helpful

RE: What SQL Statements Are Currently Executing?

Jeff Moden — Mon, 27 Oct 2008 21:52:55 GMT

Heh... and I get my butt chewed for using deprecated forms of aliased column names. ;) I don't feel so bad, now.

What SQL Statements Are Currently Executing?

ianstirk — Mon, 27 Oct 2008 21:50:46 GMT

Comments posted to this topic are about the item [B]What SQL Statements Are Currently Executing?[/B]