在阻止的流程报告中清空阻止流程

我正在使用扩展事件收集被阻止的流程报告，并且由于某些原因，某些报告中的blocking-process节点为空。这是完整的xml：

 <blocked-process-report monitorLoop="383674">
 <blocked-process>
  <process id="processa7bd5b868" taskpriority="0" logused="106108620" waitresource="KEY: 6:72057613454278656 (8a2f7bc2cd41)" waittime="25343" ownerId="1051989016" transactionname="user_transaction" lasttranstarted="2017-03-20T09:30:38.657" XDES="0x21f382d9c8" lockMode="X" schedulerid="7" kpid="15316" status="suspended" spid="252" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2017-03-20T09:39:15.853" lastbatchcompleted="2017-03-20T09:39:15.850" lastattention="1900-01-01T00:00:00.850" clientapp="Microsoft Dynamics AX" hostname="***" hostpid="1348" loginname="***" isolationlevel="read committed (2)" xactid="1051989016" currentdb="6" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame line="1" stmtstart="40" sqlhandle="0x02000000f7def225b0edaecd8744b453ce09bdcff9b291f50000000000000000000000000000000000000000" />
    <frame line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000" />
   </executionStack>
   <inputbuf>
(@P1 bigint,@P2 int)DELETE FROM DIMENSIONFOCUSUNPROCESSEDTRANSACTIONS WHERE ((PARTITION=5637144576) AND ((FOCUSDIMENSIONHIERARCHY=@P1) AND (STATE=@P2)))   </inputbuf>
  </process>
 </blocked-process>
 <blocking-process>
  <process />
 </blocking-process>
</blocked-process-report>

此hobt_id所属索引的索引定义是

CREATE UNIQUE CLUSTERED INDEX [I_7402FOCUSDIMENSIONHIERARCHYIDX] ON [dbo].[DIMENSIONFOCUSUNPROCESSEDTRANSACTIONS]
(
    [PARTITION] ASC,
    [FOCUSDIMENSIONHIERARCHY] ASC,
    [STATE] ASC,
    [GENERALJOURNALENTRY] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

不涉及分区，这是表的定义：

CREATE TABLE [dbo].[DIMENSIONFOCUSUNPROCESSEDTRANSACTIONS](
    [FOCUSDIMENSIONHIERARCHY] [bigint] NOT NULL DEFAULT ((0)),
    [GENERALJOURNALENTRY] [bigint] NOT NULL DEFAULT ((0)),
    [STATE] [int] NOT NULL DEFAULT ((0)),
    [RECVERSION] [int] NOT NULL DEFAULT ((1)),
    [PARTITION] [bigint] NOT NULL DEFAULT ((5637144576.)),
    [RECID] [bigint] NOT NULL,
 CONSTRAINT [I_7402RECID] PRIMARY KEY NONCLUSTERED 
(
    [RECID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[DIMENSIONFOCUSUNPROCESSEDTRANSACTIONS]  WITH CHECK ADD CHECK  (([RECID]<>(0)))
GO

没有触发器或在整个数据库的任何表上定义的外键。

确切的SQL Server构建为：

Microsoft SQL Server 2012（SP3-CU4 ）（KB3165264）-11.0.6540.0（X64）
2016年6月23日17:45:11版权所有（c）Microsoft Corporation Enterprise
版本：Windows NT 6.3上的基于核心的许可（64位）（ Build
14393：）（管理程序）

扩展事件相当简单，只需记录被阻止的进程报告即可：

CREATE EVENT SESSION [Dynperf_Blocking_Data] ON SERVER 
ADD EVENT sqlserver.blocked_process_report(
    ACTION(package0.collect_system_time,sqlserver.client_hostname,sqlserver.context_info)),
ADD EVENT sqlserver.lock_escalation(
    ACTION(package0.collect_system_time,sqlserver.client_hostname,sqlserver.context_info)),
ADD EVENT sqlserver.xml_deadlock_report(
    ACTION(package0.collect_system_time,sqlserver.client_hostname,sqlserver.context_info)) 
ADD TARGET package0.event_file(SET filename=N'F:\SQLTrace\Dynamics_Blocking.xel',max_file_size=(100),max_rollover_files=(10))
WITH (MAX_MEMORY=32768 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=5 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=PER_NODE,TRACK_CAUSALITY=ON,STARTUP_STATE=ON)
GO

在“读取提交的快照隔离”中配置了数据库，并且最大并行度设置为1。这是服务器配置：

+------------------------------------+-------+
|                name                | value |
+------------------------------------+-------+
| access check cache bucket count    |     0 |
| access check cache quota           |     0 |
| Ad Hoc Distributed Queries         |     0 |
| affinity I/O mask                  |     0 |
| affinity mask                      |     0 |
| affinity64 I/O mask                |     0 |
| affinity64 mask                    |     0 |
| Agent XPs                          |     1 |
| allow updates                      |     0 |
| backup compression default         |     1 |
| blocked process threshold (s)      |     2 |
| c2 audit mode                      |     0 |
| clr enabled                        |     0 |
| common criteria compliance enabled |     0 |
| contained database authentication  |     0 |
| cost threshold for parallelism     |     5 |
| cross db ownership chaining        |     0 |
| cursor threshold                   |    -1 |
| Database Mail XPs                  |     1 |
| default full-text language         |  1033 |
| default language                   |     0 |
| default trace enabled              |     1 |
| disallow results from triggers     |     0 |
| EKM provider enabled               |     0 |
| filestream access level            |     0 |
| fill factor (%)                    |     0 |
| ft crawl bandwidth (max)           |   100 |
| ft crawl bandwidth (min)           |     0 |
| ft notify bandwidth (max)          |   100 |
| ft notify bandwidth (min)          |     0 |
| index create memory (KB)           |     0 |
| in-doubt xact resolution           |     0 |
| lightweight pooling                |     0 |
| locks                              |     0 |
| max degree of parallelism          |     1 |
| max full-text crawl range          |     4 |
| max server memory (MB)             | 65536 |
| max text repl size (B)             | 65536 |
| max worker threads                 |     0 |
| media retention                    |     0 |
| min memory per query (KB)          |  1024 |
| min server memory (MB)             |     0 |
| nested triggers                    |     1 |
| network packet size (B)            |  4096 |
| Ole Automation Procedures          |     0 |
| open objects                       |     0 |
| optimize for ad hoc workloads      |     1 |
| PH timeout (s)                     |    60 |
| precompute rank                    |     0 |
| priority boost                     |     0 |
| query governor cost limit          |     0 |
| query wait (s)                     |    -1 |
| recovery interval (min)            |     0 |
| remote access                      |     1 |
| remote admin connections           |     0 |
| remote login timeout (s)           |    10 |
| remote proc trans                  |     0 |
| remote query timeout (s)           |   600 |
| Replication XPs                    |     0 |
| scan for startup procs             |     1 |
| server trigger recursion           |     1 |
| set working set size               |     0 |
| show advanced options              |     1 |
| SMO and DMO XPs                    |     1 |
| transform noise words              |     0 |
| two digit year cutoff              |  2049 |
| user connections                   |     0 |
| user options                       |     0 |
| xp_cmdshell                        |     0 |
+------------------------------------+-------+

我运行了一段时间的服务器端跟踪，并且在跟踪文件中得到了与使用扩展事件相同的空节点。
此阻塞的过程报告也是使用另一台服务器上的服务器端跟踪捕获的运行Dynamics AX，因此它不特定于此服务器或内部版本。

<blocked-process-report monitorLoop="1327922">
 <blocked-process>
  <process id="processbd9839848" taskpriority="0" logused="1044668" waitresource="KEY: 5:72057597098328064 (1d7966fe609a)" waittime="316928" ownerId="3415555263" transactionname="user_transaction" lasttranstarted="2017-03-27T07:59:29.290" XDES="0x1c1c0c3b0" lockMode="U" schedulerid="3" kpid="25236" status="suspended" spid="165" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2017-03-27T07:59:47.873" lastbatchcompleted="2017-03-27T07:59:47.873" lastattention="2017-03-27T07:58:01.490" clientapp="Microsoft Dynamics AX" hostname="***" hostpid="11072" loginname="***" isolationlevel="read committed (2)" xactid="3415555263" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame line="1" stmtstart="236" stmtend="676" sqlhandle="0x020000004d6830193d42a167edd195c201f40bb772e9ece20000000000000000000000000000000000000000"/>
   </executionStack>
   <inputbuf>
(@P1 numeric(32,16),@P2 int,@P3 bigint,@P4 nvarchar(5),@P5 nvarchar(36),@P6 int,@P7 numeric(32,16),@P8 bigint,@P9 int)UPDATE PRODCALCTRANS SET REALCOSTAMOUNT=@P1,RECVERSION=@P2 WHERE (((((((PARTITION=@P3) AND (DATAAREAID=@P4)) AND (COLLECTREFPRODID=@P5)) AND (COLLECTREFLEVEL=@P6)) AND (LINENUM=@P7)) AND (RECID=@P8)) AND (RECVERSION=@P9))   </inputbuf>
  </process>
 </blocked-process>
 <blocking-process>
  <process/>
 </blocking-process>
</blocked-process-report>

是否有人对这些报告有解释？是什么阻止了查询？

如果锁很久以后我正在查看报告，有什么办法可以找出正在发生的事情？

可能有用的补充是，这些查询通过sp_cursorprepare和sp_cursorexecute运行

到目前为止，我还无法重现它，它似乎是随机发生的，但经常发生。

它发生在几个实例（具有不同的内部版本）和几个表/查询中，都与Dynamics AX有关。

当时在后台没有索引或其他数据库维护作业。

使用srutzky答案中提供的代码，我能够捕获与此阻止的流程报告相关的一些日志记录：

<blocked-process-report monitorLoop="1621637">
 <blocked-process>
  <process id="processd06909c28" taskpriority="0" logused="0" waitresource="KEY: 5:72057597585719296 (d2d87c26d920)" waittime="78785" ownerId="4436575948" transactionname="user_transaction" lasttranstarted="2017-04-13T07:39:17.590" XDES="0x3219d034e0" lockMode="U" schedulerid="3" kpid="133792" status="suspended" spid="106" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2017-04-13T07:39:17.657" lastbatchcompleted="2017-04-13T07:39:17.657" lastattention="1900-01-01T00:00:00.657" clientapp="Microsoft Dynamics AX" hostname="****" hostpid="11800" loginname="****" isolationlevel="read committed (2)" xactid="4436575948" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame line="1" stmtstart="72" stmtend="256" sqlhandle="0x0200000076a6a92ab1256af09321b056ab243f187342f9960000000000000000000000000000000000000000"/>
    <frame line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000"/>
   </executionStack>
   <inputbuf>
(@P1 int,@P2 int,@P3 bigint,@P4 int)UPDATE PRODROUTEJOB SET JOBSTATUS=@P1,RECVERSION=@P2 WHERE ((RECID=@P3) AND (RECVERSION=@P4))   </inputbuf>
  </process>
 </blocked-process>
 <blocking-process>
  <process/>
 </blocking-process>
</blocked-process-report>

在同一时间的同一资源的日志表中可以找到：由于字符数限制，要点

进一步的研究表明，之前和之后带有空阻塞进程的报告之后，我有相同的resourceid的报告，但确实有阻塞进程节点：

<blocked-process-report monitorLoop="1621636">
 <blocked-process>
  <process id="processd06909c28" taskpriority="0" logused="0" waitresource="KEY: 5:72057597585719296 (d2d87c26d920)" waittime="73765" ownerId="4436575948" transactionname="user_transaction" lasttranstarted="2017-04-13T07:39:17.590" XDES="0x3219d034e0" lockMode="U" schedulerid="3" kpid="133792" status="suspended" spid="106" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2017-04-13T07:39:17.657" lastbatchcompleted="2017-04-13T07:39:17.657" lastattention="1900-01-01T00:00:00.657" clientapp="Microsoft Dynamics AX" hostname="***" hostpid="11800" loginname="***" isolationlevel="read committed (2)" xactid="4436575948" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame line="1" stmtstart="72" stmtend="256" sqlhandle="0x0200000076a6a92ab1256af09321b056ab243f187342f9960000000000000000000000000000000000000000"/>
    <frame line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000"/>
   </executionStack>
   <inputbuf>
(@P1 int,@P2 int,@P3 bigint,@P4 int)UPDATE PRODROUTEJOB SET JOBSTATUS=@P1,RECVERSION=@P2 WHERE ((RECID=@P3) AND (RECVERSION=@P4))   </inputbuf>
  </process>
 </blocked-process>
 <blocking-process>
  <process status="sleeping" spid="105" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2017-04-13T07:40:31.417" lastbatchcompleted="2017-04-13T07:40:31.423" lastattention="1900-01-01T00:00:00.423" clientapp="Microsoft Dynamics AX" hostname="**" hostpid="11800" loginname="**" isolationlevel="read committed (2)" xactid="4436165115" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack/>
   <inputbuf>
(@P1 bigint,@P2 nvarchar(5),@P3 bigint,@P4 bigint,@P5 nvarchar(11),@P6 int,@P7 nvarchar(21),@P8 datetime2)SELECT T1.REGDATETIME,T1.REGDATETIMETZID,T1.WORKERPILOT,T1.WORKER,T1.WRKCTRIDPILOT,T1.REGTYPE,T1.PROFILEDATE,T1.JOBID,T1.JOBIDABS,T1.MATCHRECIDSTARTSTOP,T1.JOBACTIVE,T1.RESNO,T1.STARTITEMS,T1.GOODITEMS,T1.SCRAPITEMS,T1.FINISHEDCODE,T1.TMPGOODITEMS,T1.TMPSCRAPITEMS,T1.SYSMRPUPDATEREQUEST,T1.ERROR,T1.ERRORTXT,T1.TMPSTARTITEMS,T1.AUTOSTAMP,T1.ERRORSPECIFICATION,T1.COSTCATEGORY,T1.ONCALLACTIVITY,T1.TERMINALID,T1.PDSCWGOODITEMS,T1.PDSCWSCRAPITEMS,T1.PDSCWSTARTITEMS,T1.RETAILTERMINALID,T1.MODIFIEDDATETIME,T1.RECVERSION,T1.PARTITION,T1.RECID FROM JMGTERMREG T1 WHERE (((PARTITION=@P1) AND (DATAAREAID=@P2)) AND (((((WORKER=@P3) OR ((WORKER=@P4) AND (WRKCTRIDPILOT=@P5))) AND (REGTYPE=@P6)) AND (JOBID=@P7)) AND (REGDATETIME&gt;=@P8))) ORDER BY T1.REGDATETIME   </inputbuf>
  </process>
 </blocking-process>
</blocked-process-report>

使用srutzky新数据提供的新脚本已已收集。
由于帖子的最大长度而在github上发布。

由于原始发布的数据没有两个会话ID，所以一些新数据已再次发布在github上

新数据包括github上的连接

#1 楼

我目前无法测试该理论，但是基于发布到GitHub的最新捕获数据，我会说<process>节点为空的原因是它需要当前正在运行的请求（找到了许多属性）在sys.dm_exec_requests中而不在sys.dm_exec_sessions中），并且没有当前正在运行的请求，它无法报告任何详细信息，类似于在INNER JOIN和sys.dm_exec_requests之间执行sys.dm_exec_sessions时将排除会话处于活动状态但由于没有当前请求而处于空闲状态的行。 br />
查看最顶部的数据集（monitorLoop值：1748823，1748824，1748825和1748827），我们可以看到以下内容：

id的blocked-process在每种情况下都是相同的：process2552c1fc28，唯一不同的属性是waittime（可以理解）。
blocking-process节点的属性在lastbatchstarted和lastbatchcompleted中都显示出差异

blocking-process节点的spid显示相同的值和xactid

那么，在4个不同的查询批次中，阻塞过程的SessionID和TransactionID如何相同？轻松地，开始显式事务，然后执行这些批处理。而且由于这些是独立的批次，因此在它们之间有一定的提交时间，此时没有当前请求，因此没有要显示的过程信息（但会话和事务仍然存在）。

为了对此进行更多研究，您可以通过将以下T-SQL放在SQL Server代理“ Transaction-SQL脚本（T-SQL）”作业步骤中，并将“数据库”设置为sys.dm_exec_requests和sys.dm_tran_locks来捕获有用的信息。成为要研究的对象（在本例中为ID为6的对象），并安排此作业每10秒运行一次。下面的T-SQL将在同一个数据库中创建两个表（如果它们不存在），然后，如果任何请求本身被阻塞，或者是被阻塞的Delete或Update操作，则将填充“ Requests”表。如果找到任何请求，它将尝试捕获：关于阻塞过程的会话和请求信息（此部分不假定存在活动的请求，因此RIGHT JOIN为至少获得会话信息）
被阻止和（希望是）被阻止进程的连接信息。

相同session_id的当前锁（请记住，不能保证该锁信息（因为该信息可以在执行这两个语句之间的时间之间发生变化，因此信息的准确度为100％；不过，该信息足够好，足以值得捕获）。本节当前已被注释掉。

SQL Server代理T-SQL作业步骤：

-- !! Remember to set the "Database" for the T-SQL Job Step to
--    the DB that has database_id = 6 !!
SET NOCOUNT ON;
IF (OBJECT_ID(N'dbo.tmpBlockingResearch_Requests') IS NULL)
BEGIN
  -- Create requests capture table
  SELECT SYSDATETIME() AS [CaptureTime], req.*,
         ses.login_time, ses.[host_name], ses.[program_name], ses.host_process_id,
         ses.client_version, ses.client_interface_name, ses.security_id,
         ses.login_name, ses.nt_domain, ses.nt_user_name, ses.memory_usage,
         ses.total_scheduled_time, ses.endpoint_id, ses.last_request_start_time,
         ses.last_request_end_time, ses.is_user_process, ses.original_security_id,
         ses.original_login_name, ses.last_successful_logon, ses.last_unsuccessful_logon,
         ses.unsuccessful_logons, ses.authenticating_database_id
  INTO   dbo.tmpBlockingResearch_Requests
  FROM   sys.dm_exec_requests req
  INNER JOIN sys.dm_exec_sessions ses
          ON ses.[session_id] = req.[session_id]
  WHERE  1 = 0;
END;

IF (OBJECT_ID(N'dbo.tmpBlockingResearch_Connections') IS NULL)
BEGIN
  -- Create connections capture table
  SELECT SYSDATETIME() AS [CaptureTime], con.*
  INTO   dbo.tmpBlockingResearch_Connections
  FROM   sys.dm_exec_connections con
  WHERE  1 = 0;
END;

IF (OBJECT_ID(N'dbo.tmpBlockingResearch_Locks') IS NULL)
BEGIN
  -- Create locks capture table
  SELECT SYSDATETIME() AS [CaptureTime], loc.*
  INTO   dbo.tmpBlockingResearch_Locks
  FROM   sys.dm_tran_locks loc
  WHERE  1 = 0;
END;
---------------------------------
DECLARE @SessionIDs TABLE (SessionID SMALLINT NOT NULL,
                           BlockingSessionID SMALLINT NOT NULL);

INSERT INTO dbo.tmpBlockingResearch_Requests
OUTPUT inserted.[session_id], inserted.[blocking_session_id]
INTO   @SessionIDs ([SessionID], [BlockingSessionID])
  SELECT SYSDATETIME() AS [CaptureTime], req.*,
         ses.login_time, ses.[host_name], ses.[program_name], ses.host_process_id,
         ses.client_version, ses.client_interface_name, ses.security_id,
         ses.login_name, ses.nt_domain, ses.nt_user_name, ses.memory_usage,
         ses.total_scheduled_time, ses.endpoint_id, ses.last_request_start_time,
         ses.last_request_end_time, ses.is_user_process, ses.original_security_id,
         ses.original_login_name, ses.last_successful_logon, ses.last_unsuccessful_logon,
         ses.unsuccessful_logons, ses.authenticating_database_id
  FROM   sys.dm_exec_requests req
  INNER JOIN sys.dm_exec_sessions ses
          ON ses.[session_id] = req.[session_id]
  WHERE ses.[is_user_process] = 1
  AND   req.[database_id] = DB_ID()
  AND   (
          req.blocking_session_id IN (req.[session_id], -2, -3, -4)
    OR   (req.[command] IN (N'DELETE', N'UPDATE') AND req.[blocking_session_id] > 0)
        );

-- Get at least session info, if not also request info, on blocking process
INSERT INTO dbo.tmpBlockingResearch_Requests
  SELECT SYSDATETIME() AS [CaptureTime], req.*,
         ses.login_time, ses.[host_name], ses.[program_name], ses.host_process_id,
         ses.client_version, ses.client_interface_name, ses.security_id,
         ses.login_name, ses.nt_domain, ses.nt_user_name, ses.memory_usage,
         ses.total_scheduled_time, ses.endpoint_id, ses.last_request_start_time,
         ses.last_request_end_time, ses.is_user_process, ses.original_security_id,
         ses.original_login_name, ses.last_successful_logon, ses.last_unsuccessful_logon,
         ses.unsuccessful_logons, ses.authenticating_database_id
  FROM   sys.dm_exec_requests req
  RIGHT JOIN sys.dm_exec_sessions ses
          ON ses.[session_id] = req.[session_id]
  WHERE ses.[session_id] IN (SELECT DISTINCT [BlockingSessionID] FROM @SessionIDs);

-- If any rows are captured this time, try to capture their connection info
INSERT INTO dbo.tmpBlockingResearch_Connections
  SELECT SYSDATETIME() AS [CaptureTime], con.*
  FROM   sys.dm_exec_connections con
  WHERE  con.[session_id] IN (
                              SELECT [SessionID]
                              FROM @SessionIDs
                              UNION -- No "ALL" so it does DISTINCT
                              SELECT [BlockingSessionID]
                              FROM @SessionIDs
                             );

/*
-- If any rows are captured this time, try to capture their lock info
INSERT INTO dbo.tmpBlockingResearch_Locks
  SELECT SYSDATETIME() AS [CaptureTime], loc.*
  FROM   sys.dm_tran_locks loc
  WHERE  loc.[request_session_id] IN (
                                      SELECT [SessionID]
                                      FROM @SessionIDs
                                      UNION -- No "ALL" so it does DISTINCT
                                      SELECT [BlockingSessionID]
                                      FROM @SessionIDs
                                     );
 */

我认为您应该能够重现此内容通过打开一个查询选项卡并执行以下操作：

CREATE TABLE dbo.tmp (Col1 INT);
BEGIN TRAN;
INSERT INTO dbo.tmp (Col1) VALUES (1);

然后打开第二个查询选项卡并执行以下操作：

UPDATE dbo.tmp
SET    Col1 = 2
WHERE  Col1 = 1;

附言只是说了一点，唯一没有意义的是请求和会话信息dbo.tmpBlockingResearch_Requests仍然从不包含阻止会话的行。但是我知道table变量中有阻塞的会话ID，因为它确实拉了两个SessionID的锁。这可能指向一个场景，在该场景中，在关闭来自客户端的“连接”之后，允许事务保持打开状态，但是由于连接池的缘故，该连接仍然得以维持。

@TomV我已经审阅了最新的研究数据，并拥有扎实的理论。我已经相应地更新了我的答案，包括在研究查询中添加了一部分，因此请在此处用新查询替换作业步骤SQL（我也注释掉了“锁”查询，因为我们现在确实不需要该数据了，很多数据）。我建议截断/删除现有研究表以从头开始。

–所罗门·鲁兹基
17年6月20日在20:15

@TomV好。而且我已经将repro查询更新为UPDATE而不是SELECT，因此无论如何它应该更能代表您的情况。我还在末尾添加了关于请求表中缺少行的注释。希望新的Connections表至少可以确认阻塞的SessionID是否继续存在。（附言，我开始清理上面的评论）。

–所罗门·鲁兹基
17年6月21日在15:30

您的工作很活跃。我需要一些时间来测试repro并在下周进行分析

–汤姆五世
17年6月23日在8:19

您好所罗门。在github上发布了2个新示例。不幸的是，使用提供的repro案例，我无法触发空的阻止进程BPR。

–汤姆五世
17年7月6日在12:54

我没有太多时间，快速看了一下。看起来“连接”信息显示阻止会话ID仍处于活动状态，但不在会话表中。我可以稍后进行测试，但是我很确定这表明连接池（连接仍然存在）并且命令之间的连接已关闭，但事务显然已打开（因为transaction_id始终与上次看到的相同）。稍后再仔细看。

–所罗门·鲁兹基
17年7月6日在13:26

#2 楼

由于锁升级而可能发生阻塞的事务。

Microsoft支持文章对此进行了解释：

如何解决由SQL Server中的锁升级引起的阻塞问题
/>

...
锁升级不会引起大多数阻塞问题。若要确定在遇到阻塞问题时是否正在发生锁定升级，请启动包含Lock：Escalation事件的SQL Profiler跟踪。如果您没有看到任何Lock：Escalation事件，则您的服务器上没有发生锁升级，并且本文中的信息不适用于您的情况。

如果正在发生锁升级，请验证已升级的表锁是否阻止了其他用户
...

检查扩展事件（物理文件）），以了解在阻止的进程事件之前发生的锁升级事件。

解释

有一篇Microsoft Blog文章，其中有更详细的说明：

SQL Server锁升级和阻止

...
步骤2：收集锁升级和阻止的进程报告事件。

锁升级和阻止的进程。 SQL Server不会自动捕获报表事件。为了知道这些事件是否正在发生，我们需要告诉SQL Server记录它们。我们的团队使用Performance Analyzer for Microsoft Dynamics工具来收集该信息。查看Rod Hansen的这篇文章，以获取有关该工具以及如何使用该工具收集阻止详细信息的更多信息。如果只想使用SQL Server Profiler，则需要收集的事件如下所示：
...

捕获锁升级并阻止进程后，您可以必须确定锁升级是否是被阻止进程的根本原因：

...
步骤3：在SQL Server Profiler中查看跟踪。

有两个主要指示器可以告诉您阻塞是否与锁升级有关。

首先，您在阻塞的进程报告事件之前立即看到一系列锁升级事件。下面是从Performance Analyzer for Microsoft Dynamics工具生成的跟踪中获取的示例。这是在跟踪中查找的一件事，但这并不意味着锁升级会导致阻塞。
...

以及其他

要验证阻止是否确实与锁升级有关，您需要查看被阻止的进程报告详细信息。在TextData部分中寻找waitresource（请参见下面的屏幕截图）。如果waitresource以OBJECT开头，那么我们知道阻塞的语句正在等待表级锁释放，然后才能继续执行。如果waitresource以KEY或PAG而不是OBJECT开头，则该特定块中不涉及锁升级。锁定升级将始终增加OJBECT锁定的范围，无论它从何处开始

解决方案

（仅在上述匹配的情况下）

解决方案显然是打开跟踪标志1224，它将关闭锁升级：

SQL Server锁升级和阻止

一起看这两个方面，可以肯定，锁升级会导致阻塞，您可能会受益于实现SQL Server跟踪标志1224。

Dynamics AX的SQL Server跟踪标志

跟踪标志1224禁用基于锁数的锁升级。启用此跟踪标志可以减少由于锁升级而导致阻塞的可能性，这是我在许多AX实现中看到的。出现问题的最常见情况是白天需要执行总体规划。

答案

最后，锁升级可能是阻塞的进程的根本原因。

替代解决方案（进程节点为空）

在进一步研究了blocked_process_reports后，可以进行以下替代说明。

扩展事件捕获的是当时与其他任何进程都不相关的blocked_process_reports。

Ergo：必须将它们阻塞才能进行其他操作原因

我建议您从SQL Server的sys.dm_os_wait_stats视图中捕获等待类型的时间范围，并将这些数字与测量期间发生的blocked_process_reports相关联。保罗·兰德尔（Paul Randall）有一个很好的脚本：向我发送您的等待统计信息并获取我的建议，以及30天免费的Pluralsight作为回报

脚本捕获当前计数器，等待23小时（可以修改），重新捕获当前计数器再次进行比较，并为您提供等待类型最多的95％。您可以尝试运行1个小时，并准备好XEL文件。

您可能会发现等待类型（例如LCK_M_SH等），它告诉您存储空间写得很慢。或者您还有其他一些开销（例如CX_PACKET_WAITS，...）。某些情况会减慢您的更新速度。然后，您可以查看sys.dm_os_wait_stats是否与带有空节点的blocked_process_reports相关。

在某些情况下，被相同的SPID阻止了被阻止的SPID：

安装SQL Server 2000 SP4之后，填充sysprocesses表中的“受阻止的列”以进行闩锁等待

当SPID等待I / O页面锁存器时，您可能会注意到被阻止的列简要地报告了该SPID正在自身阻塞。此行为是将闩锁用于数据页上的I / O操作的方式的副作用。当线程发出I / O请求时，发出I / O请求的SPID获取页面上的闩锁。所有SQL Server 2000 I / O操作都是异步的。因此，如果发出I / O请求的SPID必须等待请求完成，则SPID将尝试在同一页上获取另一个闩锁。该第二闩锁被第一闩锁阻挡。因此，被阻止的列报告该SPID正在阻止自身。 I / O请求完成后，第一个锁存器被释放。然后，第二个闩锁请求被批准。

替代答案

这进一步表明您可能遇到IO问题。这些问题导致“进程阻塞”，但没有相关的外部SPID。扩展事件可能不会在单独的节点中报告进程/ SPID。

我可能会误读此信息，但是此信息不能证明问题不是锁升级吗？引号中的一节说“查看被阻止的流程报告的详细信息。”，问题中最上面的XML是被阻止的流程报告。接下来，同一引号中的部分说：“如果waitresource以KEY或PAG而不是OBJECT开头，则该特定块中不涉及锁升级。”，并且blocked-process-report XML显示waitresource =“ KEY：6： 72057 ....这意味着此处不涉及“锁升级”。

–所罗门·鲁兹基
17年4月5日在21:30

不，您没有误读它。问题中提供的部分是此服务器上的一个问题。我的答案是针对由于阻塞和锁定升级而可能发生的问题的全局方法。如果可以解决一些主要问题（用于OBJECT级别锁定的blocked_process_reports），则较小的问题（在其他级别上的blocked_process_reports）可以解决。这就是为什么我还添加了第二个替代答案的原因。

– John K. N.
17年4月6日在6:29

编程黑洞网

在阻止的流程报告中清空阻止流程

#1 楼

评论

#2 楼

评论