Wednesday, September 3, 2008

Find Currently running query/process in MS SQL Server 2005

Hi Friends,

While managing a server, we might come across a point where we want to know which of our database is loading on the server.

Let's take a case. We have hosted many sites on a server with MS SQL Server 2005. Now the traffic on some sites suddenly increase or new code is uploaded to some of site. This causes the MS SQL Server 2005 and as well server to be on a hike of 100% CPU usage.

Now we want to investigate that which query or process of MS SQL Server 2005 causing to take the CPU on the 100%  of usage. I found many people that are searching for finding currently running query/process in MS SQL Server 2005. Here is a help for them to find out which queries are currently running in MS SQL Server 2005.

 

You can run this query.

SELECT * FROM MASTER..SYSPROCESSES

Other queries I found are

 

SELECT   st.text, r.session_id, r.status, r.command, r.cpu_time, r.total_elapsed_time
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st

 

Now if you have the session id of your request then you can try with this query

SELECT
st.text
FROM
sys.dm_exec_requests r
CROSS APPLY
sys.dm_exec_sql_text(sql_handle) AS st


WHERE r.session_id = <spid number>

Here <spid number> is your session id.