Azure Synapse专用SQL池并行插入staging表单任务运行问题咨询

你遇到的并行INSERT串行执行问题确实是锁机制导致的。

Azure Synapse专用SQL池的HEAP表默认会对写入操作触发锁升级，当单批次写入数据量达到阈值时，会将行/页级锁升级为全表排他锁，同一时间仅允许一个会话持有该锁，其余写入任务会进入挂起状态。你当前配置的500 DWU对应最高支持20个并发查询槽位，工作负载分类器的资源配置也足够支撑并行执行，瓶颈完全来自HEAP表的表锁阻塞。

可实施的调整方案 #

• 修改目标staging表的存储结构
  优先将HEAP表改为聚集列存储索引（CCI）表，Synapse对CCI表的并行写入支持更优，默认不会触发全表排他锁，多个会话可同时写入不同的行组，是staging表的最优存储选择。修改参考语句：

  -- 重建表为CCI+轮询分布（可根据业务查询逻辑调整为哈希分布）
  CREATE TABLE [staging].[SrcChroniquesPGA_new]
  WITH (
      CLUSTERED COLUMNSTORE INDEX,
      DISTRIBUTION = ROUND_ROBIN
  )
  AS SELECT * FROM [staging].[SrcChroniquesPGA];
  
  -- 替换原表
  RENAME OBJECT [staging].[SrcChroniquesPGA] TO [SrcChroniquesPGA_old];
  RENAME OBJECT [staging].[SrcChroniquesPGA_new] TO [SrcChroniquesPGA];
  

• 保留HEAP表时关闭锁升级
  如果需要保留HEAP表结构，可关闭表的锁升级机制，允许会话持有行/页级锁实现并行写入，仅适合单批次插入行数小于65536的场景：

  ALTER TABLE [staging].[SrcChroniquesPGA] SET (LOCK_ESCALATION = DISABLE);
  

• 优化存储过程执行逻辑
  现有逻辑中先将全量JSON加载到nvarchar(max)变量的操作会额外占用内存，拉长锁持有时间，可直接关联源表减少执行耗时：

  ALTER PROC [staging].[usp_stg_load_SrcChroniquesPGA] @file_name [varchar](100) AS
  BEGIN
  INSERT INTO [staging].[SrcChroniquesPGA]
  select 
              @file_name as [NomFichier]
              ,[DatePublication],[NomSource],[Pas],[Type],[DateChronique]
              ,CASE WHEN Pas = 'H' AND DATEPART(hh, DateChronique) BETWEEN 0 AND 5 THEN DATEADD(day, -1, CAST(DateChronique AS DATE)) 
                      ELSE CAST(DateChronique AS DATE) END 
               AS [DateJourneeGaziere]
              ,[HorodateMaj],[Qualite]
              ,[ValeurChronique],[DateStatut],[HorodateMajStatut],[ValeurStatut]
      from [staging].[xml_to_json] t
      CROSS APPLY OPENJSON( t.json_file ,'$') 
      WITH (
          [DatePublication] [datetime2](7) '$.Transaction.HorodateInfos',
          [PGA] nvarchar(MAX) '$.PGA' as JSON
      ) j
      CROSS APPLY OPENJSON(PGA)
      WITH (
          [NomSource] [nvarchar](256) '$.PCEIdentification.NomSource',
          [PGAStatut] nvarchar(MAX) '$.PGAStatut' as JSON,
          [PGAChronique] nvarchar(MAX) '$.PGAChronique' as JSON
      )
      CROSS APPLY OPENJSON(PGAChronique)
      WITH (
          [Pas] [nvarchar](256) '$.Pas',
          [Type] [nvarchar](256) '$.Type',
          [Mesure]  nvarchar(MAX) '$.Mesure' as JSON
      ) 
      CROSS APPLY OPENJSON([Mesure])
      WITH (
          [DateChronique] [datetime2](7) '$.Date',
          [ValeurChronique] [float] '$.Valeur',
          [HorodateMaj] [datetime2](7) '$.HorodateMaj',
          [Qualite] [nvarchar](256) '$.Qualite'
      ) msr
      OUTER APPLY (
              SELECT * FROM OPENJSON(PGAStatut)
              WITH (
                  [DateStatut] [datetime2](7) '$.Date',
                  [HorodateMajStatut] [datetime2](7) '$.HorodateMaj',
                  [ValeurStatut] [nvarchar](256) '$.Valeur'
              )
              WHERE [DateStatut] = [DateChronique]
                  ) jj
      WHERE t.[file_name] = @file_name
  END
  

• 调整ADF并行度配置
  完成上述锁优化后，可将ADF ForEach的最大并行批次调整为12~16，避免过高并行度带来的额外调度开销，充分利用DWU资源。

内容的提问来源于stack exchange，提问作者SniperPro