Published: 2026-11-08

SSMS Activity Monitor on a Mac: Find the Blocking Session and Kill It

Activity Monitor only exists inside SQL Server Management Studio, and SSMS only runs on Windows. On a Mac you find a blocker with a query instead: sys.dm_exec_requests.blocking_session_id says who waits on whom, and the head blocker is the session that blocks others while waiting on nobody. Very often it is not running anything at all: it is a sleeping connection with an open transaction. Check who it is, then end it with KILL <session id>, which needs ALTER ANY CONNECTION. We built a blocking chain and a deadlock on SQL Server 2025 and ran every query below against them; the output is from those runs, trimmed to the interesting columns.

What runs on a Mac instead of Activity Monitor (as of November 2026)

Activity Monitor is a tab in SSMS with Overview, Processes, Resource Waits, Data File I/O, Recent Expensive Queries and Active Expensive Queries panes (Microsoft Learn). It is still in SSMS 22: the release notes list Activity Monitor fixes as late as 22.7 (June 2026), and as of November 2026 the current version is 22.11.3 (October 29, 2026). The SSMS FAQ answers “Does SSMS run on macOS?” with “No. SSMS is available only for Windows.” The Arm64 build that arrived with SSMS 22 is for Windows on Arm, so on an Apple-silicon Mac it means a Windows 11 virtual machine. Here is what the Mac-native options do with a blocked server, checked on November 5, 2026:

ToolSession list with “blocked by”Kill a sessionNotes
SSMS 22 Activity MonitorYes, Processes paneYesWindows only. Learn says viewing needs VIEW SERVER STATE and killing needs sysadmin or processadmin (source).
Azure Data StudioNo built-in viewNoRetired on February 28, 2026 (notice). Its first-party “WhoIsActive” extension showed sp_WhoIsActive output on a dashboard.
VS Code MSSQL extension (1.47, October 2026)NoNoHas a Query Profiler (Extended Events, generally available since 1.42 in April 2026, needs ALTER ANY EVENT SESSION) whose TSQL_Locks template records lock events. That is a trace of events, not a list of who is blocked right now.
DBeaverSession Manager lists SQL Server sessionsYes, Kill SessionSQL Server has no PRO marker in the Session Manager guide; the Lock Manager does not list SQL Server.
DataGripNoNoIts sessions are DataGrip's own connections, not the server's.
Any query window (sqlcmd, VS Code, DBeaver…)Yes, with the queries belowYes, KILLWorks everywhere; you have to remember the queries.
Jam SQL StudioYes, Session Browser with a Blocked By column, a head-blocker badge and a blocking treeYes, after a confirmationNative macOS app; details in the last section.

None of this needs Windows. For running SQL Server itself on a Mac, see How to Run SQL Server on a Mac; for the wider list of SSMS replacements, SSMS for Mac: what works on Apple Silicon.

The lab: one open transaction, two waiters

We created a scratch database with a 1,000-row dbo.Orders table (primary key OrderId, default READ COMMITTED, no row versioning) and opened three connections with sqlcmd, each with its own host name so the sessions are easy to tell apart:

-- session 54, host "checkout-api": changes a row and never commits.
-- The client stays connected and sends nothing else, as an app does
-- when it forgets to commit or waits for a user.
BEGIN TRAN;
UPDATE JamLabW.dbo.Orders SET Status = 'shipped' WHERE OrderId = 42;

-- session 61, host "report-job", two seconds later
SELECT OrderId, Status FROM dbo.Orders WHERE OrderId = 42;

-- session 62, host "batch-update", one second after that
UPDATE dbo.Orders SET Amount = Amount * 1.10 WHERE OrderId = 42;

The SELECT and the second UPDATE hung. We waited eight seconds and then looked at the server from a fourth connection.

sp_who2: quick, but read BlkBy carefully

EXEC sp_who2;

It returned 77 rows on an otherwise quiet test server, most of them system sessions. The three that matter (columns trimmed):

SPID  Status     Login  HostName      BlkBy  DBName   Command
54    sleeping   sa     checkout-api    .    master   AWAITING COMMAND
61    SUSPENDED  sa     report-job     54    JamLabW  SELECT
62    SUSPENDED  sa     batch-update   61    JamLabW  UPDATE

Read it as a chain: 62 waits on 61, 61 waits on 54, and 54 waits on nobody (. in BlkBy). Session 54 is the head blocker, and it is sleeping with AWAITING COMMAND: it is not running anything, it just holds a lock inside a transaction it never finished. Two traps:

  • The blocker can sit in another database. 54 is listed under master, because that was its current database and it updated JamLabW.dbo.Orders by a three-part name. Filter sp_who2's output by database and you lose the one row you need.
  • 62 is not blocked by 54. It queued behind 61's request for the same key, so its BlkBy is 61. Killing 61 would not help; you have to follow the chain to its end.

sp_who2 is not documented by Microsoft; sp_who is, and has a blk column with the same meaning. Both fit when you have a handful of sessions. On a busy server you want a query that returns only the chain.

The DMVs: who waits on whom, and who started it

Blocked requests come from sys.dm_exec_requests:

SELECT r.session_id, r.blocking_session_id, r.status, r.command,
       r.wait_type, r.wait_time AS wait_ms, r.wait_resource, t.text AS sql_text
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.blocking_session_id <> 0;
session_id  blocking_session_id  status     command  wait_type  wait_ms  wait_resource
61          54                   suspended  SELECT   LCK_M_S    9212     KEY: 28:72057594047234048 (c1ceb6ec8491)
62          61                   suspended  UPDATE   LCK_M_X    8207     KEY: 28:72057594047234048 (c1ceb6ec8491)

Both wait on the same key of the same index (LCK_M_S is a wait for a shared lock, LCK_M_X for an exclusive one). Session 54 does not appear here at all: a sleeping session has no request. To see the head blocker, ask sys.dm_exec_sessions for sessions that block someone and are not blocked themselves, and read their last batch with sys.dm_exec_input_buffer:

SELECT s.session_id, s.status, s.host_name, s.open_transaction_count AS open_tran,
       s.last_request_end_time, ib.event_info AS last_batch
FROM sys.dm_exec_sessions AS s
OUTER APPLY sys.dm_exec_input_buffer(s.session_id, NULL) AS ib
WHERE s.session_id IN (SELECT blocking_session_id FROM sys.dm_exec_requests WHERE blocking_session_id <> 0)
  AND s.session_id NOT IN (SELECT session_id FROM sys.dm_exec_requests WHERE blocking_session_id <> 0);
session_id  status    host_name     open_tran  last_request_end_time    last_batch
54          sleeping  checkout-api  1          2026-11-05 21:09:51.410  SET NOCOUNT ON; SELECT @@SPID AS spid; BEGIN TRAN;
                                                                        UPDATE JamLabW.dbo.Orders SET Status = 'shipped' WHERE OrderId = 42;

So the blocker is a connection from checkout-api that finished its last batch at 21:09:51 with one transaction still open, and the statement that took the lock. last_request_end_time tells you how long the transaction has been idle, which is usually the first thing the application team asks.

sys.dm_os_waiting_tasks gives the same chain per task, with the lock described in resource_description:

session_id  blocking_session_id  wait_type  wait_duration_ms  resource_description
62          61                   LCK_M_X    8368              keylock hobtid=72057594047234048 dbid=28 id=lock12f44ec800 mode=X ...
61          54                   LCK_M_S    9372              keylock hobtid=72057594047234048 dbid=28 id=lock12f44ec800 mode=X ...

And sys.dm_tran_locks shows the locks themselves: 54 holds the exclusive key lock, 61 and 62 wait for it.

SELECT l.request_session_id AS session_id, l.resource_type, l.resource_description,
       l.request_mode, l.request_status
FROM sys.dm_tran_locks AS l
WHERE l.resource_database_id = DB_ID('JamLabW') AND l.resource_type <> 'DATABASE'
ORDER BY l.request_session_id, l.resource_type;
session_id  resource_type  resource_description  request_mode  request_status
54          KEY            (c1ceb6ec8491)        X             GRANT
54          OBJECT                               IX            GRANT
54          PAGE           1:376                 IX            GRANT
61          KEY            (c1ceb6ec8491)        S             WAIT
61          OBJECT                               IS            GRANT
61          PAGE           1:376                 IS            GRANT
62          KEY            (c1ceb6ec8491)        X             WAIT
62          OBJECT                               IX            GRANT
62          PAGE           1:376                 IX            GRANT

sp_WhoIsActive: the chain in one call

sp_WhoIsActive is Adam Machanic's free monitoring procedure, published on GitHub under the GPLv3 licence. The current build as of November 2026, v2200.20260409 (April 9, 2026), states “Compatibility: SQL Server 2022+”; the repository keeps older builds for older versions. We installed it into the scratch database only, by running sp_WhoIsActive.sql there, and asked for block leaders:

EXEC JamLabW.dbo.sp_WhoIsActive
  @find_block_leaders = 1,
  @sort_order = '[blocked_session_count] DESC',
  @output_column_list = '[session_id][blocking_session_id][blocked_session_count][wait_info][status][open_tran_count][host_name][sql_text]';
session_id  blocking_session_id  blocked_session_count  wait_info        status     open_tran_count  host_name
54          NULL                 2                      NULL             sleeping   1                checkout-api
61          54                   1                      (9766ms)LCK_M_S  suspended  0                report-job
62          61                   0                      (8762ms)LCK_M_X  suspended  2                batch-update

The sql_text column (left out above) held the same UPDATE … WHERE OrderId = 42 batch for 54. blocked_session_count counts everything queued behind a session, directly or not, so the head blocker sorts to the top even when it blocks only one session directly, as 54 does here. sp_WhoIsActive shows sleeping sessions that have an open transaction by default, which is why 54 is in the list at all. Its output is wide; @output_column_list keeps it readable in a Mac terminal.

KILL the head blocker

Killing a session closes its connection and rolls back its open transaction; the user of that application loses whatever the transaction had not committed. Look at the session right before you kill it:

SELECT session_id, login_time, host_name, program_name
FROM sys.dm_exec_sessions WHERE session_id = 54;

KILL 54;
session_id  login_time               host_name     program_name
54          2026-11-05 21:12:09.653  checkout-api  SQLCMD

What happened next, within the same second:

  • 61 returned its row with Status = open: the shipped update was rolled back.
  • 62 got the lock and updated the row; Amount went from 10.00 to 11.00.
  • The checkout-api client learned about it only when it sent its next batch, which failed with Communication link failure.

The rollback of a one-row update is instant. A transaction that changed many rows has to undo all of them first, and the waiters stay blocked until that rollback finishes. KILL 54 WITH STATUSONLY reports the rollback's progress; per the KILL reference it only reports and does not end anything.

Session ids are reused quickly. We ran KILL 54 WITH STATUSONLY right after the kill, from a new connection, and got:

Msg 6104, Level 16, State 1, Server de7facc2c80e, Line 1
Cannot use KILL to kill your own process.

The new connection had been given id 54. Had another application connected first, a KILL 54 typed from a note made a minute earlier would have ended the wrong session. Compare login_time and host_name with what you saw when you diagnosed the block.

What a monitoring login needs

We repeated the diagnosis with a login that had no server permissions, then granted them one at a time:

GrantedDMVs and sp_who2sp_WhoIsActiveKILL 54
NothingOnly its own session: 1 row in sys.dm_exec_sessions, 1 row from sp_who2, no blocked requestsMsg 300: VIEW SERVER PERFORMANCE STATE permission was denied on object 'server', database 'master'.Msg 6102: User does not have permission to use the KILL statement.
VIEW SERVER PERFORMANCE STATEWhole chain, incl. sys.dm_os_waiting_tasks and sys.dm_tran_locksWorksStill Msg 6102
+ ALTER ANY CONNECTIONWhole chainWorksWorks

On SQL Server 2022 and later, VIEW SERVER PERFORMANCE STATE is the permission the DMVs ask for (sys.dm_exec_requests, sys.dm_os_waiting_tasks); the older VIEW SERVER STATE includes it and still works. Without either, sys.dm_exec_sessions and sys.dm_exec_requests do not fail: they quietly return your own session only, which looks like “nothing is blocked”. KILL needs ALTER ANY CONNECTION, which sysadmin and processadmin include. On Azure SQL Database the permission is KILL DATABASE CONNECTION.

GRANT VIEW SERVER PERFORMANCE STATE TO [monitoring_login];  -- see the chain
GRANT ALTER ANY CONNECTION TO [monitoring_login];          -- and end a session

Deadlocks are different: SQL Server picks the victim

A blocking chain waits until someone acts. A deadlock does not: two transactions each hold a lock the other wants, and SQL Server ends one of them by itself. We ran two sessions that update the same two tables in opposite order:

-- host "order-service"
BEGIN TRAN;
UPDATE dbo.Orders SET Status = 'picking' WHERE OrderId = 7;
WAITFOR DELAY '00:00:02';
UPDATE dbo.Inventory SET OnHand = OnHand - 1 WHERE ProductId = 7;
COMMIT;

-- host "stock-service", started at the same time
BEGIN TRAN;
UPDATE dbo.Inventory SET OnHand = OnHand + 5 WHERE ProductId = 7;
WAITFOR DELAY '00:00:02';
UPDATE dbo.Orders SET Status = 'restocked' WHERE OrderId = 7;
COMMIT;

The order-service session committed. The other one got:

Msg 1205, Level 13, State 51, Server de7facc2c80e, Line 1
Transaction (Process ID 54) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

There is nothing to kill. What you want afterwards is the deadlock graph, and SQL Server already keeps it: the built-in system_health Extended Events session records every deadlock. This read it back from the event files:

SELECT TOP (1) CAST(event_data AS nvarchar(max)) AS deadlock_xml
FROM sys.fn_xe_file_target_read_file('system_health*.xel', NULL, NULL, NULL)
WHERE object_name = 'xml_deadlock_report'
ORDER BY timestamp_utc DESC;

We pasted the <deadlock> element into our free deadlock viewer, which parses it in the browser. It reported the victim as SPID 54 and the cycle as:

SPID 54 waits for SPID 59 on JamLabW.dbo.Orders (PK__Orders__C3905BCFB3995750) (requests X, owner holds X)
SPID 59 waits for SPID 54 on JamLabW.dbo.Inventory (PK__Inventor__B40CC6CD60FA8C31) (requests X, owner holds X)

Fix it in the application: touch tables in the same order in every transaction, keep transactions short, and retry on error 1205. system_health is a rolling window, so read it soon after the incident.

The same steps in Jam SQL Studio

Jam SQL Studio is a desktop SQL client that runs natively on macOS (and on Windows and Linux). For blocking on SQL Server it has a Session Browser: open it from More → DBA → Session Browser or the command palette, and pick the connection. It reads sys.dm_exec_sessions joined to sys.dm_exec_requests, so it needs the same VIEW SERVER PERFORMANCE STATE (or VIEW SERVER STATE) as the queries above, and ALTER ANY CONNECTION to kill.

We built the same chain again (a sleeping session holding order 42, a reader and a writer queued behind it) and opened the Session Browser on it. With the default Active Only filter:

  • The sleeping session is listed, with a Head blocker badge in the Blocked By column. Active Only includes any session that a request is waiting on, so you don't need to switch to All Sessions to find it.
  • The two waiting sessions show the session they wait on in Blocked By: the reader the head blocker, the writer the reader. That is the same chain as sp_who2's BlkBy.

Then:

  1. Click the head blocker's row. A sleeping session has no running statement, so the Info tab shows Last SQL: the last batch the session sent, read from sys.dm_exec_connections.most_recent_sql_handle. For a sqlcmd session like the one above, that is its whole batch: SET NOCOUNT ON, the SELECT @@SPID, BEGIN TRAN and the UPDATE of order 42.
  2. Open the Locks tab. It lists the KEY, PAGE and OBJECT locks of all sessions with the table they are on, and a Blocking Tree that starts at the head blocker, with the reader under it and the writer under the reader.
  3. Check the Host and Program columns against what you saw when you diagnosed the block; session ids are reused, as shown above. Then click Kill in the Info tab (or hover the head blocker in the blocking tree and click the kill icon) and confirm. Jam sends KILL with the session id and refreshes. SQL Server has a single KILL, so there is no separate Kill Immediate.
  4. Set Auto to 5, 10 or 30 seconds to watch the waiters clear.

Two related tools: the Performance Dashboard shows server-wide wait statistics, where lock waits such as LCK_M_X tell you whether blocking is a pattern rather than a one-off, and the Deadlock Analyzer reads the system_health deadlocks straight from a connection and draws the same graph as the browser viewer.

Quick Answers

Q: Is there an SSMS Activity Monitor for Mac?

A: No. Activity Monitor is part of SQL Server Management Studio, and SSMS runs only on Windows (SSMS 22.11, October 2026). On a Mac you read the same kind of information from the dynamic management views with any query tool, from sp_WhoIsActive, or from a client with a session list, such as DBeaver's Session Manager or Jam SQL Studio's Session Browser.

Q: How do I find the head blocker in SQL Server?

A: Find the session that other requests name in sys.dm_exec_requests.blocking_session_id but that is not waiting on anyone itself. It is often a sleeping session with an open transaction, so it has no row in sys.dm_exec_requests at all: read it from sys.dm_exec_sessions (open_transaction_count) and sys.dm_exec_input_buffer. sp_WhoIsActive with @find_block_leaders = 1 lists it with a blocked_session_count.

Q: How do I kill a blocking session in SQL Server?

A: Run KILL followed by the session id, for example KILL 54. SQL Server closes the connection and rolls back its open transaction, and the blocked sessions continue once the rollback is done. Check the session's login_time and host_name right before you kill it, because session ids are reused: in our test a new connection got the same id seconds later.

Q: What permission do I need to kill a session?

A: ALTER ANY CONNECTION, which the sysadmin and processadmin roles include. In our test a login with VIEW SERVER PERFORMANCE STATE could see the whole blocking chain but got error 6102 on KILL until ALTER ANY CONNECTION was granted. On Azure SQL Database the permission is KILL DATABASE CONNECTION.

Q: Why does sp_who2 list the head blocker under a different database?

A: sp_who2's DBName column is the session's current database, not the database that holds the lock. Our head blocker was connected to master and updated JamLabW.dbo.Orders with a three-part name, so sp_who2 listed it under master. Don't filter by database when you look for a blocker.

Q: Do I need to kill a session to end a deadlock?

A: No. SQL Server detects the deadlock, rolls back one of the transactions (the victim) and returns error 1205 to its client. Read the deadlock afterwards from the system_health Extended Events session and change the access order or the indexing so it does not happen again.

How we tested

Everything ran on November 5, 2026 against SQL Server 2025 Developer Edition (RTM-CU8, 17.0.4075.5) in a Docker container on an x86-64 Linux machine, from the container's own sqlcmd (mssql-tools18). The tables lived in a scratch database (JamLabW, row versioning off) that we dropped afterwards, together with the test login. Session ids, lock hashes and the server name de7facc2c80e (the container's host name) are from our run and will differ on yours; output is trimmed to the columns discussed, never edited. We ran the blocking chain twice with the same session ids and the same results after KILL; the second run added the login_time check in the KILL section. The Jam SQL Studio section was checked the same day on the same server, in new copies of the chain: with the app itself, and with the Session Browser's own queries run from sqlcmd. Its session ids differ from the ones above. Competitor facts are from the vendors' own documentation and release notes, checked on November 5, 2026.

Summary

Without Activity Monitor, three queries cover the job on a Mac: sys.dm_exec_requests for the waiters and whom they wait on, sys.dm_exec_sessions with sys.dm_exec_input_buffer for the head blocker that is usually sleeping with an open transaction, and KILL once you have confirmed it is still the same session. sp_WhoIsActive does the first two in one call. A login needs VIEW SERVER PERFORMANCE STATE to see other sessions and ALTER ANY CONNECTION to kill them. Deadlocks need no kill, only the graph from system_health and a change in the code.

Last verified against learn.microsoft.com, the vscode-mssql releases on github.com, dbeaver.com, jetbrains.com and github.com/amachanic/sp_whoisactive, and by running every query on SQL Server 2025 CU8, on 2026-11-05.

Related