為什麼使用 Format vs Right 來應用填充會導致估計的行數發生巨大變化？

我在工作中處理一個查詢，它有一個左連接，比如

cast(cola as varchar) + '-' + right('000' + cast(colb as varchar), 3) = x

此查詢的實際執行計劃相當接近，估計為 269 與實際為 475。

將正確的 +padding 更改為使用 format(colb, ‘000’) 會導致對行數的巨大錯誤估計，至少減少 400 萬，這會導致查詢花費 10-15 倍的時間。

我理解為什麼錯誤估計會導致問題，但我不明白為什麼使用 Format 會導致估計不准確。

這裡發生了一些事情：

1. 性能下降（由於邏輯讀取大大增加）
2. 估計行數的準確性降低

涉及的因素有：

1. 帶有 SQL Server 排序規則的索引VARCHAR列，與NVARCHAR數據進行比較（請注意：此場景是特定於排序規則類型的：如果排序規則是 Windows 排序規則，則不會出現明顯的性能下降。有關詳細資訊，請參閱“對索引的影響”混合 VARCHAR 和 NVARCHAR 類型時")
2. 非確定性內置函式或 SQLCLR 函式，或 T-SQL UDF（無論是否標記為確定性）
3. 碎片化索引（我原以為問題只是陳舊的統計數據，但單獨更新統計數據，即使使用WITH FULLSCAN也沒有效果）

上述三個因素已通過測試得到證實（見下文）。這三個因素中的兩個很容易糾正：

1. 如果想要使用索引列，則將NVARCHAR值轉換為，或者將列的排序規則更改為 Windows 排序規則。VARCHAR``VARCHAR
2. 做一個完整REBUILD的索引。自己做一個ALTER INDEX ... REORGANIZE;orUPDATE STATISTICS ... WITH FULLSCAN;似乎沒有幫助（至少在估計的行數方面）。
3. （可選）考慮是否有確定性替代方案可用（例如，如果CASE / CONVERT+RIGHT比 更有效FORMAT，並且產生相同的結果，那麼無論如何使用CASE / CONVERT+ RIGHT;FORMAT可以做一些漂亮的事情，但對於左填充是不必要的）。

還要記住優先級。雖然有準確的估計行數是理想的，但如果它們足夠接近，你會沒事的。意思是，如果這樣做不會帶來任何真正的性能提升，則不需要做額外的工作來獲得超準確的估計行數（尤其是因為，根據碎片的程度，非確定性函式有時具有更準確的行估計！）。另一方面，更改數據類型（被比較的值）或排序規則是值得的，因為這將產生顯著的積極影響。然後，執行REBUILD索引將使您在估計的行數上足夠接近。

---

測試方法

sys.all_objects我通過使用 500 萬行的“名稱”列（並使用 的排序規則）填充了一個本地臨時表來對此進行測試SQL_Latin1_General_CP1_CI_AS，然後在字元串列上創建一個非聚集索引，然後再添加 100k 行來分割索引.

我過濾了一個VARCHAR文字，然後是相同的字元串文字，但以大寫的“N”為前綴NVARCHAR。這隔離了比較值數據類型的問題。

然後我過濾了相同的文字值，但包含在對FORMAT. 這隔離了非確定性函式的問題。

為了確認函式確定性的行為效果，我創建了兩個 SQLCLR 函式，它們只返回傳入的值，但一個是確定性的，另一個不是。這清楚地表明問題是確定性，而不是函式發生的任何其他事情。我使用 SQLCLR 是因為在 T-SQL 中似乎沒有等效的方法。即使該函式在系統中被標記為確定性（通過使用 創建 UDF WITH SCHEMABINDING），該行為也會反映非確定性函式的行為（我確實對此進行了測試，但未將其包含在下面）。

我使用SET STATISTICS IO, TIME ON;，並檢查了 SSMS 中的“包括實際執行計劃”選項。

執行第一組測試後，我執行：

EXEC (N'USE [tempdb]; UPDATE STATISTICS #Objects [IX_#Objects_Name] WITH FULLSCAN;');

並重新執行測試。對邏輯讀取的改進最小，估計的行數沒有變化。

然後我執行：

ALTER INDEX ALL ON #Objects REORGANIZE;

並重新執行測試。估計的行數沒有變化。

然後我執行：

ALTER INDEX ALL ON #Objects REBUILD;

最後看到了邏輯讀取和估計行數的改進*。*

然後，我刪除了表，使用Latin1_General_100_CI_AS_SC排序規則重新創建了它，然後如上所述重新執行了測試。

測試程式碼

SQLCLR 程式碼

下面的程式碼用於創建兩個做完全相同的事情的標量函式：簡單地返回傳入的值。這兩個函式之間的唯一區別是一個被標記為IsDeterministic = true，另一個被標記為IsDeterministic = false。

using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public class ScalarFunctions
{
   [return: SqlFacet(MaxSize = 4000)]
   [SqlFunction(IsDeterministic = true, IsPrecise = true)]
   public static SqlString PassThrough_Deterministic(
            [SqlFacet(MaxSize = 4000)] SqlString TheString)
   {
       return TheString;
   }

   [return: SqlFacet(MaxSize = 4000)]
   [SqlFunction(IsDeterministic = false, IsPrecise = true)]
   public static SqlString PassThrough_NonDeterministic(
            [SqlFacet(MaxSize = 4000)] SqlString TheString)
   {
       return TheString;
   }
}

測試設置

-- DROP TABLE #Objects;
CREATE TABLE #Objects
(
 [ObjectID] INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
 [Name] VARCHAR(128) COLLATE SQL_Latin1_General_CP1_CI_AS
                              -- Latin1_General_100_CI_AS_SC
);


-- Insert 5 million rows:
INSERT INTO #Objects ([Name])
 SELECT TOP (5000000) ao.[name]
 FROM   [master].[sys].[all_objects] ao
 CROSS JOIN [master].[sys].[all_columns] ac;

-- Create the index:
CREATE INDEX [IX_#Objects_Name] ON #Objects ([Name]) WITH (FILLFACTOR = 100);

-- Insert another 100k rows to fragment index and reduce accuracy of the statistics:
INSERT INTO #Objects ([Name])
 SELECT TOP (100000) ao.[name]
 FROM master.sys.all_objects ao
 CROSS JOIN master.sys.all_columns ac;

測試（和結果）

結果鍵：

• 設置 “(A)” = SQL Server 排序規則 ( SQL_Latin1_General_CP1_CI_AS)
• 設置 “(B)” = Windows 排序規則 ( Latin1_General_100_CI_AS_SC)
• 每個結果評論：{ before REBUILD} / { after REBUILD}
• “CS + CS” = 計算標量 + 恆定掃描

SET STATISTICS IO, TIME ON;
-- Total rows matching filter criteria: 2203

SELECT [ObjectID] FROM #Objects WHERE [Name] = 'objects';
-- (A) logical reads 13 (est. rows: 2125.67) / 9 (2203.15) Index Seek
-- (B) logical reads 13 (est. rows: 2019.74) / 9 (2203.25) Index Seek

SELECT [ObjectID] FROM #Objects WHERE [Name] = N'objects';
-- (A) logical reads 25159 (est. rows: 2125.67) / 23158 (2203.15) Index SCAN
-- (B) logical reads 13 (est. rows: 2019.74) / 9 (2203.25) Index Seek + CS + CS


SELECT [ObjectID] FROM #Objects WHERE [Name] = FORMAT(0, N'objects');
-- (A) logical reads 25159 (est. rows: 2433.23) / 23158 (2406.8) Index SCAN
-- (B) logical reads 13 (est. rows: 2307.69) / 9 (2208.75) Index Seek + CS + CS


SELECT [ObjectID] FROM #Objects WHERE [Name] =
                                     dbo.PassThrough_Deterministic(N'objects');
-- (A) logical reads 25159 (est. rows: 2125.67) / 23158 (2203.15) Index SCAN
-- (B) logical reads 13 (est. rows: 2019.74) / 9 (2203.25) Index Seek + CS + CS

SELECT [ObjectID] FROM #Objects WHERE [Name] =
                                     dbo.PassThrough_NonDeterministic(N'objects');
-- (A) logical reads 25159 (est. rows: 2433.23) / 23158 (2406.8) Index SCAN
-- (B) logical reads 13 (est. rows: 2307.69) / 9 (2208.75) Index Seek + CS + CS


SET STATISTICS IO, TIME OFF;


EXEC (N'USE [tempdb]; UPDATE STATISTICS #Objects [IX_#Objects_Name] WITH FULLSCAN;');

-- re-run tests

ALTER INDEX ALL ON #Objects REORGANIZE;

-- re-run tests

ALTER INDEX ALL ON #Objects REBUILD;

-- re-run tests

第二變體

1. 刪除表
2. 使用 Windows 排序規則重新創建表
3. 重新執行上面“測試”部分中的所有測試

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