How to kill a SQL Connection/SPID through Activity Monitor?

How to kill a SQL Connection through Activity Monitor?

Activity Monitor is Graphical tool which tells you about the currently running processes, Resource Waits, Data file I/O and Expensive Queries.

This information is compiled from various System Tables and Views and if you have right permission you could re-arrange columns, click to sort, and right-click to kill a process and another advantage is- it will guide you to other tools that will help you solve those problems.

You need proper permissions(“View Server State”) to view Activity Monitor and to kill a connection you need SA permissions

The processor time shown here is a percentage of the time that SQL Server is exercising the processor — not the entire CPU itself. What that means is that while SQL Server might not be using a lot of processor, the Windows Operating system might have other tasks that are. Also, if you tied out only one processor to SQL Server on a dual-processor box, you need to take that into account as well.

 Waiting tasks are the SQL Server operations that are waiting on a resource to do their work. There is an entire school of thought about using wait information for Performance Tuning, and it has merit. Basically this school of thought says that if your system isn’t waiting on anything, it is working as fast as it can.

 The Database I/O graph shows how often the system is hitting the storage subsystem. It’s not an exact or comprehensive measurement, but it can quickly show you that this is where a lot of activity is going on. You want to avoid hitting the drive as much as you can, since this physical component is one of the slowest in the system.

 Batch Requests show general SQL Server query activity, although it’s only one of the measurements in this area.

How do I open Activity Monitor?

  

1. Connect to SQLServer through SSMS
2. Right click on Server and click on Activity Monitor

    

3. Expand Process box
4. Select the connection which you want to kill and right click and finally click on “Kill Process”