SQLServer 中的KILLSPID 命令
This article gives an overview of the KILL SPID command and how to monitor the progress.
quarterdollar本⽂概述了KILL SPID命令以及如何监视进度。
介绍 (Introduction)
Once we connect to a Server instance, it assigns a unique identification number to each connection. It is the Server Process ID and commonly known as SPID. SPID 1 to 50 is rerved for SQL Server internal process, and the ur process starts from SPID 51.
⼀旦连接到SQL 实例，它将为每个连接分配⼀个唯⼀的标识号。 它是服务器进程ID，通常称为SPID。 SPID 1到50保留⽤于SQL Server 内部进程，⽽⽤户进程从SPID 51开始。
在SQL Server 中检查SPID 的不同⽅法 (Different ways to check the SPID in SQL Server)You can check all process in SQL Server using the following ways.
您可以使⽤以下⽅法检查SQL Server中的所有进程。
使⽤SP_who2命令： (Using SP_who2 command:)
Execute the sp_who2 command, and it lists out all the SPID’s including the system and ur process.
执⾏sp_who2命令，它列出了所有SPID，包括系统进程和⽤户进程。
使⽤动态管理视图： (Using Dynamic Management View:)
We can u the DMV sys.dm_exec_ssions to get the ssion details.
我们可以使⽤DMV sys.dm_exec_ssions来获取会话详细信息。
使⽤活动监视器： (Using Activity Monitor:)
1
SELECT *2
FROM sys.dm_exec_ssions;3
By default, it shows all process in SQL Server. We might not be interested in the system process. We can filter the results using the following query
4
SELECT *5 FROM sys.dm_exec_ssions 6 WHERE is_ur_process = 1;
Connect to a SQL instance in SSMS and, right-click on SQL instance. Launch Activity Monitor. It opens the following Activity Monitor that contains Overview, Process tabs.
连接到SSMS中SQL实例，然后右键单击SQL实例。 启动活动监视器。 它将打开以下活动监视器，其blonde
中包含“概述”，“进程”选项卡。
In the Process tab, we can view the SP ID and details such as login, databa, application, hostname. By default, Activity Monitor displays the Ur process as shown in the following image.anvan
在“进程”选项卡中，我们可以查看SP ID和详细信息，例如登录名，数据库，应⽤程序，主机名。 默认情况下，活动监视器显⽰⽤户进程，如下图所⽰。
If we want to view all SPID’s, t the Ur Process column value from the drop-down to All (1 – Ur Process, 0- System process).
如果要查看所有SPID，请从下拉列表中将“⽤户进程”列值设置为“全部”（1 –⽤户进程，0 –系统进程）。
使⽤SSMS信息栏： (Using SSMS information bar:)
Once we open a new ssion in SSMS, it shows the following information in the information bar.
在SSMS中打开新会话后，它将在信息栏中显⽰以下信息。
Connection Status清洁提单
4级考试连接状态
upanInstance name in which we are connected
我们连接的实例名称
Ur name (SPID). In the following image, we can e that we are connected with Kashish\Test(84).
In bracket, it is a SPID. It is 84 in our ca
⽤户名（SPID）。 在下图中，我们可以看到我们已与Kashish \ Test（84）连接。 ⽅括号中是SPID。 在我们的情况下是84
@@ SPID全局变量： (The @@SPID global variable:)
We can u the global variable to know about the ssion id of the currently connected ssion. It is uful if we connect to SQL Server using the command line tools such as SQLCMD.
我们可以使⽤全局变量来了解当前连接的会话的会话ID。 如果我们使⽤命令⾏⼯具（例如SQLCMD）连接到SQL Server，这将⾮常有⽤。
SELECT @@SPID AS CurrentSPID;
SQL Server中的KILL SPID命令概述 (Overview of KILL SPID command in SQL Server)
Suppo you receive a call that the particular SQL instance is running slow. You start troubleshootin
g and identify a SPID causing blocking for other SPID’s, and it is taking many system resources. You require terminating this query to relea the high consuming resources a remove blocking due to this ssion.
假设您收到⼀个调⽤，说明特定SQL实例运⾏缓慢。 您开始进⾏故障排除并确定⼀个SPID导致其他SPID的阻塞，并且占⽤⼤量系统资源。您需要终⽌此查询以释放⾼消耗资源，并且由于此会话⽽导致移除阻塞。
You can u the KILL SPID command to kill a particular ur ssion. You can only KILL the ur process. Once we kill a ssion, it undergoes through the rollback process, and it might take time and resources as well to perform a rollback.
您可以使⽤KILL SPID命令杀死特定的⽤户会话。 您只能杀死⽤户进程。 ⼀旦我们终⽌了会话，它将经历回滚过程，执⾏回滚可能还需要时间和资源。
Before we look more on KILL SPID, we need to understand that Microsoft SQL Server follows the ACID properties. All transactions should meet the ACID criteria.
在进⼀步了解KILL SPID之前，我们需要了解Microsoft SQL Server遵循ACID属性。 所有交易均应符合ACID标准。
Atomic: Transaction should be either complete or fail. There should not be any transaction in a status other than this
原⼦：事务应该完成或失败。 除此以外的任何状态都不应进⾏任何交易
Consistent: The databa should be consistent before and after the transactions
⼀致：交易前后数据库应⼀致
Isolated: Multiple transactions should run, but it should not put the transactions in inconsistent mode
隔离：应该运⾏多个事务，但不应使事务处于不⼀致模式
Durability: All transactions should be durable. Once a record is committed, it should remain committed regardless of the system failure. SQL Server needs to maintain a failed or uncompleted transaction to roll back in ca of any failure
耐⽤性：所有交易都应持久。 提交记录后，⽆论系统故障如何，记录都应保持提交状态。 SQL Server需要维护失败或未完成的事务以在发⽣任何失败时回滚
Once we KILL SPID in SQL Server, it becomes an uncompleted transaction. SQL Server must undo all changes performed
by the uncompleted transaction and databa objects should be returned to the original state before the transactions. Due to this reason, you can e the status of SPID as KILLED\ROLLBACK once the rollback is in progress. Rollback might take less time in most of the ca; however, it entirely depends upon the changes it needs to rollback.
⼀旦我们在SQL Server中杀死SPID，它便成为未完成的事务。 SQL Server必须撤消由未完成的事务执⾏的所有更改，并且在执⾏这些事务之前，数据库对象应返回到原始状态。 由于这个原因，⼀旦进⾏回滚，您就可以看到SPID的状态为KILLED \ ROLLBACK。 在⼤多数情况下，回滚可能会花费较少的时间。 但是，这完全取决于它需要回滚的更改。
Let start an active transaction for inrting a large number of rows in the SQL table.
让我们开始⼀个活动事务，以便在SQL表中插⼊⼤量⾏。
1CREATE TABLE [dbo].[tblSQLShackDemo](
2 [S.No.] [int] IDENTITY(0,1) NOT NULL,
3 [value] [uniqueidentifier] NULL,
4 [Date] [datetime] NULL
5 ) ON [PRIMARY]
6 GO
我要翻译7
8 ALTER TABLE [dbo].[tblSQLShackDemo] ADD DEFAULT (getdate()) FOR [Date]
9 GO
practical
10 Begin transaction
11 Declare @Id int
12 Set @Id = 1
13
14 While @Id <= 1000000
15 Begin
16 Inrt Into tblSQLShackDemo(value) values (newid())
17 Set @Id = @Id + 1
18 End
Check the status of the query using the SP_who2 command.
使⽤SP_who2命令检查查询的状态。
After some time, u the KILL command to KILL SPID using the following command. Execute this command in a new query window.
⼀段时间后，使⽤KILL命令通过以下命令杀死SPID。 在新的查询窗⼝中执⾏此命令。
KILL 84
Once we execute the KILL SPID command, SQL Server starts the ROLLBACK process for this query. You can e the Status as ROLLBACK in the following image.
airpower⼀旦执⾏KILL SPID命令，SQL Server就会为该查询启动ROLLBACK进程。 下图可以看到状态为ROLLBACK。
We might be interested to know the rollback time. We can check the estimated roll back time using the following command:
我们可能想知道回滚时间。 我们可以使⽤以下命令检查估计的回滚时间：
KILL SPID with STATUSONLY
定期杀死SPID
We want to check the ROLLBACK status for the SPID 84, therefore, execute the query.
我们要检查SPID 84的ROLLBACK状态，因此，执⾏查询。
KILL 84 with STATUSONLY
杀死84⼈
The KILL command with STATUSONLY does not kill any process. It returns the estimated return time for the rollback in conds.
STATUSONLY的KILL命令不会终⽌任何进程。 它返回回滚的估计返回时间（以秒为单位）。
In the following screenshot, you can estimate completion time is 34 conds. You need to execute this query again to get the updated time.
在以下屏幕截图中，您可以估计完成时间为34秒。 您需要再次执⾏此查询以获取更新时间。
In another test, I inrted more records in the table and KILL the ssion before completion. In the following screenshot, we can e the estimated roll back time 3567 conds (approx. 1 hour).
在另⼀个测试中，我在表中插⼊了更多记录，并在完成之前杀死了该会话。 在下⾯的屏幕截图中，我们可以看到估计的回滚时间3567秒（约1⼩时）。
使⽤KILL SPID命令消除SQL Server中的阻塞 (U KILL SPID command to eliminate blocking in SQL Server )
We might face blocking in SQL Server, and sometimes we require KILLING the blocker SPID. In SQL Server, we get blocking issues when a SPID holds a lock on a specific resource and another SPID tries to acquire a conflicting lock on the same resource.
我们可能会在SQL Server中遇到阻塞，有时我们需要杀死阻塞者SPID。 在SQL Server中，当⼀个SPID持有特定资源的锁，⽽另⼀个SPID试图获取同⼀资源的冲突锁时，就会遇到阻塞问题。
Firstly, let’s generate a blocking scenario. Execute the following query in SSMS ssion. The SPID for this ssion is 60.
⾸先，让我们⽣成⼀个阻塞⽅案。 在SSMS会话中执⾏以下查询。 此会话的SPID为60。
1Begin transaction
2 Update tblSQLShackDemo t value=newid()
In another ssion, it tries to get records of the table. The SPID for this ssion is 83.
在另⼀个会话中，它尝试获取表的记录。 该会话的SPID为83。
Select * from tblSQLShackDemo
The lect statement keeps running and does not return any rows. You can e the status as Executing Query in the following screenshot.
lect语句保持运⾏状态，并且不返回任何⾏。 您可以在以下屏幕截图中看到状态为“正在执⾏查询 ”。
Let’s wait for more time, but still, the query shows the status as Executing Query.
让我们等待更多时间，但是查询仍然将状态显⽰为“正在执⾏查询” 。
We can check the blocking using the sp_who2 command or using the DMV sys.dm_exec_requests.深圳东方英文书院
我们可以使⽤sp_who2命令或DMV sys.dm_exec_requests检查阻塞。
Execute the following DMV in another query window of SSMS.
在SSMS的另⼀个查询窗⼝中执⾏以下DMV。
1SELECT blocking_ssion_id ,*
2 FROM sys.dm_exec_requests
3 WHERE blocking_ssion_id <> 0;
4 GO
In the output, we can e that ssion id 83 (Select statement) is blocked due to ssion id 60 ( Update statement).
在输出中，我们可以看到由于会话ID 60（更新语句）⽽阻⽌了会话ID 83（选择语句）。
Due to blocking, we do not get any result for the Select statement. We need to terminate SPID that is causing the blocking using the KILL SPID command. In my example, SPID 60 is the culprit, so we need to kill it. Execute the following query.
由于阻塞，Select语句不会得到任何结果。 我们需要使⽤KILL SPID命令终⽌导致阻塞的SPID。 在我的⽰例中，SPID 60是元凶，因此我们需要将其杀死。 执⾏以下查询。
