SQL Server – Dicas de Performance Tuning: Conversão implícita? NUNCA MAIS!

Fala pessoal!
Tudo na paz, né ?!

Nesse artigo eu gostaria de comentar sobre um problema de performance em consultas que encontramos bastante aqui no nosso dia a dia na Fabrício Lima – Soluções em BD, uma das melhores e mais reconhecidas empresas de Performance Tuning do Brasil. Estamos falando de algo que, muitas vezes, é terrivelmente simples de resolver e inexplicavelmente e extremamente comum, conversão implícita.

Para os exemplos desse artigo, vou utilizar a seguinte estrutura da tabela Pedidos:

O que é conversão implícita?

A conversão implícita ocorre quando o SQL Server precisa converter o valor de uma ou mais colunas ou variáveis para outro tipo de dado com a finalidade de possibilitar a comparação, concatenação ou outra operação com outras colunas ou variáveis, já que o SQL Server não consegue comparar uma coluna do tipo varchar com outra do tipo int, por exemplo, se ele não convertesse uma das colunas para que ambas tenham o mesmo tipo de dado.

Quando falamos de conversão, temos 2 tipos:

• Explícita: Ocorre quando o próprio desenvolvedor da consulta faz a conversão dos dados utilizando funções como CAST, CONVERT, etc..

  
• Implícita: Ocorre quando o SQL Server é forçado a converter o tipo de dado entre colunas ou variáveis internamente, pois eles foram declarados com tipos diferentes.

  

Como essa conversão é aplicada para todos os registros das colunas envolvidas, essa operação pode acabar sendo bem custosa e prejudicar bastante a execução da consulta, uma vez que mesmo que exista um índice para essas colunas, o otimizador de consultas acabará utilizando o operador Scan ao invés do Seek, não utilizando esse índice da melhor forma possível. Como consequência, o tempo de execução e a quantidade de leituras lógicas (logical reads) pode acabar aumentando bastante.

Qual o impacto da conversão na minha consulta?

Como eu comentei acima, quando é necessário comparar dados com tipos diferentes, ou o desenvolvedor da query precisa fazer a conversão explícita utilizando CAST/CONVERT, ou o SQL Server terá que fazer a conversão implícita internamente para equalizar os tipos de dados.

Mas será que isso realmente vai fazer alguma diferença significativa na minha consulta ? Vamos analisar..

Teste 1 – Utilizando o mesmo tipo de dado entre a coluna e a variável

Nesse primeiro teste, vamos utilizar a forma correta de se escrever as consultas. O tipo de dado da coluna (varchar) é o mesmo da variável literal (varchar) e não ocorre conversão de dados. Como resultado disso, a consulta vai ser executada em 0ms, com apenas 6 leituras lógicas no disco.

Teste 2 – Conversão implícita

No segundo teste, vou utilizar uma consulta simples, sem conversões de tipo de dados. Como a coluna Ds_Pedido é do tipo varchar e o valor literal 19870 é do tipo inteiro, o SQL Server terá que se encarregar de fazer a conversão (implícita) desses dados.

Teste 3 – Convertendo os tipos de dados (Conversão explícita)

Já nesse último teste, vou utilizar uma função de CAST para aplicar a conversão explícita e avaliar como a consulta de comportou em relação às outras duas consultas.

Conversão implícita só ocorre entre string e números?

Na verdade, não. Vou demonstrar alguns exemplos de conversão entre strings e strings, mas pode ocorrer entre strings e datas, uniqueidentifier e strings, etc.

VARCHAR e NVARCHAR

Um erro muito comum que vemos no dia a dia, é a ocorrência de conversão implícita entre valores/colunas varchar e nvarchar. Isso ocorre bastante em aplicações que utilizem ORM’s, como o Entity Framework.

Muitas pessoas acabam achando que não existe necessidade de conversão entre varchar e nvarchar, mas vamos observar no exemplo abaixo que isso ocorre sim:

Outro exemplo, utilizando uma variável do tipo NVARCHAR(10):

Conversão implícita não ocorre entre números com tipos diferentes?

Mais ou menos. Pode ocorrer a conversão implícita no operador Compute Scalar, mas não impede o uso do operador Seek. Pelo fato dos números serem todos da mesma família, o SQL Server consegue comparar nativamente números com tipos de dados diferentes, como INT vs BIGINT, ou BIGINT vs SMALLINT, por exemplo.

Após uma dica do grande José Diz, acabei me atentando ao fato que a ao utilizar algumas expressão com números de tipos de dados diferentes, não vemos um warning no plano de execução demonstrando a conversão implícita, e nem o operador Scan é utilizado ao invés do Seek, mas a conversão implícita ocorre sim, no operador Compute Scalar, como vou demonstrar abaixo:

Antes de analisar a conversão implícita, vamos criar um novo índice pra evitar esse operador de Key Lookup, numa técnica conhecida como “Cobrir o índice” (Covering index):

Analisando novamente o plano de execução, vemos que o operador Key Lookup saiu do plano, o índice novo está sendo utilizado com o operador Seek e não tem Warning de conversão implícita. Onde ela está ?

Ao visualizar as propriedades do operador Compute Scalar, conseguimos identificar que o SQL Server fez a conversão implícita para realizar a múltiplicação da coluna Valor (numeric) pela coluna Quantidade (int):

No artigo Implicit Conversions that cause Index Scans, do Jonathan Kehayias, ele fez uma série de testes entre vários tipos de dados e o resultado desse estudo é a tabela abaixo, que demonstra quais cruzamentos entre tipos de dados causam o evento de Scan ao invés do Seek ao serem comparados:

Na documentação oficial do SQL Server, podemos encontrar a tabela abaixo, que ilustra quais os cruzamentos entre tipos de dados que geram conversão implícita, quais precisam utilizar conversão explícita e quais conversões não são possíveis:

Ocorre conversão implícita em JOIN também?

Em qualquer operação ou comparação de expressão com tipos de dados diferentes, pode ocorrer a conversão implítica (de acordo com as regras vistas acima), seja no SELECT, WHERE, JOIN, CROSS APPLY, etc, conforme vou demonstrar abaixo.

Criei uma tabela chamada Pedidos2, com a mesma estrutura e dados da tabela Pedidos. Após isso, efetuei uma operação de ALTER TABLE para modificar o tipo de dado da coluna Ds_Pedido para NVARCHAR(10) e com isso, temos o seguinte exemplo:

Reparem que houve uma conversão implícita entre as colunas Ds_Pedido, já que na tabela Pedidos ela é do tipo VARCHAR e na tabela Pedidos2 ela é do tipo NVARCHAR. Por conta disso, ao invés de utilizar a operação de Index Seek, foi utilizada a Index Scan para ler os dados da tabela Pedidos.

Se analisarmos a quantidade de leituras das 2 tabelas, vemos uma diferença gritante por conta da conversão implícita:

Nesse caso, podemos observar um erro GRAVE de modelagem dos dados, que permitiu duas tabelas que tenham relacionamentos entre si utilizarem tipos de dados diferentes. Para resolver esse problema de performance nesse cenário, e obter o máximo de desempenho dessa consulta, vamos alterar o tipo da coluna Ds_Pedido na tabela Pedidos para nvarchar(10), o mesmo tipo da tabela Pedidos2, já que o filtro utilizado no WHERE é do tipo NVARCHAR:

Mas aí encontramos esse erro durante a tentativa de alterar o tipo de dado da coluna:

Msg 5074, Level 16, State 1, Line 8
The index ‘SK03_Pedidos’ is dependent on column ‘Ds_Pedido’.
Msg 4922, Level 16, State 9, Line 8
ALTER TABLE ALTER COLUMN Ds_Pedido failed because one or more objects access this column.

Nesse caso acima, vamos considerar que essa coluna ser do tipo VARCHAR foi um erro de modelagem e vamos corrigir o problema. Para isso, vou utilizar os comandos T-SQL abaixo, para apagar o índice, realizar a alteração do tipo e criar o índice novamente:

Após igualar o tipo dos dados entre as duas colunas, vamos repetir a consulta feita anteriormente e analisar os resultados:

Agora o plano de execução ficou excelente. Eliminamos a conversão implícita e estamos utilizando operadores Seek nos índices. Vamos ver como ficou o tempo de execução e as leituras lógicas:

Tempo de execução 0ms e apenas 3 leituras lógicas. Excelente!! Consulta otimizada.

Mas e nos casos onde não podemos realizar o comando ALTER TABLE devido à outros relacionamentos que já existem? Quais alternativas temos para isso ?
R: Existem várias soluções, mas uma que gosto muito é a utilização de colunas calculadas e indexadas, que possuem baixo impacto para a aplicação (embora possa ter impacto SIM, especialmente em operações de INSERT) e costumam ser bem eficazes e práticas, já que sempre que a coluna original for alterada, a coluna calculada é atualizada automaticamente também (assim como índices que referenciam a coluna calculada, se existirem). Mas lembrem-se: TESTEM ANTES DE IMPLEMENTAR!

Vou demonstrar como você pode implementar essa solução no exemplo acima:

Ao criar essa coluna calculada, ela não vai ocupar NADA de espaço no seu banco, pois ela é calculada em tempo real. Apenas o índice criado que irá ocupar espaço e ele que irá trazer o ganho de performance para essa solução:

E se analisarmos o nosso plano de execução utilizando a nova coluna calculada, vemos que ele está sem conversão implícita e fazendo operação de Seek, igualzinho quando eu demonstrei como seria se as colunas fossem do mesmo tipo:

Como o SQL Server escolhe qual tipo que será convertido?

Essa é uma excelente dúvida. Como podemos consultar da página Precedência de tipo de dados, pertencente à documentação da Microsoft, quando um operador combina duas expressões de tipos de dados diferentes, as regras para precedência de tipo de dados especificam que o tipo de dados com a precedência inferior é convertido para o tipo de dados com a precedência mais alta. Se a conversão não for uma conversão implícita com suporte, será retornado um erro.

O SQL Server usa a seguinte ordem de precedência para tipos de dados:

1. tipos de dados personalizados do usuário (maior nível)
2. sql_variant
3. xml
4. datetimeoffset
5. datetime2
6. datetime
7. smalldatetime
8. date
9. time
10. float
11. real
12. decimal
13. money
14. smallmoney
15. bigint
16. int
17. smallint
18. tinyint
19. bit
20. ntext
21. text
22. image
23. timestamp
24. uniqueidentifier
25. nvarchar (incluindo nvarchar(max) )
26. nchar
27. varchar (incluindo varchar(max) )
28. char
29. varbinary (incluindo varbinary(max) )
30. binary (menor nível)

Ou seja, por isso que no exemplo acima, quando comparamos uma coluna varchar com uma expressão nvarchar, a coluna é que foi convertida para nvarchar ao invés do inverso (que até fazia mais sentido converter um valor fixo do que uma coluna inteira).

Como identificar as conversões implícitas no seu ambiente

Como eu falei anteriormente, operações de conversão implícita são muito comuns em ambientes SQL Server e por isso, vou compartilhar duas formas de identificar a ocorrência desses eventos no seu ambiente.

Método 1 – DMV’s do Plan cache

Utilizando o script abaixo, você poderá identificar as consultas que mais consumiram CPU e possuem conversão implícita através de DMV’s do SQL Server. Não é necessário ativar nenhuma opção ou criar nenhum objeto, pois essas consultas são nativas e coletadas automaticamente, por padrão.

Resultado:

Método 2 – Extended Events (XE)

Utilizando o script abaixo, você poderá capturar os eventos de conversão implícitas gerados através de eventos do Extended Events.

A vantagem dessa solução sobre a consulta ao plancache, é que os dados ficam armazenados de forma definitiva, já que a plan cache é “truncada” sempre que o serviço do SQL Server é reiniciado, nem todas as consultas ficam armazenadas lá e quando são, o armazenamento é temporário. Além disso, se você iniciar o serviço utilizando o parâmetro -x, várias DMV’s, como a dm_exec_query_stats, não são populadas.

A desvantagem é que você precisa criar objetos no banco (Job, XE, tabela), gerando um trabalho bem maior para obter essa informação, que só será coletada a partir da criação desses controles. Os eventos que ocorreram no passado, não serão identificados.

Script do XE:

E após criar esse XE, você pode utilizar o script abaixo para coletar os dados e gravá-los numa tabela de histórico.

E agora é só criar 1 job para coletar esses dados de forma períodica. Para acessar os dados coletados, basta consultar a tabela recém criada para analisar as ocorrências de conversão implícitas no seu ambiente:

Outros artigos sobre Conversão Implícita

Quer outros pontos de vistas e exemplos sobre esse tema ? Vejam alguns artigos de outros autores que separei para vocês:

• https://portosql.wordpress.com/2018/10/25/os-perigos-da-conversao-implicita-1/
• https://portosql.wordpress.com/2018/11/28/os-perigos-da-conversao-implicita-2/
• https://sqlkiwi.blogspot.com/2011/07/join-performance-implicit-conversions-and-residuals.html
• https://www.sqlskills.com/blogs/jonathan/implicit-conversions-that-cause-index-scans/
• https://hackernoon.com/are-implicit-conversions-killing-your-sql-query-performance-70961e547f11
• https://docs.microsoft.com/en-us/sql/t-sql/data-types/data-type-conversion-database-engine?view=sql-server-2017

Bom pessoal, espero que tenham gostado desse post, entendido realmente os perigos da conversão explícita e implícita e não deixem isso acontecer mais nas suas consultas. O DBA agradece.
Forte abraço e até a próxima.