SQL Server 2016 SP 1在HP 3 3PAR环境中会导致奇怪的头阻止程序

Question

我的客户是在HP3PAR硬件上。三台服务器位于不同的地理位置，与40 gbit光纤连接。SAN只是SSD，机器有很多核心。

ETL进程启动一个连接两个表的单个select查询。执行计划显示非聚集索引的使用，并结合对两个表的堆扫描。

这个简单的查询将成为所有其他后续查询的头阻止程序，甚至是那些引用完全不同的表的查询。

我的第一个猜测是，可能涉及到与3PAR有关的内容，但我不太确定。除此之外，奇怪的是

select a.[value] from table a, inner join table b where table a.column = 12345 

设法阻止

update table c set column a = [value].

AFAIK没有锁定提示。

在使用的表上没有外键触发器。数据库中没有任何功能或其他创造性的额外功能。

我想知道HP3PAR故障转移是否会在某种程度上导致查询锁定数据库或表、数据分区或HOBT。有什么想法吗？如果需要更多的信息，请告诉我。

==编辑12/28/2017回答了问题:)

Answer 1

当您对正在发生的阻塞链进行故障排除时，请从服务提供商_WhoIsActive开始：

sp_WhoIsActive @get_locks = 1

有一个被封锁的列显示是谁挡住了谁。领头阻滞剂不会让任何人进入他们的封堵栏。

一旦找到了引导阻止程序，就单击locks列--它是一个XML字段，它扩展为显示该会话所持有的锁的完整列表。

可能是select是更长事务的一部分(还有一个开放事务列)，或者select实际上不是阻塞的。

在这里获得具体的、可操作的建议的最佳选择是发布sp_WhoIsActive输出的图片，包括阻塞列，然后是锁的XML内容。

Answer 2

如果您的SAN复制设置为同步(取决于各框之间的连接)，则会对性能产生可测量的影响。衡量它的最好方法是对存储进行同步复制和不同步复制的基准测试。

我还在一个完整的SSD3PAR上看到了去重复的问题，但这是固件中的一个bug，后来得到了修复。

也就是说，存储延迟本身不会导致SQL Server中的阻塞。但是，它可以使您的事务运行时间更长，因此您的锁(由Server而不是SAN持有)可以保持更长的时间。

由于您是在声明站点位于不同的地理位置，同步复制可能不是您的最佳选择(IIRC )，因此需要最大延迟时间为2ms。3 3PAR提供了其他3种可能更适合您的复制方法。

• 异步周期:允许您配置频率并具有配置频率的RPO (通常是几分钟)
• 异步流:在提交上一次快照后，会立即拍摄新快照，通常有几秒钟的RPO。
• SLD同步长距离:需要3个数据中心，但可以通过同步和异步相结合实现RPO为0

Answer 3

在使用了sp_WhoIsActive (谢谢@布伦特-厄扎尔)之后，我可以继续下一步了。

下面是阻塞查询的执行计划：https://www.brentozar.com/pastetheplan/?id=Sy7rYfGXG

这是锁XML：

<Database name="DS4_P_Productie">
  <Locks>
    <Lock request_mode="S" request_status="GRANT" request_count="1" />
  </Locks>
  <Objects>
    <Object name="AL_STATISTICS" schema_name="dbo">
      <Locks>
        <Lock resource_type="OBJECT" request_mode="IX" request_status="WAIT" 
request_count="1" />
      </Locks>
    </Object>
    <Object name="AL_VERSION" schema_name="dbo">
      <Locks>
        <Lock resource_type="KEY" 
index_name="PK__AL_VERSI__D9C1FA015BCBE3FC" request_mode="X" 
request_status="GRANT" request_count="1" />
        <Lock resource_type="OBJECT" request_mode="IX" request_status="GRANT" request_count="1" />
    <Lock resource_type="PAGE" page_type="*" index_name="PK__AL_VERSI__D9C1FA015BCBE3FC" request_mode="IX" request_status="GRANT" request_count="1" />
  </Locks>
</Object>
 </Objects>
</Database>

热心的读者会注意到索引更新的名称(AL_STAT_INDX2和AL_STAT_INDX1)与另一个索引(PK_AL_VERSI_.)之间的差异。后一个索引位于AL_VERSION表上，这个表甚至在查询中都没有引用。

这个表也被使用，但更早的是：

<?query --
UPDATE "AL_STATISTICS" SET "KEY2" = 194462   WHERE "OBJECT_KEY" = -1
--?>

这个查询(具有不同的KEY2值)是反复执行的。在执行此更新之间，AL_STATISTICS表中有不同的插入。

所以，继续进一步的调查。事务的来源是ETL进程。错，两个ETL进程。从同一秒钟开始。接下来发生的情况是，两个进程都希望访问表。大多数情况下，这是没有问题的，除非读或写只是为了慢下来。这就是严重阻塞开始的关键。其中一个ETL进程每5分钟运行一次，另一个每15分钟运行一次。您可以猜到当该块在午夜前后发出，而第一次检查是在早上7点左右时，会发生什么情况。

解决方案。同样，热心的读者已经看到了这一点，没有聚集索引。在统计表中添加了一个聚集索引之后，这些锁就消失了，就像对退欧的支持一样，在含义变得清晰之后。

为了更好地理解最后一个注释，在索引之前，sp_whoisactive表在5天内为一个数据库记录了大约700个条目。在过去的3天里，只增加了6个条目。

所以，再往前走一步吧！