February 4, 2015 at 4:08 am
Hi all!
I've got to automate killing off connections based on the name of my database and application that is connecting to it.
So I've had a go, looked around, but I keep coming up across something weird....
If I look in activity monitor on my server, all sessions have database names against them. Great. However, from within SQL most of them are missing.
SELECT
[SessionId] = s.session_id,
[Login] = s.login_name,
[Database] = db_name(r.database_id),
[Application] = s.program_name,
[HostName] = s.host_name
FROM
sys.dm_exec_sessions s
LEFT OUTER JOIN
sys.dm_exec_requests r ON (s.session_id = r.session_id)
WHERE
s.is_user_process = 1
ORDER BY
s.session_id;
select db_name(database_id), * from sys.dm_exec_requests
...as an example, I currently have 135 sessions running, and a small handful (ignoring the system ones) coming from dm_exec_requests.
I am probably missing something simple! Any ideas?
Many thanks
Martin 🙂
February 4, 2015 at 5:59 am
isn't this just the difference between a session(connection), and having actually committed a request?
if i open ssms and open ten blank tabs, that's ten sessions, and zero requests.
until i run a query, there'd be nothing in sys.dm_exec_requests, right?
Lowell
February 4, 2015 at 6:29 am
If I open activity monitor though on the server, it populates database names for everything
February 4, 2015 at 6:40 am
Here's the query Activity Monitor runs, database is coming from the sysprocesses table:
SELECT
[Session ID] = s.session_id,
[User Process] = CONVERT(CHAR(1), s.is_user_process),
[Login] = s.login_name,
[Database] = ISNULL(db_name(p.dbid), N''),
[Task State] = ISNULL(t.task_state, N''),
[Command] = ISNULL(r.command, N''),
[Application] = ISNULL(s.program_name, N''),
[Wait Time (ms)] = ISNULL(w.wait_duration_ms, 0),
[Wait Type] = ISNULL(w.wait_type, N''),
[Wait Resource] = ISNULL(w.resource_description, N''),
[Blocked By] = ISNULL(CONVERT (varchar, w.blocking_session_id), ''),
[Head Blocker] =
CASE
-- session has an active request, is blocked, but is blocking others or session is idle but has an open tran and is blocking others
WHEN r2.session_id IS NOT NULL AND (r.blocking_session_id = 0 OR r.session_id IS NULL) THEN '1'
-- session is either not blocking someone, or is blocking someone but is blocked by another party
ELSE ''
END,
[Total CPU (ms)] = s.cpu_time,
[Total Physical I/O (MB)] = (s.reads + s.writes) * 8 / 1024,
[Memory Use (KB)] = s.memory_usage * 8192 / 1024,
[Open Transactions] = ISNULL(r.open_transaction_count,0),
[Login Time] = s.login_time,
[Last Request Start Time] = s.last_request_start_time,
[Host Name] = ISNULL(s.host_name, N''),
[Net Address] = ISNULL(c.client_net_address, N''),
[Execution Context ID] = ISNULL(t.exec_context_id, 0),
[Request ID] = ISNULL(r.request_id, 0),
[Workload Group] = ISNULL(g.name, N'')
FROM sys.dm_exec_sessions s
LEFT OUTER JOIN sys.dm_exec_connections c ON (s.session_id = c.session_id)
LEFT OUTER JOIN sys.dm_exec_requests r ON (s.session_id = r.session_id)
LEFT OUTER JOIN sys.dm_os_tasks t ON (r.session_id = t.session_id AND r.request_id = t.request_id)
LEFT OUTER JOIN
(
-- In some cases (e.g. parallel queries, also waiting for a worker), one thread can be flagged as
-- waiting for several different threads. This will cause that thread to show up in multiple rows
-- in our grid, which we don't want. Use ROW_NUMBER to select the longest wait for each thread,
-- and use it as representative of the other wait relationships this thread is involved in.
SELECT *, ROW_NUMBER() OVER (PARTITION BY waiting_task_address ORDER BY wait_duration_ms DESC) AS row_num
FROM sys.dm_os_waiting_tasks
) w ON (t.task_address = w.waiting_task_address) AND w.row_num = 1
LEFT OUTER JOIN sys.dm_exec_requests r2 ON (s.session_id = r2.blocking_session_id)
LEFT OUTER JOIN sys.dm_resource_governor_workload_groups g ON (g.group_id = s.group_id)
LEFT OUTER JOIN sys.sysprocesses p ON (s.session_id = p.spid)
ORDER BY s.session_id;
February 4, 2015 at 6:53 am
That is perfect, thank you 🙂 Different from the variants I was looking at.
BTW just for info, connections with nothing happening are included, in my case, as MS Access client links to the database that sit around doing nothing until called into action.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply