SQLServerCentral.com / Article Discussions by Author / Article Discussions / Discuss content posted by Ian Stirk / What SQL Statements Are Currently Executing? / Latest PostsInstantForum.NET v99.99.99SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 09 Dec 2016 09:31:28 GMT20RE: What SQL Statements Are Currently Executing?http://www.sqlservercentral.com/Forums/Topic592610-1358-1.aspxnice script very handy...Fri, 20 Jan 2012 09:30:54 GMTlogicinside22RE: What SQL Statements Are Currently Executing?http://www.sqlservercentral.com/Forums/Topic592610-1358-1.aspxHi 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_idFri, 03 Jun 2011 09:45:47 GMTianstirkRE: What SQL Statements Are Currently Executing?http://www.sqlservercentral.com/Forums/Topic592610-1358-1.aspx[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 ?CheersPreetFri, 03 Jun 2011 04:21:10 GMTPreet_SRE: What SQL Statements Are Currently Executing?http://www.sqlservercentral.com/Forums/Topic592610-1358-1.aspx[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]Mon, 27 Sep 2010 17:00:26 GMTSQLRNNRRE: What SQL Statements Are Currently Executing?http://www.sqlservercentral.com/Forums/Topic592610-1358-1.aspxWhen executed on a local machine, why doesn't this stored procedure find itself running?Mon, 27 Sep 2010 16:33:52 GMTDavid.LaversRE: What SQL Statements Are Currently Executing?http://www.sqlservercentral.com/Forums/Topic592610-1358-1.aspxGood work. Very useful script.Thanks!Wed, 21 Apr 2010 11:42:02 GMTmEmENT0m0RIRE: What SQL Statements Are Currently Executing?http://www.sqlservercentral.com/Forums/Topic592610-1358-1.aspx[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.Tue, 06 Apr 2010 21:03:56 GMTSQLRNNRRE: What SQL Statements Are Currently Executing?http://www.sqlservercentral.com/Forums/Topic592610-1358-1.aspxHi 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/stirkThanksIanTue, 06 Apr 2010 20:59:12 GMTianstirkRE: What SQL Statements Are Currently Executing?http://www.sqlservercentral.com/Forums/Topic592610-1358-1.aspxNice and relevant article. I would have liked to have seen it not use a deprecated object though.Tue, 06 Apr 2010 18:16:53 GMTSQLRNNRRE: What SQL Statements Are Currently Executing?http://www.sqlservercentral.com/Forums/Topic592610-1358-1.aspxNice article I'd agree that it is my first time seeing CROSS APPLY as well.Tue, 15 Dec 2009 01:14:53 GMTBudaCliRE: What SQL Statements Are Currently Executing?http://www.sqlservercentral.com/Forums/Topic592610-1358-1.aspxThanks 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.Fri, 11 Dec 2009 14:51:09 GMTJStineyRE: What SQL Statements Are Currently Executing?http://www.sqlservercentral.com/Forums/Topic592610-1358-1.aspx[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!Fri, 11 Dec 2009 13:30:44 GMTRod at workRE: What SQL Statements Are Currently Executing?http://www.sqlservercentral.com/Forums/Topic592610-1358-1.aspxIf 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()) descFri, 11 Dec 2009 12:08:14 GMTSteven HanleyRE: What SQL Statements Are Currently Executing?http://www.sqlservercentral.com/Forums/Topic592610-1358-1.aspxNo 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.Fri, 11 Dec 2009 09:11:54 GMTkevrileyRE: What SQL Statements Are Currently Executing?http://www.sqlservercentral.com/Forums/Topic592610-1358-1.aspxI'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?Fri, 11 Dec 2009 08:49:37 GMTRod at workRE: What SQL Statements Are Currently Executing?http://www.sqlservercentral.com/Forums/Topic592610-1358-1.aspxVery interesting and knowledge increasing article.Thanks.Chandresh Kumar Chhatlani3 PA 46Prabhat NagarSector-5, Hiran MagariUDAIPUR (Rajasthan) - India313002http://chandreshkumar.wetpaint.comFri, 28 Nov 2008 23:45:53 GMTchandresh_kumarRE: What SQL Statements Are Currently Executing?http://www.sqlservercentral.com/Forums/Topic592610-1358-1.aspxIt really helped me.Thank you very much :)Regards, BabuWed, 05 Nov 2008 03:23:51 GMTBabu-563807RE: What SQL Statements Are Currently Executing?http://www.sqlservercentral.com/Forums/Topic592610-1358-1.aspxIf 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.Wed, 29 Oct 2008 21:18:27 GMTTroy Gatchell-386101RE: What SQL Statements Are Currently Executing?http://www.sqlservercentral.com/Forums/Topic592610-1358-1.aspxVery useful and relevant article ...:)Tue, 28 Oct 2008 23:08:07 GMTAnipaulRE: What SQL Statements Are Currently Executing?http://www.sqlservercentral.com/Forums/Topic592610-1358-1.aspxGreat 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,CreightonTue, 28 Oct 2008 16:28:11 GMTCR8NKRE: What SQL Statements Are Currently Executing?http://www.sqlservercentral.com/Forums/Topic592610-1358-1.aspxI 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 &gt; 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]Tue, 28 Oct 2008 14:17:01 GMTAdam GojdasRE: What SQL Statements Are Currently Executing?http://www.sqlservercentral.com/Forums/Topic592610-1358-1.aspxHi,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…ThanksIanTue, 28 Oct 2008 10:48:28 GMTianstirkRE: What SQL Statements Are Currently Executing?http://www.sqlservercentral.com/Forums/Topic592610-1358-1.aspxVery 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.Tue, 28 Oct 2008 10:26:25 GMTtimothyawisemanRE: What SQL Statements Are Currently Executing?http://www.sqlservercentral.com/Forums/Topic592610-1358-1.aspxThis was a good one , Helped me quite a bit.Tue, 28 Oct 2008 09:54:52 GMTRaj GujarRE: What SQL Statements Are Currently Executing?http://www.sqlservercentral.com/Forums/Topic592610-1358-1.aspxYou will need 'VIEW SERVER STATE' permission to run the query that is in the proc.Tue, 28 Oct 2008 09:39:51 GMTAdam GojdasRE: What SQL Statements Are Currently Executing?http://www.sqlservercentral.com/Forums/Topic592610-1358-1.aspxThis 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!Tue, 28 Oct 2008 08:52:17 GMTJamie PickRE: What SQL Statements Are Currently Executing?http://www.sqlservercentral.com/Forums/Topic592610-1358-1.aspxHow to change the compatibility mode?Tue, 28 Oct 2008 07:40:11 GMTo-3463522RE: What SQL Statements Are Currently Executing?http://www.sqlservercentral.com/Forums/Topic592610-1358-1.aspxThat explains it! Thanks!Tue, 28 Oct 2008 06:41:14 GMTRoyce PowersRE: What SQL Statements Are Currently Executing?http://www.sqlservercentral.com/Forums/Topic592610-1358-1.aspxI got that same message when trying this script on a SQL2000 server. This script is only for SQL2005.Tue, 28 Oct 2008 06:40:15 GMTdhayes-797480RE: What SQL Statements Are Currently Executing?http://www.sqlservercentral.com/Forums/Topic592610-1358-1.aspxThis is very Useful..Thanks Very Much!Tue, 28 Oct 2008 06:36:49 GMTchinnRE: What SQL Statements Are Currently Executing?http://www.sqlservercentral.com/Forums/Topic592610-1358-1.aspxMsg 170, Level 15, State 1, Procedure dba_WhatSQLIsExecuting, Line 26Line 26: Incorrect syntax near 'MAX'.-- IRADBA WannabeTue, 28 Oct 2008 06:31:57 GMTRoyce PowersRE: What SQL Statements Are Currently Executing?http://www.sqlservercentral.com/Forums/Topic592610-1358-1.aspxThank 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.DonTue, 28 Oct 2008 06:09:31 GMTdhayes-797480RE: What SQL Statements Are Currently Executing?http://www.sqlservercentral.com/Forums/Topic592610-1358-1.aspxdhayes,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.....KevTue, 28 Oct 2008 05:59:25 GMTkevrileyRE: What SQL Statements Are Currently Executing?http://www.sqlservercentral.com/Forums/Topic592610-1358-1.aspxI 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.Tue, 28 Oct 2008 05:56:16 GMTdhayes-797480RE: What SQL Statements Are Currently Executing?http://www.sqlservercentral.com/Forums/Topic592610-1358-1.aspxKev,Thanks - the server was 2005 but the compatibility level was still 2000. It worked find after that change.MarkTue, 28 Oct 2008 03:36:16 GMTMark UnderhillRE: What SQL Statements Are Currently Executing?http://www.sqlservercentral.com/Forums/Topic592610-1358-1.aspxMark,are you sure you are running this against a SQL2005 instance?KevTue, 28 Oct 2008 03:21:31 GMTkevrileyRE: What SQL Statements Are Currently Executing?http://www.sqlservercentral.com/Forums/Topic592610-1358-1.aspxI 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?Tue, 28 Oct 2008 02:45:18 GMTMark UnderhillRE: What SQL Statements Are Currently Executing?http://www.sqlservercentral.com/Forums/Topic592610-1358-1.aspxReally helpfulTue, 28 Oct 2008 01:02:11 GMTGerhard SchmeusserRE: What SQL Statements Are Currently Executing?http://www.sqlservercentral.com/Forums/Topic592610-1358-1.aspxHeh... and I get my butt chewed for using deprecated forms of aliased column names. ;) I don't feel so bad, now.Mon, 27 Oct 2008 21:52:55 GMTJeff ModenWhat SQL Statements Are Currently Executing?http://www.sqlservercentral.com/Forums/Topic592610-1358-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/DMV/64425/">What SQL Statements Are Currently Executing?</A>[/B]Mon, 27 Oct 2008 21:50:46 GMTianstirk