Fala pessoal tudo bem? Na semana passada eu postei o [T-SQL Challenge #01] que foi o primeiro post da série de desafios aqui do nosso blog. Eu estou muito feliz com a repercussão que essa série teve, pois houve bastante devolutiva sobre a resolução do desafio e todos acertaram, inclusive um amigo meu que é DBA Oracle.
Antes da resolução do problema, segue abaixo o nome de todos que colaboraram com a resolução do problema.
Fábio Martinho, Tiago Balabuch, Rodrigo Mateus, Sinval Pereira, Lincoln Borges, Bianca Castro, Logan Merazzi, Hugo Torralbo, Marcio Junior, Vitor Gonçalves, Iago Lourenço, Wolney Marconi e Paulo Roberto Correia.
Galera, muito obrigado pela colaboração e parabéns por terem acertado o desafio.
Resolução
A resolução para esse problema onde não podemos alterar a procedure é relativamente simples. Basta criarmos uma coluna computada com a expressão YEAR(OrderDate) na tabela Sales.SalesOrderHeaderEnlarged marcando-a com a opção PERSISTED e após isso indexarmos a coluna computada. Pronto! Problema resolvido.
ALTER TABLE Sales.SalesOrderHeaderEnlarged ADD YearOrderDate AS YEAR(OrderDate) PERSISTED
GO
CREATE INDEX IDX_YearOrderDate ON Sales.SalesOrderHeaderEnlarged(YearOrderDate) INCLUDE(CustomerID, OrderDate)
GO
O Query Optimizer do SQL Server identifica que há uma coluna computada e indexada com a expressão utilizada na procedure sp_getSalesOrder e utiliza a coluna computada e índice para criar o plano de execução e com isso fazer um Index Seek no índice criado.
Galera, isso é SENSACIONAL! Aplausos para o Query Optimizer.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
(7853 rows affected)
Table 'SalesOrderHeaderEnlarged'. Scan count 1, logical reads 29,
physical reads 0, read-ahead reads 0, lob logical reads 0,
lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 169 ms.
Como podemos notar, tivemos um ótimo ganho de performance. Na execução do desafio tivemos 3522 páginas lidas contra apenas 29 e 419 ms contra 169 ms.
Analisando de forma geral com o intuito de resolver o problema do Index Scan do desafio, podemos dizer que esso é um problema resolvido e que venha o próximo desafio.
Agora, imagine que esse seja um problema do seu mundo real. Ambiente de produção sendo altamente impactado por uma procedure executando uma função para tratar o ano, ou qualquer outro tipo de expressão que a torne uma procedure non-sarg.
Como forma PALIATIVA eu daria essa solução para o time de desenvolvimento com intuito de estabilizar o ambiente, porém eu iria salientar com todos os responsáveis pelo sistema que essa é uma medida PALIATIVA e que a forma DEFINITIVA de solucionar o problema é alterar o código da procedure aplicando as boas práticas para torná-la uma procedure com uma expressão sargable, pois uma coluna calculada persistida e indexada pode impactar em um maior tempo de escrita.
Caso vocês tenham alguma outra solução para esse tipo de problema, por favor, compartilhe conosco. Eu só conheço essa solução.
Pessoal, espero que tenham gostado do primeiro desafio. Mais uma vez nosso muito obrigado a todos que desprenderam seu tempo resolvendo a questão. É isso que nos motiva a escrever e compartilhar um pouco do nosso dia a dia.
Olá pessoal, No post anterior [T-SQL Tip #01] Como filtrar coluna datetime? demos início a série de dicas sobre T-SQL e Performance, onde eu explico um pouco sobre como devemos realizar filtros em coluna datetime para termos sempre a melhor performance possível e evitarmos futuros problemas.
Hoje iniciamos a série T-SQL Challenge onde postaremos alguns desafios de T-SQL baseado nos posts do T-SQL Tips. Essa série será sempre composta por dois posts onde o primeiro é o desafio e o segundo será a resolução do desafio. Caso o desafio seja resolvido por um leitor do blog, a resolução dada pelo leitor será postada no blog caso ele permita.
Baseado no problema discutido no [T-SQL Tip #01] vamos ao desafio.
Imagine que você tenha uma procedure que realiza filtro por uma coluna datetime, essa procedure está com uma performance degradada pois no plano de execução o operador de leitura do índice da tabela é um Index Scan. Você precisa resolver esse problema de performance, fazendo com que o operador de leitura do índice mude para um Index Seek, mas você não pode alterar o código da procedure. Como você resolveria esse problema?
Procedure de exemplo
Vamos criar uma procedure de exemplo onde o desenvolvedor quer retornar todos os registros de venda de um determinado ano, utilizando a função YEAR para facilitar o trabalho dele.
CREATE PROCEDURE dbo.sp_getSalesOrder
@YEAR INT
AS
BEGIN
SELECT SalesOrderID, CustomerID, OrderDate
FROM Sales.SalesOrderHeaderEnlarged
WHERE YEAR(OrderDate) = @YEAR
END
Sensacional essa abordagem acima, se não fosse a utilização da funçao YEAR em uma coluna que está indexada.
Abaixo o plano de execução e estatísticas de leitura e tempo.
Execution Plan – Procedure non-sarg
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
(7853 rows affected)
Table 'SalesOrderHeaderEnlarged'. Scan count 1, logical reads 3522, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row affected)
SQL Server Execution Times:
CPU time = 234 ms, elapsed time = 419 ms.
Como podemos notar o SQL Server fez um Index Scan no índice devido o uso da função YEAR e realizou 3522 logical reads com 419 ms de tempo.
Solução(alterando o código)
Em uma análise de performance de query eu sempre começo analisando o código da query em busca de um ponto de melhoria e somente depois eu parto para a criação/manutenção de índice e em alguns casos modelagem da tabela. No exemplo acima a resolução seria remover a função YEAR do where e fazer um filtro na coluna OrderDate que contemple todo o período do ano desejado. É uma resolução fácil, mas um pouco mais trabalhosa do que simplesmente fazer YEAR(OrderDate) = @YEAR. Veja uma possível solução abaixo.
CREATE PROCEDURE dbo.sp_getSalesOrder
@YEAR INT
AS
BEGIN
SELECT SalesOrderID, CustomerID, OrderDate
FROM Sales.SalesOrderHeaderEnlarged
WHERE OrderDate BETWEEN
CAST(@YEAR AS VARCHAR(4)) +'0101 00:00:00.000' AND
CAST(@YEAR AS VARCHAR(4)) +'1231 23:59:59.997'
END
DESAFIO
Levando em consideração que você NÃO PODE(em hipótese alguma) alterar a procedure como eu alterei acima, como você resolveria o problema do Index Scan causado pelo predicado YEAR(OrderDate) = @YEAR e faria essa procedure executar um Index Seek e ter uma performance aceitável?
Seguem as regras: 1. Não é permitido alterar a procedure; 2. Criar outra procedure não é uma solução válida; 3. É permitido alterar a tabela, exceto a coluna OrderDate; 4. É permitido criar/modificar índice;
Favor enviar as possíveis soluções para marcel@datatuning.com.br e se alguém me der uma solução eu publicarei os créditos no próximo post e PAGAREI um almoço no próximo SQL Saturday que nos encontrarmos.
Nesse primeiro post da série T-SQL Tips iremos abordar como realizar filtros em colunas do tipo datetime. Esse é um tema relativamente simples, porém pode causar muitos problemas de performance na execução das queries se não forem feitos da forma correta.
É muito comum o uso do campo datetime na modelagem das tabelas para armazenar registros com data e hora como “2019-01-01 23:59:59.997”, porém também é muito comum o desenvolvedor não querer utilizar a hora armazenada na coluna para realizar o filtro e para isso ele acaba realizando algum tipo de conversão para remover/truncar a hora da coluna em tempo de execução da query e esse é o principal ponto que iremos abordar, pois isso pode causar alguns problemas de performance.
Antes de ir para os exemplos, é necessário entender que qualquer tipo de conversão em uma coluna que é utilizada em where ou join, pode fazer com que o predicado de busca se torne um predicado non-sarg(non search argument) e com isso o SQL Server pode optar por um Index Scan ao invés de um Index Seek ,o que normalmente é o mais indicado para ler menos páginas do índice/tabela. O recomendado é que sempre tenhamos predicados de busca SARG(Search Argument) e para isso devemos evitar ao máximo as conversões ou uso de funções nas buscas. Falaremos um pouco mais sobre SARG/Nonsarg em um post futuro, mas durante os exemplos abaixo ficará mais claro o que é um predicado SARG e um Non-sarg e eu usarei esses termos para referenciar uma query “boa” de uma query “ruim”.
A tabela está com pouco mais de 1.290.000 de registros. Abaixo pequena amostragem de distribuição dos registros.
Amostra de dados
Para uma melhor performance eu criei um índice na tabela acima pela coluna OrderDate e adicionei a coluna CustomerID como include, pois serão essas colunas utilizadas nos exemplos.
CREATE NONCLUSTERED INDEX IX_SalesOrderHeaderEnlarged_OrderDate
ON Sales.SalesOrderHeaderEnlarged(OrderDate) INCLUDE(CustomerID)
Vamos aos exemplos
Filtrando registros de um dia específico
Já presenciei em muitos casos onde o desenvolvedor deseja filtrar os dados de um determinado dia e a coluna por estar armazenando a hora, o desenvolvedor resolveu o problema da seguinte maneira:
SET STATISTICS IO, TIME ON
SELECT SalesOrderID, CustomerID, OrderDate
FROM Sales.SalesOrderHeaderEnlarged
WHERE CONVERT(CHAR(8),OrderDate,112) = '20110531'
--Ou assim:
SET STATISTICS IO, TIME ON
SELECT SalesOrderID, CustomerID, OrderDate
FROM Sales.SalesOrderHeaderEnlarged
WHERE YEAR(OrderDate) = 2011
AND MONTH(OrderDate) = 5
AND DAY(OrderDate) = 31
OK! zero problemas com relação a essa abordagem, ela FUNCIONA mas qual o problema dela? Vamos analisar o plano de execução dessa query.
Execution plan – Queries non-sarg
Como podemos notar em ambos os casos o operador com maior custo foi um Index Scan no índice criado. Abaixo vamos ver como ficaram as estatísticas de leitura e tempo da query. Para isso eu utilizei SET STATISTICS IO, TIME ON antes da execução das queries.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
(41 rows affected)
Table 'SalesOrderHeaderEnlarged'. Scan count 1, logical reads 3522,
physical reads 0, read-ahead reads 0, lob logical reads 0,
lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 281 ms, elapsed time = 371 ms.
(41 rows affected)
Table 'SalesOrderHeaderEnlarged'. Scan count 1, logical reads 3522,
physical reads 0, read-ahead reads 0, lob logical reads 0,
lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 94 ms, elapsed time = 116 ms.
Como podemos ver em ambos os casos tivemos 3522 páginas lidas e tempos de 371ms e 116ms para apenas uma execução. Considerando que cada página de dados equivale a 8KB então tivemos 21,51MB de páginas lidas em cada execução.
Reescrevento a query e melhorando a performance
Conforme citamos acima, temos que evitar ao máximo a conversão da coluna que será utilizada como predicado de busca, para evitar que esse predicado se torne um predicado non-sarg, portanto o ideal é executarmos o filtro sempre respeitando o tipo do dado que foi definido para a coluna como no exemplo abaixo, onde eu farei a comparação de data acrescentando a hora das 00:00:00.000 até 23:59:59.997 para que eu possa retornar todos os registros do dia 31/05/2011.
SET STATISTICS IO, TIME ON
SELECT SalesOrderID, CustomerID, OrderDate
FROM Sales.SalesOrderHeaderEnlarged
WHERE OrderDate BETWEEN '20110531 00:00:00.000' AND '20110531 23:59:59.997'
Abaixo o plano de execução de estatísticas de leitura e tempo de uma execução.
Execution Plan – Query SARG
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
(41 rows affected)
Table 'SalesOrderHeaderEnlarged'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Analisando o plano de execução podemos notar que o operador de leitura do índice mudou para o Index Seek o que de cara pode me indicar uma performance melhor e a leitura de página caiu de 3522 para apenas 3 páginas lidas, ou seja, estamos falando de 27,51 MB contra apenas 24 KB e o tempo de 116 ms no melhor caso do exemplo anterior para 0 ms. Bom…116 ms no melhor caso para 0 ms fica claro que tivemos 100% de melhor no tempo de execução da query, mas vamos continuar com mais alguns exemplos e comparativos abaixo.
Filtrando registros de um determinado mês
Abaixo alguns exemplos de abordagens que eu presencio constantemente de como desenvolvedores normalmente fazem para filtrar os registros de um determinado mês.
SET STATISTICS IO, TIME ON
SELECT SalesOrderID, CustomerID, OrderDate
FROM Sales.SalesOrderHeaderEnlarged
WHERE CONVERT(CHAR(8),OrderDate,112) BETWEEN '20110701' and '20110731'
-- ou assim quando o dev não quer se preocupar com quantos dias tem o mês (y)
-- meu coração chega explode de alegria quando eu vejo isso!!!
SET STATISTICS IO, TIME ON
SELECT SalesOrderID, CustomerID, OrderDate
FROM Sales.SalesOrderHeaderEnlarged
WHERE LEFT(CONVERT(CHAR(8),OrderDate,112),6) = '201107'
Vejamos como ficaram os planos de execução e estatísticas de leitura e tempo dos exemplos acima.
Exectution Plan – Queries non-sarg
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
(609 rows affected)
Table 'SalesOrderHeaderEnlarged'. Scan count 1, logical reads 3522, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 719 ms, elapsed time = 746 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
(609 rows affected)
Table 'SalesOrderHeaderEnlarged'. Scan count 1, logical reads 3522, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 437 ms, elapsed time = 488 ms.
Mais uma vez como podemos ver o Index Scan foi o operador com o maior custo nos planos acima e a leitura de páginas assim como nos exemplos non-sarg anteriores foi de 3522 páginas lidas, ou seja, 27,52 MB de dados lidos da memória.
Reescrevento a query e melhorando a performance
Mais uma vez vamos reescrever a query utilizando todo o range possível de hora para filtramos todos os registros que estiverem em um determinado range de data.
SET STATISTICS IO, TIME ON
SELECT SalesOrderID, CustomerID, OrderDate
FROM Sales.SalesOrderHeaderEnlarged
WHERE OrderDate BETWEEN '20110701 00:00:00.000' AND '20110731 23:59:59.997'
Vejamos como ficaram os planos de execução e estatísticas de leitura e tempo dos exemplos acima.
Execution Plan – Query SARG
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
(609 rows affected)
Table 'SalesOrderHeaderEnlarged'. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Como podemos ver acima, mais uma vez o operador de leitura do índice mudou de Index Scan para Index Seek, o tempo de execução caiu de 488 ms no melhor caso para 0 ms e as leituras de páginas de 3522 páginas para apenas 6 páginas lidas(estamos lendo um mês inteiro, por isso o aumento é natural).
Estressando o ambiente
Vocês podem estar pensando: ok, mas 488 ms é um tempo baixo para a execução de uma query. Eu poderia dizer que sim é um tempo relativamente baixo para a execução de uma query, dependendo de sua importância dentro de um sistema. Agora imagine se essa query é responsável por capturar o valor das ações em tempo real da bovespa? Eu posso afirmar que 488 ms para o retorno do valor de uma ação é um problema para um sistema.
Abaixo vamos fazer uma pequena comparação de tempo utilizando o aplicativo SQL Query Stress com 100 execuções de cada query.
100 execuções da query non-sarg retornando dados de um mês
Query non-sarg
100 execuções da query Sarg retornando dados de um mês
Query sarg
Agora ficou claro, provar que uma query com tempo na casa dos 400 ms é um problema. Com apenas 100 execuções da query utilizando conversão levou-se 42 segundos, contra apenas 593 ms das mesmas 100 execuções da query escrita de forma correta.
Conclusão
Podemos ver que é muito mais fácil filtrar as datas utilizando a conversão para truncar a hora ou funções como DAY, MONTH e YEAR para não ter que se preocupar se o dia tem 28, 30 ou 31 dias, mas toda a facilidade tem seu preço.
Eu gosto sempre de pensar que não é porque eu posso fazer algo, que eu devo fazer algo e isso eu aplico nas minhas análises de performance do dia a dia.
Evitem ao máximo o uso das conversões ou funções para facilitar você chegar a um objetivo por mais cômodo/fácil que seja e tenham sempre em mente que:
O jeitinho/gambiarra de hoje será seu problema de amanhã.
Está publicado no Git da Data Tuning, os scripts do nosso segundo Meetup que foi realizado no último dia 27/06/2019. Fiquem a vontade para tirar dúvidas sobre a reprodução dos problemas discutidos no Meetup.
Recentemente ao realizar o acerto dos arquivos do tempdb em instâncias do SQL Server 2012 e superiores, me deparei com uma situação que ainda não tinha visto nas versões anteriores ao SQL Server 2012.
Vamos a simulação do problema!
Ao executar o comando abaixo:
ALTER DATABASE tempdb REMOVE FILE temp03
O erro abaixo é apresentado:
Msg 5042, Level 16, State 1, Line 2 The file ‘temp03’ cannot be removed because it is not empty.
Com a mensagem acima, executei o comando abaixo:
USE tempdb
GO
DBCC SHRINKFILE(temp03,EMPTYFILE)
DBCC SHRINKFILE: Page 3:32 could not be moved because it is a work table page.
Msg 2555, Level 16, State 1, Line 4 Cannot move all contents of file “temp03” to other places to complete the emptyfile operation.
E agora? O SQL Server informa que não é possível remover o arquivo do tempdb porque o mesmo não está vazio e ao tentar realizar o shrink o SQL informa que não consegue mover todo o conteúdo para outro arquivo. Confesso que a primeira vez que vi o erro fiquei confuso!
Abaixo, eu mostro como está a distribuição dos arquivos do tempdb da instância.
Trace Flag 3608
Para a simulação desse problema, estou utilizando o SQL Server 2017. Uma das formas que eu encontrei para contornar esse comportamento é utilizando a Trace Flag 3608. A TF 3608 faz com que não ocorra o start e recovery de nenhuma base de dados, exceto a master. Nessa situação as bases de dados só passarão pelo start e recovery ao serem acessadas e com isso é possível executar o comando de exclusão dos arquivos do tempb sem receber o erro “The file xxx cannot be removed because it is not empty”.
A primeira coisa a fazer é acessar o SQL Server Configuration Manager, ir nas propriedades do serviço do SQL Server e selecionar a aba Startup Parameter.
No campo Specify a startup parameter adicione o parâmetro -T3608 e clique no botão Add.
Feito isso, basta realizar stop/start do serviço do SQL Server e com o SQL Server reiniciado, podemos observar no ERRORLOG que a engine fez o recovery apenas na base de dados master.
Com isso podemos observar que ao listar os arquivos do tempdb novamente, apenas os arquivos padrão de quando o SQL Server foi instalado será listado.
Porém se listarmos os arquivos do tempdb pela sys.master_files iremos observar que os demais arquivos estão configurados, porém como o tempdb não foi realizado o recovery eles não estão em uso.
Feito isso agora iremos conseguir realizar a exclusão dos arquivos do tempdb com sucesso.
Agora iremos remover a TF 3608 e dar stop/start no serviço do SQL Server para verificarmos se a remoção do arquivo temp03 de fato ocorreu e analisar que sem a TF 3608 todas as bases são startadas e o recovery é executado após o restart do serviço.
tempdb files
ERRORLOG
ERRORLOG após remoção da TF 3608.
Bom pessoal, espero que isso possa ajudá-los no dia a dia. Remover arquivos do tempdb não é uma tarefa que se realiza com frequência nos ambientes, mas é importante sempre checar essas configurações para que o tempdb não esteja configurado de forma errada. Até a próxima.
