SQL Server 2016 並發限制？調整數據庫並發性

我有一個包含約 10 億條時間戳記錄的表，每條記錄都包含一個會話表的 FK（每天一個會話和每天 3-500,000 條記錄），因此查找給定日期的記錄只是一個整數連接。

我正在嘗試分析此表中的數據（數據按會話分組），當使用客戶端電腦上的 C# 控制台應用程序時，我可以在 70 分鐘內執行完整的分析（每條記錄）。當我嘗試直接在 TSQL 中執行類似的分析時，需要 12 多個小時。我預計會受到一些懲罰，因為 TSQL 查詢使用標量函式和自定義聚合 (clr))。

**我的問題：**在 C# 中，我了解如何最大化和調整併發性，因此 70 分鐘是一個經過調整的數字。是否可以直接在 SQL 中調整查詢以獲得最大並發性，還是留給 C# api 更好？（我也可以在 R、db 或外部完成這項工作，但 .Net 並發 API 讓我覺得更勝一籌。）

詢問：

SELECT TypeNumber, SessionId, dbo.udf_SessionName([timestamp]) SessionName, 
CAST(max(price)-min(price) AS REAL) as Variance, sum(EventNumber) as Volume, 
dbo.Direction(price,[timestamp]) as MoveDirection
INTO temp.AnalysisResults
FROM MyTable
WHERE ISNULL(price,0)<>0
GROUP BY TypeNumber, SessionId, dbo.udf_SessionName([timestamp])

雜項

• 由於插入，為此查詢啟用了批量記錄
• 此查詢中未使用主鍵（它是跨三個欄位的複合鍵，此處不需要。但是，查詢計劃顯示正在掃描此索引，而不是我在下面提到的索引（計劃最初推薦） ）。
• 啟用行級壓縮
• 數據跨越五年，每個月都有一個只讀文件組（按月分區）；所有文件組都駐留在同一個 SSD 上（不是很好，我知道）
• 索引：在 SessionId asc 上非聚集，包括 TypeNumber,Timestamp,Price
• 4 個 CPU 核心可用
• 標量函式獲取每個時間戳，使用 AT TIME ZONE（兩次呼叫）將其轉換為本地時間，並在 5 記錄表中查找它。
• 自定義聚合使用自定義序列化程序，該序列化程序接受小數和 datetime2 並返回字元串。序列化程序傳遞需要解析的字元串（這不是很好）
• 查看查詢計劃（刪除插入），迄今為止最昂貴的操作是排序（98% 的成本；我明確啟動的唯一排序是在 clr 聚合器函式中）：

**警告：**我知道使用 CLR 聚合會花費我查詢時間以及壓縮。如果我使用控制台應用程序，我可以將所有分析工作解除安裝到更強大的機器上，讓數據庫伺服器只做 IO。這是“顯而易見的答案”，還是我可以將大部分工作保留在數據庫中（通常，我可以直接在數據庫中做的越多越好）。

---

我意識到，通過這個數據庫的設置方式，以及它的壓縮設置，它對 IO 的調整比對 CPU 的調整更多。我不希望能夠達到與數據庫僅執行 IO 的純 C 解決方案相當的性能；但是通過最大化 db 可以做的 cpu 工作可以獲得很多好處。

udf_SessionName：

create function dbo.[udf_SessionName](@timestamp datetime2)
returns nvarchar(100)
begin
declare @localTime time = CAST(@timestamp at time zone 'UTC' at time zone 'Pacific Standard Time' as time)
declare @result nvarchar(100) = (select top 1 sessionname from MarketSessions where @localTime>=StartTime and @localTime < EndTime)
if (@result is null) set @result = 'European'
return @result
end

SQL Fiddle中的表結構

---

**行動後報告：**我已經實施了@SolomonRutzky 的建議，查詢現在在 3 小時內完成，而 12 小時以上。

變更摘要

1. 將時區操作從標量 udf 更改為 clr 函式（SAFE沒有 的實現TimeZoneInfo）。
2. 將該 clr 函式滾動到非持久計算列中。
3. 添加了一個新索引：

創建非聚集索引

$$ inx_MyIndex $$在$$ dbo $$.MyTable (TypeNumber ASC, SessionId ASC) INCLUDE (SessionName,Price,Timestamp,Volume])

SessionName 作為索引中的鍵確實會更好，但即使它既精確又具有確定性，因為它是一個 CLR 函式，除非它被持久化，否則它不能成為鍵，而且該列雖然大部分是靜態的，但還不夠靜態要持久。

4. 已移除ISNULL

修改後的查詢

INSERT INTO temp.AnalysisResults
SELECT TypeNumber, SessionId, SessionName, 
CAST(max(price)-min(price) AS REAL) as Variance, sum(EventNumber) as Volume, 
dbo.Direction(price,[timestamp]) as MoveDirection
FROM MyTable
WHERE price <> 0 AND price IS NOT NULL
GROUP BY TypeNumber, SessionId, SessionName

新的執行計劃

首先，我認為您需要嘗試一些事情來減少 12 小時以上的查詢：

1. 我要檢查的第一件事是索引。你有一個 GROUP BY ，TypeNumber, SessionId, dbo.udf_SessionName([timestamp])但索引是 on SessionId asc, INCLUDE TypeNumber,Timestamp,Price。意思是，順序不一樣（因此很可能會忽略索引並掃描表的原因）。您至少需要索引TypeNumber, SessionId以匹配GROUP BY這些列的順序。然後INCLUDE ([price], [timestamp], [EventNumber])使它成為一個覆蓋指數。關於索引還有更多要說的，但這會導致下一部分……

2. 接下來是標量 UDF。眾所周知，這些都是不好的，原因有幾個。並且使用AT TIME ZONE不會非常快。所以，考慮：

3. 將其轉換為內聯 TVF 通常通常會產生奇蹟，但不確定這是否會與最適合索引的內容相衝突

4. 使標量 UDF 變慢的部分原因是它們阻止了並行計劃。一個 SQLCLR Scalar UDF 被標記為DataAccessand SystemDataAccessboth = noneANDIsDeterministic=true 不會阻止並行計劃:-)。如果您要從 UTC 轉換為本地時間（反之亦然），那麼您可以使用可以在SAFE程序集中呼叫的類。如果您需要從不同的時區進行轉換，那麼您需要使用TimezoneInfo該類（我認為），這需要將 Assembly 標記為UNSAFE. 存在UNSAFE不會帶走允許並行計劃的好處，但如果有可能採用該SAFE方法，那麼就這樣做。

5. 使 SQLCLR UDF 方法有點複雜的是您正在查找表：MarketSessions. 你提到這只是 5 行。那 5 行是相當靜態的嗎？如果是這樣，您可能仍然可以通過在程序集中創建一個靜態集合，然後在靜態類建構子中從表中填充它來避免使用不進行任何數據訪問的 SQLCLR UDF。udf_SessionName靜態類建構子將在載入程序集的任何時候執行，並且可以使用在UDF中檢查所需的值預填充集合。唯一的問題是靜態類建構子中沒有context connection可用的內部，因此需要將程序集標記為EXTERNAL_ACCESS. 但是 UDF 不會呼叫SqlConnection，它只會從靜態集合中讀取:-)。

如果其中的值MarketSessions更不穩定，您始終可以創建呼叫相同方法的 SQLCLR UDF 或儲存過程來填充類建構子呼叫的靜態集合。然後您可以在執行此查詢之前執行該操作，以便內部靜態集合具有該表中的“目前”記錄。但是，在這種情況下，您可能無法執行以下兩個步驟，因為索引值可能過時/不正確。但是您仍然可以從能夠制定並行計劃中受益。 4. 無論您是添加（如果尚未這樣做）WITH SCHEMABINDING到 T-SQL UDF還是將其轉換為具有如上所述設置的屬性的 SQLCLR UDF，您都應該向表中添加一個列作為 NONPERSISTED 計算列，該列僅僅是對 UDF 的呼叫。 5. 一旦 NONpersisted Computed Column 存在，您就可以在以下位置創建實際索引： TypeNumber, SessionId, computedColumn INCLUDE ([price], [timestamp], [EventNumber])。您可能還需要設置 SQLCLR UDF 的SqlFunction屬性IsPrecise=true以使其可索引。 3. 您可能需要重新考慮ISNULL. price可能只是一個INCLUDE列，因此 ISNULL 函式可能不會在此處使用索引時出現問題，但您可能需要將其分解為price <> 0 AND price IS NOT NULL. 4. 我不確定整體性能影響，但我從來都不是該SELECT...INTO構造的粉絲。最好先創建表然後再做INSERT INTO...SELECT。 5. 正如@LowlyDBA 在對該問題的評論中指出的那樣：謹慎使用REAL（或FLOAT）作為財務價值。是的，它們可以更好地壓縮並更快地傳輸，但它們有時也可以在非常低的端獲得額外的值。如果您進行計算，我當然不會使用該數據類型。但是僅僅傳回應用程序可能沒問題。但是如果你在做計算，你真的應該使用DECIMAL()or 甚至MONEY.

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