Who is using my database

Who is using my database


Whenever users report that a system is slow, there can be many reasons. It can also be several different problems causing the system to be slow. However, more often than not, the problem is due to your code accessing data from something, and it is usually a database.

Even the best and most optimised database application can still have problems; perhaps too many users are running complex processes or just one long-running query impacting every other function.

How do we check who is running what?

There are lots of built-in SPs and Queries that allow you to view what is running right now, including: –

SELECT * FROM sys.dm_exec_requests

This outputs something like this: –

image-85-1024x141 Who is using my database


SELECT      r.start_time [Start Time],session_ID [SPID],
            DB_NAME(database_id) [Database],
            CASE WHEN statement_end_offset=-1 OR statement_end_offset=0
            THEN (DATALENGTH(t.Text)-r.statement_start_offset/2)+1
            ELSE (r.statement_end_offset-r.statement_start_offset)/2+1
            END) [Executing SQL],
FROM        sys.dm_exec_requests r
OUTER APPLY sys.dm_exec_sql_text(sql_handle) t
WHERE       session_id != @@SPID -- don't show this query
AND         session_id > 50 -- don't show system queries
ORDER BY    r.start_time

Which outputs something like this: –

image-86-1024x99 Who is using my database

What is really running? What SQL?

The problem with the above scripts is that you can’t easily see the SQL being executed.

So, the SQL below can be helpful in such cases: –

    SessionId           = ses.session_id
    ,[Database]    = DB_Name(er.database_id)
    ,[Login]            = ses.login_name
    ,Host               = ses.host_name
    ,StartTime          = er.start_time
    ,ClientAddress      = con.client_net_address
    ,SQLStatement       = st.text
FROM sys.dm_exec_requests er
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st
LEFT JOIN sys.dm_exec_sessions ses ON ses.session_id = er.session_id
LEFT JOIN sys.dm_exec_connections con ON con.session_id = ses.session_id
WHERE ses.is_user_process = 0x1
AND ses.session_id != @@SPID

It produces this: –

image-88-1024x118 Who is using my database

You can see who is running what.


Hi, my name is Stephen Finchett. I have been a software engineer for over 30 years and worked on complex, business critical, multi-user systems for all of my career. For the last 15 years, I have been concentrating on web based solutions using the Microsoft Stack including ASP.Net, C#, TypeScript, SQL Server and running everything at scale within Kubernetes.