[T-SQL Tip #01] Como filtrar colunas datetime?

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”.

Tabela com coluna datetime

Abaixo uma tabela modelada com coluna datetime.

Tabela: Sales.SalesOrderHeaderEnlarged

A tabela acima faz parte do banco de dados AdventureWorks e foi criada utilizando o script de enlarged para deixá-la com uma massa de dados maior. Abaixo link para download do script:
https://www.sqlskills.com/blogs/jonathan/enlarging-the-adventureworks-sample-databases/

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ã.

Dica: removendo arquivos do tempdb

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”.

https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql?view=sql-server-2017

Habilitando a Trace Flag 3608

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.

SQLServerConfigurationManagerStartupParameter

No campo Specify a startup parameter adicione o parâmetro -T3608 e clique no botão Add.

3608

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.

tempdb3608

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.

sysmasterfile3608

Feito isso agora iremos conseguir realizar a exclusão dos arquivos do tempdb com sucesso.

tempdb_removefile

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

tempdb2

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.

SQL Server + Docker – Provisionando uma Instância de Banco de Dados Rapidamente

Fala meu povo! Espero que todos estejam bem. 

Sabe quanto você quer testar um sistema, um código que desenvolveu ou até mesmo uma nova feature de um banco de dados, mas não quer instalar todo o SGBD? Quem já instalou alguns SGBDs (Sistemas Gerenciadores de Banco de Dados) sabe que alguns deles possuem setups extensos como Microsoft SQL Server e Oracle, por exemplo. 

Neste post trago uma alternativa extremamente rápida que você pode utilizar para provisionar uma instância SQL Server em poucos minutos. 

Para isso, antes de tudo, precisamos instalar o Docker, sistema gerenciador de container mais popular existente. Esse tutorial está sendo feito em cima de Linux, mais especificamente o Ubuntu 18.04: 

Instalação do Docker CE no Ubuntu

Agora vamos instalar o Docker Community Edition ou apenas Docker CE.  

1. Atualize o seu repositório apt

$ sudo apt-get update

2. Instale alguns pacotes para que o apt possa utilizar repositórios HTTPS

$ sudo apt-get install \
    apt-transport-https \
    ca-certificates \
    curl \
    gnupg-agent \
    software-properties-common

3. Adicione a chave GPG oficial do Docker:

$ curl -fsSL https://download.docker.com/linux/ubuntu/gpg | sudo apt-key add -

4. Verifique se a chave corresponde com o fingerprint 9DC8 5822 9FC7 DD38 854A E2D8 8D81 803C 0EBF CD88

5. Com o próximo comando adicionaremos o repositório da versão estável (stable) do Docker CE, para instalar versões que ainda estão em teste utilize o tutorial que deixaremos nos links finais do post. 

Como meu computador possui um sistema operacional 64 bits, utilizarei a versão x86_64, caso seu sistema operacional seja 32 bits, arm entre outros, utilize os repositórios existentes no link que irei deixar no final do post. 

$ sudo add-apt-repository \
   "deb [arch=amd64] https://download.docker.com/linux/ubuntu \
   $(lsb_release -cs) \
   stable"

Após feito os passos acima, vamos então instalar o Docker CE.  

6. Atualize o índice de pacotes do apt

$ sudo apt-get update

7. Agora sim! Vamos instalar a última versão do Docker CE e do containerd: 

$ sudo apt-get install docker-ce docker-ce-cli containerd.io

8. Verifique se a instalação funcionou executando o comando a seguir. Esse comando baixa uma imagem de container chamada Hello World e depois executa esta imagem: 

$ sudo docker run hello-world

A saída do comando acima deverá ser essa: 

Bom, agora que estamos com o Docker devidamente instalado, podemos partir para o provisionamento da instância SQL Server. O objetivo deste post é ser o mais simples possível, voltado para uma utilização controlada da instância de banco de dados, ou seja, um ambiente para testes e talvez, para alguns casos, desenvolvimento de aplicações. Futuramente farei outros posts voltados mais especificamente para ambientes que vão do desenvolvimento até ambientes produtivos altamente escaláveis.

Provisionamento da Instância SQL Server em container

Bora subir um SQL Server 2019 CTP 2.5 então.

1. Baixar a imagem do SQL Server do repositório da Microsoft: 

$ sudo docker pull mcr.microsoft.com/mssql/server:2019-CTP2.5-ubuntu

2. Baixou a imagem? Então agora é só executar um novo container que a magia vai acontecer: 

$ sudo docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=SENHAdoS@2019' \
   -p 1433:1433 --name sqldt \
   -d mcr.microsoft.com/mssql/server:2019-CTP2.5-ubuntu

O que temos no comando acima:

  • run: comando do docker para executar um novo container; 
  • -e 'ACCEPT_EULA=Y': aqui você está aceitando aquele contrato de termos de uso que aparece na instalação do SQL; 
  • -e 'SA_PASSWORD=SENHAdoS@2019': senha do usuário SA, precisa ser uma senha forte; 
  • -p 1433:1433: no comando -p você declara a porta em que o SQL Server ficará disponível, o primeiro número antes do : é a porta que ficará disponível para o host, ou seja, para sua máquina. O número depois do : é a porta que será aberta dentro do container. Calma que mais pra frente ficara mais claro; 
  • --name sqldt: aqui você coloca um nome para o container, é uma boa prática fazer isso pois além de ficar mais fácil de identificar o seu container, também facilita com que outros containers comuniquem com este container. Se você não declara um nome, o próprio Docker seta um nome para o seu container; 
  • -d: esse comando faz com que o container seja executado em backgroud, ou seja, você executa o comando, um hash é printado na saída do comando, esse hash é a identificação de execução do seu container; 
  • mcr.microsoft.com/mssql/server:2019-CTP2.5-ubuntu: aqui temos a imagem que baixamos anteriormente e que é utilizada para executar o container. 

Depois de executar o comando acima apenas apareceu um hash gigante, não é? Então, essa é a identificação de execução do container. Para verificar se o container está rodando, execute o comando abaixo: 

$ docker ps

A saída do comando é essa: 

Como podemos ver, o container está em execução. Sendo assim, vamos conectar na instância. 

Estou utilizando um Linux, logo, vou utilizar o Azure Data Studio para conectar no SQL Server: 

Se estiver tudo certo, o client irá conectar: 

Clique em New Query para que possamos executar algumas consultas interessantes, como:

SELECT @@VERSION

Com este comando podemos ver qual é a versão do SQL que estamos utilizando: 

Como podemos na imagem, o comando trouxe a versão do SQL que escolhemos (2019 – CTP2.5), a edição (Developer – Padrão, podemos especificar qual edição queremos antes de iniciar o container, é só utilizar a opção -e 'MSSQL_PID=Enterprise') e a versão do Linux, que no caso é o Ubuntu 16.04. 

Mas calma, eu havia dito no começo do post que a versão que estou executando no meu computador é a 18.04, então como o container está executando uma versão abaixo da minha? Essa é a magia dos containers. Os containers se beneficiam do LXC (Linux Containers), sistema que abstrai algumas camadas de acesso ao kernel (cgroups e namespace são algumas delas), tornando-o “compartilhado”, desta forma, possibilitando que outras distribuições de Linux usufruam do mesmo kernel do sistema operacional em execução no host. O Docker foi construído para facilitar a execução de containers no Linux e vem revolucionando a forma como fazemos aplicações atualmente. 

Não irei me estender muito sobre Docker neste post, mas deixarei links onde vocês poderão conhecer mais sobre Docker e sobre containers. 

Vamos agora criar um banco de dados e ver se tudo se comporta bem:

CREATE DATABASE test;

Aparentemente o banco de dados foi criado, mas será mesmo que foi? Vamos verificar: 

SELECT 
    name
    ,state_desc
    ,compatibility_level
FROM sys.databases 
WHERE name = 'test';

O banco de dados está lá conforme podemos ver. 

Bom pessoal, esse foi nosso post sobre SQL Server em containers Docker. Deixo alguns links uteis os quais me baseei para fazer este post e outros que utilizei para aprender mais sobre Docker. 

Links Úteis 

https://docs.docker.com/install/linux/docker-ce/ubuntu/