合 MSSQL中的KILL SPID命令

This article gives an overview of the KILL SPID command and how to monitor the rollback progress.
本文概述了KILL SPID命令以及如何监视回滚进度。

介绍 (INTRODUCTION)

Once we connect to a SQL 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 reserved for SQL Server internal processes, and the user process starts from SPID 51.
一旦连接到SQL Server实例，它将为每个连接分配一个唯一的标识号。 它是服务器进程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 processes 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 user processes.
执行sp_who2命令，它列出了所有SPID，包括系统进程和用户进程。

使用动态管理视图： (USING DYNAMIC MANAGEMENT VIEW:)

We can use the DMV sys.dm_exec_sessions to get the session details.
我们可以使用DMV sys.dm_exec_sessions来获取会话详细信息。

使用活动监视器： (USING ACTIVITY MONITOR:)

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, Processes tabs.
连接到SSMS中SQL实例，然后右键单击SQL实例。 启动活动监视器。 它将打开以下活动监视器，其中包含“概述”，“进程”选项卡。
In the Processes tab, we can view the SP ID and details such as login, database, application, hostname. By default, Activity Monitor displays the User processes as shown in the following image.
在“进程”选项卡中，我们可以查看SP ID和详细信息，例如登录名，数据库，应用程序，主机名。 默认情况下，活动监视器显示用户进程，如下图所示。

If we want to view all SPID’s, set the User Process column value from the drop-down to All (1 – User Process, 0- System process).
如果要查看所有SPID，请从下拉列表中将“用户进程”列值设置为“全部”（1 –用户进程，0 –系统进程）。

使用SSMS信息栏： (USING SSMS INFORMATION BAR:)

Once we open a new session in SSMS, it shows the following information in the information bar.
在SSMS中打开新会话后，它将在信息栏中显示以下信息。

• Connection Status
  连接状态
• Instance name in which we are connected
  我们连接的实例名称
• User name (SPID). In the following image, we can see that we are connected with Kashish\Test(84). In bracket, it is a SPID. It is 84 in our case
  
  用户名（SPID）。 在下图中，我们可以看到我们已与Kashish \ Test（84）连接。 方括号中是SPID。 在我们的情况下是84

  @@ SPID全局变量： (THE @@SPID GLOBAL VARIABLE:)

  We can use the global variable to know about the session id of the currently connected session. It is useful if we connect to SQL Server using the command line tools such as SQLCMD.
  我们可以使用全局变量来了解当前连接的会话的会话ID。 如果我们使用命令行工具（例如SQLCMD）连接到SQL Server，这将非常有用。

  SELECT @@SPID AS CurrentSPID;

  1	SELECT @@SPID AS CurrentSPID;

  

  SQL SERVER中的KILL SPID命令概述 (OVERVIEW OF KILL SPID COMMAND IN SQL SERVER)

  Suppose you receive a call that the particular SQL instance is running slow. You start troubleshooting and identify a SPID causing blocking for other SPID’s, and it is taking many system resources. You require terminating this query to release the high consuming resources a remove blocking due to this session.
  假设您收到一个调用，说明特定SQL实例运行缓慢。 您开始进行故障排除并确定一个SPID导致其他SPID的阻塞，并且占用大量系统资源。 您需要终止此查询以释放高消耗资源，并且由于此会话而导致移除阻塞。
  You can use the KILL SPID command to kill a particular user session. You can only KILL the user processes. Once we kill a session, 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 database 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 case 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 database objects should be returned to the original state before these transactions. Due to this reason, you can see the status of SPID as KILLED\ROLLBACK once the rollback is in progress. Rollback might take less time in most of the case; 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 inserting a large number of rows in the SQL table.
  让我们开始一个活动事务，以便在SQL表中插入大量行。
  
  CREATE TABLE [dbo].[tblSQLShackDemo]( [S.No.] [int] IDENTITY(0,1) NOT NULL, [value] [uniqueidentifier] NULL, [Date] [datetime] NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[tblSQLShackDemo] ADD DEFAULT (getdate()) FOR [Date] GO Begin transaction Declare @Id int Set @Id = 1 While @Id <= 1000000 Begin Insert Into tblSQLShackDemo(value) values (newid()) Set @Id = @Id + 1 End

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

  CREATE TABLE [dbo].[tblSQLShackDemo](     [S.No.] [int] IDENTITY(0,1) NOT NULL,     [value] [uniqueidentifier] NULL,     [Date] [datetime] NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[tblSQLShackDemo] ADD  DEFAULT (getdate()) FOR [Date] GO Begin transaction Declare @Id int Set @Id = 1 While @Id <= 1000000 Begin    Insert Into tblSQLShackDemo(value) values (newid())    Set @Id = @Id + 1 End

  Check the status of the query using the SP_who2 command.
  使用SP_who2命令检查查询的状态。
  
  After some time, use the KILL command to KILL SPID using the following command. Execute this command in a new query window.
  一段时间后，使用KILL命令通过以下命令杀死SPID。 在新的查询窗口中执行此命令。

  KILL 84

  1

  KILL 84

  Once we execute the KILL SPID command, SQL Server starts the ROLLBACK process for this query. You can see the Status as ROLLBACK in the following image.
  一旦执行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 seconds.
  STATUSONLY的KILL命令不会终止任何进程。 它返回回滚的估计返回时间（以秒为单位）。
  In the following screenshot, you can estimate completion time is 34 seconds. You need to execute this query again to get the updated time.
  在以下屏幕截图中，您可以估计完成时间为34秒。 您需要再次执行此查询以获取更新时间。
  
  In another test, I inserted more records in the table and KILL the session before completion. In the following screenshot, we can see the estimated roll back time 3567 seconds (approx. 1 hour).
  在另一个测试中，我在表中插入了更多记录，并在完成之前杀死了该会话。 在下面的屏幕截图中，我们可以看到估计的回滚时间3567秒（约1小时）。
  

  使用KILL SPID命令消除SQL SERVER中的阻塞 (USE 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 session. The SPID for this session is 60.
  首先，让我们生成一个阻塞方案。 在SSMS会话中执行以下查询。 此会话的SPID为60。

  Begin transaction Update tblSQLShackDemo set value=newid()

  1 2	Begin transaction     Update tblSQLShackDemo set value=newid()

  In another session, it tries to get records of the table. The SPID for this session is 83.
  在另一个会话中，它尝试获取表的记录。 该会话的SPID为83。

  Select * from tblSQLShackDemo

  1	Select * from tblSQLShackDemo

  The select statement keeps running and does not return any rows. You can see the status as Executing Query in the following screenshot.
  
  select语句保持运行状态，并且不返回任何行。 您可以在以下屏幕截图中看到状态为“正在执行查询 ”。
  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。

  本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
  后续精彩内容已被站长无情隐藏，请输入验证码解锁本文！

  验证码：

  获取验证码： 请先关注本站微信公众号，然后回复“验证码”，获取验证码。在微信里搜索“AiDBA”或者“dbaup6”或者微信扫描右侧二维码都可以关注本站微信公众号。

  相关文章

  • MSSQL无法kill掉会话，会话一直处于KILLED/ROLLBACK状态
  • SQL Server查询会话、锁、杀会话
  • sqlcmd备份MSSQL数据库报错 SSL Provider: [error:1416F086:SSL routines:tls_process_server_certificate:certificate verify failed:self signed certificate]
  • MSSQL中的幽灵数据删除进程、虚影清除进程（ghost cleanup）
  • SSMS配置错误，导致 SELECT 失败，因为下列 SET 选项的设置不正确: 'QUOTED_IDENTIFIER, ANSI_PADDING'。请确保 SET 选项正确无误，可以用于 计算列上的索引视图和/或索引和/或筛选的索引和/或查询通知和/或 XML 数据类型方法和/或空间索引操作。。
  • MSSQL删除tempdb的数据文件
  • MSSQL中的等待事件SQLTRACE_FILE_BUFFER说明
  • MSSQL的数据库快照
  • MSSQL 2014新功能之延迟事务持续性(Delayed Transaction Durability)--延迟提交
  • MSSQL中的实时查询统计
  • MSSQL中查询索引的创建时间和删除时间
  • MSSQL中查询依赖关系
  • MSSQL的ldf日志文件丢失, 如何仅用mdf文件恢复数据库呢
  • MSSQL移动数据库文件（分离和附加）
  • Windows的资源监视器中进程对磁盘的响应时间多少毫秒即为延迟比较厉害影响磁盘性能IO
  • MSSQL扩展事件之system_health会话
  • MSSQL的创建索引占用的内存
  • MSSQL 2014新功能之缓存池扩展BPE (Buffer Pool Extension)
  • MSSQL中的锁和阻塞
  • MSSQL中的数据库兼容性级别
  打赏赞(1)分享