Erros ilógicos podem ser um fator do plano – SQLBlog

Erros ilógicos podem ser um fator do plano – SQLBlog

Erros ilógicos podem ser um fator do plano - SQLBlog
cupom com desconto - o melhor site de cupom de desconto cupomcomdesconto.com.br


Erros ilógicos podem ser um fator do plano - SQLBlog 1 Eu já falei sobre erros ilógicos antes. Em várias respostas sobre administradores de banco de dados (um, dois, três), mostro como você pode usar um CASE expressão ou TRY_CONVERT para solucionar um erro em que um valor não numérico, que deveria ter sido filtrado por uma cláusula de junção ou outra, ainda leva a um erro de conversão. Erland Sommarskog criou um item de Conexão mais de uma década atrás, ainda não endereçado, chamado “SQL Server não deve gerar erros ilógicos”.

Recentemente, tivemos um cenário em que uma consulta estava falhando em um servidor, mas não em outro. Mas isso foi um pouco diferente; não havia números envolvidos. Imagine este cenário: uma tabela de origem possui uma coluna que é varchar(20). Uma consulta cria uma variável de tabela com uma coluna que é varchar(10)e insere linhas da tabela de origem, com um filtro no lugar que expõe apenas valores com 10 caracteres ou menos.

Em muitos casos, é claro, esse cenário é perfeitamente adequado e tudo funciona como esperado.

CREATE TABLE dbo.src(i int, a varchar(20));
 
INSERT dbo.src VALUES(1,'aaaaa'),(2,'bbbbbbbbbbbbbbb');
 
DECLARE @t table(a varchar(10));
 
INSERT @t SELECT a FROM dbo.src WHERE i = 1;
 
DROP TABLE dbo.src;

Se você mudar isso para WHERE i = 2;, porém, você receberá esta * mensagem de erro:

Msg 2628, Nível 16, Estado 1
Os dados de sequência ou binários seriam truncados na tabela ‘tempdb.dbo. # A60B3C91’, coluna ‘a’. Valor truncado: ‘bbbbbbbbbb’.

Não há surpresas aqui; é assim que deve funcionar.

Um exemplo mais complexo

Quando o esquema e as consultas ficam mais complicadas, o plano se torna importante. Aqui está um cenário um pouco mais complicado, mas mantido o mais simples possível, para demonstrar o problema:

CREATE TABLE dbo.t1(id int NOT NULL, s varchar(5) NOT NULL);
CREATE TABLE dbo.t2(id int NOT NULL);
 
INSERT dbo.t1 (id, s) VALUES (1, 'l=3'), (2, 'len=5'), (3, 'l=3');
INSERT dbo.t2 (id)    VALUES (1), (3), (4), (5);
GO
 
DECLARE @t table(dest varchar(3) NOT NULL);
 
INSERT @t(dest) SELECT t1.s 
  FROM dbo.t1 
  INNER JOIN dbo.t2 ON t1.id = t2.id;
GO
 
DROP TABLE dbo.t1, dbo.t2;

Isso gera um erro semelhante ao que vimos anteriormente:

Leia Também  [Video] Assista ao trabalho de Brent no sp_Blitz

Msg 2628, Nível 16, Estado 1
Dados binários ou de string seriam truncados na tabela ‘tempdb.dbo. # AC65D70E’, coluna ‘dest’. Valor truncado: ‘len’.

Embora devamos ter recuperado apenas linhas com valores que cabem na coluna de destino (id é 1 ou 3, pois essas são as únicas duas linhas que correspondem aos critérios de junção), a mensagem de erro indica que a linha em que id também foi retornado 2, mesmo sabendo que não poderia ter sido. Vamos olhar para o estimado plano (com desculpas por essa terminologia a Grant, Hugo e Erin):

Plano derivado de Plano derivado de “Explain” no Azure Data Studio

Você pode ver o CONVERT_IMPLICIT lá, o que você acha que deveria ter tornado esse erro impossível. Você também pode ver que a correspondência de hash tem apenas duas linhas saindo, mas algo que falta nos diagramas é quantas linhas vão para dentro um operador e, nesse caso, a folha de propriedades do operador revela que a tabela de origem é usada para materializar o lado da construção. É aqui que o erro ocorre, porque a conversão implícita precisa ser avaliada.

Posso mostrar algumas maneiras pelas quais, sem alterar a consulta, podemos fazer o erro desaparecer (ou reaparecer).

Dados diferentes

Com apenas uma alteração nos dados de origem:

CREATE TABLE dbo.t1(id int NOT NULL, s varchar(5) NOT NULL);
CREATE TABLE dbo.t2(id int NOT NULL);
 
INSERT dbo.t1 (id, s) VALUES (1, 'l=3'), (2, 'len=5'), (3, 'l=3');
INSERT dbo.t2 (id)    VALUES (1), (3), (4);--, (5);
GO
 
DECLARE @t table(dest varchar(3) NOT NULL);
 
INSERT @t(dest) SELECT t1.s 
  FROM dbo.t1 
  INNER JOIN dbo.t2 ON t1.id = t2.id;
GO
 
DROP TABLE dbo.t1, dbo.t2;

Desta vez, a correspondência de hash não gera erroDesta vez, a correspondência de hash não gera erro

Ainda temos uma correspondência de hash, mas observe que o lado da construção e o lado do probe foram trocados, um efeito colateral de dados ligeiramente diferentes. Como a tabela de hash não precisa mais materializar o resultado da conversão implícita, nenhum erro ocorre.

Esquema diferente

Outra “solução” é adicionar um índice em cluster ao dbo.t2 (e volte a inserir as 4 linhas originais):

CREATE TABLE dbo.t1(id int NOT NULL, s varchar(5) NOT NULL);
CREATE TABLE dbo.t2(id int PRIMARY KEY CLUSTERED);
 
INSERT dbo.t1 (id, s) VALUES (1, 'l=3'), (2, 'len=5'), (3, 'l=3');
INSERT dbo.t2 (id)    VALUES (1), (3), (4), (5);
GO
 
DECLARE @t table(dest varchar(3) NOT NULL);
 
INSERT @t(dest) SELECT t1.s 
  FROM dbo.t1 
  INNER JOIN dbo.t2 ON t1.id = t2.id;
GO
 
DROP TABLE dbo.t1, dbo.t2;

Aqui está como o plano muda:

Leia Também  Comparação do controlador VMware LSI Logic SAS com o controlador PVSCSI |
cupom com desconto - o melhor site de cupom de desconto cupomcomdesconto.com.br

Agora obtemos uma junção de loops aninhados e nenhum erroAgora obtemos uma junção de loops aninhados e nenhum erro

o CONVERT_IMPLICIT ainda está lá no escalar de computação, mas a avaliação não é forçada antes que a linha ruim seja eliminada, porque a junção de loops aninhados não precisa materializar nada.

Consulta diferente

Você também pode alterar a consulta; por exemplo, você pode aplicar funções como LEFT, SUBSTRINGou um explícito CONVERT (ou CAST) para a coluna:

CREATE TABLE dbo.t1(id int NOT NULL, s varchar(5) NOT NULL);
CREATE TABLE dbo.t2(id int NOT NULL);
 
INSERT dbo.t1 (id, s) VALUES (1, 'l=3'), (2, 'len=5'), (3, 'l=3');
INSERT dbo.t2 (id)    VALUES (1), (3), (4), (5);
GO
 
DECLARE @t table(dest varchar(3) NOT NULL);
 
INSERT @t(dest) SELECT s = LEFT(t1.s, 3) -- or SUBSTRING(t1.s, 1, 3)
                                         -- or CONVERT(varchar(3), t1.s)
  FROM dbo.t1                            -- or CAST(t1.s AS varchar(3))
  INNER JOIN dbo.t2 ON t1.id = t2.id;
GO
 
DROP TABLE dbo.t1, dbo.t2;

A transformação subjacente aqui é implementada como um SUBSTRING, e isso fornece ao otimizador informações suficientes para saber que nenhum valor pode ter mais de três caracteres:

Nenhum erro ao aplicar LEFT ()Nenhum erro ao aplicar LEFT ()

Você também pode aplicar um WHERE cláusula para eliminar as linhas explicitamente. Isso funciona nesse caso específico, mas, como demonstrado nas postagens acima, isso nem sempre funciona (nem usa uma CTE ou tabela derivada):

CREATE TABLE dbo.t1(id int NOT NULL, s varchar(5) NOT NULL);
CREATE TABLE dbo.t2(id int NOT NULL);
 
INSERT dbo.t1 (id, s) VALUES (1, 'l=3'), (2, 'len=5'), (3, 'l=3');
INSERT dbo.t2 (id)    VALUES (1), (3), (4), (5);
GO
 
DECLARE @t table(dest varchar(3) NOT NULL);
 
INSERT @t(dest) SELECT t1.s
  FROM dbo.t1 
  INNER JOIN dbo.t2 ON t1.id = t2.id
  WHERE LEN(t1.s) <= 3;
GO
 
DROP TABLE dbo.t1, dbo.t2;

O plano é agora:

Nenhum erro com um filtro explícitoNenhum erro com um filtro explícito

Nesse caso, a linha incorreta é filtrada com antecedência suficiente para que se torne irrelevante a maneira como a correspondência de hash opera.

Você também pode tentar sugerir uma junção de loop para evitar a correspondência de hash e, provavelmente, meia dúzia de outras maneiras de alterar a consulta para forçar a filtragem anterior ou eliminar a materialização.

Leia Também  Estatísticas de atualização do SQL Server usando planos de manutenção de banco de dados

Observe que algumas dicas podem trazer o erro de volta. Por exemplo, neste caso, OPTION (FORCE ORDER) (ou qualquer dica que preserve a ordem) traz t1 de volta ao lado da compilação da combinação de hash e o erro retorna:

CREATE TABLE dbo.t1(id int NOT NULL, s varchar(5) NOT NULL);
CREATE TABLE dbo.t2(id int NOT NULL);
 
INSERT dbo.t1 (id, s) VALUES (1, 'l=3'), (2, 'len=5'), (3, 'l=3');
INSERT dbo.t2 (id)    VALUES (1), (3), (4), (5);
GO
 
DECLARE @t table(dest varchar(3) NOT NULL);
 
INSERT @t(dest) SELECT t1.s
  FROM dbo.t1 
  INNER JOIN dbo.t2 ON t1.id = t2.id
  OPTION (FORCE ORDER);
GO
 
DROP TABLE dbo.t1, dbo.t2;

Um grama de prevenção …

Uma solução mais lógica é alterar a variável da tabela para combinar tipos de dados ou, se dados mais longos não fizerem sentido na tabela de origem, conserte na fonte. Isso pode ter efeitos de gotejamento, mas, geralmente, o problema decorre da origem, permitindo valores muito grandes. Corrija a fonte e é improvável que declarações muito grandes em outros lugares causem problemas, a menos que sejam preenchidas por outras fontes.

Enquanto isso, verifique se não há dados errados que estejam causando explicitamente o erro, fazendo você seguir o seu caminho. Não seria a primeira vez que o código seria responsabilizado quando dados defeituosos (ou melhor, um esquema permissivo demais) estavam realmente com defeito.

Conclusão

Meu objetivo aqui não é explicar como solucionar o erro, é mais que você pode ou não ver o erro de truncamento, mesmo quando não faz sentido. E você pode ver o inverso acontecer – um código que funcione com facilidade pode ser interrompido repentinamente quando alguém adiciona dados, atualiza estatísticas, cria ou reconstrói um índice ou ocorre um failover ou a reinicialização do serviço.

O esquema e os dados subjacentes podem desempenhar um papel no plano que você obtém e outros fatores podem fazer com que você obtenha planos diferentes em servidores diferentes ou mesmo no mesmo servidor em momentos diferentes, mesmo quando o esquema e os dados são os mesmos .

Enorme grito para Paul White, cuja contribuição inestimável tornou esta postagem muito melhor do que poderia ter sido.



cupom com desconto - o melhor site de cupom de desconto cupomcomdesconto.com.br