What is a process in SQL Server?
In a nutshell, any instruction to SQL Server to do something is a process. It could be an internal LOG WRITER operation or an external SELECT 1 T-SQL query sent from SSMS. Every process in SQL Server would have a unique identity – Session ID or Session Process ID or just SPID.
Why would you want to terminate a process?
Occasionally, SQL Server would run competing processes (see Fig #2 – processes that compete on the same resources) that would block one another (read: blocking) which might lead to process slow down without causing a deadlock (read: deadlock is when SQL Server decides which process to terminate (read: victim process)).
Without terminating (read: killing) one of the processes, we basically have 2 slow processes that have the potential to run forever (see Fig #2).
How to terminate a process?
Terminating a process is super easy. You can either do it within SSMS by right-clicking on the SPID (see Fig #3) that you are trying to terminate and clicking Kill Process.
Alternatively, you can ask SQL Server to terminate a process by executing a KILL command (see Fig #4).
While in most cases running a KILL <spid> command is fast and easy, in some cases you want to have a “confirmed kill” – you want to know the progress of the process termination.
Think about a Change Data Capture (aka CDC) activity or an update/delete done in batches that need a rollback and suddenly it’s a heavy rollback that needs to occur to bring back everything to the original state. How would you monitor this rollback progress?
Introducing a KILL with a “progress bar” command (see Fig #5).
Running KILL with STATUSONLY would give you an idea of the progress or essentially a percentage completion of process termination as well as its success and/or failure.