Por que o uso de Format vs Right para aplicar preenchimento faz com que o número estimado de linhas mude drasticamente?

FORMATretorna nvarcharque tem uma precedência de tipo de dados mais alta do que a coluna varchar comparada. Além da estimativa imprecisa da contagem de linhas, a conversão implícita da varcharcoluna comparada para nvarcharimpedirá que os índices dessa coluna sejam usados ​​com eficiência

Tente transmitir o FORMATresultado para varchar.

Há algumas coisas acontecendo aqui:

1. Desempenho diminuído (devido a leituras lógicas muito aumentadas)
2. Precisão diminuída do número estimado de linhas

Os fatores envolvidos são:

1. Coluna indexada VARCHAR, com um agrupamento do SQL Server, comparado aos NVARCHARdados (Observação: este cenário é específico do tipo de agrupamento: se o agrupamento fosse um agrupamento do Windows, não haveria degradação discernível de desempenho. Para obter detalhes, consulte " Impacto nos índices Ao misturar os tipos VARCHAR e NVARCHAR ")
2. Função interna ou SQLCLR não determinística, OU T-SQL UDF (marcado como determinístico ou não)
3. Índice fragmentado (eu pensei que eram apenas estatísticas obsoletas que eram o problema, mas atualizar estatísticas sozinhas, mesmo usando WITH FULLSCANnão tem efeito)

Os três fatores mencionados acima foram confirmados por meio de testes (veja abaixo). Dois dos três fatores são fáceis de corrigir:

1. Converta NVARCHARvalores para VARCHARse desejar usar uma VARCHARcoluna indexada OU altere o Collation da coluna para um Collation do Windows.
2. Faça um completo REBUILDdo(s) índice(s). Fazer um ALTER INDEX ... REORGANIZE;ou UPDATE STATISTICS ... WITH FULLSCAN;por si só não parece ajudar (pelo menos em termos de contagem de linhas estimadas).
3. (opcionalmente) Considere se uma alternativa determinística está disponível (por exemplo, se CASE / CONVERT+ RIGHTé mais eficiente que FORMAT, AND produz o mesmo resultado, então use CASE / CONVERT+ RIGHT; FORMATpode fazer algumas coisas bacanas, mas é desnecessário para preenchimento à esquerda).

Tenha também em mente as prioridades. Embora seja ideal ter contagens de linhas estimadas precisas, se elas estiverem próximas o suficiente, você ficará bem. Ou seja, não sinta a necessidade de fazer um trabalho extra para obter contagens de linhas estimadas super precisas se isso não proporcionar nenhum ganho real de desempenho (especialmente porque, dependendo do nível de fragmentação, a função não determinística às vezes tem um estimativa de linha mais precisa!). Por outro lado, alterar o tipo de dados (do valor que está sendo comparado) ou Collation vale o esforço, pois isso terá um impacto positivo perceptível. Então, fazer um REBUILDdo índice o aproximará o suficiente das contagens de linhas estimadas.

Método de teste

Eu testei isso preenchendo uma tabela temporária local com 5 milhões de linhas da coluna "name" de sys.all_objects(e usando um Collation of SQL_Latin1_General_CP1_CI_AS), criando um índice não clusterizado na coluna de string e adicionando outras 100k linhas para fragmentar o índice .

Eu filtrei um VARCHARliteral e, em seguida, o mesmo literal de string, mas prefixado com um "N" maiúsculo para torná-lo NVARCHAR. Isso isolou a questão do tipo de dados do valor de comparação.

Em seguida, filtrei o mesmo valor literal, mas envolto em uma chamada para FORMAT. Isso isolou a questão das funções não determinísticas.

Para confirmar o efeito comportamental do determinismo de função, criei duas funções SQLCLR que nada mais faziam do que retornar os valores passados, mas uma é determinística e a outra não. Isso deixa claro que a questão é determinismo e nada mais acontecendo com a função. Eu usei SQLCLR porque não parece haver uma maneira equivalente de fazer isso em T-SQL. Mesmo que a função seja marcada no sistema como sendo determinística (criando a UDF usando WITH SCHEMABINDING), o comportamento espelhará o de funções não determinísticas (eu testei isso, mas não o incluí abaixo).

Eu usei SET STATISTICS IO, TIME ON;e marquei a opção "Incluir Plano de Execução Real" no SSMS.

Depois de executar o primeiro conjunto de testes, executei:

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

e re-executou os testes. Melhoria mínima nas leituras lógicas e nenhuma alteração no número estimado de linhas.

Executei então:

ALTER INDEX ALL ON #Objects REORGANIZE;

e re-executou os testes. Nenhuma alteração no número estimado de linhas.

Executei então:

ALTER INDEX ALL ON #Objects REBUILD;

e , finalmente , vimos uma melhoria nas leituras lógicas e no número estimado de linhas.

Em seguida , larguei a tabela, recriei-a usando Latin1_General_100_CI_AS_SCcomo Collation e executei novamente os testes conforme descrito acima.

Código de teste

Código SQLCLR

O código a seguir foi usado para criar duas funções escalares que fazem exatamente a mesma coisa: simplesmente retornam o valor passado. A única diferença entre as duas funções é que uma está marcada como IsDeterministic = truee a outra está marcada como 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;
    }
}

Configuração de teste

-- 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;

Os testes (e resultados)

Chave de resultados:

• Definir "(A)" = Agrupamento do SQL Server ( SQL_Latin1_General_CP1_CI_AS)
• Definir "(B)" = Agrupamento do Windows ( Latin1_General_100_CI_AS_SC)
• Cada comentário de resultados: { antes REBUILD} / { depois REBUILD}
• "CS + CS" = Calcular Escalar + Varredura Constante

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

Segunda Variação

1. DROP tabela
2. recriar a tabela usando o Windows Collation
3. execute novamente todos os testes na seção "Os testes" acima

A seção de comentários para FORMAT (Transact-SQL) diz

A função FORMAT é não determinística.

Portanto, o planejador de consulta está confuso sobre o que esperar como resultado dessa função. Talvez o comportamento não determinístico até o impeça de aplicar algumas otimizações, como armazenar em cache resultados intermediários.