使用 CROSS APPLY OPENJSON 會導致 Azure 掛起

我有一個大約 800 萬行的表，其架構為：

CREATE TABLE [dbo].[Documents](
   [Id] [uniqueidentifier] NOT NULL,
   [RemoteId] [int] NOT NULL,
   [Json] [nvarchar](max) NULL,
   [WasSuccessful] [bit] NOT NULL,
   [StatusCode] [int] NULL,
   [Created] [datetime2](7) NULL,
CONSTRAINT [PK_Documents] PRIMARY KEY CLUSTERED 
(
   [Id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[Documents] ADD  CONSTRAINT [DF_Documents_Id]  DEFAULT (newsequentialid()) FOR [Id]
GO

ALTER TABLE [dbo].[Documents] ADD  CONSTRAINT [DF_Documents_Created]  DEFAULT (getdate()) FOR [Created]
GO

json 文件的結構為

{
   "Id": 1,
   "Data": [
       {
           "Id": 99,
           "Name": "Person 1"
       },
       {
           "Id": 100,
           "Name": "Person 2"
       }
   ]
}

我正在嘗試從文件中提取 JSON 數組，並使用以下查詢將其插入到新表中：

;WITH CTE (Json) AS 
(
   SELECT TOP 10
       JSON_QUERY([Json], '$.Data')
   FROM 
       Documents
   WHERE 
       ISJSON([Json]) > 0
)
INSERT INTO [dbo].[ParsedDocuments] (Id, Name)
SELECT
   JSON_VALUE([Value], '$.Id') AS [Id],
   JSON_VALUE([Value], '$.Name') AS [Name],
FROM 
   CTE
CROSS APPLY 
   OPENJSON([Json]) as X

我發現如果我提取和查詢數據樣本，比如 1000 行。一切都按預期工作，數據被插入到目標表中。但是，當我查詢主表時，伺服器似乎只是掛起並且沒有響應。

我懷疑它試圖在插入數據之前跨所有行執行交叉應用。有什麼方法可以提高性能嗎？或者允許作業開始將結果“流式傳輸”到目標表中，而不是嘗試將它們批量處理？

最後，如您所見，我正在使用“TOP 10”結果。然而，我仍然體驗到性能懸而未決。我不確定為什麼。

永遠不會生成大型表的查詢計劃，當針對數據樣本執行時，查詢計劃可以在此處找到。

您甚至無法獲得針對大表的查詢的估計執行計劃這一事實表明編譯正在等待該nvarchar(max)列的統計資訊創建（或更新）。

當針對較小的樣本表執行時，統計資訊的創建會很快完成，因此可以快速創建執行計劃。

我可以在低功率 (S0) Azure SQL 數據庫上重現您的情況。nvarchar(max)在稍微不那麼貧乏的 S3 實例上，由於讀取列以生成統計資訊涉及大量 I/O，因此編譯仍需要大約 30 秒。需要高級層才能獲得良好的 I/O 性能——即使是冷 P1 也可以在 3 秒左右的時間內創建這些統計資訊。

您可以通過暫時禁用自動統計創建來確認原因：

ALTER DATABASE CURRENT SET AUTO_CREATE_STATISTICS OFF;

然後 SQL 伺服器將快速生成一個計劃，儘管會有關於缺少統計資訊的警告：

確保在測試後重新啟用此功能，因為良好的統計數據對於總體執行計劃質量至關重要。或者承諾手動創建必要的統計數據。

如果問題是自動刷新過時的統計資訊，您可以選擇非同步更新這些資訊：

ALTER DATABASE CURRENT SET AUTO_UPDATE_STATISTICS_ASYNC ON;

或者，將您的 Azure SQL 數據庫擴展到適合您的工作負載和預算的性能級別。

引用自：https://dba.stackexchange.com/questions/211622