Find SQL queries running against multiple spid’s
There are different ways to find sql query running against any particular spid like:-
1) Using the old traditional ways by running
2) Since DBCC Inputbuffer will not give the full query (incase the sql is too long), we can make use of function ” ::fn_get_sql “.
DECLARE @sqltext VARBINARY(128)
SELECT @sqltext = sql_handle FROM sys.sysprocesses WHERE spid = 59
SELECT TEXT FROM ::fn_get_sql(@sqltext)
In both the cases, replace ’59’ with the actual spid against which you want to find the sql query.
Now, if you have to find all sql queries running on a particular server or database and say number of connections are huge.
One way is to check one by one for each spid (like we did above. It will be very time consuming and frustrating) or make use of the following script.
select spid, blocked, login_time,last_batch, status, hostname,
program_name, loginame, st.text as SQL from sysprocesses SP
CROSS APPLY sys.dm_exec_sql_text(SP.sql_handle) AS ST
Output of the query will contain the sql script running against each connection (spid) and some other details about that connection.
This script can save lots of effort, if we want to find sql’s running against multiple spid’s.