How to monitor actitvity in a MS SQL Server

...and find out everything you ever wanted to know about slow queries or locks.

Prepare

Open SQL Management Studio and then in the context menu of your database server the Activity Monitor:

Overview

Blocked Processes

USE Master
GO
EXEC sp_who2
GO

A list of processes will be displayed, and any processes which are currently in a blocked state will display the SPID of the processes blocking them in the ‘BlkBy’ column.

Expensive Queries

Use the context menu to get the query text or see the execution plan

Example Query Text

Example Execution Plan

Links

• https://www.wearediagram.com/blog/sql-server-locks-blocked-processes-and-two-easy-ways-to-find-them
• https://www.mssqltips.com/sqlservertip/1917/performance-analysis-using-sql-server-2008-activity-monitor-tool/
• https://docs.microsoft.com/en-US/troubleshoot/sql/performance/understand-resolve-blocking