Browse cheat sheets · SQL Server
SQL Server Login, Host and Application Activity
Group SQL Server activity by login, host, application, and database so you can identify which client or workload is generating connections, requests, CPU, reads, or blocking.
On this page
- Problem
- Quick answer
- Code / commands
- 1. Group active requests by login
- 2. Group active requests by application
- 3. Group active requests by host
- 4. Group activity by login, host, app, and database
- 5. Count sessions, including sleeping connections
- 6. Find sessions with open transactions by application
- 7. Find blocked requests by application
- 8. Find blockers and their owning application
- 9. Show detailed sessions for one application
- 10. Inspect connection source and authentication details
- What the result means
- What to look for
- One application dominates active requests
- One login dominates CPU or reads
- Many sessions but few active requests
- Many blocked requests from one application
- Blocker is sleeping with an open transaction
- Unexpected application or host
- Production notes
- host_name and program_name are client-supplied
- Active requests and connected sessions answer different questions
- Aggregate DMV results are point-in-time snapshots
- Shared logins reduce observability
- Connection pooling affects session counts
- Permissions vary by version
- Common mistakes
- 1. Treating host_name as authoritative
- 2. Assuming many sleeping sessions are a problem
- 3. Blaming the blocked application
- 4. Grouping only by login
- 5. Disabling a login during an incident without ownership confirmation
- 6. Treating one snapshot as traffic history
- 7. Ignoring open transactions
- Next step
- Related deep dives
Problem
A SQL Server instance is busy, request volume increased, or one application appears to be causing pressure.
You need to answer questions such as:
- Which login is generating the most active requests?
- Which host is opening the most sessions?
- Which application is consuming CPU or reads?
- Which database is receiving the activity?
- Is one login responsible for blocking?
- Are there unexpected clients connected to the instance?
This is especially useful during incidents where multiple applications share the same SQL Server.
Quick answer
Start by grouping active requests by login, host, application, and database.
SELECT
s.login_name,
s.host_name,
s.program_name,
DB_NAME(r.database_id) AS database_name,
COUNT(*) AS active_request_count,
SUM(r.cpu_time) AS total_cpu_ms,
SUM(r.logical_reads) AS total_logical_reads,
SUM(r.reads) AS total_physical_reads,
SUM(r.writes) AS total_writes,
SUM(
CASE
WHEN r.blocking_session_id > 0 THEN 1
ELSE 0
END
) AS blocked_request_count,
MAX(r.total_elapsed_time) AS max_elapsed_ms
FROM sys.dm_exec_requests AS r
INNER JOIN sys.dm_exec_sessions AS s
ON s.session_id = r.session_id
WHERE
r.session_id <> @@SPID
AND s.is_user_process = 1
GROUP BY
s.login_name,
s.host_name,
s.program_name,
DB_NAME(r.database_id)
ORDER BY
active_request_count DESC,
total_cpu_ms DESC;
Use this first when you need to identify which client workload is active right now.
Code / commands
1. Group active requests by login
SELECT
s.login_name,
COUNT(*) AS active_request_count,
SUM(r.cpu_time) AS total_cpu_ms,
SUM(r.logical_reads) AS total_logical_reads,
SUM(r.reads) AS total_physical_reads,
SUM(r.writes) AS total_writes
FROM sys.dm_exec_requests AS r
INNER JOIN sys.dm_exec_sessions AS s
ON s.session_id = r.session_id
WHERE
r.session_id <> @@SPID
AND s.is_user_process = 1
GROUP BY
s.login_name
ORDER BY
active_request_count DESC,
total_cpu_ms DESC;
Use this when the same SQL login or contained user may be shared by many application sessions.
2. Group active requests by application
SELECT
s.program_name,
COUNT(*) AS active_request_count,
SUM(r.cpu_time) AS total_cpu_ms,
SUM(r.logical_reads) AS total_logical_reads,
SUM(r.writes) AS total_writes,
MAX(r.total_elapsed_time) AS max_elapsed_ms
FROM sys.dm_exec_requests AS r
INNER JOIN sys.dm_exec_sessions AS s
ON s.session_id = r.session_id
WHERE
r.session_id <> @@SPID
AND s.is_user_process = 1
GROUP BY
s.program_name
ORDER BY
total_cpu_ms DESC,
active_request_count DESC;
This is useful when multiple services use the same login but identify themselves through the connection’s application name.
3. Group active requests by host
SELECT
s.host_name,
COUNT(*) AS active_request_count,
SUM(r.cpu_time) AS total_cpu_ms,
SUM(r.logical_reads) AS total_logical_reads,
SUM(r.writes) AS total_writes
FROM sys.dm_exec_requests AS r
INNER JOIN sys.dm_exec_sessions AS s
ON s.session_id = r.session_id
WHERE
r.session_id <> @@SPID
AND s.is_user_process = 1
GROUP BY
s.host_name
ORDER BY
active_request_count DESC,
total_cpu_ms DESC;
Use this to identify which application server, workstation, or batch host is producing the workload.
4. Group activity by login, host, app, and database
SELECT
s.login_name,
s.host_name,
s.program_name,
DB_NAME(r.database_id) AS database_name,
COUNT(*) AS active_request_count,
SUM(r.cpu_time) AS total_cpu_ms,
SUM(r.logical_reads) AS total_logical_reads,
SUM(r.reads) AS total_physical_reads,
SUM(r.writes) AS total_writes,
SUM(
CASE
WHEN r.blocking_session_id > 0 THEN 1
ELSE 0
END
) AS blocked_request_count
FROM sys.dm_exec_requests AS r
INNER JOIN sys.dm_exec_sessions AS s
ON s.session_id = r.session_id
WHERE
r.session_id <> @@SPID
AND s.is_user_process = 1
GROUP BY
s.login_name,
s.host_name,
s.program_name,
DB_NAME(r.database_id)
ORDER BY
active_request_count DESC,
total_cpu_ms DESC;
This is often the best incident-level view because it shows workload ownership and resource consumption together.
5. Count sessions, including sleeping connections
Active requests only show currently executing work.
To inspect connection footprint:
SELECT
s.login_name,
s.host_name,
s.program_name,
s.status,
COUNT(*) AS session_count
FROM sys.dm_exec_sessions AS s
WHERE
s.is_user_process = 1
GROUP BY
s.login_name,
s.host_name,
s.program_name,
s.status
ORDER BY
session_count DESC;
This helps answer:
- which applications maintain the most connections
- whether connection pools are very large
- whether many sessions are sleeping
A large number of sleeping sessions is not automatically a problem.
6. Find sessions with open transactions by application
SELECT
s.login_name,
s.host_name,
s.program_name,
s.status,
COUNT(*) AS sessions_with_open_transactions,
SUM(s.open_transaction_count) AS total_open_transactions
FROM sys.dm_exec_sessions AS s
WHERE
s.is_user_process = 1
AND s.open_transaction_count > 0
GROUP BY
s.login_name,
s.host_name,
s.program_name,
s.status
ORDER BY
total_open_transactions DESC;
Use this when one application is suspected of leaving transactions open.
Follow up with session-level transaction analysis before taking action.
7. Find blocked requests by application
SELECT
s.login_name,
s.host_name,
s.program_name,
COUNT(*) AS blocked_request_count,
MAX(r.wait_time) AS max_wait_ms
FROM sys.dm_exec_requests AS r
INNER JOIN sys.dm_exec_sessions AS s
ON s.session_id = r.session_id
WHERE
r.blocking_session_id > 0
AND s.is_user_process = 1
GROUP BY
s.login_name,
s.host_name,
s.program_name
ORDER BY
blocked_request_count DESC,
max_wait_ms DESC;
This shows which applications are being blocked.
It does not tell you which application is causing the blocking.
8. Find blockers and their owning application
WITH blockers AS
(
SELECT DISTINCT
blocking_session_id AS session_id
FROM sys.dm_exec_requests
WHERE
blocking_session_id > 0
)
SELECT
b.session_id,
s.login_name,
s.host_name,
s.program_name,
s.status,
s.open_transaction_count
FROM blockers AS b
INNER JOIN sys.dm_exec_sessions AS s
ON s.session_id = b.session_id
ORDER BY
b.session_id;
Use this to identify the application or login associated with the blocking session.
9. Show detailed sessions for one application
DECLARE @program_name nvarchar(128) = N'Your Application Name';
SELECT
s.session_id,
s.login_name,
s.host_name,
s.program_name,
s.status AS session_status,
s.login_time,
s.last_request_start_time,
s.last_request_end_time,
s.open_transaction_count,
r.status AS request_status,
DB_NAME(r.database_id) AS database_name,
r.command,
r.cpu_time AS cpu_ms,
r.logical_reads,
r.wait_type,
r.blocking_session_id,
txt.text AS batch_text
FROM sys.dm_exec_sessions AS s
LEFT JOIN sys.dm_exec_requests AS r
ON r.session_id = s.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS txt
WHERE
s.is_user_process = 1
AND s.program_name = @program_name
ORDER BY
s.session_id;
Use this after the aggregate view identifies a suspicious application.
10. Inspect connection source and authentication details
SELECT
s.session_id,
s.login_name,
s.host_name,
s.program_name,
c.client_net_address,
c.net_transport,
c.protocol_type,
c.auth_scheme,
c.connect_time
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_exec_connections AS c
ON c.session_id = s.session_id
WHERE
s.is_user_process = 1
ORDER BY
c.connect_time DESC;
This is useful when the application identity is unclear or multiple systems report the same program_name.
Be careful when sharing client IP addresses or hostnames outside operational teams.
What the result means
| Field | Why it matters |
|---|---|
login_name |
SQL Server login or authentication identity associated with the session. |
host_name |
Client host name reported by the connection. Useful for tracing workload ownership. |
program_name |
Application name reported by the client driver. Often the easiest way to separate services. |
active_request_count |
Number of currently executing requests in the group. |
session_count |
Number of connected sessions, including sleeping sessions. |
total_cpu_ms |
CPU accumulated by current requests in the group. |
total_logical_reads |
Page activity from current requests. |
blocked_request_count |
Number of active requests currently blocked. |
open_transaction_count |
Session-level signal that transactions remain open. |
client_net_address |
Client-side network address as seen by SQL Server. |
auth_scheme |
Authentication mechanism used by the connection. |
What to look for
One application dominates active requests
This may indicate:
- traffic spike
- retry storm
- inefficient chatty workload
- batch job
- reporting workload
- connection-pool surge
Compare request count with CPU, reads, and elapsed time.
One login dominates CPU or reads
The login may represent:
- one application
- several applications sharing credentials
- a scheduled job
- a reporting user
Drill into host_name and program_name before assigning ownership.
Many sessions but few active requests
This is often normal connection pooling.
Do not assume session count alone indicates pressure.
Look at:
- active requests
- worker usage
- open transactions
- connection churn
- application behavior
Many blocked requests from one application
This identifies the victim workload.
Next, find the actual blocker and its owning application.
Blocker is sleeping with an open transaction
This is a strong signal of transaction-management problems.
Investigate:
- commit/rollback handling
- application errors
- timeout paths
- connection pooling
Unexpected application or host
This can reveal:
- ad hoc SSMS activity
- forgotten jobs
- old application versions
- test tools hitting production
- unexpected reporting traffic
Validate ownership before blocking or disabling access.
Production notes
host_name and program_name are client-supplied
These fields are operationally useful, but they are not trustworthy security identities.
A client can set or omit them.
Use them for troubleshooting, not authorization.
Active requests and connected sessions answer different questions
sys.dm_exec_requests shows executing work.
sys.dm_exec_sessions shows connected sessions.
A workload may have:
- many sleeping sessions
- few active requests
or:
- few sessions
- many expensive requests over time
Interpret both.
Aggregate DMV results are point-in-time snapshots
The queries above tell you what exists now.
For traffic spikes or recurring patterns, persist snapshots or use:
- Extended Events
- Query Store
- application telemetry
- monitoring tools
Shared logins reduce observability
If multiple applications use the same SQL login, login-level aggregation may be too coarse.
Good Application Name values in connection strings make production troubleshooting much easier.
Connection pooling affects session counts
Connection pools intentionally keep connections open.
High sleeping-session counts can be normal.
Focus on:
- churn
- active request pressure
- leaked transactions
- pool exhaustion symptoms
Permissions vary by version
For broad access to these performance DMVs:
- SQL Server 2019 and earlier commonly require
VIEW SERVER STATE - SQL Server 2022 and later commonly require
VIEW SERVER PERFORMANCE STATE
Azure SQL Database uses different scope and permission rules.
Common mistakes
1. Treating host_name as authoritative
It is client-supplied metadata.
Use it as a clue, not as a security fact.
2. Assuming many sleeping sessions are a problem
Connection pools can maintain many idle sessions intentionally.
Look at workload and resource pressure before acting.
3. Blaming the blocked application
An application with many blocked requests may be the victim.
Find the blocking session before assigning root cause.
4. Grouping only by login
Shared logins can hide the actual application.
Use login + host + program + database together.
5. Disabling a login during an incident without ownership confirmation
A login may serve several applications.
Before disabling access, confirm:
- business owner
- dependent applications
- rollback plan
- re-enable procedure
- incident window
6. Treating one snapshot as traffic history
A single DMV query cannot tell you how activity changed over the last hour.
Persist snapshots if you need trend analysis.
7. Ignoring open transactions
An apparently idle application can still hold locks through an open transaction.
Check transaction state before concluding that sleeping sessions are harmless.
Was this helpful?
What could be better?
Thanks — that helps me improve Javi Builds.
Stay in the loop
Get the Javi Builds updates you actually care about.
Check your inbox
I sent you a confirmation link. Your subscription will be active after you confirm it.