Wednesday, February 15, 2017

Find currently executing Query

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:
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.
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.
SQL SERVER - Get Last Running Query Based on SPID spid1
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
The following image portrays that we get the latest run query in our input buffer.
SQL SERVER - Get Last Running Query Based on SPID spid2
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
SQL SERVER - Get Last Running Query Based on SPID spid3
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.

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
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.
SQL SERVER – Get Query Running in Session sysprocesses
Additionally, you can use following T-SQL script as well.
sys.dm_exec_connectionsCROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle)WHERE session_id (yoursessionID)GO

No comments:

Post a Comment