Azure SQL数据库删除查询执行异常的排查与解决

问题场景 #

在Azure SQL弹性池环境中，执行一段批量删除Blobs表中未被引用记录的查询时，出现服务器间表现不一致的问题：

• 一台服务器上查询正常执行，能完成删除操作
• 另一台服务器上查询持续运行数小时，Blobs表文件大小无明显变化，怀疑临时表#UnusedBlobIDs的插入操作未成功

原查询代码如下：

DECLARE @r INT;
DECLARE @batchsize INT;
 
create table #UnusedBlobIDs (
    ID UNIQUEIDENTIFIER PRIMARY KEY (ID)
);
 
SET @r = 1;
SET @batchsize=1000;
 
WITH [ExistingBlobs] ([BlobId])
    AS
    (SELECT [Blobs].[BlobId]
    FROM [Blobs]
    JOIN [SharedFields]
    ON '{' + CONVERT(NVARCHAR(MAX), [Blobs].[BlobId]) + '}' = [SharedFields].[Value]
    UNION
    SELECT [Blobs].[BlobId]
    FROM [Blobs]
    JOIN [SharedFields]
    ON CONVERT(NVARCHAR(MAX), [Blobs].[BlobId]) = [SharedFields].[Value]
    UNION
    SELECT [Blobs].[BlobId]
    FROM [Blobs]
    JOIN [VersionedFields]
    ON '{' + CONVERT(NVARCHAR(MAX), [Blobs].[BlobId]) + '}' = [VersionedFields].[Value]
    UNION
    SELECT [Blobs].[BlobId]
    FROM [Blobs]
    JOIN [VersionedFields]
    ON CONVERT(NVARCHAR(MAX), [Blobs].[BlobId]) = [VersionedFields].[Value]
    UNION
    SELECT [Blobs].[BlobId]
    FROM [Blobs]
    JOIN [UnversionedFields]
    ON '{' + CONVERT(NVARCHAR(MAX), [Blobs].[BlobId]) + '}' = [UnversionedFields].[Value]
    UNION
    SELECT [Blobs].[BlobId]
    FROM [Blobs]
    JOIN [UnversionedFields]
    ON CONVERT(NVARCHAR(MAX), [Blobs].[BlobId]) = [UnversionedFields].[Value]
    UNION
    SELECT [Blobs].[BlobId]
    FROM [Blobs]
    JOIN [ArchivedFields]
    ON '{' + CONVERT(NVARCHAR(MAX), [Blobs].[BlobId]) + '}' = [ArchivedFields].[Value]
    UNION
    SELECT [Blobs].[BlobId]
    FROM [Blobs]
    JOIN [ArchivedFields]
    ON CONVERT(NVARCHAR(MAX), [Blobs].[BlobId]) = [ArchivedFields].[Value])
     
    INSERT INTO #UnusedBlobIDs (ID) SELECT DISTINCT  [Blobs].[BlobId]
    FROM [Blobs]
    WHERE NOT EXISTS
    (  SELECT NULL
    FROM [ExistingBlobs]
    WHERE [ExistingBlobs].[BlobId] = [Blobs].[BlobId])
     
WHILE @r > 0
   BEGIN
       BEGIN TRANSACTION;
        DELETE TOP (@batchsize) FROM [Blobs] where [Blobs].[BlobId] IN (SELECT ID from #UnusedBlobIDs);
        SET @r = @@ROWCOUNT;
        COMMIT TRANSACTION;
    END
 

DROP TABLE #UnusedBlobIDs;

根本原因分析 #

1. CTE冗余与低效逻辑：原CTE中存在大量重复的UNION分支（例如SharedFields表被重复关联两次），UNION本身会触发去重排序，重复分支会额外增加CPU和IO负担。
2. 索引失效：关联条件中对BlobId进行了CONVERT字符串转换和'{' + ... + '}'拼接操作，导致SQL Server无法利用BlobId或Value字段的索引，只能执行全表扫描。在数据量较大的服务器上，这种操作会导致CTE执行时间极长，甚至无法完成，临时表自然没有数据插入，后续的DELETE循环也不会执行，因此表大小无变化。
3. 资源竞争：Azure SQL弹性池的资源（DTU/CPU/内存）可能被其他任务占用，导致查询无法获得足够资源推进执行。

排查步骤 #

• 验证临时表数据：在INSERT语句后添加SELECT COUNT(*) FROM #UnusedBlobIDs，确认是否有数据插入。如果返回0，说明CTE未生成有效数据。
• 查看执行计划：在SSMS中启用执行计划查看，或执行SET SHOWPLAN_XML ON后运行查询，重点检查是否存在全表扫描、高成本的排序操作。
• 检查资源使用：在Azure Portal中查看弹性池的DTU/CPU/内存使用率，确认是否存在资源瓶颈。
• 对比数据量：检查两台服务器上Blobs、SharedFields等关联表的数据量，异常服务器的数据量可能远大于正常服务器，放大了低效逻辑的影响。

有效解决方法 #

将原CTE的多表联合查询改为逐个查询各关联表，逐步排除被引用的BlobId，避免一次性处理复杂的多表联合逻辑。优化后的示例代码如下：

DECLARE @r INT;
DECLARE @batchsize INT;

CREATE TABLE #UnusedBlobIDs (
    ID UNIQUEIDENTIFIER PRIMARY KEY (ID)
);

SET @batchsize = 1000;

-- 先将所有BlobId导入临时表
INSERT INTO #UnusedBlobIDs (ID)
SELECT BlobId FROM Blobs;

-- 排除SharedFields中引用的BlobId（带大括号和不带大括号两种格式）
DELETE FROM #UnusedBlobIDs
WHERE ID IN (
    SELECT b.BlobId
    FROM Blobs b
    JOIN SharedFields sf ON '{' + CONVERT(NVARCHAR(MAX), b.BlobId) + '}' = sf.Value
)
OR ID IN (
    SELECT b.BlobId
    FROM Blobs b
    JOIN SharedFields sf ON CONVERT(NVARCHAR(MAX), b.BlobId) = sf.Value
);

-- 排除VersionedFields中引用的BlobId
DELETE FROM #UnusedBlobIDs
WHERE ID IN (
    SELECT b.BlobId
    FROM Blobs b
    JOIN VersionedFields vf ON '{' + CONVERT(NVARCHAR(MAX), b.BlobId) + '}' = vf.Value
)
OR ID IN (
    SELECT b.BlobId
    FROM Blobs b
    JOIN VersionedFields vf ON CONVERT(NVARCHAR(MAX), b.BlobId) = vf.Value
);

-- 排除UnversionedFields中引用的BlobId
DELETE FROM #UnusedBlobIDs
WHERE ID IN (
    SELECT b.BlobId
    FROM Blobs b
    JOIN UnversionedFields uf ON '{' + CONVERT(NVARCHAR(MAX), b.BlobId) + '}' = uf.Value
)
OR ID IN (
    SELECT b.BlobId
    FROM Blobs b
    JOIN UnversionedFields uf ON CONVERT(NVARCHAR(MAX), b.BlobId) = uf.Value
);

-- 排除ArchivedFields中引用的BlobId
DELETE FROM #UnusedBlobIDs
WHERE ID IN (
    SELECT b.BlobId
    FROM Blobs b
    JOIN ArchivedFields af ON '{' + CONVERT(NVARCHAR(MAX), b.BlobId) + '}' = af.Value
)
OR ID IN (
    SELECT b.BlobId
    FROM Blobs b
    JOIN ArchivedFields af ON CONVERT(NVARCHAR(MAX), b.BlobId) = af.Value
);

-- 执行批量删除
SET @r = 1;
WHILE @r > 0
BEGIN
    BEGIN TRANSACTION;
    DELETE TOP (@batchsize) FROM Blobs WHERE BlobId IN (SELECT ID FROM #UnusedBlobIDs);
    SET @r = @@ROWCOUNT;
    COMMIT TRANSACTION;
END

DROP TABLE #UnusedBlobIDs;

优化逻辑说明 #

• 拆分复杂的多表联合为多个独立的删除操作，降低单查询的复杂度，减少资源占用。
• 避免了UNION带来的额外排序去重开销，提升执行效率。
• 逐步缩小临时表中的待删除ID范围，后续的DELETE操作更高效。

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