Find currently executing Query

https://blog.sqlauthority.com/2009/07/19/sql-server-get-last-running-query-based-on-spid/

SQL SERVER – Get Last Running Query Based on SPID

July 19, 2009Pinal DaveSQL, SQL Server, SQL Tips and Tricks31 comments

We often need to find the last running query or based on SPID need to know which query was executed. SPID is returns sessions ID of the current user process. The acronym SPID comes from the name of its earlier version, Server Process ID.

To know which sessions are running currently, run the following command:

SELECT @@SPIDGO

In our case, we got SPID 57, which means the session that is running this command has ID of 57.

Now, let us open another session and run the same command. Here we get different IDs for different sessions.

In our case, we got SPID 61. Please note here that this ID may or may not be sequential.

In session with SPID 61, we will run any query. In session with SPID 57, we will see which query was run in session with SPID 61.

Let us run a simple SELECT statement in session with SPID 61 and in session with SPID 57 run the following command.

DBCC INPUTBUFFER(61)GO

Now, here in DBCC command we have passed the SPID of previous session; we will see the text below. The following image illustrates that we get the latest run query in our input buffer.

There are several ways to find out what is the latest run query from system table sys.sysprocesses.

DECLARE @sqltext VARBINARY(128)SELECT @sqltext = sql_handleFROM sys.sysprocessesWHERE spid = 61SELECT TEXT
FROM sys.dm_exec_sql_text(@sqltext)GO

The following image portrays that we get the latest run query in our input buffer.

Please note that in any session there may be multiple running queries, but the buffer only saves the last query and that is what we will be able to retrieve.

There is one more way to achieve the same thing – using function fn_get_sql

DECLARE @sqltext VARBINARY(128)SELECT @sqltext = sql_handleFROM sys.sysprocessesWHERE spid = 61SELECT TEXT
FROM ::fn_get_sql(@sqltext)GO

All the three methods are same but I always prefer method 2 where I have used sys.sysprocesses.

Today, we have explored a very simple topic. Let me know if you find it useful.

SQL SERVER – Get Query Running in Session

October 1, 2010Pinal DaveSQL, SQL Server, SQL Tips and Tricks5 comments

I was recently looking for syntax where I needed a query running in any particular session. I always remembered the syntax and ha d actually written it down before, but somehow it was not coming to mind quickly this time. I searched online and I ended up on my own article written last year SQL SERVER – Get Last Running Query Based on SPID. I felt that I am getting old because I forgot this really simple syntax.

This post is a refresher to me. I knew it was something so familiar since I have used this syntax so many times during my performance tuning project.

Run the following query to find out what the latest query that was executed in the session. There are various methods mentioned in my earlier post, so here I am picking only the one that I use most of the time.

Please use Shortcut – CTRL+T or enable “result to text” in the resultset to get formatted output.

DECLARE @sqltext VARBINARY(128)SELECT @sqltext = sql_handleFROM sys.sysprocessesWHERE spid = (YourSessionID)SELECT TEXT
FROM sys.dm_exec_sql_text(@sqltext)GO

You can find a range of active session IDs in your system by running system stored procedure sp_who2.

The following is the resultset where I have selected the session id that is the same as from where I am running above statement.

Additionally, you can use following T-SQL script as well.

SELECT TEXT
FROM sys.dm_exec_connectionsCROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle)WHERE session_id = (yoursessionID)GO