- SQL Server – IntroduĂ§Ă£o ao estudo de Performance Tuning
- Entendendo o funcionamento dos Ăndices no SQL Server
- SQL Server – Como identificar uma query lenta ou “pesada” no seu banco de dados
- SQL Server – Dicas de Performance Tuning: ConversĂ£o implĂcita? NUNCA MAIS!
- SQL Server – ComparaĂ§Ă£o de performance entre Scalar Function e CLR Scalar Function
- SQL Server – Dicas de Performance Tuning: Qual a diferença entre Seek Predicate e Predicate?
- SQL Server – Utilizando colunas calculadas (ou colunas computadas) para Performance Tuning
- SQL Server – Como identificar e coletar informações de consultas demoradas utilizando Extended Events (XE)
- SQL Server – Como identificar todos os Ăndices ausentes (Missing indexes) de um banco de dados
- SQL Server e Azure SQL Database: Como Identificar ocorrĂªncias de Key Lookup atravĂ©s da plancache
Fala galera!!!
Neste artigo, eu gostaria de compartilhar com vocĂªs algo que vejo bastante no dia a dia quando estou realizando consultoria de Tuning, que sĂ£o consultas demoradas, com alto consumo de I/O e CPU, e que utilizam funções no WHERE ou JOIN em tabelas com muitos registros e como podemos utilizar uma tĂ©cnica bem simples de indexaĂ§Ă£o de coluna calculada (ou computada) para resolver esse problema.
Conforme eu comento no artigo Entendendo o funcionamento dos Ăndices no SQL Server, ao utilizar funções em clĂ¡usulas WHERE ou JOINS, nĂ³s estamos ferindo o conceito de SARGability da consulta, ou seja, estamos fazendo com que essa consulta passe a nĂ£o utilizar operações de Seek nos Ăndices, uma vez que o SQL Server precisa ler toda a tabela, aplicar a funĂ§Ă£o desejada para depois, comparar os valores e retornar os resultados.
O que eu quero nesse artigo, Ă© demonstrar a vocĂªs esse cenĂ¡rio acontecendo, como identificar isso e algumas possĂveis soluções para melhorar a performance das consultas. EntĂ£o, vamos lĂ¡!
Criando a base de demonstraĂ§Ă£o desse artigo
Para criar essa tabela de exemplo parecida com a minha (os dados sĂ£o aleatĂ³rios, nĂ©.. rs), para conseguir acompanhar o artigo e simular esses cenĂ¡rios, vocĂª pode utilizar o script abaixo:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
IF (OBJECT_ID('_Clientes') IS NOT NULL) DROP TABLE _Clientes CREATE TABLE _Clientes ( Id_Cliente INT IDENTITY(1,1), Dados_Serializados VARCHAR(MAX) ) INSERT INTO _Clientes ( Dados_Serializados ) SELECT CONVERT(VARCHAR(19), DATEADD(SECOND, (ABS(CHECKSUM(PWDENCRYPT(N''))) / 2147483647.0) * 199999999, '2015-01-01'), 121) + '|' + CONVERT(VARCHAR(20), CONVERT(INT, (ABS(CHECKSUM(PWDENCRYPT(N''))) / 2147483647.0) * 9)) + '|' + CONVERT(VARCHAR(20), CONVERT(INT, (ABS(CHECKSUM(PWDENCRYPT(N''))) / 2147483647.0) * 10)) + '|' + CONVERT(VARCHAR(20), CONVERT(INT, 0.459485495 * (ABS(CHECKSUM(PWDENCRYPT(N''))) / 2147483647.0)) * 1999) GO 10000 INSERT INTO _Clientes ( Dados_Serializados ) SELECT Dados_Serializados FROM _Clientes GO 9 CREATE CLUSTERED INDEX SK01_Pedidos ON _Clientes(Id_Cliente) CREATE NONCLUSTERED INDEX SK02_Pedidos ON _Clientes(Dados_Serializados) GO |
DemonstraĂ§Ă£o utilizando funĂ§Ă£o nativa
Para demonstrar como uma consulta pode ficar lenta simplesmente pelo fato de utilizar funĂ§Ă£o no WHERE ou JOIN, vou utilizar a query abaixo inicialmente:
1 2 3 |
SELECT * FROM _Clientes WHERE Dados_Serializados = '2016-11-22 04:49:06|2|0|0' |
Se analisarmos o plano de execuĂ§Ă£o dessa consulta, vemos que ela estĂ¡ utilizando o operador Index Seek, fazendo apenas 6 leituras e 512 registros. Analisando as informações de CPU e IO, podemos concluir que 3ms de CPU (compile) e 3ms de tempo de execuĂ§Ă£o estĂ£o bem aceitĂ¡veis:
Agora vamos utilizar uma funĂ§Ă£o nessa mesma consulta:
1 2 3 |
SELECT * FROM _Clientes WHERE SUBSTRING(Dados_Serializados, 1, 10) = '2016-11-22' |
Ou seja, o resultado ficou pĂ©ssimo.. Index Scan, tempo de cpu alto, tempo de execuĂ§Ă£o alto, muitas leituras lĂ³gicas. Tudo isso por causa da funĂ§Ă£o utilizada, que deixou de utilizar o operador Index Seek e passou a fazer Index Scan.
Para resolver isso, Ă© bem simples, especialmente porque essa funĂ§Ă£o do jeito que estĂ¡ montada (igual a um LEFT), estĂ¡ nos ajudando, pois nesses casos, nĂ³s podemos substituir a funĂ§Ă£o pelo LIKE ‘texto%’ tranquilamente, pois o SQL Server irĂ¡ utilizar a operaĂ§Ă£o de Seek no Ăndice:
1 2 3 |
SELECT * FROM _Clientes WHERE Dados_Serializados LIKE '2016-11-22%' |
Podemos notar que ao utilizar o LIKE ‘texto%’, o Ăndice foi utilizado com a operaĂ§Ă£o Seek, fazendo com que a nossa consulta volte a ser performĂ¡tica.
LIKE e o SARGability
IMPORTANTE: Ao contrĂ¡rio do LIKE ‘texto%’, se vocĂª adicionar o sĂmbolo de ‘%’ antes do texto, para filtrar tudo que contenha ou termine com uma determinada expressĂ£o, o Ăndice nĂ£o serĂ¡ utilizado com o operador Seek, e sim o Scan.
Para entender o motivo disso, faça uma analogia com um Ăndice de um dicionĂ¡rio: Para achar todas as palavras do dicionĂ¡rio que começam com ‘test’ Ă© muito simples, basta ir na letra T, depois na letra ‘e’, depois na letra ‘s’ e por aĂ vai atĂ© encontrar as palavras desejadas.. Quando a prĂ³xima palavra da lista for maior que ‘test’, podemos encerrar a busca.
JĂ¡ para identificar todas as palavras do dicionĂ¡rio que contenham a palavra ‘test’ ou terminem com ‘test’, teremos que olhar todas as palavras do dicionĂ¡rio para conseguir identificĂ¡-las.
Facilitou o entendimento de como os Ăndices funcionam? Se ainda tiver dĂºvidas, leia o meu artigo Entendendo o funcionamento dos Ăndices no SQL Server.
Mas e se fosse a funĂ§Ă£o RIGHT, por exemplo? SerĂ¡ que a nossa consulta nĂ£o vai utilizar a operaĂ§Ă£o de Seek no Ăndice mesmo?
Como vimos acima, a consulta ficou bem ruim, com alto nĂºmero de logical reads, tempo de execuĂ§Ă£o e de CPU. Para resolver esse problema, vamos utilizar o recurso de coluna calculada e indexando essa coluna calculada:
1 2 3 4 5 6 7 8 9 10 11 12 |
-- Cria a nova coluna calculada ALTER TABLE _Clientes ADD Right_5 AS (RIGHT(Dados_Serializados, 5)) GO -- Cria um Ăndice para a nova coluna criada CREATE NONCLUSTERED INDEX SK03_Clientes ON dbo._Clientes(Right_5) GO -- Executa a consulta nova SELECT Right_5 FROM _Clientes WHERE Right_5 = '1|4|0' |
Wow! A consulta ficou bem mais rĂ¡pida agora! Isso acontece porque na criaĂ§Ă£o do Ăndice, ele jĂ¡ calculou esses dados para a coluna toda e os deixou ordenados. Com isso, as consultas ficam muito mais rĂ¡pidas que ter que calcular isso em tempo real para depois comparar os valores.
Considerações sobre criaĂ§Ă£o de Ăndices e determinismo de funções de sistema
ObservaĂ§Ă£o 1: Fique atento ao fato que a criaĂ§Ă£o do Ăndice vai consumir espaço em disco e incluir uma coluna em uma tabela, mesmo que calculada, deve ser testada antes para garantir que isso nĂ£o vĂ¡ gerar nenhum erro durante uma operaĂ§Ă£o de insert que nĂ£o esteja especificando os campos, por exemplo.
ObservaĂ§Ă£o 2: Um ponto muito importante a se destacar, Ă© que criar colunas calculadas persistidas em disco e indexar colunas calculadas sĂ³ Ă© possĂvel ao se utilizar funĂ§Ă£o determinĂsticas.
Todas as funções que existem no SQL Server sĂ£o determinĂsticas ou nĂ£o determinĂsticas. O determinismo de uma funĂ§Ă£o Ă© definido pelos dados retornados pela funĂ§Ă£o. O seguinte descreve o determinismo de uma funĂ§Ă£o:
- Uma funĂ§Ă£o Ă© considerada determinĂstica se sempre retorna o mesmo conjunto de resultados quando Ă© chamado com o mesmo conjunto de valores de entrada.
- Uma funĂ§Ă£o Ă© considerada nĂ£o determinĂstica se nĂ£o retornar o mesmo conjunto de resultados quando for chamada com o mesmo conjunto de valores de entrada.
Isso pode soar um pouco complicado, mas na verdade nĂ£o Ă©. Veja, por exemplo, as funções DATEDIFF e GETDATE. DATEDIFF Ă© determinĂstico porque sempre retornarĂ¡ os mesmos dados sempre que for executado com os mesmos parĂ¢metros de entrada. O GETDATE nĂ£o Ă© determinĂstico porque nunca retornarĂ¡ a mesma data toda vez que for executado.
DemonstraĂ§Ă£o utilizando funĂ§Ă£o definida pelo usuĂ¡rio (UDF)
Se utilizando funĂ§Ă£o nativa no WHERE/JOIN jĂ¡ piora a performance das nossas consultas, utilizando funĂ§Ă£o personalizada do usuĂ¡rio o cenĂ¡rio Ă© ainda pior. Para esse post, vou utilizar a funĂ§Ă£o fncSplit (com schema binding):
1 2 3 |
SELECT Dados_Serializados FROM _Clientes WHERE dbo.fncSplit(Dados_Serializados, '|', 3) = '1' |
Como vocĂª pode observar, essa consulta simples, numa tabela de 10.000 registros, demorou cerca de 35 segundos para ser executada, consumindo quase 15s de CPU. Foram feitas cerca de 240 mil leituras lĂ³gicas, sendo processadas 610 mil linhas para retornar as 1.040 linhas do resultado final. Resumo: TĂ¡ muito ruim!
Para tentar melhorar a performance dessa consulta, vamos utilizar a mesma soluĂ§Ă£o do exemplo anterior, criando uma coluna calculada e indexando essa coluna:
1 2 3 4 5 6 7 8 9 10 11 12 |
-- Cria a nova coluna calculada ALTER TABLE _Clientes ADD Coluna_Teste AS (dbo.fncSplit(Dados_Serializados, '|', 3)) GO -- Cria um Ăndice para a nova coluna criada CREATE NONCLUSTERED INDEX SK04_Clientes ON dbo._Clientes(Coluna_Teste) INCLUDE(Dados_Serializados) GO -- Executa a consulta nova SELECT Dados_Serializados FROM _Clientes WHERE Coluna_Teste = '1' |
Antes de analisar a execuĂ§Ă£o deste teste, preciso fazer um alerta sobre a criaĂ§Ă£o de coluna calculada persistida em disco utilizando funĂ§Ă£o definida pelo usuĂ¡rio (UDF) e criaĂ§Ă£o de Ăndices nessas colunas calculadas:
Determinismo de funĂ§Ă£o definida pelo usuĂ¡rio (UDF)
Importante: Quando vocĂª cria uma funĂ§Ă£o definida pelo usuĂ¡rio (UDF), o SQL Server registra o determinismo. O determinismo de uma funĂ§Ă£o definida pelo usuĂ¡rio Ă© determinado em como vocĂª cria a funĂ§Ă£o. Uma funĂ§Ă£o definida pelo usuĂ¡rio Ă© considerada determinĂstica se todos os critĂ©rios a seguir forem atendidos:
- A funĂ§Ă£o Ă© vinculada ao esquema (schema-bound) para todos os objetos de banco de dados aos quais faz referĂªncia.
- Qualquer funĂ§Ă£o chamada pela funĂ§Ă£o definida pelo usuĂ¡rio Ă© determinĂstica. Isso inclui todas as funções definidas pelo usuĂ¡rio e do sistema.
- A funĂ§Ă£o nĂ£o faz referĂªncia a nenhum objeto de banco de dados que esteja fora de seu escopo. Isso significa que a funĂ§Ă£o nĂ£o pode referenciar tabelas externas, variĂ¡veis ​​ou cursores.
Quando vocĂª cria uma funĂ§Ă£o, o SQL Server aplica todos esses critĂ©rios para a funĂ§Ă£o para determinar seu determinismo. Se uma funĂ§Ă£o nĂ£o passar em nenhuma dessas verificações, a funĂ§Ă£o serĂ¡ marcada como nĂ£o determinĂstica. Ă€s vezes, essas verificações podem produzir funções marcadas como nĂ£o determinĂsticas, mesmo quando vocĂª espera que elas sejam marcadas como determinĂsticas.
No caso deste exemplo, caso eu nĂ£o inclua o parĂ¢metro WITH SCHEMABINDING na declaraĂ§Ă£o da fncSplit, iremos nos deparar com a seguinte mensagem de erro:
Column ‘Coluna_Teste’ in table ‘dbo._Clientes’ cannot be used in an index or statistics or as a partition key because it is non-deterministic.
Wow!! De 32 segundos nossa consulta caiu para 8ms!! A quantidade de CPU caiu de 14.974 para 8 e a quantidade logical reads caiu de 240.061 para 9! Esse tuning aĂ foi realmente bem efetivo. Aposto que se vocĂª fizer algo parecido em um cliente, vocĂª receberĂ¡ belos elogios đŸ™‚
Antes de finalizar este artigo, gostaria de deixar um Ăºltimo recado para vocĂªs:
Como sempre falo: Ao aplicar técnicas de performance tuning, SEMPRE TESTE!
Bom pessoal, espero que vocĂªs tenham gostado desse artigo.
Um grande abraço e até mais!