五餅二魚工作室

前一陣子在使用 Azure SQL Database 的時候，遇到一些效能上的瓶頸，只能想辦法調整  Azure SQL Database 的等級，來應付越來越大量的查詢。

剛好在整理資料的時候，看到百敬老師介紹 SQL 2019 新功能的影片，原本想在自己的電腦上測試，但因為一些問題造成無法安裝，因此就想到怎麼不使用 Azure SQL Database 來做測試，因此就將資料庫複製一個出來測試一下。

測試的標的指令是我們的一個 View，這個 View 裡面會有一些針對常用的九個資料表做資料筆數計算，因此會有類似以下的一段語法 ( 實際上會比較複雜一點，作範例就先簡化一下囉 )

WITH Item(TenantID, ItemCount )AS 
(	SELECT TenantID, COUNT(1) AS ItemCount 
	FROM dbo.Items 
	GROUP BY TenantID
), Customer(TenantID, CustomerCount) AS
(	SELECT TenantID, COUNT(1) AS CustomerCount 
	FROM dbo.Customers 
	GROUP BY TenantID
)
SELECT C.TenantID, ISNULL(Q1.ItemCount, 0) AS ItemCount, ISNULL(Q2.CustomerCount, 0) AS CustomerCount
FROM dbo.Company AS C 
LEFT OUTER JOIN Item AS Q1 ON C.TenantID = Q1.TenantID 
LEFT OUTER JOIN Customer AS Q2 ON C.TenantID = Q2.TenantID 

在這樣的指令中，如果在我們一開始用的 Azure SQL Database ，他的相容模式是 SQL Server 2016 ( 130 ) , 這下面的測試語法中，我們利用設定資料庫相容模式來做比較

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE 

SET STATISTICS IO,TIME ON

ALTER DATABASE [CloudDB] SET COMPATIBILITY_LEVEL = 130
GO

SELECT * FROM [dbo].[TenantSummary]

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE 

ALTER DATABASE [CloudDB] SET COMPATIBILITY_LEVEL = 150
GO

SELECT * FROM [dbo].[TenantSummary]

SET STATISTICS IO,TIME OFF

因此透過執行計畫查看的時候，可以看到一開始如果是 SQL Server 2016 相容模式的時候，執行計畫是採用 Row 模式去抓資料

但如果我們切換相容模式是 SQL Server 2019 ( 150 ) 的時候，會看到執行計畫是採用 Batch 模式，按照百敬老師的說法，採用批次模式一次處理是 900 筆，因此中間的實際批次數目就會是 182 ( 163712/900 = 181.9 )

另外透過統計資料，可以看到原本的時間會是

 SQL Server Execution Times:
   CPU time = 672 ms,  elapsed time = 991 ms.

而改成 SQL Server 2019 相容模式的時候

 SQL Server Execution Times:
   CPU time = 391 ms,  elapsed time = 390 ms.

雖然實際上差距並不到一秒鐘，但因為像是我們系統會很時常有這類大量的統計計算，像是計算某一段時間的庫存，銷售量，利潤等處理，也都可以利用改變相容模式的方式，再不用調整程式的情況下，讓效能也有所改善。因此如果有採用 Azure SQL Database 的朋友，可以試試看利用這樣的方式 「ALTER DATABASE [資料庫名稱] SET COMPATIBILITY_LEVEL = 150」，調整資料庫的相容層級，來體驗 SQL Server 2019 所帶來的好處囉。

---

PS. 測試語法中有一段「ALTER DATABASE SCOPED CONFIGURATION」，這個類似地端 SQL Server 上使用 DBCC FREEPROCCACHE 的功效，只是雲端不能去針對整個 Instance 去做處理，只能改成只清除該資料庫的的執行計畫。