Azure SQL中SELECT列INTO #Temp表执行极慢，如何优化？

• 检查Tempdb配置与锁争用

  • 确认4个Tempdb数据文件大小一致、自动增长设置相同（建议用固定MB增量，避免百分比增长导致的文件不均衡），执行以下语句验证：

    SELECT name, size/128 AS SizeMB, growth FROM tempdb.sys.database_files WHERE type_desc = 'ROWS'
    

  • 排查Tempdb锁阻塞，执行语句查看当前锁资源：

    SELECT resource_type, request_mode, request_session_id, resource_description 
    FROM sys.dm_tran_locks 
    WHERE resource_database_id = DB_ID('tempdb')
    

    若发现长时间持有的锁，对应杀掉阻塞会话或调整查询逻辑减少锁竞争。

• 调整查询执行计划

  • 尝试使用OPTION (RECOMPILE)让SQL Server重新生成适配临时表写入的执行计划，避免参数嗅探导致的低效计划：

    SELECT ... INTO #Temp 
    FROM SourceTable WITH (NOLOCK)
    OPTION (RECOMPILE)
    

  • 匹配Tempdb数据文件数设置MAXDOP，比如OPTION (MAXDOP 4)，让并行写入更好利用多文件资源。
  • 对比有无INTO子句的执行计划差异，重点看是否存在排序溢出到Tempdb、哈希匹配内存不足等情况，可通过SET SHOWPLAN_XML ON或Azure Portal的Query Performance Insight分析。

• 预定义临时表结构替代SELECT INTO
  手动创建临时表并指定精准数据类型（避免SELECT INTO自动推断的冗余类型，如VARCHAR(MAX)），配合TABLOCK启用批量日志提升写入速度：

  CREATE TABLE #Temp (
      Col1 INT NOT NULL,
      Col2 VARCHAR(150) NOT NULL,
      -- 按实际业务定义所有列的类型、长度、约束
  )
  
  INSERT INTO #Temp WITH (TABLOCK)
  SELECT Col1, Col2 ... 
  FROM SourceTable WITH (NOLOCK)
  

• 排查Azure SQL资源瓶颈

  • 在Azure Portal数据库监控面板查看CPU、内存、IO指标：若数据IO等待时间过高，考虑升级服务层级（如General Purpose转Business Critical）或切换至Premium存储；若CPU持续满载，优化查询减少计算开销。
  • 检查查询内存授予情况，若存在内存溢出到Tempdb，执行以下语句查看：

    SELECT session_id, granted_memory_kb, required_memory_kb, used_memory_kb, query_text 
    FROM sys.dm_exec_query_memory_grants qmg
    CROSS APPLY sys.dm_exec_sql_text(qmg.sql_handle)
    WHERE session_id = <你的查询会话ID>
    

    必要时简化查询逻辑减少内存需求，或提升数据库的内存配额。

• 其他细节优化

  • 移除SELECT语句中不必要的复杂计算、嵌套函数，将计算逻辑提前至子查询或临时变量，降低写入时的CPU消耗。
  • 若源表为分区表，添加分区筛选条件减少扫描的数据量。
  • 确保临时表无额外约束、触发器（默认无，若手动添加需移除）。

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