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
CODE
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