主页 / dba / 问题 / 214098
Accepted
jmoreno
Asked: 2018-08-06 07:56:28 +0800 CST

为什么使用 Format vs Right 来应用填充会导致估计的行数发生显着变化？

  • 772

我在工作中处理一个查询，它有一个左连接

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

此查询的实际执行计划相当接近，估计为 269，实际为 475。

将右 +padding 更改为使用 format(colb, '000') 会导致对行数的巨大错误估计，至少减少 400 万，这会导致查询花费 10-15 倍的时间。

我明白为什么错误估计会导致问题，但我不明白为什么使用 Format 会导致估计不太准确。

sql-server performance

3 个回答

  1. FORMAT返回nvarchar具有比比较的 varchar 列更高的数据类型优先级。除了不精确的行计数估计，比较varchar列的隐式转换nvarchar将阻止该列上的索引得到有效使用

    尝试将FORMAT结果转换为varchar.

    • 4
  2. Best Answer

    这里发生了一些事情：

    1. 性能下降（由于逻辑读取大大增加）
    2. 估计行数的准确性降低

    涉及的因素有：

    1. VARCHAR与数据相比，具有 SQL Server 排序规则的索引列NVARCHAR（请注意：这种情况是特定于排序规则类型的：如果排序规则是 Windows 排序规则，则不会出现明显的性能下降。有关详细信息，请参阅“对索引的影响”混合 VARCHAR 和 NVARCHAR 类型时" )
    2. 非确定性内置函数或 SQLCLR 函数，或 T-SQL UDF（无论是否标记为确定性）
    3. 碎片化索引（我原以为这只是陈旧的统计数据才是问题所在，但单独更新统计数据，即使使用WITH FULLSCAN也没有效果）

    上述三个因素已通过测试得到证实（见下文）。三个因素中的两个很容易纠正：

    1. 如果要使用索引列，则将NVARCHAR值转换为，或者将列的排序规则更改为 Windows 排序规则。VARCHARVARCHAR
    2. 做一个完整REBUILD的索引。自己做ALTER INDEX ... REORGANIZE;orUPDATE STATISTICS ... WITH FULLSCAN;似乎没有帮助（至少在估计的行数方面）。
    3. （可选）考虑是否有确定性替代方案可用（例如，如果CASE / CONVERT+RIGHT比 更有效FORMAT，并且产生相同的结果，那么一定要使用CASE / CONVERT+ RIGHT；FORMAT可以做一些漂亮的事情，但对于左填充是不必要的）。

    还要记住优先事项。虽然拥有准确的估计行数是理想的，但如果它们足够接近就没问题。意思是，如果这样做不会带来任何真正的性能提升，那么不要觉得需要做额外的工作来获得超准确的估计行数（特别是因为，根据碎片级别，非确定性函数有时有一个更准确的行估计！）。另一方面，更改数据类型（被比较的值）或排序规则是值得的，因为这将产生显着的积极影响。然后，执行REBUILD索引将使您足够接近估计的行数。

    测试方法

    我通过填充一个本地临时表来测试这个，其中包含 500 万行的“名称”列sys.all_objects（并使用排序规则SQL_Latin1_General_CP1_CI_AS），然后在字符串列上创建一个非聚集索引，然后添加另外 10 万行来分段索引.

    我过滤了一个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)
    • 每个结果评论：{之前REBUILD} / {之后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 Collat​​ion 重新创建表
    3. 重新运行上面“测试”部分中的所有测试
    • 2

  3. FORMAT (Transact-SQL)的备注部分说

    FORMAT 函数是不确定的。

    因此，查询规划器对该函数的预期结果感到困惑。也许不确定的行为甚至会阻止它应用一些优化，比如缓存中间结果。

    • 1

相关问题