Arquivo da categoria: Piores Práticas

DATEDIFF, CONVERT, SUBSTRING e funções sobre colunas na cláusula WHERE e JOINs

Boa Noite,

Já há muito tempo vejo uma prática recorrente na elaboração, manutenção e otimização de consultas presente em fóruns, aulas, softwares de terceiros enfim… Trata-se do uso de funções sobre colunas em cláusula WHERE que normalmente leva a consulta com desempenho abaixo do esperado. Para ilustrar, imaginemos a seguinte pergunta que demanda um instrução SELECT.

– Quais são os clientes que foram cadastrados há menos de trinta dias ?

Por intuição e tradução literal da pergunta para uma cláusula SQL, é bem comum que essa pergunta seja respondida com a seguinte consulta:

SELECT * FROM Clientes WHERE DateDIFF(dd,DataCadastro,GETDATE()) < 30

Se executássemos essa hipotética consulta, ela certamente retornaria os dados necessários. A função DATEDIFF calculará a diferença entre a data de cadastro e a data atual e aqueles registros que tiverem menos de trinta dias de diferença irão ser retornados. Entretanto, retornar pode ser suficiente para aplicações com poucos usuários e tabelas com pouco volume, mas aplicações críticas e alta concorrência, não basta “retornar”. É preciso fazer isso com eficiência e a construção apresentada não tem essa característica. Vejamos o porquê.

O uso do DATEDIFF

O uso do DATEDIFF é muito comum para calcular diferenças entre datas e não há nada errado em necessitar do resultado desse cálculo. Podemos calcular a diferença entre a data em que um determinado pedido foi feito e sua entrega, a diferença em horas que um empregado começou a trabalhar e encerrou o seu expediente ou ainda a diferença em segundos do início e do término de um treino de um maratonista. Ainda assim, há situações em que seu uso é indevido, especialmente em algumas situações na cláusula WHERE.

— Cria uma tabela com 2.000.000 registros
CREATE TABLE Clientes (
    ID INT NOT NULL,
    Nome VARCHAR(100) NOT NULL,
    Sexo CHAR(1) NOT NULL,
    DataNascimento DATETIME NOT NULL,
    CPF CHAR(11) NOT NULL,
    Cidade VARCHAR(100) NOT NULL,
    UF CHAR(2) NOT NULL,
    CEP CHAR(9) NOT NULL,
    Renda MONEY NOT NULL,
    DataCadastro DATETIME NOT NULL)

DECLARE @i INT
SET @i = 1

WHILE (@i < 2000000)
BEGIN
    INSERT INTO Clientes VALUES (
        @i,‘João da Cunha Costa e Silva Mendes’,‘M’,
        ‘19780101’,‘79985421240’,‘Brasília’,‘DF’,
        ‘70360-123’,4570.32,‘20110106’)
    SET @i = @i + 1
END

— Insere o cliente 2.000.000
INSERT INTO Clientes VALUES (
    2000000,‘Flávia Gomides Arnaldo Ferreira Costa’,‘F’,
    ‘19790331’,‘70034267890’,‘Brasília’,‘DF’,
    ‘70231030’,7832.46,GETDATE())

— Cria um índice sobre a data de cadastro
CREATE INDEX IX_DataCadastro ON Clientes (DataCadastro)

Esse script cadastrou 2.000.000 de clientes sendo que apenas um desses clientes foi cadastrado no dia de hoje. Se fosse perguntado quais os clientes que foram cadastrados a menos de 30 dias, esse seria o único registro retornado. Vejamos então o desempenho da consulta SQL sugerida anteriormente.

SET STATISTICS IO ON
SELECT * FROM Clientes WHERE DateDIFF(dd,DataCadastro,GETDATE()) < 30

(1 row(s) affected)
Table ‘Clientes’. Scan count 1, logical reads 28170, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Desempenho - Consulta com DATEDIFF

Como podemos perceber, para a recuperação do único registro que foi cadastrado a menos de trinta dias, foi necessário ler 28170 blocos o que em outras palavras nos levou a um TABLE SCAN inevitável. À primeira vista, alguns irão perguntar porque ler todos esses registros se existia um índice sobre a coluna Data ? Não seria melhor usar o índice para localizar o registro ao invés de fazer um SCAN nessa tabela ? Com certeza ler alguns poucos blocos seria muito melhor do que ler 28170 blocos especialmente se houvesse muita concorrência nessa tabela, mas se há um índice porque ele não foi usado ?

O índice IX_DataCadastro foi criado sobre a coluna DataCadastro produzindo uma lista ordernada por essa coluna muito semelhante à tabela abaixo:

DataCadastro ID da Linha Hash
2011-01-06 00:00:00.000 0x002A000001000000 (048da76b633b)
2011-01-06 00:00:00.000 0x002A000001000100 (6fa7aeb4ed68)
2011-01-06 00:00:00.000 0x002A000001000200 (393826e39435)
2011-07-02 11:30:25.390 0x7491000001000000 (3782542520bc)

Como o índice é sobre a coluna DataCadastro, todos os valores possíveis de DataCadastro virão ordenados e os respectivos ponteiros (Id da linha) são referenciados logo em seguida. Podemos ver que a data 06/01/2011 vem repetida diversas vezes, pois, temos quase 2.000.000 de ocorrências para esse valor. A última data 02/07/2011 (que foi o GETDATE() quando escrevi esse artigo) aparece apenas uma única vez.

A pesquisa pelo único registro que foi cadastrado a menos de trinta dias (2011-07-02) deveria pesquisar essa lista, encontrar o última entrada e com base no RowID (Id da linha) localizar o registro correspondente (Flávia Gomides). Entretanto, se observarmos a cláusula WHERE “DateDIFF(dd,DataCadastro,GETDATE()) < 30” podemos observar que nossa pesquisa não refere-se exatamente a DataCadastro, mas sim ao resultado de uma função aplicada sobre a Data de Cadastro. Nesse caso, nossa lista é praticamente inútil, pois, ela é baseada na DataCadastro e não no resultado da função DateDIFF. Teríamos aqui duas possibilidades. A primeira é converter toda a lista e fazer o cálculo produzindo uma nova lista ou simplesmente ignorar a lista e fazer a varredura de tabela. O otimizador optou por ignorar a lista, pois, possivelmente o trabalho de conversão é mais dispendioso que efetuar o TABLE SCAN. Há dois pontos de perda de desempenho aqui. O primeiro é que a lista foi ignorada e um SCAN foi realizado. O segundo é que temos uma lista que precisa ser mantida a cada INSERT, UPDATE (na DataCadastro) e DELETE. Resumidamente, temos um lista que tem um ônus para ser mantida, mas que não trás nenhum benefício para a consulta.

Se for observada a natureza da consulta, é perfeitamente possível reescrevê-la para usufruir da lista (índice) que foi criado sendo muito mais eficiente. O que se deseja saber é que clientes foram cadastrados a menos de trinta dias. Ao invés de aplicarmos uma função sobre a data e assim desprezarmos a lista, podemos simplesmente calcular a data de trinta dias atrás e fazer uma comparação da DataCadastro com esse valor.

SELECT * FROM Clientes WHERE DateDIFF(dd,DataCadastro,GETDATE()) < 30
SELECT * FROM Clientes WHERE DataCadastro > DateADD(dd,-30,GETDATE())

(1 row(s) affected)
Table ‘Clientes’. Scan count 1, logical reads 28170, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)
Table ‘Clientes’. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Desempenho - Consulta com DATEADD

Nesse caso é muito visível a diferença de desempenho. O uso do DATEDIFF produz um TABLE SCAN que lê 28170 páginas e o uso do DATEADD produz uma pesquisa de índice que lê apenas 4 páginas para trazer o registro. Não é a toa que 100% do esforço foi atribuído ao DATEDIFF já que o DATEADD representa um parcela irrisória do custo do total. Quando utilizamos o DATEADD, aplicamos essa função sobre o GETDATE() para produzir uma data que represente 30 dias atrás. Isso é feito uma única vez, pois, a função não foi aplicada sobre nenhuma coluna. Após obtermos a data de trinta dias atrás, basta compará-la com a coluna DataCadastro. Uma vez que a coluna DataCadastro não sofreu nenhuma modificação, o índice será naturalmente avaliado no plano de execução e nesse caso foi utilizado retornando a consulta de forma muito mais eficiente. A razão do desempenho não é que o DATEADD é mais rápido que DATEDIFF, mas simplesmente o fato de que a coluna DataCadastro não teve nenhuma função aplicada sobre si.

O uso do CONVERT

É bem comum o uso do CONVERT em conjunto com datas na cláusula WHERE especialmente em situações que envolvam horas. Me parece que há uma certa resistência, medo ou alguma razão muito forte para que a maioria dos códigos evite consultas com o uso do BETWEEN, <=, >= em prol de uma igualdade. Talvez seja o fato de que o padrão DMY não é o mundialmente aceito além do que normalmente durante os primeiros contatos com programação é comum o uso e abuso de funções de formatação. Bem, o fato é que o CONVERT em cláusulas WHERE padece do mesmo mal que o DATEDIFF, ou seja, o não aproveitamento pleno dos índices existentes.

No meu cadastro, o último cliente foi cadastrado no dia 02/07/2011. Vejamos se a consulta abaixo consegue recuperá-lo

SELECT * FROM Clientes WHERE DataCadastro = ‘2011-07-02’

Embora o registro “Flávia Gomides” tenha sido cadastrado no dia 02/07/2011, ele foi cadastrado às 11:30 e por isso a consulta acima não o recuperou. O sufixo do horário às vezes é bem inconveniente. Uma forma que é bem utilizada para eliminá-lo é o uso do CONVERT.

SELECT * FROM Clientes WHERE CONVERT(CHAR(10),DataCadastro,103) = ’02/07/2011′

(1 row(s) affected)
Table ‘Clientes’. Scan count 1, logical reads 28170, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Desempenho - Consulta com CONVERT

O TABLE SCAN dispensa muitos comentários. O registro dessa vez foi retornado, mas de forma bem ineficiente, pois, foi necessário ler 2.000.000 de linhas para conseguir retornar um único registro. Toda vez que vejo uma consulta no fórum ou algum aluno sugerindo o uso do CONVERT em uma situação dessas é exatamente nesse plano de execução que eu penso. Vejamos como fazer para não incorrer em tanta ineficiência.

Se a data é acrescentada do horário, podemos tentar uma comparação com base em um intervalo e não em uma data específica. Supondo que o registro tenha sido cadastrado no dia 02/07/2011 em algum horário desconhecido, temos a certeza de que esse registro está em algum momento entre a 0h do dia 02/07/2011 e a 0h do dia 03/07/2011. Só que não podemos incluir a 0h do dia 03/07/2011 para não retonar os registros desse dia. Duas boas sugestões para compararmos com o CONVERT.

SELECT * FROM Clientes WHERE CONVERT(CHAR(10),DataCadastro,103) = ’02/07/2011′
SELECT * FROM Clientes WHERE DataCadastro >= ‘20110702’ And DataCadastro < ‘20110703’
SELECT * FROM Clientes WHERE DataCadastro BETWEEN ‘20110702’ And ‘20110702 23:59.999’

(1 row(s) affected)
Table ‘Clientes’. Scan count 1, logical reads 28170, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. 

(1 row(s) affected)
Table ‘Clientes’. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)
Table ‘Clientes’. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Desempenho - Consulta com BETWEEN e intervalos

Como pode-se observar o uso de intervalos >= e < ou o BETWEEN são muito mais eficientes, pois, utilizam o índice corretamente enquanto que o CONVERT opta por um SCAN. Enquanto o CONVERT precisou ler 28710 blocos, as outras construções ficaram com apenas 4 páginas cada uma. Sem dúvida uma incomparável eficiência em relação ao CONVERT.

Uma dúvida é comum é como usar o GETDATE() para capturar os clientes que foram cadastrados no mesmo dia. Eu utilizei o dia 02/07/2011 porque era o meu GETDATE() na época desse artigo, mas como montar uma consulta que varia de acordo com o dia em que for executada sem a necessidade de extrair o dia previamente ? Bem, dá um pouquinho mais de trabalho, mas podemos fazê-lo sem evitar o CONVERT.

SELECT * FROM Clientes
WHERE
    DataCadastro >= CONVERT(CHAR(8),GETDATE(),112) And
    DataCadastro < CONVERT(CHAR(8),GETDATE()+1,112)

O CONVERT com o formato 112, colocará a data atual no formato YYYYMMDD sem a parte do horário retornando no meu caso ‘20110702’. O GETDATE() + 1 retorna a data atual com um dia a mais e com o uso do CONVERT no formato 112, teríamos ‘20110703’. Essa forma de consulta também fará uso eficiente do índice Seek e dispensa o TABLE SCAN.

Em alguns posts, aulas e consultorias já vi algumas outras soluções criativas para resolver esse problema que também merecem ser comentadas: CONVERT para datas, YEAR, MONTH e DAY.

SELECT * FROM Clientes
WHERE
    YEAR(DataCadastro) = YEAR(GETDATE()) And
    MONTH(DataCadastro) = MONTH(GETDATE()) And
    DAY(DataCadastro) = DAY(GETDATE())

(1 row(s) affected)
Table ‘Clientes’. Scan count 3, logical reads 4771, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Desempenho - Consulta com YEAR, MONTH e DAY

É uma alternativa criativa, mas não necessariamente performática. Ao contrário do CONVERT que gerou um SCAN com 28710 blocos, o uso das funções YEAR, MONTH e DAY de forma combinada fez uma leitura de 4771 blocos (pouco mais de 25% da tabela). Esse é o resultado de uma varredura e conversão da lista (índice). Considerando que varrer o índice e convertê-lo para consulta posterior foi mais performático, o otimizador optou por fazê-lo e por isso vimos um Index Scan (varredura do índice) ao invés de um TABLE SCAN (varredura de tabela). Ainda assim é quase 1200 vezes mais lento que a leitura de 4 páginas quando o índice foi usado com eficiência e para pesquisa (Seek). Outros dois pontos a comentar sobre o uso dessas três funções é que foi necessário um tabela temporária (Worktable) e o uso de paralelismo para que ela pudesse funcionar. Em um ambiente com muita concorrência, essas condições podem tornar o resultado um pouco menos performático. Embora interessante, a melhor opção ainda é evitar funções sobre colunas.

SUBSTRING, LEFT, LTRIM e funções textuais

Não raras às vezes é necessário fazer comparações com base em pedaços de uma string quando não o tratamento de dados durante a execução de uma query (como se fosse papel de uma consulta fazer tratamento de dados a cada execução). Para demonstrar que essas funções também não são uma boa idéia, vamos modificar alguns registros e criar novos índices.

— Cria um índice sobre a coluna Nome
CREATE INDEX IX_Nome ON Clientes (Nome)

— Consulta os clientes que começam com a letra F
SELECT * FROM Clientes WHERE SUBSTRING(Nome,1,1) = ‘F’
SELECT * FROM Clientes WHERE LEFT(Nome,1) = ‘F’

Table ‘Clientes’. Scan count 3, logical reads 12543, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table ‘Clientes’. Scan count 3, logical reads 12543, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Desempenho - Consulta com SUBSTRING e LEFT

As funções SUBSTRING e LEFT tiveram o mesmo papel nesse caso, ou seja, apenas retirar a primeira letra de cada nome para descobrir os clientes que iniciam o nome com a letra F. A pesquisa não efetuou um SCAN na tabela, mas sim no índice. Embora o SCAN no índice seja mais eficiente que um SCAN na tabela, isso não significa que a consulta foi eficiente. Para retornar cada uma leu 12543 páginas que representa quase 50% do total dos 28710 blocos da tabela. Vejamos como otimizar essa pesquisa:

— Consulta os clientes que começam com a letra F
SELECT * FROM Clientes WHERE SUBSTRING(Nome,1,1) = ‘F’
SELECT * FROM Clientes WHERE Nome LIKE ‘F%’

Desempenho - Consulta com LIKE

Como o LIKE não aplicou nenhuma função sobre a coluna, o índice pode ser utilizado eficientemente já que não há necessidade de conversão. O índice IX_Nome é uma lista ordenada de nomes e embora o LIKE não seja uma correspondência exata, nesse caso ele pode localizar as entradas da lista que começam com F e varrê-las até que haja um troca de letra (digamos G), pois, a lista não sofreu nenhuma conversão e o LIKE possui um string antes do % (se o LIKE começasse com % não seria possível usar o índice).

Algumas funções textuais costumam ser utilizadas para limpeza de dados como o LTRIM e o RTRIM. A seguir, farei uma atualização para que possámos utilizar essas funções:

— Atualiza o registro “Flávia Gomides”
UPDATE Clientes SET Nome = ‘ Flávia Gomides Arnaldo Ferreira Costa’
WHERE ID = 2000000

— Faz a pesquisa retirando os espaços em branco
SELECT * FROM Clientes WHERE LTRIM(Nome) LIKE ‘F%’

Table ‘Clientes’. Scan count 1, logical reads 28170, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Desempenho - Consulta LTRIM

Dessa vez foi feita uma varredura (TABLE SCAN) lendo 28170 blocos. A varredura não deve-se ao LIKE, mas ao uso do LTRIM sobre a coluna nome. Não há otimização a ser feita na query, pois, de fato o nome está com um espaço antes e normalmente não sabemos quantos espaços existem o que nos leva ao uso do LTRIM. Isso só mostra que se temos problemas de dados de má qualidade, temos que corrigí-los sempre na entrada e não a cada consulta. Um cadastro com milhares de clientes iria sofrer um bocado se cada consulta usasse um LTRIM porque a aplicação não retirou os espaços antes de efetivar o cadastro. Ainda que haja sujeira na base, é muito melhor efetuar um UPDATE para corrigir uma única vez do que usar o LTRIM a cada consulta.

User Defined Functions

A partir da versão 2000, o SQL Server permite a construção de funções próprias ao invés das funções já existentes. Será que essas funções vão utilizar da mesma lógica ?

— Cria uma função para verificar se a renda é maior que 5.000
CREATE FUNCTION dbo.FnRendaMaior5000 (@Renda MONEY)
RETURNS TINYINT
As
BEGIN
    DECLARE
@bRendaMaior5000 TINYINT
    SET @bRendaMaior5000 = 0
    IF @Renda > 5000
        SET @bRendaMaior5000 = 1
    RETURN(@bRendaMaior5000)
END
GO

— Cria um índice sobre a coluna Renda
CREATE INDEX IX_Renda ON Clientes (Renda)

— Consulta os clientes com Renda superior a 5000
SELECT * FROM Clientes WHERE Renda > 5000
SELECT * FROM Clientes WHERE dbo.FnRendaMaior5000(Renda) = 1

(1 row(s) affected)
Table ‘Clientes’. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)
Table ‘Clientes’. Scan count 1, logical reads 28170, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Desempenho - User Defined Functions

O uso de funções escalares teve um péssimo desempenho nesse caso. O TABLE SCAN ocorreu inevitavelmente pelas mesmas razões que as anteriores.

Expressões e colunas

Não é apenas o uso de funções sobre colunas que pode levar a ineficiência. Muitas vezes, utiliza-se colunas em expressões para efetuar um determinado cálculo. Vamos supor que o Marketing está avaliando a possibilidade de selecionar clientes que possuam uma renda capaz de pagar uma parcela de R$ 2.800,00 e ainda possuam R$ 2.000,00 de sobra. Há duas formas de estruturar essa consulta:

— Clientes que possuam uma renda capaz de pagar uma parcela de R$ 2.800,00
— e ainda possuam R$ 2.000,00 de sobra
SELECT * FROM Clientes WHERE Renda – 2800 >= 2000
SELECT * FROM Clientes WHERE Renda >= 4800

(1 row(s) affected)
Table ‘Clientes’. Scan count 3, logical reads 4771, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)
Table ‘Clientes’. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

20110701_10_Expressoes

Na primeira opção, apenas traduzimos o enunciado da consulta, ou seja, é preciso subtrair da renda 2.800 reais e o resultado tem de ser maior que 2.000 (Renda – 2800 > 2000). Na segunda possibilidade, apenas modificamos essa inequação repassando o 2.800 para o lado direito e invertendo o seu sinal somando-o a 2.000 e obtendo 4.800. Ambas as consultas retornam o mesmo resultado, mas a segunda consulta leu apenas 4 blocos (Seek) contra 4771 blocos (SCAN) da primeira consulta além de dispensar a tabela temporária (Work Table) e o paralelismo.

Os impactos na concorrência

Até agora demonstrei os devastadores efeitos das funções em atividades de consulta, mas será que há impactos nas operações de escrita ? E se existem quais seriam eles ? Vejamos então como o uso de funções em uma cláusula WHERE pode afetar atividades concorrentes em operações de escrita. Utilizarei três sessões (cada uma representa uma janela separada do SSMS). A primeira sessão deve ter sua consulta executada antes da segunda que deve ser executada antes da terceira.

— Sessão 1
— Muda o valor da renda do primeiro registro
UPDATE Clientes SET Renda = 1000 WHERE ID = 1

— Sessão 2
— Abre uma transação e não fecha
BEGIN TRAN
UPDATE
Clientes SET Nome = ‘Flávia Gomides Arnaldo Ferreira Costa’
WHERE DateDIFF(dd,DataCadastro,GETDATE()) < 30

— Sessão 3
SELECT * FROM Clientes WHERE Renda = 1000

Sabemos claramente que a segunda sessão irá atualizar o registro com ID 2.000.000 durante uma transação e que a terceira sessão irá ler o registro de ID 1, pois, é o único que possui renda igual a 1.000 logo após o UPDATE da primeira sessão. Entretanto, curiosamente a terceira sessão não conclui e parece estar bloqueada.

De fato a terceira sessão está bloqueada e não é preciso sequer consultar o SQL Server para verificar. Se um UPDATE é uma operação de leitura seguida de uma operação de escrita não é difícil de descobrir porque a terceira sessão ficou bloqueada. Vejamos as operações de leitura associadas às sessões dois e três.

— Sessão 2
SELECT * FROM Clientes WHERE DateDIFF(dd,DataCadastro,GETDATE()) < 30

— Sessão 3
SELECT * FROM Clientes WHERE Renda = 1000

20110701_11_Sessoes

Como a sessão 2 fez uma consulta usando o DATEDIFF sobre a coluna DataCadastro, foi necessário efetuar um TABLE SCAN sobre toda a tabela. Isso significa que toda a tabela foi lida (ainda que uma única linha tenha sido retornada). Da mesma forma que toda a tabela foi lida, toda a tabela foi bloqueada impedindo que a sessão 3 conseguisse fazer o SELECT (ainda que sobre uma linha que nada tinha a ver com a linha da sessão 2). Isso significa que consultas mal escritas que fazem TABLE SCAN são muito danosas não só às operações de consulta, mas também às operações de escrita. Para provar que a sessão 2 de fato bloqueia a sessão 3, basta efetuar um ROLLBACK na transação da sessão 2 que a sessão 3 retorna de imediato.

Há muitos softwares de prateleira medíocres que resolvem isso com o NOLOCK generalizado ao invés de se concentram no real problema e solucioná-lo de fato. O NOLOCK apenas permite que a sessão 3 funcione sem ficar bloqueada pela sessão 2, mas não iria evitar o SCAN ineficiente provocado pelo DATEDIFF. Vejamos como seria uma implementação correta (certifique-se que o ROLLBACK foi feito na sessão 2).

— Sessão 2
— Abre uma transação e não fecha
BEGIN TRAN
UPDATE
Clientes SET Nome =
‘Flávia Gomides Arnaldo Ferreira Costa’
WHERE DataCadastro > DateADD(dd,-30,GETDATE())

— Sessão 3
SELECT * FROM Clientes WHERE Renda = 1000

Como já vimos no início do artigo, a consulta de clientes com base na data de cadastro e no DateADD faz uso eficiente do índice e a consulta com base na renda também. Essa eficiência permite que as sessão 2 e a 3 possam rodar em paralelo sem que a sessão 3 seja bloqueada pela sessão 2. A implementação correta gera vários benefícios como evitar que a sessão 2 faça um SCAN e que a sessão 3 rode em paralelo. O uso do NOLOCK não proporciona os dois benefícios e seria a saída padrão para aqueles que não podem ou não querem elaborar consultas de qualidade. Agora que o efeito na concorrência foi demonstrado, não esqueça de efetuar um COMMIT na sessão 2, pois, esse registro será utilizado mais adiante.

Situações inevitáveis

Acredito que a essa altura do artigo já tenha ficado muito claro que o uso de funções sobre colunas em uma cláusula WHERE pode facilitar a escrita da consulta, mas quase sempre tem efeitos indesejados. Entretanto, algumas situações podem ser realmente difíceis de escapar do uso de funções sobre colunas. Vamos supor que exista a necessidade de consultar todas os clientes cadastrados no mesmo dia do mês da data atual para fazer alguma promoção.

— Consultar todos os cadastros efetuados no mesmo dia do mês
SELECT * FROM Clientes WHERE DAY(DataCadastro) = DAY(GETDATE())

(1 row(s) affected)
Table ‘Clientes’. Scan count 3, logical reads 28170, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Desempenho - Função DAY

Essa é uma situação em que apenas a revisão da consulta não é suficiente. No meu exemplo, o dia da data atual é 02 e a consulta precisa retornar todos os clientes cadastrados no dia 02 independente de que mês ou ano que seja. Nesse caso não há como fazer com que a consulta por si só seja eficiente. Ainda assim, há soluções para resolver o problema com a solução de COMPUTED COLUMNS.

— Altera a estrutura da tabela
ALTER TABLE Clientes ADD Dia As DAY(DataCadastro)

— Cria um índice sobre a coluna Dia
CREATE INDEX IX_Dia ON Clientes (DataCadastro)

— Consultar todos os cadastros efetuados no mesmo dia do mês
SELECT * FROM Clientes WHERE Dia = DAY(GETDATE())
SELECT * FROM Clientes WHERE DAY(DataCadastro) = DAY(GETDATE())

(1 row(s) affected)
Table ‘Clientes’. Scan count 3, logical reads 4771, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Desempenho - COMPUTED COLUMNS

A coluna DIA é uma coluna baseada em um cálculo (COMPUTED COLUMNS) e é mantida automaticamente quando há qualquer mudança na coluna DataCadastro. Após a criação da coluna, podemos materializá-la criando um índice sobre ela. Após a criação do índice, pudemos ver a redução de 28710 para 4771 páginas trocando um TABLE SCAN por um Index SCAN. Seria bom mesmo ver a eliminação dos SCANs, mas o otimizador optou por não fazê-lo nessa situação (embora haja várias situações que ele consiga fazer um Seek sobre uma coluna calculada). Um destaque é para que a criação do índice permitiu inclusive que não alterássemos a consulta. Mesmo usando a função DAY sobre a coluna DataCadastro, o otimizador percebeu que poderia utilizar a coluna calculada para otimizar a consulta reduzindo a quantidade de leituras. O uso de colunas calculadas pode otimizar consultas que inclusive não podem ser reescritas :) Um ponto positivo do ORACLE em relação ao SQL Server é que ele permitiria a criação do índice dispensando a criação da coluna calculada com a seguinte sintaxe: CREATE INDEX IX_Dia ON Clientes (DAY(DataCadastro)).

Analogias a JOINs

Embora o artigo tenha focado o uso de funções sobre colunas na cláusula WHERE até aqui, não significa que os JOINs estejam livres desses problemas. Pelo contrário, tudo o que foi dito aplica-se a eles sem exceção. Converter colunas, alterar collation ou ainda colocar expressões em JOINs também fará mal uso dos índices podendo levar a Index Scan ou ainda Table Scans. Vejamos um exemplo:

— Cria uma tabela com a coluna DataCadastro e um código promocional
CREATE TABLE Promocoes (DataCadastro CHAR(10), Desconto DECIMAL(5,2))

— Insere um registro para a promoção de hoje
INSERT INTO Promocoes VALUES (CONVERT(CHAR(10),GETDATE(),103),0.1)

— Verifica os clientes elegíveis para o desconto de hoje
SELECT C.*, P.Desconto FROM Clientes As C
INNER JOIN Promocoes As P
ON CONVERT(CHAR(10),C.DataCadastro,103) = P.DataCadastro

(1 row(s) affected)
Table ‘Promocoes’. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Clientes’. Scan count 3, logical reads 28170, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

20110701_14_JoinsConvert

A tabela de Promoções colocou a data como CHAR(10) no formato DD/MM/YYYY enquanto que a tabela de clientes tem a data com o tipo DATETIME. Não consigo imaginar os reais motivos pelos quais alguém colocaria uma data como CHAR(10), mas enfim… Como as tabelas têm tipos de dados diferentes, para uní-las será necessário usar o CONVERT. Como pudemos perceber, foi um desastre que resultou em um TABLE SCAN na tabela de clientes. O correto mesmo seria efetuar um trabalho de limpeza e igualar os tipos de dados, mas admitindo que isso não seja possível, a recomendação é não converter os dados da maior tabela (ou daquelas que farão filto na cláusula WHERE) e sim das menores tabelas. Como ainda temos a questão do horário, o JOIN fica mais complexo porém muito mais performático (muito menos I/O).

— Verifica os clientes elegíveis para o desconto de hoje
SELECT C.*, P.Desconto FROM Clientes As C
INNER JOIN Promocoes As P
ON CONVERT(CHAR(10),C.DataCadastro,103) = P.DataCadastro

SELECT C.*, P.Desconto FROM Clientes As C
INNER JOIN Promocoes As P
ON C.DataCadastro >=
    CAST(
        RIGHT(P.DataCadastro,4) +
        SUBSTRING(P.DataCadastro,4,2) +
        LEFT(P.DataCadastro,2) As DateTime) And
    C.DataCadastro <
    CAST(
        RIGHT(P.DataCadastro,4) +
        SUBSTRING(P.DataCadastro,4,2) +
        LEFT(P.DataCadastro,2) As DateTime) + 1

(1 row(s) affected)
Table ‘Promocoes’. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Clientes’. Scan count 3, logical reads 28170, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)
Table ‘Promocoes’. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Clientes’. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Como a tabela Clientes possui muito mais registros que a tabela de promoções e um índice sobre a coluna DataCadastro não é uma boa idéia efetuar a conversão na tabela Clientes e sim na tabela de promoções. O problema é que a tabela de clientes não tem só a data, mas também o horário o que atrapalharia o resultado mesmo que convertermos a data da tabela de promoções. Nesse caso, efetuei um comparação com base em um intervalo composto pela conversão da data e do seu dia subsequente de forma semelhante ao início do artigo. O resultado já fala por si próprio. Apenas 5 páginas lidas contra 28710 páginas da primeira implementação. Essa é a diferença entre uma consulta eficiente e uma que retorna um TABLE SCAN ainda que ambas tragam o mesmo resultado.

Por fim, uma última consulta envolvendo JOINs que é uma situação comum quando há collations diferentes no banco de dados.

— Cria uma tabela de Clientes VIPs
CREATE TABLE ClientesVIPs (
    Nome VARCHAR(100)
    COLLATE Latin1_General_CS_AS NOT NULL,
    Desconto DECIMAL(5,2))

— Insere um registro
INSERT INTO ClientesVIPs VALUES (‘Flávia Gomides Arnaldo Ferreira Costa’,0.1)

— Retorna os dados dos Clientes VIPs
SELECT * FROM Clientes As C
INNER JOIN ClientesVIPs As CV ON C.Nome = CV.Nome

Como a collation das colunas é diferente, a consulta não conseguirá ser executada

Msg 468, Level 16, State 9, Line 3
Cannot resolve the collation conflict between “Latin1_General_CS_AS” and “Latin1_General_CI_AI” in the equal to operation.

Aos que quiserem descobrir mais sobre como resolver problemas de Collation eu recomendo o artigo “Como resolver problemas relacionados a conflitos de Collation”, mas vejamos algumas possibilidades de retornar.

— Retorna os dados dos Clientes VIPs (converte a tabela Clientes)
SELECT * FROM Clientes As C
INNER JOIN ClientesVIPs As CV ON C.Nome COLLATE Latin1_General_CS_AS = CV.Nome

— Retorna os dados dos Clientes VIPs (converte a tabela ClientesVIP)
SELECT * FROM Clientes As C
INNER JOIN ClientesVIPs As CV ON C.Nome = CV.Nome COLLATE Latin1_General_CI_AI

— Retorna os dados dos Clientes VIPs (converte a tabela ClientesVIP)
— Força o índice
SELECT * FROM Clientes As C WITH (INDEX=IX_Nome)
INNER JOIN ClientesVIPs As CV ON C.Nome = CV.Nome COLLATE Latin1_General_CI_AI

— Subquery
SELECT * FROM Clientes As C
WHERE Nome IN (
    SELECT CAST(Nome As VARCHAR(100)) COLLATE Latin1_General_CI_AI
    FROM ClientesVIPs)

— Subquery com índice forçado
SELECT * FROM Clientes As C WITH (INDEX=IX_Nome)
WHERE Nome IN (
    SELECT CAST(Nome As VARCHAR(100)) COLLATE Latin1_General_CI_AI
    FROM ClientesVIPs)

Table ‘ClientesVIPs’. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Clientes’. Scan count 3, logical reads 28170, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Clientes’. Scan count 1, logical reads 28170, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘ClientesVIPs’. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table ‘ClientesVIPs’. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Clientes’. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table ‘ClientesVIPs’. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Clientes’. Scan count 3, logical reads 28170, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table ‘ClientesVIPs’. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Clientes’. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Como podemos ver dessa vez a recomendação de converter a tabela menor sozinha não deu efeito. Mesmo o uso de JOINs contra subqueries não surtiu diferença e foi necessário fazer um SCAN na tabela de clientes (28710 páginas). Se a gente der uma pequena mão para o otimizador e forçarmos o índice podemos reduzir a quantidade de leituras de 28710 páginas para apenas 5 páginas e assim obter muito mais eficiência. Não quero dizer com isso que forçar o índice deva ser prática comum no uso de JOINs com colunas convertidas e collations diferentes, mas sim que quando há funções sobre colunas há muitos efeitos colaterais indesejados. Se todas as colunas tivessem a mesma collation sequer teríamos de fazer essas avaliações. Bastaria que alguém atualizasse a estrutura da tabela uma única vez e a collation estaria corrigida. É muito mais eficiente do que corrigir a collation a cada consulta efetuada.

Conclusão

O uso de funções sobre colunas é prática comum em construção de consultas SQL. Quando há poucos usuários, baixo volume de dados e pouca concorrência, tais práticas podem passar muito desapercebidas principalmente no ambiente de desenvolvimento onde essas condições normalmente são encontradas. No entanto, para que aplicações tenham condições de escalar para um ambiente de missão crítica, tais práticas são proibitivas. Funções em cláusula WHERE e JOINs devem ser uma prática descartável e evitada sempre que possível. Utilizá-las é praticamente anular os benefícios de uma indexação eficiente. Boas consultas podem se beneficiar de bons índices, consultas ruins dificilmente irão fazê-lo. O comportamento aqui apresentado não restringe-se de forma nenhuma ao SQL Server. Mesmo bancos como o ORACLE irão sofrer dos mesmos problemas com o uso de funções como to_char tão utilizada para converter datas na cláusula WHERE.

[ ]s,

Gustavo

Piores Práticas – Utilizar a opção Auto Close em um banco de dados SQL Server

Boa Noite Pessoal,

No meu último post eu fiz uma lista de 5 funcionalidades que poderiam ser retiradas do SQL Server. Acabei detalhando um pouco a opção AUTO CLOSE, mas achei interessante escrever um pouco sobre o uso dessa opção em banco de dados. Acho importante esclarescer como ela funciona, cenários de utilização e o porquê da sua não recomendação. Vejamos a explicação sobre essa opção no Books OnLine do SQL Server 2000 e do SQL Server 2008

SQL Server 2000 – Auto Close

When set to ON, the database is closed and shut down cleanly when the last user of the database exits and all processes in the database complete, thereby freeing any resources. By default, this option is set to ON for all databases when using Microsoft® SQL Server™ 2000 Desktop Engine, and OFF for all other editions, regardless of operating system. The database reopens automatically when a user tries to use the database again. If the database was shut down cleanly, the database is not reopened until a user tries to use the database the next time SQL Server is restarted. When set to OFF, the database remains open even if no users are currently using the database.

Quando marcado como "ON", o banco de dados é fechado de forma segura quando o último usuário conectado na base e todos os processos que à acessam são finalizados, liberando portanto qualquer recurso relacionado. Por padrão, essa opção é marcada como "ON" para todos os bancos de dados que utilizam a edição MSDE e "OFF" para todas as outras edições independente do sistema operacional. O banco de dados é reaberto automaticamente quando um usuário tenta utilizar o banco de dados novamente. Se o banco de dados for fechado de forma segura, ele não será reaberto até que um usuário tente utilizar novamente o banco de dados ou o SQL Server seja reiniciado. Quando marcado como "OFF", o banco de dados permanecerá aberto mesmo que nenhum usuário esteja utilizando o banco de dados.

The AUTO_CLOSE option is useful for desktop databases because it allows database files to be managed as normal files. They can be moved, copied to make backups, or even e-mailed to other users. The AUTO_CLOSE option should not be used for databases accessed by an application that repeatedly makes and breaks connections to SQL Server. The overhead of closing and reopening the database between each connection will impair performance.

A opção AUTO CLOSE é útil para bases Desktop, pois, permite que os arquivos de banco de dados sejam gerenciados como arquivos normais. Eles podem ser movidos, copiados para fazer backups, ou inclusive enviados por e-mail para outros usuários. A opção AUTO CLOSE não deve ser utilizada por bancos de dados acessados por uma aplicação que repetidamente conecta-se e desconecta-se ao SQL Server. O Overhead de fechar e abrir o banco de dados entre cada conexão irá impactar o desempenho.

SQL Server 2008 – Auto Close

Há poucas modificações na escrita do texto, mas o entendimento é o mesmo. O destaque vem para a complementação que fala sobre o comportamento assíncrono da opção AUTO CLOSE a partir do 2005 e presente no 2008.

In earlier versions of SQL Server, AUTO_CLOSE is a synchronous process that can degrade performance when the database is accessed by an application that repeatedly makes and breaks connections to the Database Engine. Starting in SQL Server 2005, the AUTO_CLOSE process is asynchronous; repeatedly opening and closing the database no longer reduces performance.

Em versões anteriores do SQL Server, a opção AUTO CLOSE era um processo síncrono que degradava o desempenho quando um banco de dados é acessado por uma aplicação que repetidamente conecta-se e desconecta-se ao banco de dados. A partir do SQL Server 2005, o processo de abrir e fechar o banco de dados é assíncrono. Abrir e fechar conexões repetidas vezes não irá mais degradar o desempenho.

Uma outra explicação

Todas as versões e edições do SQL Server possuem um serviço (sqlservr.exe) que representa o sistema gerenciador de banco de dados (Database Engine). Quando esse serviço é iniciado, ele lê na registry a localização do banco de dados MASTER e a partir daí encontra seus arquivos (master.mdf e mastlog.ldf) e os bloqueia de forma exclusiva, ou seja, nenhum outro processo poderá acessar esses arquivos que não o sqlservr.exe. Após bloquear esses arquivos, o SQL Server lê os dados na sys.master_files (ou sysaltfiles para o SQL Server 7 e 2000) e localiza todos os arquivos MDF, NDF e LDF de todas as bases de dados. Ele acessa esses arquivos de forma exclusiva para subir cada banco de dados. Se a opção AUTO CLOSE estiver marcada como OFF, esse bloqueio imposto pelo SQL Server ficará vigente até que o serviço seja parado. Se a opção AUTO CLOSE estiver marcada como ON, o SQL Server não irá acessar os arquivos da base na inicialização. Apenas quando um usuário precisar acessar o banco, o SQL Server tentará bloquear os arquivos de forma exclusiva durante o tempo que algo estiver sendo realizado no banco de dados. No momento em que não houver mais nenhuma atividade no banco, o SQL Server irá retirar os bloqueios exclusivos liberando os arquivos para qualquer outro processo.

Para não ficar na teoria e mostrar todo esse processo na prática vou utilizar o Process Monitor (disponível gratuitamente no site da Microsoft) para acompanhar o processo sqlservr.exe e suas atividades. A demonstração será feita utilizando-se uma base chamada Pedidos (inicialmente com a propriedade Auto Close desmarcada). O primeiro passo é parar o serviço do SQL Server, ligar o Process Monitor e em seguida reiniciar o serviço. A figura abaixo utilizou diversos filtros no Process Monitor para filtrar apenas os eventos relevantes.

As chaves de registry possuem os parâmetros –d e –l que representam a localização dos arquivos master.mdf e mastlog.ldf que inicializam o banco MASTER. Posteriormente esses arquivos são lidos e consultados e em seguida alguns dos demais bancos de dados como o TempDB, ReportServer, MSDB e a base "Pedidos" existente na minha instância. Algumas outras bases com o ResourceDB e Model também são lidas, mas para contemplar essas bases na visualização seria necessário retirar alguns outros filtros (aqueles que usarem o Process Monitor diretamente podem visualizar essas entradas).

O SQL Server bloqueou todos os arquivos listados de forma exclusiva e a tentativa de qualquer outro processo (e até mesmo o próprio Windows) em acessar esses arquivos não é bem sucedida. A figura abaixo mostra uma tentativa de copiar o arquivo Pedidos.mdf.

Agora que o SQL Server foi reiniciado, o script a seguir muda a propriedade AUTO CLOSE do banco de dados Pedidos, aumenta-o para 1GB e em seguida o serviço é parado.

ALTER DATABASE Pedidos SET AUTO_CLOSE ON
ALTER DATABASE Pedidos MODIFY FILE (NAME=‘Pedidos’, SIZE=1024MB)
SHUTDOWN WITH NOWAIT

Os dados coletados pelo Process Monitor antes do serviço do SQL Server reinicializar mostram o efeito da opção AUTO CLOSE no banco de dados Pedidos conforme a figura abaixo:

Dessa vez, o arquivo .MDF e .LDF sequer foram acessados pelo SQL Server durante sua inicialização. Na lista anterior, o arquivo ReportServer.MDF era acessado antes do arquivo Pedidos.MDF. Após o acesso do arquivo Pedidos.MDF, o arquivo ReportServerTempDB.mdf era o próximo da lista. Com a opção AUTO CLOSE não existe mais o acesso aos arquivos da base Pedidos. Isso significa que o SQL Server não irá impor bloqueios exclusivos a esses arquivos no ato de sua inicialização, mas somente quando o arquivo for solicitado. Como a base ainda não foi acessada, os arquivos estão livres para outros processos manipulá-los e uma simples cópia pelo Windows Explorer é permitida.

A propriedade AUTO CLOSE viabiliza algumas estratégias de backup alternativas. Quando essa opção está ativa, é possível por exemplo copiar os arquivos .MDF e .LDF para outros locais como se fosse arquivos comuns o que não seria possível caso essa opção estivesse marcada como OFF. Isso pode dar uma certa sensação de utilidade, mas da mesma forma que representa uma possibilidade bastante arriscada. O que aconteceria se alguém estivesse acessando o banco de dados e outra pessoa tentasse copiar o arquivo no mesmo momento ?

O script a seguir cria e popula uma tabela com 100 mil registros (pouco mais de 1 minuto até o seu término). Enquanto esse script é executado, uma cópia do arquivo é realizada simultaneamente pelo File System.

— Muda o contexto de banco de dados
USE Pedidos

— Cria uma tabela
CREATE TABLE T (Seq INT IDENTITY(1,1),
    ID UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID(),
    Data DATETIME DEFAULT GETDATE(),
    TextoLivre CHAR(8000) DEFAULT REPLICATE(‘A’,8000))

— Insere 100.000 registros
INSERT INTO T DEFAULT VALUES
GO 100000

Não foi possível realizar a cópia, pois, no momento do acesso, o SQL Server bloqueou o arquivo exclusivamente. Isso pode ser confirmado pelo Process Monitor. É visível o acesso ao arquivo Pedidos.mdf.

O bloqueio exclusivo protegeu o arquivo contra uma cópia não autorizada (afinal ao final da cópia o arquivo já estaria diferente), mas o que aconteceria se fosse ao contrário, ou seja, se alguém estivesse copiando o arquivo e o acesso ao banco fosse necessário ? Para verificar essa hipótese é necessário desconectar-se do banco para que o SQL Server desbloquei os arquivos. O script abaixo, desconecta-se do banco, aguarda 10 segundos e tenta um novo acesso. Nesse tempo, uma cópia do arquivo é realizada pelo Windows Explorer. Uma mensagem de erro é retornada pelo SQL Server.

Msg 5120, Level 16, State 101, Line 8
Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Pedidos.mdf".
Operating system error 32: "32(The process cannot access the file because it is being used by another process.)".
Msg 945, Level 14, State 2, Line 8
Database ‘Pedidos’ cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.

A mensagem de erro é bem clara. O SQL Server não conseguiu obter um bloqueio exclusivo sobre os arquivos da base pedidos, pois, os mesmos estavam sendo utilizados por outro processo (no caso o Windows Explorer). Mesmo após a cópia dos arquivos ter terminado e os arquivos estarem disponíveis, o SQL Server não consegue mais acessar a base de dados. A consulta abaixo mostra que a mesma foi marcada como SUSPECT o que indica dados corrompidos.

SELECT ‘Pedidos’ As Base, DATABASEPROPERTYEX(‘Pedidos’,‘Status’) As Status

Base Status
Pedidos SUSPECT

A solução é colocar o banco em modo de emergência e em seguida devolvê-lo para o status de ONLINE além de preventivamente checar sua integridade.

— Muda o contexto de banco de dados
USE MASTER

— Coloca a base em modo de emergência
ALTER DATABASE Pedidos SET EMERGENCY

— Tenta colocar a base em modo ONLINE
ALTER DATABASE Pedidos SET ONLINE

— Verifica se houve corrupção de dados
DBCC CHECKDB(‘Pedidos’) WITH NO_INFOMSGS

Por sorte, o CHECKDB não retornou nenhum erro e a base voltou a ficar operacional.

Resumidamente, a opção AUTO CLOSE abre a possibilidade de se trabalhar arquivos fora do SQL Server. O principal argumento para utilizar essa opção é flexibilidade para "fazer os backups" sem a indisponibilidade do serviço. Ao meu ver esse não é um argumento válido uma vez que o comando BACKUP DATABASE é o utilizado para fazer o backup e isso é feito de forma OnLine. Pode contra-argumentar que a cópia do arquivo de forma direta torna o processo mais rápido, pois, é necessária uma única operação enquanto que o BACKUP e o RESTORE incorrem em duas operações (mesmo a operação de attach é mais rápida que um RESTORE). Eu concordo, mas se essa é a justificativa, que se desatache então a base e se faça a cópia dos arquivos. Copiar os arquivos com a base atachada é arriscado, pois, o acesso a essa base durante a cópia pode eventualmente corrompê-la (conforme demonstrado). Ao meu ver, utilizar essa opção é incorrer em um risco desnecessário.

[ ]s,

Gustavo

Piores Práticas – Elaborar triggers preparadas para linhas e não para conjuntos

Bom Dia Pessoal,

Após uma tempestuosa semana e iniciada a contagem regressiva para o show do Paul Van Dyk, estou dando uma passadinha por aqui. Já faz um tempinho que não posto nada na categoria "Piores Práticas". Hoje vi no fórum MSDN uma dúvida a respeito de triggers e fiquei pensando um pouco sobre elas. Não exatamente sobre as questões de desempenho, mas sobre a forma como elas normalmente são construídas e exemplificadas. A receita é bem simples. Basta construir a trigger, capturar o valor de cada coluna da INSERTED ou da DELETED em uma variável e fazer as devidas manipulações.

Embora a receita básica funcione na maioria das situações e seja impressionante o quanto se codifica dessa forma, a verdade é que ela é deficiente. Trabalhar com variáveis em triggers é restringí-las a registros e operações linha a linha. Toda vez que se constrói uma trigger com essa lógica, normalmente se retira a capacidade delas em trabalhar com múltiplos registros, pois, haverá problemas ou BUGs de aplicação. Vejamos essas limitações um pouco mais de perto.

— Muda o contexto para o TempDB
Use TempDB

— Cria um tabela de Produtos
CREATE TABLE Produto (
    ProdutoID INT NOT NULL,
    NomeProduto VARCHAR(50) NOT NULL,
    Estoque INT NOT NULL,
    UltimaAtualizacao DATE NOT NULL,

    CONSTRAINT PK_Produto PRIMARY KEY (ProdutoID))

— Cria uma tabela de Vendas
CREATE TABLE Venda (
    VendaID INT NOT NULL,
    DataVenda DATE NOT NULL, ProdutoID INT NOT NULL,
    Quantidade INT NOT NULL, Preco SMALLMONEY NOT NULL,
    CONSTRAINT PK_Venda PRIMARY KEY (VendaID))

O raciocínio é bem simples. A cada venda realizada, o estoque do produto deve ser diminuído, pois, uma venda representa uma saída de estoque. Isso pode ser feito via aplicação ou via stored procedure, mas esse é um caso clássico para utilização de triggers. Vejamos um exemplo bem típico de implementação para esse caso.

— Cria uma trigger para atualizar o estoque
CREATE TRIGGER trgBaixaEstoque ON Venda
FOR INSERT
AS
BEGIN

    — Declara as variáveis para atualização
    DECLARE @ProdutoID INT, @Quantidade INT, @DataVenda DATE

    — Captura os valores das colunas para as variáveis
    SELECT @ProdutoID = ProdutoID, @Quantidade = Quantidade, @DataVenda = DataVenda
    FROM INSERTED

    — Atualiza o estoque
    UPDATE Produto SET Estoque = Estoque – @Quantidade, UltimaAtualizacao = @DataVenda
    WHERE ProdutoID = @ProdutoID
END

Vejamos agora a trigger em ação. O código a seguir irá criar alguns produtos e efetuar algumas vendas. Se tudo funcionar como proposto, a cada venda o estoque deve ter a devida baixa.

— Insere dois produtos
INSERT INTO Produto (ProdutoID, NomeProduto, Estoque, UltimaAtualizacao)
VALUES (1,‘MP3 Player’,20,‘20100120’)

INSERT INTO Produto (ProdutoID, NomeProduto, Estoque, UltimaAtualizacao)
VALUES (2,‘Pen Drive’,17,‘20100120’)

— Insere três vendas
INSERT INTO Venda (VendaID, DataVenda, ProdutoID, Quantidade, Preco)
VALUES (1,‘20100121’,1,3,150.00)

INSERT INTO Venda (VendaID, DataVenda, ProdutoID, Quantidade, Preco)
VALUES (2,‘20100121’,1,2,145.00)

INSERT INTO Venda (VendaID, DataVenda, ProdutoID, Quantidade, Preco)
VALUES (3,‘20100122’,2,1,90.00)

— Verifica a tabela de Produtos
SELECT ProdutoID, NomeProduto, Estoque, UltimaAtualizacao FROM Produto

Antes de verificarmos os resultados provocados pela trigger, podemos fazer uma simples conta. O produto 1 (MP3 Player) possui 20 unidades e sua data de atualização foi 20/01/2010. Como foram realizadas duas vendas no dia 21, e cada venda teve respectivamente a quantidade de 3 e 2 unidades, o estoque deve ficar em 15 unidades e a última atualização deve ser dia 21/01/2010. No caso do produto 2 (Pen Drive), o estoque possuía 17 unidades. Com a venda do dia 22/01/2010 em uma unidade, o estoque deve ficar em 16 unidades e a data da última atualização deve ser 22/01/2010. O resultado do SELECT é exibido conforme a tabela abaixo:

ProdutoID NomeProduto Estoque UltimaAtualizacao
1 MP3 Player 15 21/01/2010
2 Pen Drive 16 22/01/2010

Os resultados bateram com a conta e a trigger parece estar funcionando adequadamente, mas será que só isso é suficiente ? A trigger está preparada para lidar com inserções individuais, mas o que aconteceria se houvesse uma carga em lote ?

— Cria uma tabela para armazenar as vendas ao longo do dia (supostamente de outra loja)
CREATE TABLE VendaFilial (
    VendaID INT NOT NULL,
    DataVenda DATE NOT NULL, ProdutoID INT NOT NULL,
    Quantidade INT NOT NULL, Preco SMALLMONEY NOT NULL,
    CONSTRAINT PK_VendaFilial PRIMARY KEY (VendaID))

— Insere duas vendas
INSERT INTO VendaFilial (VendaID, DataVenda, ProdutoID, Quantidade, Preco)
VALUES (4,‘20100122’,1,2,147.50)

INSERT INTO VendaFilial (VendaID, DataVenda, ProdutoID, Quantidade, Preco)
VALUES (5,‘20100123’,1,1,148.50)

— Insere todas as vendas da filial na tabela de vendas
INSERT INTO Venda (VendaID, DataVenda, ProdutoID, Quantidade, Preco)
SELECT VendaID, DataVenda, ProdutoID, Quantidade, Preco FROM VendaFilial

— Verifica a tabela de Produtos
SELECT ProdutoID, NomeProduto, Estoque, UltimaAtualizacao FROM Produto

As duas vendas realizadas na filial e registradas nas tabelas de vendas de filial foram referente ao produto 1 (MP3 Player) e totalizam 3 unidades. Se o estoque do produto 1 era de 15 unidades e houve uma baixa de 3 unidades sendo a última venda do dia 23/01/2010, o natural é esperar que o estoque fique em 12 unidades e a data de atualização seja a do dia 23/01/2010. Vejamos o resultado da última consulta:

ProdutoID NomeProduto Estoque UltimaAtualizacao
1 MP3 Player 13 22/01/2010
2 Pen Drive 16 22/01/2010

Ao contrário do esperado, o produto 1 (MP3 Player) ficou com o estoque em 13 unidades (e não 12) e a data de atualização foi 22/01/2010 e não 23/01/2010. A lógica da trigger não parece ter erros mas o que será que aconteceu ? Há algumas respostas possíveis, mas o fato é que a trigger foi preparada para trabalhar com linhas e não com conjuntos. O trecho de código simula o mesmo problema da trigger.

— Declara as variáveis para atualização
DECLARE @ProdutoID INT, @Quantidade INT, @DataVenda DATE

— Captura os valores das colunas para as variáveis
SELECT @ProdutoID = ProdutoID, @Quantidade = Quantidade, @DataVenda = DataVenda
FROM VendaFilial

— Recupera o valor das variáveis
SELECT @ProdutoID As ProdutoID, @Quantidade As Quantidade, @DataVenda As DataVenda

O resultado é interessante. Embora a tabela VendaFilial tenha duas linhas, a atribuição das variáveis capturou apenas uma das linhas (no meu caso a do dia 23). Isso é compreensível, pois, as variáveis estão preparadas para receber um valor e não múltiplos valores. Se há por exemplo duas quantidades, não há como a variavél @quantidade capturar as duas quantidades (2 e 1). Se há duas datas, não há como a variável @DataVenda capturar as duas datas (22/01/2010 e 23/01/2010). É por isso que a trigger não funcionou. Por que ela estava preparada para trabalhar com uma linha por vez e quando foram submetidas duas linhas, ou melhor um conjunto de duas linhas, a trigger se perdeu e não pode calcular corretamente o estoque. Ainda que trocássemos o SELECT por SET, o problema iria persistir.

— Atualiza o produto 1 em relação ao estoque
UPDATE Produto SET Estoque = 15, UltimaAtualizacao = ‘20100121’
WHERE ProdutoID = 1

— Exclui as duas últimas vendas para não ocorrer violação de PK
DELETE FROM Venda WHERE VendaID > 3

— Troca o SELECT da trigger por SET
ALTER TRIGGER trgBaixaEstoque ON Venda
FOR INSERT
AS
BEGIN

    — Declara as variáveis para atualização
    DECLARE @ProdutoID INT, @Quantidade INT, @DataVenda DATE

    — Captura os valores das colunas para as variáveis
    SET @ProdutoID = (SELECT ProdutoID FROM INSERTED)
    SET @Quantidade = (SELECT Quantidade FROM INSERTED)
    SET @DataVenda = (SELECT DataVenda FROM INSERTED)

    — Atualiza o estoque
    UPDATE Produto SET Estoque = Estoque – @Quantidade, UltimaAtualizacao = @DataVenda
    WHERE ProdutoID = @ProdutoID
END

— Insere todas as vendas da filial na tabela de vendas
INSERT INTO Venda (VendaID, DataVenda, ProdutoID, Quantidade, Preco)
SELECT VendaID, DataVenda, ProdutoID, Quantidade, Preco FROM VendaFilial

A substituição do SELECT pelo SET não muda o fato de que variáveis só podem receber um único valor. Como as inserções são feitas da tabela de filial para a tabela de vendas e a tabela de filial contém duas linhas, a tabela INSERTED fatalmente terá duas linhas (as triggers são disparadas por evento e não por linhas). No momento da atribuição da variável @Quantidade, a tabela INSERTED terá duas quantidades (2 e 1). No momento da atribuição da variável @DataVenda, a tabela INSERTED terá duas datas (22/01/2010 e 23/01/2010). Mesmo o ProdutoID sendo repetido, não é possível armazenar o ID 1 duas vezes na mesma variável em um mesma instrução. Após a execução, um erro é gerado:

Msg 512, Level 16, State 1, Procedure trgBaixaEstoque, Line 10
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

The statement has been terminated.

O erro só evidencia o que já foi colocado. O fato das três subqueries retornarem dois valores cada e tentarem atribuir os resultados às variáveis @ProdutoID, @Quantidade e @DataVenda faz com que múltiplos valores sejam atribuídos às variáveis incorrendo no erro "Subquery returned more than 1 value". Isso mostra que em algumas situações usar o SET pode ser mais "inteligente" que o SELECT, pois, o SELECT repassou um cálculo errado enquanto que o SET impediu o cálculo. Entretanto, ambas as implementações foram preparadas para utilizar linhas e não conjuntos.

Alguns praticantes do ORACLE vão dizer que "se fosse no ORACLE, teríamos o comando FOR EACH ROW dentro da trigger". Concordo que o FOR EACH ROW tem sua utilidade e que possivelmente veremos alguma coisa desse tipo em uma release futura do SQL Server, mas não acho que ele seja a solução para os problemas. Na verdade acho que ela é a continuação para a filosofia "linha a linha" que performaticamente não é a melhor situação para esse caso. Como o SQL Server ainda não possui o recurso de FOR EACH ROW dentro da trigger, a solução é utilizar os cursores que podem trabalhar linha a linha.

— "Prepara" a trigger para operar múltiplas linhas
ALTER TRIGGER trgBaixaEstoque ON Venda
FOR INSERT
AS
BEGIN

    — Declara as variáveis para atualização
    DECLARE @ProdutoID INT, @Quantidade INT, @DataVenda DATE

    — Declara um cursor para "varrer" a INSERTED
    DECLARE cVendas CURSOR
    FAST_FORWARD
    FOR SELECT
ProdutoID, Quantidade, DataVenda FROM INSERTED
    — É necessário ordenar para evitar que uma venda mais antiga atualize a data erroneamente
    ORDER BY ProdutoID, DataVenda    

    — Abre o cursor
    OPEN cVendas

    — Lê o primeiro registro
    FETCH NEXT FROM cVendas INTO @ProdutoID, @Quantidade, @DataVenda

    — Varre os demais registros
    WHILE @@FETCH_STATUS = 0
    BEGIN
        — Atualiza o estoque
        UPDATE Produto SET Estoque = Estoque – @Quantidade, UltimaAtualizacao = @DataVenda
        WHERE ProdutoID = @ProdutoID

        — Passa para o próximo registro
        FETCH NEXT FROM cVendas INTO @ProdutoID, @Quantidade, @DataVenda
    END

    — Fecha o cursor
    CLOSE cVendas

    — Desaloca o cursor
    DEALLOCATE cVendas
END

— Insere todas as vendas da filial na tabela de vendas
INSERT INTO Venda (VendaID, DataVenda, ProdutoID, Quantidade, Preco)
SELECT VendaID, DataVenda, ProdutoID, Quantidade, Preco FROM VendaFilial

— Verifica a tabela de Produtos
SELECT ProdutoID, NomeProduto, Estoque, UltimaAtualizacao FROM Produto

Como o cursor faz a varredura linha a linha, dessa vez o estoque e a última atualização foram atualizados corretamente:

ProdutoID NomeProduto Estoque UltimaAtualizacao
1 MP3 Player 12 23/01/2010
2 Pen Drive 16 22/01/2010

Dizem que cursores não são performáticos. Falam o mesmo sobre as triggers. Se cursores não são performáticos e triggers também não, é bem provável que colocar um cursor dentro de uma trigger não seja nada performático. Vejamos então como resolver isso de uma maneira simples e performática.

— Exclui as vendas da filial
DELETE FROM VendaFilial

— Insere duas vendas
INSERT INTO VendaFilial (VendaID, DataVenda, ProdutoID, Quantidade, Preco)
VALUES (6,‘20100124’,1,2,146.00)

INSERT INTO VendaFilial (VendaID, DataVenda, ProdutoID, Quantidade, Preco)
VALUES (7,‘20100125’,2,3,85.50)

— Altera a trigger
ALTER TRIGGER trgBaixaEstoque ON Venda
FOR INSERT
AS
BEGIN

    — Atualiza a tabela de produtos
    UPDATE Produto SET Estoque = Estoque – Quantidade, UltimaAtualizacao = DataVenda
    FROM Produto As Prod
    INNER JOIN INSERTED As Ins ON Prod.ProdutoID = Ins.ProdutoID
END

— Insere todas as vendas da filial na tabela de vendas
INSERT INTO Venda (VendaID, DataVenda, ProdutoID, Quantidade, Preco)
SELECT VendaID, DataVenda, ProdutoID, Quantidade, Preco FROM VendaFilial

— Verifica a tabela de Produtos
SELECT ProdutoID, NomeProduto, Estoque, UltimaAtualizacao FROM Produto

Se a conta for feita, o produto 1 (MP3 Player) tinha 12 unidades e a última atualização era 23/01/2010. No caso do produto 2, o estoque era de 16 unidades e a última atualização era de 22/01/2010. Com as últimas vendas, o produto 1 teve uma baixa de 2 unidades no dia 24/01/2010 e o produto 2 teve uma baixa de 3 unidades no dia 25, o que deixa os produtos com 10 e 13 unidades respectivamente.

ProdutoID NomeProduto Estoque UltimaAtualizacao
1 MP3 Player 10 24/01/2010
2 Pen Drive 13 25/01/2010

Se o código dessa nova versão da trigger for comparado com o código anterior há notórias vantagens. O tamanho do código é muito inferior o que acaba tornando-o muito mais simples e fácil de manter. Substituir o uso do cursor por um único comando, sem declaração de variáveis, e atualizações linha a linha é também muito mais performático.

Embora a última versão da trigger tenha melhorado a qualidade do código, ela ainda possui uma falha. Vejamos o exemplo abaixo:

— Exclui as vendas da filial
DELETE FROM VendaFilial

— Insere duas vendas
INSERT INTO VendaFilial (VendaID, DataVenda, ProdutoID, Quantidade, Preco)
VALUES (8,‘20100127’,2,1,90.00)

INSERT INTO VendaFilial (VendaID, DataVenda, ProdutoID, Quantidade, Preco)
VALUES (9,‘20100126’,2,4,87.50)

— Insere todas as vendas da filial na tabela de vendas
INSERT INTO Venda (VendaID, DataVenda, ProdutoID, Quantidade, Preco)
SELECT VendaID, DataVenda, ProdutoID, Quantidade, Preco FROM VendaFilial

— Verifica a tabela de Produtos
SELECT ProdutoID, NomeProduto, Estoque, UltimaAtualizacao FROM Produto

As duas vendas são do produto 2 (Pen Drive) e totalizam 5 unidades. Embora seja o primeiro registro, a venda mais recente é do dia 27/01/2010. Se a trigger funcionar corretamente, o estoque deve ficar em 8 unidades e a data da última atualização deve ser 27/01/2010.

ProdutoID NomeProduto Estoque UltimaAtualizacao
1 MP3 Player 10 24/01/2010
2 Pen Drive 9 26/01/2010

A trigger não funcionou corretamente, pois, o estoque ficou em 9 unidades e a data da última atualização ficou em 26/01/2010. Visualmente é possível perceber que a primeira venda não foi considerada. Se o estoque possuía 13 unidades e ficou com apenas 9 unidades e a data de atualização foi 26/01/2010 é porque apenas a segunda venda foi considerada.

Isso ocorre porque um comando de updade só pode atualizar um registro apenas um vez. A presença de duas vendas para o produto 2 (Pen Drive) iria requerer duas atualizações para esse produto, mas não é possível fazer isso em um único update. Para evitar essa restrição, basta fazer um pequeno ajuste na trigger.

— Atualiza o produto 2 em relação ao estoque
UPDATE Produto SET Estoque = 13, UltimaAtualizacao = ‘20100125’
WHERE ProdutoID = 2

— Exclui as duas últimas vendas (8 e 9)
DELETE FROM Venda WHERE VendaID IN (8,9)

— Altera a trigger
ALTER TRIGGER trgBaixaEstoque ON Venda
FOR INSERT
AS
BEGIN

    — Totaliza as vendas por produto
    ;WITH UltimasVendas (ProdutoID, QuantidadeTotal, UltimaVenda)
    As (
        SELECT ProdutoID, SUM(Quantidade), MAX(DataVenda)
        FROM INSERTED
        GROUP BY ProdutoID)

    — Atualiza a tabela de produtos
    UPDATE Produto SET Estoque = Estoque – QuantidadeTotal, UltimaAtualizacao = UltimaVenda
    FROM Produto As Prod
    INNER JOIN UltimasVendas As UV ON Prod.ProdutoID = UV.ProdutoID
END

— Insere todas as vendas da filial na tabela de vendas
INSERT INTO Venda (VendaID, DataVenda, ProdutoID, Quantidade, Preco)
SELECT VendaID, DataVenda, ProdutoID, Quantidade, Preco FROM VendaFilial

— Verifica a tabela de Produtos
SELECT ProdutoID, NomeProduto, Estoque, UltimaAtualizacao FROM Produto

A primeira parte do script retorna a situação do produto para o momento anterior ao das vendas 8 e 9 realizadas na filial. Essa alteração foi feita, porque o estoque tinha sido incorretamente calculado no pela versão anterior da trigger. Ainda nessa primeira parte, as vendas 8 e 9 são excluídas da tabela Vendas para serem reinseridas com o disparo da trigger. A segunda parte do script altera o código da trigger e faz a reinserção das vendas da filial para que o estoque e data da última atualização dos produtos seja recalculada.

ProdutoID NomeProduto Estoque UltimaAtualizacao
1 MP3 Player 10 24/01/2010
2 Pen Drive 8 27/01/2010

Dessa vez a atualização do produto foi feita corretamente. O estoque ficou em 8 unidades e a data da última atualização foi 27/01/2010. Como a trigger totalizou os dados de produto somando a quantidade e calculado a última data, cada produto terá apenas uma única ocorrência na CTE UltimasVendas. Isso possibilita que o UPDATE seja feito corretamente e o estoque seja atualizado. Mesmo com o passo adicional o código ainda se torna simples e enxuto. Não foi declarado um único cursor e nem sequer uma variável. Enquanto o primeiro código estava preparado para operar apenas um linha por vez, esse código está apto a trabalhar com quantas vendas ocorrerem. Essa é a diferença entre trabalhar em uma filosofia linha a linha e uma filosofia baseada em conjuntos. A filosofia em conjuntos além de mais performática, consegue trabalhar com linhas individuais e o contrário não é verdadeiro.

Haverá aqueles que questionarão esse exemplo. Se existir uma tabela de filial, a solução seria bem simples. Bastaria incluir a trigger na tabela de vendas da filial e assim os produtos seriam atualizados automaticamente. Para combinar as vendas um UNION ALL bastaria. A verdade é que dificilmente haverá uma tabela de vendas da filial. A idéia de criá-la foi apenas para mostrar as deficiências de triggers preparadas para lidar linha a linha principalmente em processos de carga. Podemos imaginar as mesmas consequências, se por exemplo, houvesse uma carga de vendas a partir de um arquivo texto, um job, um processo de replicação, ou um pacote SSIS. Independente do método, o fato é que triggers linha a linha não estão prontas para operar operações que envolvam mais de um registro e triggers com lógica baseadas em conjunto sempre estarão aptas a trabalhar com qualquer situação seja um ou registro por vez ou vários de uma única só vez. Por essas e outras que o mais recomendável é sempre pensar em conjuntos e nunca em linhas quando estiver codificando uma trigger.

[ ]s,

Gustavo

Piores Práticas – Utilizar o comando BACKUP LOG com a opção WITH TRUNCATE_ONLY – Parte II

Boa Noite Pessoal,

Dada as limitações do Spaces (eu nem sabia que existiam) no espaço do post, não pude colocar tudo em um único artigo sobre as consequências do uso da opção TRUNCATE_ONLY para truncar e diminuir o tamanho do Log. Após explicar as teorias necessárias para o entendimento dessas consequências na parte I do artigo, apresento agora um exemplo prático.

Um exemplo prático

Para tornar práticos alguns dos conceitos apresentados aqui, vejamos um exemplo:

— Cria um banco de dados
CREATE DATABASE SisMtr

— Garante que o Recovery Model é Full
ALTER DATABASE SisMtr SET RECOVERY FULL

— Cria alguns objetos no banco de dados SisMtr
CREATE TABLE SisMtr.dbo.Disciplinas (
    IDDisciplina INT, NomeDisciplina VARCHAR(100))

CREATE TABLE SisMtr.dbo.Cursos (
    IDCurso INT, NomeCurso VARCHAR(80))

— Faz um backup full da base
BACKUP DATABASE SisMtr TO DISK = ‘C:\Temp\SisMtrFull.BAK’

— Verifica informações do backup recém gravado
RESTORE HEADERONLY FROM DISK = ‘C:\Temp\SisMtrFull.BAK’

A exceção do último comando, esse script não faz nada demais. Ele apenas cria um banco, duas tabelas e faz um backup full. O último comando lê as informações do cabeçalho do backup full e mostra algumas informações de LSN sobre esse backup. Existem várias colunas, mas em especial duas merecem atenção:

FirstLSN LastLSN
22000000008400155 22000000015000001

Possivelmente os LSNs exibidos não serão os mesmos obtidos, pois, a sua numeração depende de várias características próprias de cada instância, bancos de dados criados, etc. A coluna FirstLSN mostra o LSN mais antigo encontrado no backup (22000000008400155) e o mais recente (22000000015000001). As transações que estão contempladas no backup full (juntamente com os dados), são todas as que ocorreram entre os LSNs 22000000008400155 e 22000000015000001.

Seguindo com o exemplo, serão realizadas algumas inserções nessas tabelas e será realizado um backup de log.

— Insere registros nas tabelas de Disciplinas e Cursos
INSERT INTO SisMtr.dbo.Disciplinas VALUES (1,‘História e Formação do Direito’)
INSERT INTO SisMtr.dbo.Disciplinas VALUES (2,‘Introdução à Economia’)
INSERT INTO SisMtr.dbo.Disciplinas VALUES (3,‘Administração Contemporânea’)
INSERT INTO SisMtr.dbo.Disciplinas VALUES (4,‘Ética’)
INSERT INTO SisMtr.dbo.Disciplinas VALUES (5,‘Metodologia Científica’)

INSERT INTO SisMtr.dbo.Cursos VALUES (1,‘Direito’)
INSERT INTO SisMtr.dbo.Cursos VALUES (2,‘Economia’)

— Faz um backup de Log
BACKUP LOG SisMtr TO DISK = ‘C:\Temp\SisMtrLog01.TRN’

— Verifica as informações do backup de Log
RESTORE HEADERONLY FROM DISK = ‘C:\Temp\SisMtrLog01.TRN’

O comando de RESTORE mostra as seguintes informações:

FirstLSN LastLSN
22000000008400155 22000000016900001

A coluna FirstLSN mostra que o primeiro LSN contemplado no backup de log é 22000000008400155. A coluna LastLSN mostra que o último LSN contemplado é 22000000016900001. Isso significa que esse backup de Log contempla todas as transações ocorridas entre os LSNs 22000000008400155 e 22000000016900001. Pode parecer estranho, pois, o backup full contemplava as transações entre o LSN 22000000008400155 e o LSN 22000000015000001 e o de Log de transações também contempla essas transações já que se inicia no LSN 22000000008400155 e termina no LSN 22000000016900001 que é superior ao LSN 22000000015000001 (LastLSN) do backup full. Isso é esperado porque esse foi o primeiro backup de log de transações e todo o conteúdo do arquivo do Log de transações foi "despejado" para o backup de Log.

— Cria uma tabela Associativa
CREATE TABLE SisMtr.dbo.DisciplinaCurso (
    IDDisciplina INT, IDCurso INT)

— Insere alguns registros na tabela recém criada
INSERT INTO SisMtr.dbo.DisciplinaCurso VALUES (1,1)
INSERT INTO SisMtr.dbo.DisciplinaCurso VALUES (2,1)
INSERT INTO SisMtr.dbo.DisciplinaCurso VALUES (4,1)
INSERT INTO SisMtr.dbo.DisciplinaCurso VALUES (5,1)
INSERT INTO SisMtr.dbo.DisciplinaCurso VALUES (2,2)
INSERT INTO SisMtr.dbo.DisciplinaCurso VALUES (3,2)
INSERT INTO SisMtr.dbo.DisciplinaCurso VALUES (4,2)
INSERT INTO SisMtr.dbo.DisciplinaCurso VALUES (5,2)

— Faz um backup de Log
BACKUP LOG SisMtr TO DISK = ‘C:\Temp\SisMtrLog02.TRN’

— Verifica as informações do backup de Log
RESTORE HEADERONLY FROM DISK = ‘C:\Temp\SisMtrLog02.TRN’

O comando de RESTORE mostra as seguintes informações:

FirstLSN LastLSN
22000000016900001 22000000018800001

O primeiro LSN contemplado no arquivo é o LSN 22000000016900001 e o último LSN contemplado é o 22000000018800001. Isso significa que esse backup de Log contempla todos os LSNs entre 22000000016900001 e 22000000018800001. As sequências de LSNs após a realização dos backups são mostradas na tabela abaixo:

Backup Lsn Inicial Lsn Final
SisMtrFull 22000000008400155 22000000015000001
SisMtrLog01 22000000008400155 22000000016900001
SisMtrLog02 22000000016900001 22000000018800001

Com esses três backups é possível "refazer" a história do banco, pois, todas as transações estão contempladas e não há quebras nas sequências de LSN. O script abaixo conta quantos registros existem em cada uma das tabelas e faz a exclusão do banco de dados:

— Verifica quantos registros tem em cada tabela
SELECT
    (SELECT COUNT(*) FROM SisMtr.dbo.Disciplinas) As Disciplinas,
    (SELECT COUNT(*) FROM SisMtr.dbo.Cursos) As Cursos,
    (SELECT COUNT(*) FROM SisMtr.dbo.DisciplinaCurso) As DisciplinaCurso

— Efetua a exclusão do Banco
DROP DATABASE SisMtr

A tabela Disciplinas tem 5 registros, a tabela Cursos tem 2 registros e a tabela DisciplinasCurso tem 8 registros. Ao final do processo de restauração, esse mesmo resultado deve ser obtido.

— Restaura o Backup Full
RESTORE DATABASE SisMtr FROM DISK = ‘C:\Temp\SisMtrFull.BAK’ WITH NORECOVERY

— Verifica o status dos arquivos de dados do banco
SELECT
    DB_Name(Database_ID) As Banco, Name As ArquivoLogico,
    Differential_Base_LSN As LSN_Inicial, Redo_Start_Lsn As LSN_Final
FROM sys.master_files
WHERE Database_ID = DB_ID(‘SisMtr’) And Data_Space_Id > 0

Após restaurar o backup full, verifica-se que os LSNs contemplados no banco são justamente os mesmos obtidos pelo processo de restauração:

Banco Arquivo LSN_Inicial LSN_Final
SisMtr SisMtr 22000000008400155 22000000015000001

O banco encontra-se indisponível e em processo de restauração. O próximo passo é a aplicação do primeiro Log.

— Restaura o primeiro Log
RESTORE LOG SisMtr FROM DISK = ‘C:\Temp\SisMtrLog01.TRN’ WITH NORECOVERY

— Verifica o status dos arquivos de dados do banco
SELECT
    DB_Name(Database_ID) As Banco, Name As ArquivoLogico,
    Differential_Base_LSN As LSN_Inicial, Redo_Start_Lsn As LSN_Final
FROM sys.master_files
WHERE Database_ID = DB_ID(‘SisMtr’) And Data_Space_Id > 0

Após a restauração do primeiro backup de Log, verifica-se que o banco parou no LSN final (Last LSN) do backup de Log:

Banco Arquivo LSN_Inicial LSN_Final
SisMtr SisMtr 22000000008400155 22000000016900001

Embora o backup de Log tivesse as transações anteriores ao LSN final do backup Full (22000000015000001), apenas as transações entre o LSN 22000000015000001 e o LSN 22000000016900001 foram aplicadas, pois, as anteriores já estavam contempladas no backup full. O banco ainda está indisponível e necessita da aplicação do segundo log.

— Restaura o segundo Log
RESTORE LOG SisMtr FROM DISK = ‘C:\Temp\SisMtrLog02.TRN’ WITH NORECOVERY

— Verifica o status dos arquivos de dados do banco
SELECT
    DB_Name(Database_ID) As Banco, Name As ArquivoLogico,
    Differential_Base_LSN As LSN_Inicial, Redo_Start_Lsn As LSN_Final
FROM sys.master_files
WHERE Database_ID = DB_ID(‘SisMtr’) And Data_Space_Id > 0

Após a aplicação do segundo backup de Log, verifica-se que o banco parou no LSN final (Last LSN) do backup de Log:

Banco Arquivo LSN_Inicial LSN_Final
SisMtr SisMtr 22000000008400155 22000000018800001

Embora o segundo Log tenha sido aplicado, o banco de dados ainda não encontra-se plenamente operacional. O comando abaixo solicita ao SQL Server que o banco fique operacional.

— Deixa o banco operacional
RESTORE LOG SisMtr WITH RECOVERY

— Verifica o status dos arquivos de dados do banco
SELECT
    DB_Name(Database_ID) As Banco, Name As ArquivoLogico,
    Differential_Base_LSN As LSN_Inicial, Redo_Start_Lsn As LSN_Final
FROM sys.master_files
WHERE Database_ID = DB_ID(‘SisMtr’) And Data_Space_Id > 0

Após o banco de dados ficar OnLine e disponível não é possível mais determinar qual é o LSN final, pois, nunca se sabe quando em qual LSN as transações irão parar de ocorrer, pois, é esperado que o banco de dados não tenha nenhum tipo de problema que afete o seu funcionamento e que transações futuras venham a ser realizadas. Essa é a razão pela qual o valor da coluna LSN_Final é nula nesse caso. O objetivo foi demonstrar que os backups foram aplicados e que os LSNs estavam sendo contemplados. O processo de restauração foi possível porque as sequências de LSNs estavam íntegras e refletidas no backup.

O exemplo prático com o BACKUP LOG WITH TRUNCATE_ONLY

Como será que o uso do truncate pode afetar o processo de backup ? Vou utilizar um outro exemplo para simular o que costuma ocorrer em ambiente de produção quando esse comando é utilizado.

— Cria o banco de dados
CREATE DATABASE BDCargas ON (
    NAME=‘BDCargas_Data’, SIZE=10MB,
    FILENAME=‘C:\Temp\BDCargas_Data.MDF’)
LOG ON (
    NAME=‘BDCargas_Log’, SIZE=1MB,
    FILENAME=‘C:\Temp\BDCargas_Log.LDF’)

— Muda o Recovery Model para Full
ALTER DATABASE BDCargas SET RECOVERY FULL

— Verifica o tamanho dos arquivos
SELECT
    DB_Name(Database_ID) As Banco, Name As ArquivoLogico,
    Physical_Name As ArquivoFisico, (Size * 8) / 1024.00 As TamanhoMB
FROM sys.master_files WHERE Database_ID = DB_ID(‘BDCargas’)

— Realiza um Backup Full
BACKUP DATABASE BDCargas TO DISK = ‘C:\Temp\BDCargasFull.BAK’

— Verifica as informações do Backup Full
RESTORE HEADERONLY FROM DISK = ‘C:\Temp\BDCargasFull.BAK’

O script simplesmente cria um banco de dados chamado BDCargas, muda o Recovery Model para Full (caso já não esteja) e verifica o tamanho dos arquivos utilizados respectivamente 10MB para dados e 1MB para Log. As informações do Backup Full em relação ao LSN são:

FirstLSN LastLSN
23000000005900037 23000000007700001

O próximo passo é fazer com que o Log encha fazendo-se "necessário" o comando de TRUNCATE. O script abaixo cria uma tabela e efetua vários Inserts e Deletes. O tamanho do arquivo de dados não irá se alterar, pois, cada inserção gera uma exclusão, mas como as duas ações são logadas, o arquivo de log ficará muito grande.

— Cria uma tabela
CREATE TABLE BDCargas.dbo.Registros (
    ID INT IDENTITY(1,1), Mascara UNIQUEIDENTIFIER DEFAULT NEWID())

— Faz um backup de Log
BACKUP LOG BDCargas TO DISK = ‘C:\Temp\BDCargasLog01.TRN’

— Insere e exclui 200.000 registros
DECLARE @i INT
SET @i = 1

WHILE @i <= 200000
BEGIN
    INSERT INTO
BDCargas.dbo.Registros DEFAULT VALUES
    DELETE FROM BDCargas.dbo.Registros
    SET @i = @i + 1
END

— Verifica o tamanho dos arquivos
SELECT
    DB_Name(Database_ID) As Banco, Name As ArquivoLogico,
    Physical_Name As ArquivoFisico, (Size * 8) / 1024.00 As TamanhoMB
FROM sys.master_files WHERE Database_ID = DB_ID(‘BDCargas’)

Após aproximadamente cinco minutos, é possível notar que houve um aumento considerável do tamanho do Log que passou de 1MB para pouco mais de 214MB conforme a tabela abaixo:

Banco ArquivoLogico ArquivoFisico TamanhoMB
BDCargas BDCargas_Data C:\Temp\BDCargas_Data.MDF 10.0000000
BDCargas BDCargas_Log C:\Temp\BDCargas_Log.LDF 214.1250000

Não acho que 214MB seja um espaço absurdo, mas para realmente mostrar os malefícios do truncate do log, adicionei mais algumas transações antes de truncar o log.

— Insere dois registros
INSERT INTO BDCargas.dbo.Registros
DEFAULT VALUES
INSERT INTO BDCargas.dbo.Registros DEFAULT VALUES

— Trunca o Log
BACKUP LOG BDCargas
 WITH TRUNCATE_ONLY

— Muda o contexto de banco de dados
USE BDCargas;

— Diminui o tamanho do arquivo de LOG
DBCC SHRINKFILE(BDCargas_Log,1)

— Verifica o tamanho dos arquivos
SELECT
    DB_Name(Database_ID) As Banco, Name As ArquivoLogico,
    Physical_Name As ArquivoFisico, (Size * 8) / 1024.00 As TamanhoMB
FROM sys.master_files WHERE Database_ID = DB_ID(‘BDCargas’)

— Muda o contexto de banco de dados
USE MASTER;

Após executar o comando de truncagem e em seguida o Shrink é notável a diminuição do arquivo de Log de 214MB para o seu tamanho original:

Banco ArquivoLogico ArquivoFisico TamanhoMB
BDCargas BDCargas_Data C:\Temp\BDCargas_Data.MDF 10.0000000
BDCargas BDCargas_Log C:\Temp\BDCargas_Log.LDF 1.0000000

Após as 200.000 linhas terem sido inseridas e excluídas, o Log de transações cresceu rapidamente. Para diminuí-lo, utilizou-se o comando de truncate em conjunto com o Shrink devolvendo-o ao tamanho original. Antes do truncate haviam duas linhas que foram inseridas. Irei inserir mais três linhas e prosseguirei com um backup de Log e excluirei o banco de dados.

— Insere três registros
INSERT INTO BDCargas.dbo.Registros DEFAULT VALUES
INSERT INTO BDCargas.dbo.Registros DEFAULT VALUES
INSERT INTO BDCargas.dbo.Registros DEFAULT VALUES

— Verifica os registros que estão na tabela
SELECT ID, Mascara FROM BDCargas.dbo.Registros

— Faz um backup de Log
BACKUP LOG BDCargas TO DISK = ‘C:\Temp\BDCargasLog02.TRN’

— Exclui o banco de dados
DROP DATABASE BDCargas

Antes de excluir o banco de dados, o SELECT mostrou que haviam cinco registros cadastrados com os respectivos IDs 200.001, 200.002, 200.003, 200.004 e 200.005. Porém, ao tentar fazer um novo backup de Log, o SQL Server advertiu uma mensagem de erro.

Msg 4214, Level 16, State 1, Line 1
BACKUP LOG cannot be performed because there is no current database backup.
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.

Não foi possível fazer o backup de Log, pois, ele está sem referências. Como o log foi truncado entre o último backup de Log e a tentativa de um novo backup, transações foram descartadas do Log sem terem sido copiadas. Isso significa que embora esteja tudo certo com o banco, a estratégia de backups baseada em Logs ficou inviabilizada, pois, houve uma quebra de sequência. Uma forma de tornar a estratégia de Logs possível novamente é a realização de um novo backup full.

— Efetua um novo Backup Full
BACKUP DATABASE BDCargas TO DISK = ‘C:\Temp\BDCargasFull02.BAK’

— Efetua um novo Backup de Log
BACKUP LOG BDCargas TO DISK = ‘C:\Temp\BDCargasLog02.TRN’

Até pode-se tirar um novo backup full para viabilizar os logs posteriores, mas o fato é que algumas possibilidades foram perdidas. A tabela abaixo mostra o resumo dos backups e seus LSNs.

Backup Lsn Inicial Lsn Final
BDCargasFull 23000000005900037 23000000007700001
BDCargasLog01 23000000005900037 23000000010400001
BDCargasFull02 183000000008600037 183000000010200001
BDCargasLog02 183000000008600037 183000000010800001

É possível restaurar o primeiro backup full e aplicar o primeiro backup de log ou restaurar o segundo backup full e aplicar o segundo backup de log. As transações entre o primeiro backup de log e o segundo backup full não podem ser recuperadas por um processo de backup, pois, a truncagem do log fez com que elas fossem perdidas e não pudessem ser copiadas. Se o DBA necessitar de restaurar o banco em algum momento desse intervalo, ele não conseguirá. Possivelmente se isso acontecer, será uma demanda de extrema importância e o DBA descobrirá que esse intervalo foi perdido somente quando for "tarde demais".

Se por um lado o truncate do log pode realmente liberar espaço, por outro lado ele poderá causar um grave problema para o futuro. Utilizar o TRUNCATE_ONLY é semelhante a plantar uma bomba relógio para algum momento posterior. Pode ser que ela não exploda, pois, não necessariamente alguém irá solicitar um backup exatamente em um trecho não coberto, mas de qualquer forma, aqueles que o fazem com frequência, estão plantando não somente uma mas várias bombas relógio e nesse caso as chances de alguma delas explodir aumenta. Será terrível ter de dizer que não foi possível restaurar um backup não porque o arquivo corrompeu-se, porque a mídia foi roubada, etc mas simplesmente porque "utilizou-se um comando que não se sabia as consequências".

Alternativas para evitar o uso do BACKUP LOG com o TRUNCATE_ONLY

Acho que os benefícios e os malefícios desse comando já estão demonstrados. A regra geral é bem simples: "Se você realmente necessita de restaurações em momentos específicos e (ou) depende dos backups de log, a solução é ter uma rotina para fazer esses backups. Se eles forem feitos, o log não irá encher. Se você realmente não necessita de restaurações em momentos específicos e (ou) não depende dos backups de log, simplesmente altere o Recovery Model para Simple, pois, nesse modelo, as entradas de log serão eliminadas automaticamente dispensando a necessidade de truncar o log".

Ainda assim podem ocorrer situações em que o log fuja ao controle. Um processo de carga mal planejado, uma tentativa de invasão, uma falha no job de backups são algumas razões pelas quais um arquivo de log pode crescer rapidamente e talvez não haja tempo de esperar que o backup de log seja concluído. Nesse caso, pode ser tentador executar o TRUNCATE_ONLY, mas ainda assim essa deve ser a última alternativa e não a primeira. Algumas soluções paleativas que podem ser usadas.

  • Se houver mais arquivos no mesmo drive que o log cresceu, faça o backup de log dos arquivos menores e execute um shrink. Isso deve dar algum tempo para que o log que estourou possa ser copiado.
  • Se houver espaço disponível em outras unidades, considere efetuar um backup de log dividido em vários arquivos. Isso faz com que ele seja executado mais rapidamente.
  • Se houver espaço disponível em outra unidade, crie um segundo arquivo de log para que as transações sejam gravadas nesse arquivo enquanto o backup de log é realizado.
  • Se realmente for imprescindível fazer um truncate do log, lembre-se de fazer um backup full e (ou) diferencial logo em seguida para que as perdas sejam menores. Não esvazie o log e deixe por isso mesmo, pois, quanto mais tempo o backup demorar a ser feito depois do truncate, maior será o período de não recuperação.

Ao meu ver, se o profissional que trabalha com o banco tiver conhecimento e o ambiente for organizado, o truncate_only passa a ser um comando completamente dispensável. Se alguém precisa utilizá-lo é porque houve falta de planejamento e organização do ambiente ou há falta de conhecimento daquele que o utiliza. Tive a chance de ver um ambiente com mais de 600 bases em SQL Server de missão altamente crítica e simplesmente há mais de dois anos que a equipe de DBAs sequer sentiu a necessidade de executar esse comando em ambiente de produção. Problemas de espaço em disco e Logs estourando aconteceram, mas  foram contornados com as soluções corretas e não com o truncate do log. Acredito que se um ambiente desses consegue viver sem o TRUNCATE_ONLY, qualquer um pode organizar-se para não utilizá-lo.

Alguns podem até considerar exagero, mas vale a pena lembrar que o SQL Server 2008 não aceita o uso do BACKUP LOG com a opção WITH TRUNCATE_ONLY. No SQL Server 2008 é necessário mudar o Recovery Model para Simple se o log necessitar ser truncado. Se o banco realmente não necessita dos backups de Log, mudar o recovery model para Simple é a solução definitiva. A Microsoft também percebe que esse comando é completamente dispensável e que não deve ser utilizado.

"We recommend that you never use NO_LOG or TRUNCATE_ONLY to manually truncate the transaction log, because this breaks the log chain. Until the next full or differential database backup, the database is not protected from media failure. Use manual log truncation in only very special circumstances, and create backups of the data immediately"

Aqueles que tem problemas com o Log de transações e ao procurar ajuda se depararam com o BACKUP LOG WITH TRUNCATE_ONLY não devem simplesmente executá-lo, mas sim avaliar se ele é realmente a solução. Aqueles que "aconselham" usar o BACKUP LOG WITH TRUNCATE_ONLY devem pensar melhor antes de dar um conselho desses, pois, a vontade de ajudar pode acabar atrapalhando.

Quando alguém aparecer gritando "Socorro meu log cresceu demais. Como faço para zerá-lo ou diminuir o Log ?", espero que haja questionamentos e não simplesmente "use o BACKUP LOG WITH TRUNCATE_ONLY com o SHRINK que resolve".

[ ]s,

Gustavo

Piores Práticas – Utilizar o comando BACKUP LOG com a opção WITH TRUNCATE_ONLY – Parte I

Boa Noite Pessoal,

Nos últimos dias estive um pouco ausente dos fóruns e de diversas comunidades que participo, pois estou ministrando um curso oficial de SQL Server 2008. Ontem durante a aula apresentei a parte de backups e após as explicações, participei para os alunos uma situação muito comum que indiretamente está relacionada com backups. Hoje nos fóruns MSDN e Technet também presenciei a mesma situação acontecendo novamente intitulando-se "Log Gigantesco".

Acredito que depois da dificuldade de conectar-se remotamente ao SQL Server (se alguém estiver com essa dificuldade leia esse excelente artigo "Como configurar Conexão Remota no SQL Server 2005") , problemas com o log de transação que cresce assustadoramente representam provavelmente a dúvida mais comum de SQL Server que exista. De fato já vi inúmeras dúvidas relacionadas ao log do SQL Server que cresce, cresce, cresce e deixa alguns desenvolvedores, dbas iniciantes e profissionais preocupados e aparentemente sem ter o que fazer. Na maioria das vezes a solução é uma só conforme descrito abaixo (o texto foi adaptado para preservar a identidade de seus participantes):

"Oi Gente,

Estou com um banco de dados com um arquivo de LOG com mais de 40Gb e preciso de espaço.
O HD só tem mais 2GB e LOG continua crescendo. O que devo fazer ?"

"Oi Fulano,

Cara é muito simples. Basta só rodar esses dois comandos:

Backup Log NomeDoSeuBanco WITH TRUNCATE_ONLY
GO
DBCC SHRINKFILE(NomeLogicoArquivoLog, 10)
GO

Se você não souber o nome do arquivo lógico roda a sp_helpdb ‘SeuBanco’. É importante rodar os dois comandos conectado no seu banco."

De fato essa é a solução padrão e realmente funciona. Na maioria das situações, aqueles que aplicaram essa solução certamente viram seus arquivos de Log diminuir tão assustadoramente quanto cresceram. Com uma solução tão "eficiente" a receita de bolo é escrita. Toda vez que o LOG atingir tamanhos absurdos, basta então rodar o BACKUP LOG com o TRUNCATE_ONLY e logo em seguida rodar o SHRINK FILE. A eficiência desse comando parece ser tanta que não me estranha que ele seja contemplado em jobs, planos de manutenção e nem que seja encapsulado em uma stored procedure.

Inquestionavalmente arquivos de Log gigantescos são um incômodo. Não só pelo fato de ameaçaram acabar com todo o espaço livre disponível na unidade, mas também porque se um backup possui um arquivo de LOG gigantesco, será necessário reservar um espaço gigantesco em outro ambiente no momento da restauração. E como normalmente os ambientes de homologação e desenvolvimento tem menos recursos, arquivos muito grandes podem ser verdadeiros inconvenientes. Após ver tantas dezenas de threads com problemas de arquivos de log que crescem além da conta fico com duas preocupações: Será que aqueles que simplesmente executam esses comandos tem a menor noção do problema que eles podem provocar ? E será que aqueles que repassam esses comandos sabem exatamente o tamanho do risco que podem estar introduzindo em outros ambientes ?

"Afinal o que há de mal em executar esses dois comandos ? Eles sempre irão reduzir o log de transações e "resolver o problema" e essa é a solução que está escrita na maioria dos tutoriais, fóruns, etc. Se o arquivo de log não for limpo aí sim é que os problemas aparecem." Não posso deixar de concordar com a afirmação, mas vejamos algo um pouco mais apurado antes de tirar as conclusões sobre algo praticamente inquestionável.

Um pouco sobre o Log de transações

Todo banco de dados possui uma estrutura de log que registra tudo o que aconteceu que represente escrita nesse banco de dados. Essa estrutura é imprescindível para que o banco de dados saiba o que aconteceu na eventualidade de uma queda de energia entre outros problemas e mantenha-se consistente. De tempos em tempos (mais precisamente a cada Checkpoint), as transações registradas no Log de transações são efetivamente aplicadas no banco de dados mantendo sua consistência. As entradas no Log possuem um sequencial chamado LSN que é o acrônimo de Log Sequence Number. À medida que o Log recebe novas instruções, novos LSNs são adicionados. A tabela abaixo mostra um hipotético exemplo de um Log de transações.

LSN Instrução
001 INSERT INTO Clientes VALUES (1,‘Alberto’,2560.92,‘M’)
002 DELETE FROM AjusteEstoque WHERE IDAjuste = 3097
003 CREATE TABLE Defeitos (IDDef INT, IDProd INT, Data SMALLDATETIME)
004 CHECKPOINT
005 ALTER TABLE Produtos ADD DataUltimaAtualizacao SMALLDATETIME
006 UPDATE Produtos SET DataUltimaAtualizacao = DataCadastro
007 GRANT SELECT ON Produtos TO UsrMarketing
008 EXEC sp_addrolemember ‘db_datareader’, ‘UsrRelatorios’
009 CHECKPOINT
010 DROP TABLE ProdutosHistorico

A propriedade RECOVERY MODEL

O Recovery Model de um banco de dados tem papel fundamental no funcionamento dessas estruturas de log e nas estratégias possíveis de Disaster Recover a partir de um backup. Há três opções para o Recovery Model: Simple, Bulk Logged e Full. Não explicarei os detalhes do funcionamento do Bulk Logged dada sua proximidade com o Recovery Model Full. Para o entendimento desse artigo, o Recovery Model Bulk Logged e Full podem ser interpretados como iguais embora não o sejam em alguns casos.

No Recovery Model Simple todas as transações são gravadas no arquivo de Log, mas a cada Checkpoint elas são excluídas do arquivo de Log liberando espaço para novas entradas. Se considerarmos que o arquivo de log exemplificado tem capacidade para gravar 10 LSNs e que o Recovery Model está marcado como Simple, após o Checkpoint do LSN004, os LSNs 001, 002, 003 e 004 seriam eliminados do arquivo de log, o mesmo valeria para os LSNs 005, 006, 007, 008 e 009 após o Checkpoint em LSN009. Se nesse momento aparecessem mais algumas entradas, elas seriam gravadas no início do arquivo que foi liberado. Ex

LSN Instrução
011 TRUNCATE TABLE TMP
012 EXEC UspRelatorioVendas @DataInicio = ‘20090720’
013 DBCC CHECKTABLE(‘RelatoriosConsolidados’)
014 CHECKPOINT
015 DROP USER UsrApp
   
   
   
   
010 DROP TABLE ProdutosHistorico

Os LSNs 011, 012, 013 e 014 são posteriores ao LSN010, mas como o arquivo tem capacidade para 10 entradas, é necessário acomodar esses LSNs. Como há espaço no início do arquivo, as entradas podem ser cadastradas perfeitamente sem prejudicar a consistência desse Log. No Recovery Model Simple, o Log recicla-se automaticamente e apaga as entradas que ficaram para trás e que foram contempladas no banco pelo processo de Checkpoint. Após o processo de Checkpoint, as transações ficam refletidas no banco e portanto são apagadas do Log de transações. O checkpoint contemplado no LSN014 apagaria todos os LSNs deixando somente o LSN015 e liberaria espaço para mais nove entradas de Log (quatro na área antes do LSN015 e cinco após a presença do LSN015). O Recovery Model Simple irá sempre reciclar as entradas do log formando um circulo na qual hora determinadas posições estaram preenchidas, serão apagadas pelo processo de Checkpoint, serão utilizadas novamente e excluídas novamente. É por esse comportamento em forma de círculo que essa arquitetura é chamada em alguns produtos de Log Circular.

No Recovery Model Full, as coisas funcionam um pouco diferentes. Ao contrário do Recovery Model Simple, o Recovery Model Full não irá apagar de forma automática os LSNs do log de transações independente do que aconteça. Mesmo que o processo de Checkpoint ocorra e as transações já estejam contempladas no banco de dados, o Log continuará a mantê-las no arquivo de Log. Essa característica de arquivamento faz com que alguns chamem essa arquitetura de Archieve Log. Se admitirmos que esse arquivo possui capacidade apenas para 10 LSNs, após completar os 10 LSNs, o arquivo ficará completamente cheio. O cadastro do LSN 011 ao LSN015 irá demandar que o arquivo de Log cresça conforme a tabela abaixo:

LSN Instrução
001 INSERT INTO Clientes VALUES (1,‘Alberto’,2560.92,‘M’)
002 DELETE FROM AjusteEstoque WHERE IDAjuste = 3097
003 CREATE TABLE Defeitos (IDDef INT, IDProd INT, Data SMALLDATETIME)
004 CHECKPOINT
005 ALTER TABLE Produtos ADD DataUltimaAtualizacao SMALLDATETIME
006 UPDATE Produtos SET DataUltimaAtualizacao = DataCadastro
007 GRANT SELECT ON Produtos TO UsrMarketing
008 EXEC sp_addrolemember ‘db_datareader’, ‘UsrRelatorios’
009 CHECKPOINT
010 DROP TABLE ProdutosHistorico
011 TRUNCATE TABLE TMP
012 EXEC UspRelatorioVendas @DataInicio = ‘20090720’
013 DBCC CHECKTABLE(‘RelatoriosConsolidados’)
014 CHECKPOINT
015 DROP USER UsrApp

Uma vez que o Recovery Model Full faz com que o banco de dados não exclua as entradas antigas do log de transações, ou seja, as transações que já estão contempladas no banco de dados, não é de se esperar que o log cresça de forma indefinida. Afinal novas transações irão gerar novos LSNs e como esses não são limpos de forma automática o resultado final é um arquivo de log de tamanhos acima do normal. O crescimento do Log se dará na velocidade em que novas transações ocorrem, mas é fato que mais cedo ou mais tarde ele irá incomodar.

Uma conclusão inicial é que se o arquivo de Log cresce de forma descomunal, o banco de dados não pode estar em Recovery Model Simple. Se o Recovery Model Simple faz a devida exclusão das entradas inativas (aquelas já contempladas pelo Checkpoint), não há como o Recovery Model Simple fazer com que um arquivo de Log cresça de forma desproporcional (muitas vezes ele não cresce). Se o banco de dados estiver com o Recovery Model Simple, a única preocupação é ter um arquivo de log que consiga acomodar a quantidade média de transações entre um checkpoint e outro.

A primeira recomendação é que se o arquivo de Log cresce demais, ao invés de simplesmente efetuar o Truncate com o Shrink funcionem, é mais factível mudar o Recovery Model para Simple. Assim, o arquivo de Log não irá crescer de forma descomunal e tenderá a ficar constante. Embora o Truncate com o Shrink funcione, o problema voltará a acontecer, pois, se o Recovery Model é Full o Log irá crescer novamente. Ao invés de executar sucessivos Truncates e Shrinks é mais sensato alterar o Recovery Model para Simple eliminando a causa raiz do problema.

A título de curiosidade, o truncate iria eliminar as entradas inativas do log, ou seja, todas aquelas anteriores ao último checkpoint e o shrink iria reduzir o arquivo, possivelmente devolvendo-o ao tamanho de 10 LSNs ao invés de 15 conforme a tabela abaixo:

LSN Instrução
   
   
   
   
   
   
   
   
   
015 DROP USER UsrApp

O início do arquivo ficaria pronto para receber novas entradas de LSN. Com o tempo, o Log cresceria e provocaria sucessivas expansões até que um novo truncate e um novo shrink fossem executados.

Se a diferença entre o Recovery Model Simple e o Recovery Model fosse apenas a administração do tamanho do arquivo de log seria interessante perguntar qual a utilidade do Recovery Model Full. Enquanto o Recovery Model Simple dispensa maiores preocupações com o tamanho do arquivo de log, o Recovery Model Full traz responsabilidades e preocupações adicionais e isso é um forte indício para pensar porque os bancos de dados não são criados com o Recovery Model Simple ? Dá muito menos trabalho.

É sem dúvida um raciocínio que faz sentido, mas vejamos a seguir porque o Recovery Model Full é importante mesmo com as implicações e overheads administrativos.

A escolha do Recovery Model e o processo de restauração

O Recovery Model tem papel fundamental no processo de backup e restauração do banco de dados. Alguns Recovery Models permitem a realização de backups de log e posterior recuperação enquanto outro simplesmente não permitem. O uso de backup de logs também é imprecindível para algumas estratégias de backup e restore mais complexas como o Restore Online e o Piecemal Restore ambos utilizados em conjunto com backups e filegroups. Por hora basta saber que o Recovery Model Simple não possibilita a realização de backups de logs enquanto que os demais Recovery Models possibilitam.

A razão para o Recovery Model Simple não permitir backups de logs é um pouco óbvia. Como ele exclui as entradas mais antigas do Log de transações a cada Checkpoint e como cada Checkpoint ocorre em média de minuto em minuto, não faria muito sentido fazer o backup do log de transações de uma base com o Recovery Model Simple. Não haveria o que gravar no backup, pois, provavelmente no momento de fazê-lo restariam apenas as entradas mais recentes (as que o Checkpoint) ainda não contemplou enquanto que muitos outros LSNs teriam sido deixados de lado. Como o Recovery Model Full não exclui as entradas do Log (mesmo as já contempladas pelo Checkpoint), faz sentido em falar de backups de log para bases com o Recovery Model Full. Vejamos como isso funciona através do hipotético arquivo de log.

LSN Hora Instrução
001 00:00 BACKUP DATABASE BD TO DISK = ‘C:\Temp\BDFull.BAK’
002 08:00 CREATE TABLE Tbl (Codigo INT)
003 09:00 INSERT INTO Tbl VALUES (1)
004 09:30 INSERT INTO Tbl VALUES (2)
005 10:00 BACKUP LOG BD TO DISK = ‘C:\Temp\BDLog01.TRN’
006 14:45 INSERT INTO Tbl VALUES (4)
007 15:20 INSERT INTO Tbl VALUES (5)
008 15:30 CHECKPOINT
009 16:00 INSERT INTO Tbl VALUES (6)
010 18:00 BACKUP LOG BD TO DISK = ‘C:\Temp\BDLog02.TRN’

Os backups contemplam a relação de LSNs conforme a tabela abaixo:

Backup Lsn Inicial Lsn Final
BDFull 001 001
BDLog01 002 005
BDLog02 006 010

Com essa relação de backups é visível que o banco de dados pode ser reconstruído para parar em qualquer posição possível entre o LSN 001 e o LSN 010. A tabela abaixo mostra as sequências necessárias para voltar o banco em qualquer LSN, ou melhor dizendo qualquer milissegundo entre às 00:00 e às 18:00.

LSN Sequência Necessária
001 Apenas o BDFull
002 Restaurar o BDFull e o BDLog01 com parada às 08:00
003 Restaurar o BDFull e o BDLog01 com parada às 09:00
004 Restaurar o BDFull e o BDLog01 com parada às 09:30
005 Restaurar o BDFull e o BDLog01 com aplicação total de BDLog01
006 Restaurar o BDFull, o BDLog1 com aplicação total e o BDLog02 com parada às 14:45
007 Restaurar o BDFull, o BDLog1 com aplicação total e o BDLog02 com parada às 15:20
008 Restaurar o BDFull, o BDLog1 com aplicação total e o BDLog02 com parada às 15:30
009 Restaurar o BDFull, o BDLog1 com aplicação total e o BDLog02 com parada às 16:00
010 Restaurar o BDFull, o BDLog1 e o BDLog02 com aplicação total para ambos

Segundo a tabela, é possível restaurar em qualquer LSN aplicando-se o backup full e restaurando-se os Logs necessários. Embora o exemplo pare em LSNs específicos, uma vez que os backups contemplem toda a duração de 00:00 até às 18:00 é possível parar em qualquer posição nesse intervalo. É totalmente factível voltar o banco às 12:00 mesmo que não haja nenhum LSN identificado.

Após a realização do backup de Log (BDLog01) os LSNs 001, 002, 003, 004 e 005 foram retirados do arquivo de Log e copiados para o backup de Log BDLog01. O backup de Log BDLog02 irá contemplar todos os LSNs gravados desde o último backup de Log (no caso o BDLog01) e portanto irá retirar do arquivo de Log os LSNs 006, 007, 008, 009 e 010. A realização desses backups retirou as entradas de Log inativas, liberou espaço e fez com que o arquivo de Log possuísse espaço disponível para acomodar futuras transações sem provocar um crescimento exagerado ou ainda o estouro do espaço disponível em disco.

Da mesma forma que um banco com o Recovery Model Simple não permite realizar backup de Log, ele também não permite efetuar o truncate do Log. Então situações que exijam o uso do truncate do Log só podem contemplar outros Recovery Models como no caso o Full. Vejamos como o uso do truncate (independente do Shrink ou não) afetaria o exemplo.

LSN Hora Instrução
001 00:00 BACKUP DATABASE BD TO DISK = ‘C:\Temp\BDFull.BAK’
002 08:00 CREATE TABLE Tbl (Codigo INT)
003 09:00 INSERT INTO Tbl VALUES (1)
004 09:30 INSERT INTO Tbl VALUES (2)
005 10:00 BACKUP LOG BD TO DISK = ‘C:\Temp\BDLog01.TRN’
006 14:45 CREATE TABLE EmpregadoMes (Nome VARCHAR(50))
007 15:20 INSERT INTO Tbl VALUES (5)
008 15:30 BACKUP LOG BD WITH TRUNCATE_ONLY
009 16:00 INSERT INTO EmpregadoMes VALUES (‘Fernando’)
010 18:00 BACKUP LOG BD TO DISK = ‘C:\Temp\BDLog02.TRN’

A relação de LSNs conforme os backups ficaria da seguinte forma:

Backup Lsn Inicial Lsn Final
BDFull 001 001
BDLog01 002 005
BDLog02 009 010

É possível observar que o LSN 008 fez um truncate no Log e por isso o backup de Log BDLog02 só contemplou o LSN subsequente ao LSN que efetuou o truncate. Vejamos agora as possibilidades de restauração:

LSN Sequência Necessária
001 Apenas o BDFull
002 Restaurar o BDFull e o BDLog01 com parada às 08:00
003 Restaurar o BDFull e o BDLog01 com parada às 09:00
004 Restaurar o BDFull e o BDLog01 com parada às 09:30
005 Restaurar o BDFull e o BDLog01 com aplicação total de BDLog01
006 Não é possível restaurar, pois, não há como chegar até o LSN 006
007 Não é possível restaurar, pois, não há como chegar até o LSN 007
008 Não é possível restaurar, pois, não há como chegar até o LSN 008
009 Não é possível restaurar, pois, não há como pular para o LSN 009
010 Não é possível restaurar, pois, não há como pular para o LSN 010

Como o log foi truncado no LSN 008, o backup de Log BDLog02 não irá contemplar os LSNs 006, 007 e 008 que são os LSNs subsequentes ao backup de Log BDLog01. Assim é impossível restaurar o banco em uma posição que contemple esses LSNs, ou seja, não será possível restaurar o banco em nenhum horário entre às 10:00 (LSN 005) e às 16:00 (LSN 009). Embora os LSNs 009 e 010 estejam contemplados no backup de Log BDLog02, também não será possível restaurá-los, pois, a restauração de backups de log não permite que sequências de log sejam quebradas. Se o Backup Full (BDFull) e o primeiro backup de Log (BDLog) forem restaurados, o banco ficará parado no LSN 005 às 10:00. Não será possível simplesmente "pular" das 10:00 (LSN 005) para às 16:00 (LSN 009), pois, nesse tempo houve atividades no banco de dados que não podem ser desconsideradas. Podemos ver que o LSN 006 cria a tabela EmpregadoMes e que o LSN 009 faz um INSERT nessa tabela. Se os LSNs 006, 007 e 008 pudessem ser ignorados, o LSN 009 iria gerar um erro, pois, se o LSN 006 foi ignorado, a tabela EmpregadoMes não exisitiria. A verdade é que mesmo que essa tabela fosse criada em um LSN contemplado no backup (digamos o LSN 004), não é possível fazer a restauração, pois, a única forma de garantir a consistência é passando por todos os LSNs contemplados sem "pular" (ou melhor desconsiderar) etapas da história do banco de dados. A verdade é que no momento em que se trunca o Log todo o processo de restauração é invalidado a partir do último backup de log antes do truncate (no caso após o LSN 005 o banco não pode ser recuperado).

Observa-se que um simples comando de backup com a opção truncate_only inviabilizou parte do processo de restauração. Inegavelmente o truncate do log de transações liberou espaço e talvez tenha "resolvido o problema do log de transações grande demais". Sim, essa é justamente a parte que todos conhecem e esperam como resultado do comando. Se o Shrink for executado em seguida, a área livre do arquivo de Log será devolvida ao Windows fazendo com que o arquivo seja efetivamente reduzido. O que maioria das pessoas efetivamente não avalia ou simplesmente desconhece é o efeito colateral desse comando. De fato ele liberou espaço, mas acabou de invalidar parte do processo de restauração. Possivelmente o executor do comando não irá descobrir isso após truncar o log, mas sim em uma situação futura quando seu banco de dados estiver com problemas e ele precisar restaurar um backup. Nesse hora ele encherá o peito e falará a si próprio (ou ao chefe se ele estiver próximo): "Tenho uma rotina de backups full e de Log e posso portanto restaurar o backup em qualquer posição desejada". Mal sabe ele que após rodar o inocente comando de truncate do log sua rotina de backups de Log foi jogada no lixo. O problema é que essa descoberta se dará no momento em que o backup será o mais imprescindível possível e infelizmente não será possível contar com ele.

Dada as limitações atuais do Spaces, não pude publicar nesse artigo um exemplo prático. Eu o apresento logo a seguir na parte II do artigo.

[ ]s,

Gustavo

Piores Práticas – Uso do COUNT(*)

Olá Pessoal,

Duas das minhas recentes postagens trataram a respeito de JOINS (Será que LEFT OUTER JOIN, RIGHT OUTER JOIN, *= e =* são sinônimos ? e OUTER JOIN com mais de duas tabelas ? Será que está mesmo "certo" ?). A razão desses posts terem sido escritos deu-se por conta de uma consultoria que realizei em um importante órgão do governo. Na ocasião da migração do SQL Server 2000 para o SQL Server 2005, alguns problemas aconteceram e mesmo fugindo um pouco ao escopo, me encarreguei de fazer uma documentação sobre o funcionamento desses operadores em certas "circunstâncias". Ainda na mesma consultoria, apareceu algumas outras "surpresas" relacionadas ao COUNT(*). Não me surpreendi, pois, já havia visto a "surpresa" em outro órgão público que trabalhei e que também surpreendeu alguns analistas. Não é praga de órgão público não, pois, vejo isso acontecendo todos os dias em vários fóruns e comunidades e lógico em empresas privadas também. Vejamos através de um script baseado no clássico OLTP de pedidos. Há algumas práticas de desnormalização na tabela de ItensPedido que não constituem a melhor prática, mas que são irrelevantes para esse artigo.

CREATE TABLE Clientes (
    ClienteCodigo INT,
    ClienteNome VARCHAR(80),
    Cidade VARCHAR(50))

CREATE TABLE Pedidos (
    PedidoCodigo INT, ClienteCodigo INT,
    PedidoData SMALLDATETIME, PedidoFrete SMALLMONEY)

CREATE TABLE ItensPedido (
    ItemSeq INT, PedidoCodigo INT, ProdutoNome VARCHAR(50),
    Quantidade INT, PrecoUnitario SMALLMONEY)

INSERT INTO Clientes VALUES (1,‘Wendell’,‘Goiânia’)
INSERT INTO Clientes VALUES (2,‘Bianca’,‘São Paulo’)
INSERT INTO Clientes VALUES (3,‘Gabriel’,‘Rio de Janeiro’)
INSERT INTO Clientes VALUES (4,‘Carol’,‘Porto Alegre’)

INSERT INTO Pedidos VALUES (1,1,‘20090328’,5.89)
INSERT INTO Pedidos VALUES (2,1,‘20090329’,15.54)
INSERT INTO Pedidos VALUES (3,1,‘20090330’,25.80)
INSERT INTO Pedidos VALUES (4,2,‘20090329’,15.00)
INSERT INTO Pedidos VALUES (5,2,‘20090331’,35.00)
INSERT INTO Pedidos VALUES (6,3,‘20090327’,0.00)
INSERT INTO Pedidos VALUES (7,3,‘20090330’,5.63)

INSERT INTO ItensPedido VALUES (01,1,‘Sabonete’,1,5.25)
INSERT INTO ItensPedido VALUES (02,1,‘Shampoo’,2,23.42)
INSERT INTO ItensPedido VALUES (03,1,‘Condicionador’,1,15.33)
INSERT INTO ItensPedido VALUES (04,2,‘MP5 Player’,2,250.67)
INSERT INTO ItensPedido VALUES (05,2,‘Mouse’,1,95.99)
INSERT INTO ItensPedido VALUES (06,3,‘Introdução a Banco de Dados’,1,50.00)
INSERT INTO ItensPedido VALUES (07,3,‘XML – Teoria e Prática’,1,42.00)
INSERT INTO ItensPedido VALUES (08,3,‘Destimificando SOA’,1,78.00)
INSERT INTO ItensPedido VALUES (09,3,‘PHP e MySQL’,1,89.00)
INSERT INTO ItensPedido VALUES (10,3,‘Visual Studio – Técnicas Avançadas’,1,125.00)
INSERT INTO ItensPedido VALUES (11,4,‘Whisky Blue Label’,1,659.00)
INSERT INTO ItensPedido VALUES (12,4,‘Vodka Stolichnaya Black – 1000 ml’,2,69.99)
INSERT INTO ItensPedido VALUES (13,5,‘Nintendo Wii’,1,1399.00)
INSERT INTO ItensPedido VALUES (14,6,‘Academia Particular Acadmix Premium’,1,899.99)
INSERT INTO ItensPedido VALUES (15,6,‘Bicicleta Mountain Bike Caloi 10 Aro 26 12v’,1,639)
INSERT INTO ItensPedido VALUES (16,6,‘Esteira Elétrica Premium Caloi CLE30’,2,1549.00)
INSERT INTO ItensPedido VALUES (17,6,‘Barraca Indy Nautika’,1,369.00)
INSERT INTO ItensPedido VALUES (18,6,‘Piscina Master Oval 2600 Litros’,2,419.99)
INSERT INTO ItensPedido VALUES (19,6,‘Piscina Master 7.800L’,1,514.00)
INSERT INTO ItensPedido VALUES (20,7,‘Camaleão – BOX 17 CDs – Ney Matogrosso’,1,329.90)

Uma vez que as tabelas estejam devidamente populadas, algumas consultas com o uso do COUNT(*) podem ser formuladas

— Contar o total de Clientes
SELECT COUNT(*) As TotalClientes FROM Clientes;

— Contar o total de Pedidos
SELECT COUNT(*) As TotalPedidos FROM Pedidos;

— Contar o total de Itens
SELECT COUNT(*) As TotalItens FROM ItensPedido;

— Contar quantos Itens existem em cada Pedido
SELECT PedidoCodigo, COUNT(*) As TotalItensPedido FROM ItensPedido
GROUP BY PedidoCodigo

Não é preciso tentar descobrir o que há errado. Simplesmente não há nada de errado. As consultas estão perfeitamente elaboradas e retornam os resultados corretos. Alguns podem pensar que o COUNT(*) poderia ser substituído por COUNT(1) ou COUNT(0) como já vi alguns programadores fazerem. Isso é sem dúvida uma falácia, pois, substituir COUNT(*) por expressões como COUNT(0) ou COUNT(1) definitivamente não melhoram o desempenho. Não é esse o ponto onde reside o problema e sem dúvidas as consultas apresentadas não contém nada de errado.

A atenção deve voltar-se para as consultas abaixo:

— Retornar o total de Pedidos por Cliente (Alternativa 1)
SELECT ClienteNome, COUNT(*) As TotalPedidos
FROM Clientes As C
INNER JOIN Pedidos As P ON C.ClienteCodigo = P.ClienteCodigo
GROUP BY ClienteNome;

— Retornar o total de Pedidos por Cliente (Alternativa 2)
SELECT ClienteNome, COUNT(*) As TotalPedidos
FROM Clientes As C
LEFT OUTER JOIN Pedidos As P ON C.ClienteCodigo = P.ClienteCodigo
GROUP BY ClienteNome;

Se a primeira utiliza o operador INNER JOIN e a segunda opta pelo operador LEFT OUTER JOIN é possível que diferenças sejam apresentadas (e nesse caso são apresentadas). O resultado das consultas é expresso abaixo:

Uso do INNER JOIN

ClienteNome

TotalPedidos

Bianca

2

Gabriel

2

Wendell

3

Uso do LEFT OUTER JOIN

ClienteNome

TotalPedidos

Bianca

2

Carol

1

Gabriel

2

Wendell

3

Como era previsto, o operador INNER JOIN só retornou os clientes que possuem pedidos, ou seja, aqueles clientes cujo ClienteCodigo esteja presente tanto na tabela de Clientes quanto na tabela de pedidos. O uso do operador LEFT OUTER JOIN retornou todos os clientes, ou seja, todos os registros da esquerda (Clientes) quer tenham ou não correspondentes na tabela de Pedidos através da coluna ClienteCodigo.

Se os resultados forem analisados, há uma fato muito curioso no uso do LEFT OUTER JOIN. De fato os quatro clientes foram retornados (Bianca, Carol, Gabriel e Wendell) como era de se esperar, mas será que as quantidades estão de fato corretas ? Se o uso do INNER JOIN exclui a Carol do resultado é de se esperar que ela não tenha nenhum pedido (e de fato não tem), mas se for observado o LEFT OUTER JOIN inclui Carol como se ela tivesse um pedido (mesmo não tendo). As consultas abaixo mostram essa contradição.

— Retornar o total de Pedidos por Cliente (Apenas a Carol)
SELECT ClienteNome, COUNT(*) As TotalPedidos
FROM Clientes As C
LEFT OUTER JOIN Pedidos As P ON C.ClienteCodigo = P.ClienteCodigo
WHERE ClienteNome = ‘Carol’
GROUP BY ClienteNome;

— Retornar todos os pedidos de Carol
SELECT * FROM Pedidos
WHERE ClienteCodigo IN
(SELECT ClienteCodigo FROM Clientes WHERE ClienteNome = ‘Carol’)

Como pode a primeira consulta mostrar que Carol possui um pedido e a segunda consulta não retornar os pedidos de Carol ? É sem dúvida um paradoxo no qual inclina-se a acreditar que apenas uma das consultas pode de fato estar correta. Na verdade o erro não reside no paradoxo ou em um hipotético BUG mas sim na forma que a consulta foi construída. Eis a armadilha que o COUNT(*) pode ocultar.

O resultado pode ser revelador mas por que ele acontece ? A resposta é bem simples, vejamos por etapas.

— Retornar todos os clientes e seus pedidos (existentes ou não)
SELECT ClienteNome, PedidoCodigo
FROM Clientes As C
LEFT OUTER JOIN Pedidos As P ON C.ClienteCodigo = P.ClienteCodigo;

O resultado é exposto abaixo:

ClienteNome

PedidoCodigo

Wendell

1

Wendell

2

Wendell

3

Bianca

4

Bianca

5

Gabriel

6

Gabriel

7

Carol

NULL

Considerando que o LEFT OUTER JOIN foi utilizado é esperado que todos os clientes e seus pedidos sejam retornados e no caso dos clientes sem pedidos, o NULL é apresentado para a coluna PedidoCodigo. A presença do COUNT(*) irá fazer o que esse operador se dispõe a fazer, ou seja, contar o total de linhas existentes para cada clientes. Qual seria então o total de linhas para cada cliente observando-se a tabela acima ? Podemos ver que Wendell possui 3 linhas, Bianca 2 linhas, Gabriel 2 linhas e Carol possui uma linha.

Pode parecer errado mas está correto. Sem dúvida Carol possui uma linha. Não se trata de um pedido já que PedidoCodigo é nulo, mas se trata de uma linha e o uso do COUNT(*) se propõe a contar linhas e não pedidos. A contagem de linhas está correta e o paradoxo citado anteriormente é perfeitamente compreensível. Carol possui uma linha, mas não possui pedidos.

A "surpresa" está demonstrada e a solução torna-se óbvia, ou seja, não utilize expressões do tipo COUNT(*). Sempre que possível especifique os campos que você se propõe a contar.

— Retornar o total de Pedidos por Cliente (Apenas a Carol)
SELECT ClienteNome, COUNT(PedidoCodigo) As TotalPedidos
FROM Clientes As C
LEFT OUTER JOIN Pedidos As P ON C.ClienteCodigo = P.ClienteCodigo
GROUP BY ClienteNome;

Agora que a consulta teve as devidas adaptações, segue o resultado "correto":

ClienteNome

PedidoCodigo

Bianca

2

Carol

0

Gabriel

2

Wendell

3

A contagem dessa vez foi feita sobre um campo específico (PedidoCodigo) e como Carol não possui pedidos não pode ter uma contagem diferente de zero como é mostrado no resultado. Isso porque a consulta se propôs a contar o total de pedidos (COUNT sobre PedidoCodigo) e não o total de linhas (COUNT sobre o *). Pode parecer "inocente", mas fica explícito que COUNT(*) e COUNT(Coluna) nem sempre tem o mesmo resultado.

Pode ser que o leitor desse artigo ponha a mão na cabeça e pense "Puxa quantos relatórios errados eu já fiz nessa vida" ou ainda "Amanhã eu tenho que corrigir a consulta do relatório do diretor" mas antes que pensamentos como esse venham a mente não se desespere. Essa divergência só ocorre se algumas condições forem obedecidas.

 

COUNT(*)

COUNT(X)

Explicação

INNER JOIN
(A e B)

Contagem de linhas ou registros correspondentes

Contagem de linhas ou registros correspondentes

Nesse caso o INNER JOIN só retornará registros que existam nas tabelas A e B e não há perigo da contagem de linhas divergir da contagem de correspondentes com base na coluna "X".

OUTER JOIN
(A e B)

Contagem de linhas

Contagem de registros correspondentes

Nesse caso o COUNT(*) fará a contagem de linhas enquanto o COUNT(X) fará a contagem de registros correspondentes com base na coluna "X". Para registros não correspondentes a contagem de linhas irá divergir da contagem de colunas. Vale a pena lembrar que se o operador for LEFT OUTER JOIN, a coluna "X" tem de ser da tabela à direita (no caso B). Se o operador for o RIGHT OUTER JOIN, a coluna "X" tem de ser da tabela à esquerda (no caso B). Utilizar uma coluna do mesmo lado do OUTER JOIN é sinônimo do COUNT(*). Alternativas como COUNT(1) ou COUNT(0) também tem o mesmo efeito do COUNT(*).

E quando o COUNT(*) é permitido ?

Normalmente é sempre bom evitá-lo e utilizar o nome de colunas. Não que usar o COUNT(*) incorra em problemas de desempenho, mas utilizar o nome de uma coluna pode evitar que "surpresas" aconteçam. Se o objetivo for contar o total de linhas de uma tabela ou caso as tabelas estejam relacionadas com INNER JOIN (ou via cláusula WHERE sem *= e =*) não há problema em utilizá-lo.

Espero que a prática do COUNT(*) seja muito bem pensada antes de utilizada. Tirando o fato de que o COUNT(*) dispensa a necessidade de informar o nome de uma coluna ele só representa desvantagens. Avalie bem antes de utilizá-lo para não se surpreender. Um relatório errado pode render muitas dores de cabeça.

[ ]s,

Gustavo

Piores práticas – Utilizar a cláusula ORDER BY em Views

Bom Dia Pessoal,

Hoje vou falar sobre um péssimo hábito que alguns desenvolvedores e DBAs praticam de vez em quando (sobretudo os que só utilizam ferramentas gráficas) que é o de colocar a cláusula ORDER BY em Views. Não posso dizer que nunca fiz isso (mas já faz muito tempo) e que certamente alguns dos que lêem esse artigo também o fizeram (ou ainda fazem). O que há de tão ruim em utilizar uma cláusula ORDER BY em uma View ? Não se pode desejar obter o resultado já ordenado ? Não posso deixar de reconhecer que isso facilita em alguns aspectos (sobretudo os de formatação), mas nada mais justo de explicar o porquê de caracterizar a ordenação de dados em Views como uma pior prática.

Antes de iniciar, como é de praxe, um script pode dizer mais que mil palavras. O script abaixo cria uma tabela com vários dados nos quais as explicações serão baseadas:

— Cria uma tabela de clientes com cerca de 10.000 linhas
CREATE TABLE tblClientes (
    ID INT IDENTITY(1,1), Nome VARCHAR(50),
    NomeDoMeio VARCHAR(50), SobreNome VARCHAR(50),
    CPF CHAR(11), DataCad SMALLDATETIME, CEP CHAR(8))

— Cria tabelas auxiliares para ajudar a "gerar" os registros
DECLARE @Nome TABLE (Nome VARCHAR(50))
DECLARE @NomeDoMeio TABLE (NomeDoMeio VARCHAR(50))
DECLARE @SobreNome TABLE (SobreNome VARCHAR(50))

— Insere os registros necessários para "gerar" os Clientes
INSERT INTO @Nome VALUES (‘Bárbara’)
INSERT INTO @Nome VALUES (‘Íris’)
INSERT INTO @Nome VALUES (‘Davi’)
INSERT INTO @Nome VALUES (‘Bia’)
INSERT INTO @Nome VALUES (‘Gabriela’)
INSERT INTO @Nome VALUES (‘Gonçalo’)
INSERT INTO @Nome VALUES (‘Caio’)
INSERT INTO @Nome VALUES (‘Yasmin’)
INSERT INTO @Nome VALUES (‘Aline’)
INSERT INTO @Nome VALUES (‘Larissa’)

INSERT INTO @NomeDoMeio VALUES (‘Bernado’)
INSERT INTO @NomeDoMeio VALUES (‘Prado’)
INSERT INTO @NomeDoMeio VALUES (‘Sallas’)
INSERT INTO @NomeDoMeio VALUES (‘Romualdo’)
INSERT INTO @NomeDoMeio VALUES (‘Tuma’)
INSERT INTO @NomeDoMeio VALUES (‘Antunes’)
INSERT INTO @NomeDoMeio VALUES (‘Ribeiro’)
INSERT INTO @NomeDoMeio VALUES (‘Rodrigues’)
INSERT INTO @NomeDoMeio VALUES (‘Macedo’)
INSERT INTO @NomeDoMeio VALUES (‘Mendes’)

INSERT INTO @SobreNome VALUES (‘Pereira’)
INSERT INTO @SobreNome VALUES (‘Silva’)
INSERT INTO @SobreNome VALUES (‘Souza’)
INSERT INTO @SobreNome VALUES (‘Gonçalves’)
INSERT INTO @SobreNome VALUES (‘Costa’)
INSERT INTO @SobreNome VALUES (‘Garcia’)
INSERT INTO @SobreNome VALUES (‘Ferreira’)
INSERT INTO @SobreNome VALUES (‘Matarazzo’)
INSERT INTO @SobreNome VALUES (‘Melo’)
INSERT INTO @SobreNome VALUES (‘Gomes’)

— Gera os registros de clientes
INSERT INTO tblClientes (Nome, NomeDoMeio, SobreNome)
SELECT Nome, NomeDoMeio, SobreNome
FROM @Nome CROSS JOIN @NomeDoMeio CROSS JOIN @SobreNome
ORDER BY NewID()

— Popula os demais campos de clientes de forma aleatória
UPDATE tblClientes SET
    DataCad = DateADD(DD,-ABS(CHECKSUM(NewID())) / 100000,GETDATE()),
    CPF = CONVERT(CHAR(1),ABS(CHECKSUM(NewID())) % 10) +
        CONVERT(CHAR(1),ABS(CHECKSUM(NewID())) % 10) +
        CONVERT(CHAR(1),ABS(CHECKSUM(NewID())) % 10) +
        CONVERT(CHAR(1),ABS(CHECKSUM(NewID())) % 10) +
        CONVERT(CHAR(1),ABS(CHECKSUM(NewID())) % 10) +
        CONVERT(CHAR(1),ABS(CHECKSUM(NewID())) % 10) +
        CONVERT(CHAR(1),ABS(CHECKSUM(NewID())) % 10) +
        CONVERT(CHAR(1),ABS(CHECKSUM(NewID())) % 10) +
        CONVERT(CHAR(1),ABS(CHECKSUM(NewID())) % 10) +
        CONVERT(CHAR(1),ABS(CHECKSUM(NewID())) % 10) +
        CONVERT(CHAR(1),ABS(CHECKSUM(NewID())) % 10),
    CEP = CONVERT(CHAR(1),ABS(CHECKSUM(NewID())) % 10) +
        CONVERT(CHAR(1),ABS(CHECKSUM(NewID())) % 10) +
        CONVERT(CHAR(1),ABS(CHECKSUM(NewID())) % 10) +
        CONVERT(CHAR(1),ABS(CHECKSUM(NewID())) % 10) +
        CONVERT(CHAR(1),ABS(CHECKSUM(NewID())) % 10) +
        CONVERT(CHAR(1),ABS(CHECKSUM(NewID())) % 10) +
        CONVERT(CHAR(1),ABS(CHECKSUM(NewID())) % 10) +
        CONVERT(CHAR(1),ABS(CHECKSUM(NewID())) % 10)

O script acima produziu uma tabela de 1000 clientes com registros e colunas completamente aleatórios. O primeiro passo é tentar criar uma View ordenada sobre os registros em questão através do script abaixo:

CREATE VIEW vClientes
AS
SELECT
    Nome + ‘ ‘ + NomeDoMeio + ‘ ‘ + SobreNome AS NomeCompleto,
    CPF, DataCad, CEP FROM tblClientes
ORDER BY NomeCompleto

Embora o SELECT não tenha absolutamente nada de errado (basta executá-lo separadamente), ao executar o script de criação da View, um erro é retornado

Msg 1033, Level 15, State 1, Procedure vClientes, Line 7
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

A mensagem de erro é bem clara e a cláusula ORDER BY não pode ser utilizada em Views, InLine Functions, Derived Tables, Subqueries e CTEs a menos que a cláusula TOP ou FOR XML seja especificada. Essa clara limitação (não é exclusiva do SQL Server) impede que Views possam retornar seus dados de forma ordenada.

Um pouco de conceitos SQL ANSI

A SQL é uma linguagem que atua sobre conjuntos e não sobre linhas. Ela preocupa-se em recuperar e gravar conjuntos sem fazer qualquer distinção entre linhas individualmente. Claro que é possível recuperar um conjunto de uma linha ou ainda gravar um conjunto de uma linha, mas mesmo nesse caso ainda se trabalha em conjuntos. A manipulação individual de registros não é prerrogativa da SQL. Para esta, não há nenhum sentido em manter a ordem de um conjunto de registros ou ainda conhecer a posição individual de cada linha. Uma consulta como "Recupere todos os clientes cujo nome do meio seja Mendes" trará sempre o mesmo resultado independente desse resultado estar ordenado por CPF ou qualquer outra coluna. Não faria sentido por exemplo efetuar um JOIN entre os "clientes ordenados por CPF" com qualquer outra tabela. Bastaria apenas fazer o JOIN de clientes com outra tabela, pois, a ordem não afetaria os clientes participantes do JOIN e nem o seu resultado.

A única situação que a ordem importa, ou melhor dizendo, que ela é necessária são situações que envolvam o ranqueamento. Se desejarmos obter os 10 clientes que mais compraram no ano de 2008 é necessário utilizar o ORDER BY sobre uma possível coluna de total ou quantidade. Essa coluna é fundamental para descobrir que são os 10 clientes que mais compraram e nesse caso, a ausência do ORDER BY poderá mudar o resultado da consulta. Para essa situação o ORDER BY se faz necessário, mas não por uma questão estética, mas sim para garantia que os 10 clientes que mais compraram em 2008 são aqueles recuperados por ordem decrescente de total ou quantidade (TOP 10 e ORDER BY Total DESC).

O "erro" das ferramentas gráficas

Ferramentas gráficas como o Enterprise Manager e o SQL Server Management Studio facilitam o desenvolvimento de código de Views introduzindo alguns assistentes para criá-las. Basta apenas escolher as tabelas (opcionalmente realizar as junções desejadas), colunas, expressões calculadas, etc. Nesses assistentes há a possibilidade de utilizar ordenação visto que conforme descrito anteriormente, em situações de ranqueamento ela é necessária. O erro desses assistentes é dar a impressão ao usuário de que os dados podem ser ordenados por uma questão meramente estética ou facilitar sua apresentação. Para não violar as regras de criação de Views, normalmente os assistentes utilizam o artifício do TOP. Se a View apresentada fosse criada por um desses assistentes, possivelmente teria o código parecido com o abaixo:

CREATE VIEW vClientes
AS
SELECT TOP
100 PERCENT
    Nome + ‘ ‘ + NomeDoMeio + ‘ ‘ + SobreNome AS NomeCompleto,
    CPF, DataCad, CEP FROM tblClientes
ORDER BY NomeCompleto

Conseqüências no SQL Server 2000

Se você estiver utilizando o SQL Server 2000 e emitir um comando de SELECT contra a View vClientes, poderemos visualizar o plano de execução dessa consulta onde a ordenação é bem clara.

O SELECT na View vClientes produziu um plano que leu os 1.000 registros da tabela tblClientes e os ordenou com base no nome completo. É interessante perceber que se a consulta tem um custo total de 100%, cerca de 43% desse esforço foi apenas para ordenar os resultados. Isso mostra o quanto o custo de ordenação (SORT) pode ser oneroso.

Não haveria a menor diferença entre colocar o ORDER BY na View ou retirá-lo da View e colocá-lo no SELECT. Se o ORDER BY é realmente necessário ele terá que ser contemplado quer seja dentro do código da View ou na instrução SELECT feita contra a View. O problema de embutir o ORDER BY dentro da View é que os seus resultados sempre serão ordenados pelas colunas especificadas na cláusula ORDER BY. Isso é prejudicial, pois, caso alguém solicite os dados da View, mas queira alterar a ordem colocando um outro ORDER BY, haverá um duplo SORT. Ex:

SELECT * FROM vClientes ORDER BY DataCad

Como a View ordena por NomeCompleto e a instrução de SELECT ordena por DataCad, haverá a ordenação por NomeCompleto e em seguida a ordenação por DataCad produzindo o plano abaixo:

Se for observada a instrução SELECT, no final das contas, o que importa é a ordenação final (DataCad). Como a ordenação por nome completo está embutida na View, ela também foi realizada, mas na prática seus efeitos foram desprezados, pois, ao final os resultados são exibidos com base na ordem de data de cadastro (DataCad).

Esse plano mostra que a ordenação embutida dentro de uma view é prejudicial, pois, pode aumentar a ocorrência de um duplo SORT e como sabemos o custo de ordenação é elevado. No exemplo em questão, a ordenação por nome completo tem um custo de 33% do resultado da consulta e esse custo é literalmente inútil. Essa consulta poderia ser feita gastando-se 67% dos recursos se o ORDER BY não estivesse na View. Vale a pena lembrar que esse custo inútil de ordenação também terá impactos toda vez que a tabela for utilizada em JOINs. Pode ser que o JOIN recebe os dados em uma certa ordem, mas opte por atribuir uma outra ordem mais interessante para a junção. Nesse caso teríamos outra situação de duplo SORT.

Conseqüências para o SQL Server 2005 e 2008

Se você estiver utilizando o SQL Server 2005 ou 2008, terá uma surpresa ao executar uma consulta contra a View. É nítido que a ordem dos registros com base no nome completo não é respeitada mesmo que a View tenha forçado essa ordenação. Isso ocorre porque o otimizador é esperto o suficiente para perceber que um código do tipo TOP 100 PERCENT representa todos os registros e nesse caso ele e sua respectiva cláusula ORDER BY podem ser completamente ignorados uma vez que o melhor é que o SQL Server recupere os dados da forma que achar mais conveniente. Isso pode ser claramente representado no plano de execução.

Como podemos observar é feito um SCAN na tabela tblClientes e logo após  é feita uma operação do tipo Compute Scalar (responsável por fazer a concatenação para gerar o nome completo) e por fim o SELECT para exibir os resultados. O ORDER BY foi literalmente desprezado.

O próximo passo é verificar como o SQL Server 2005 e 2008 se comportam com a ordenação interna na View (Nome Completo) seguida da ordenação externa (Data Cadastro).

SELECT * FROM vClientes ORDER BY DataCad

Se utilizarmos uma ordenação externa à View, perceberemos que o duplo SORT não ocorre. Embora a View tenha em seu corpo o uso de um ORDER BY, esse foi completamente ignorado e apenas o ORDER BY externo foi utilizado.

Isso significa dizer que no SQL Server 2005 e 2008, se uma cláusula ORDER BY for adicionada na View com o artifício de TOP 100 PERCENT essa cláusula é completamente ignorada já que afinal o duplo sort é maléfico (e inútil) para um bom desempenho. Ainda que não haja o duplo SORT, acreditar que o ORDER BY com TOP 100 PERCENT irá retornar os dados ordenados é um equívoco, pois, como vimos no plano anterior, o operador SORT não aparece no plano. Mesmo que a View possua o ORDER BY os registros não serão recuperados na ordem.

Se você possui o SQL Server 2000, não deve colocar o ORDER BY na View com o TOP 100 PERCENT, pois, isso pode levar ao problema do duplo SORT. Se você possui o SQL Server 2005 ou 2008 não deve colocar o ORDER BY na View com o TOP 100 PERCENT simplesmente porque ele não tem nenhum efeito e pode ser desagradável você supor que os registros virão em ordem quando na verdade isso não acontece. A melhor prática é criar Views sem a cláusula ORDER BY com o TOP 100 PERCENT e colocar o ORDER BY de forma externa, ou seja, no comando de SELECT.

Alguns argumentarão que o custo de duplo SORT pode ser desprezado, já que um SELECT idealmente deve retornar poucos registros e ele não faria diferença. Há também outro argumento de que o duplo SORT só ocorre se a View possuir uma ordem e se o SELECT mudar essa ordem. O último argumento irá defender que a utilização de índices irá diminuir os custos de SORT se eles ocorrerem. Embora corretos, são argumentos típicos daqueles que se recusam a rever suas boas práticas de código, mas à medida que o SQL Server 2000 está cada vez mais distante da realidade atual e as instalações contemplam o SQL Server 2005 e 2008, o duplo SORT ou até a recuperação ordenada para um JOIN não são mais os grandes problemas. Se o SQL Server 2005 e 2008 forem utilizados temos a certeza de que o ORDER BY com o TOP 100 PERCENT não fará de fato nenhum efeito. Aqueles que o utilizam pensarão que a View está ordenada quando na verdade não está.

Uma pergunta que pode surgir é o que aconteceria com situações que envolvam ranqueamento. Nesse caso não há problema, pois, a ordem é necessária para garantir os registros corretos e será respeitada. Um exemplo pode ser demonstrado conforme o script abaixo:

CREATE VIEW vTOP10ClientesAntigos
AS
SELECT TOP
10 WITH TIES
    Nome + ‘ ‘ + NomeDoMeio + ‘ ‘ + SobreNome AS NomeCompleto,
    CPF, DataCad, CEP FROM tblClientes
ORDER BY DataCad

Para descobrir quem são os dez clientes mais antigos é necessário verificar quais são os dez clientes que tem a data de cadastro mais antiga. Esse é o código que a View se propõe a executar e são retornados os clientes mais antigos. Em todo caso, o que aconteceria se fosse necessário retornar os 10 clientes mais antigos, mas ordenados por NomeCompleto ? Nesse caso um SELECT sobre a View com a cláusula ORDER BY é a escolha óbvia.

SELECT * FROM vTOP10ClientesAntigos ORDER BY NomeCompleto

O plano de execução dessa consulta é exibido logo abaixo:

Se observarmos o plano de execução perceberemos as seguintes tarefas:

  • O operador Table SCAN varre todos os registros da tabela de clientes
  • O operador SORT organiza os registros por ordem de data de cadastro
  • O operador Compute Scalar monta o nome completo contaneando as partes individuais do nome
  • O operador TOP é aplicado para filtrar apenas os 10 primeiros registros
  • O operador SORT organiza o resultado por ordem de nome

Embora possa parecer que há um custo de ordenação desnecessário já que os resultados finais são exibidos por ordem de nome, o plano de execução acima não está errado. Ele está correto e é exatamente o mesmo independente de ser executado no SQL Server 2000, 2005 ou 2008. Ocorre que o primeiro SORT é necessário para descobrir quem são os 10 clientes mais antigos, ou seja, ele realmente é imprescindível para que esse conjunto de 10 clientes seja montado e não se trata de mera estética para exibir os dados ordenados. Já o segundo SORT é realizado apenas para retornar o conjunto dos 10 clientes de forma ordenada por nome completo.

O ORDER BY em Views é incorreto ou desnecessário quando utilizado com o único objetivo de formatar resultados, mas se houver um ranqueamento envolvido ele é imprescindível. Com o avanço do SQL Server 2005 e 2008, utilizar ORDER BY em Views é claramente uma péssima prática e que não retorna o resultado esperado.

[ ]s,

Gustavo

Piores práticas – Utilização do operador NOT IN

Boa Madrugada Pessoal,

Estou aqui enrolado em uma pleno sábado (na verdade já é domingo) trabalhando em um projeto de migração de uma única base de dados. A base em si é bem tranqüila (195MB só), o problema é que há integrações via Linked Servers, pacotes SSIS, COM+ e muitos executáveis não mapeados. Por conta dessa migração, estou aqui aguardando que certos procedimentos sejam executados para verificar se tudo aparentemente funcionou (com certeza na segunda-feira aparecerão outros problemas). Não tive tempo de escrever aqueles imensos artigos que posto semanalmente então hoje falarei de algo mais suscinto. Estou postando um artigo da categoria "piores práticas" que certamente é bem conhecida.

Antes de relatar os pontos negativos da utilização do NOT IN, é necessário como de costume fazer um script de demonstração.

— Cria uma tabela T1
CREATE TABLE T1 (ID INT, NOME CHAR(400))

— Popula a tabela T1 com 1 milhão de registros
DECLARE @i INT
SET @i = 1

WHILE @i <= 1000000
BEGIN
    INSERT INTO T1 VALUES (@i,REPLICATE(CHAR(ABS(CHECKSUM(NewID())/10000000)),400))
    SET @i = @i + 1
END

— Cria uma tabela T2 com 950 mil de registros aleatórios com base em T1
SELECT TOP(1950000) * INTO T2 FROM T1 ORDER BY NewID()

Se T1 possui um milhão de registros, se T2 possui 950.000 registros e T1 possui tudo que T2 possui, então T1 possui 50.000 registros que T2 não possui. Para descobrirmos que registros são esses, basta realizar uma query que nos diga o que há em T1 que não tenha em T2. A utilização do NOT IN é certamente a escolha mais natural possível.

SELECT ID, NOME FROM T1
WHERE ID NOT IN (SELECT ID FROM T2)

No meu notebook essa instrução demorou 7 segundos para terminar e um péssimo plano de execução. Tenho de reconhecer que é uma consulta muito simples e bastante intuitiva, pois, entre outras coisas ela simplesmente retorna todos os registros em T1 cujo o ID não esteja em T2. Vejamos algumas alternativas para o uso do NOT IN e seus respectivos tempos.

— Uso do NOT EXISTS
SELECT ID, NOME FROM T1
WHERE NOT EXISTS (
    SELECT ID FROM T2
    WHERE T1.ID = T2.ID)

— Uso do LEFT OUTER JOIN
SELECT T1.ID, T1.NOME FROM T1
LEFT OUTER JOIN T2 ON T1.ID = T2.ID
WHERE T2.ID IS NULL

— Uso do EXCEPT (Somente 2005 e superiores)
SELECT ID, NOME FROM T1 WHERE ID IN (
    SELECT ID
FROM T1 EXCEPT SELECT ID FROM T2)

O tempo de consulta de todas as alternativas foi superior ao do NOT IN e se considerássemos todos os 4 comandos em um único BATCH (100%), obteríamos estimativas próximas às estimativas abaixo:

Comando

% do Batch

Tempo

% de Tempo

NOT IN

49%

7s

41%

NOT EXISTS

14%

3s

18%

LEFT OUTER JOIN

14%

3s

18%

EXCEPT

23%

4s

23%

A grande maioria iria argumentar que o NOT IN tem um comportamento mais lento, pois, para cada linha em T1 será necessário ler várias vezes as linhas em T2. Isso não deixa de ser verdade e explicar em boa parte a causa de tanta lentidão, uma vez que as demais construções terão um algoritmo um pouco mais inteligente. A presença de um índice nas colunas IDs irá mudar esse comportamento do NOT IN. Uma vez que o índice seja organizado, não será necessário para cada linha em T1 varrer todas as linhas em T2. Para tornar a situação ainda mais performática, os índices serão clusterizados.

— Cria Índices Cluster em ID
CREATE CLUSTERED INDEX IXT1 ON T1 (ID)
CREATE CLUSTERED INDEX IXT1 ON T2 (ID)

Se repetirmos as consultas, teremos os seguintes resultados:

Comando

Tempo (1ª execução)

Tempo (2ª execução)

NOT IN

18s

3s

NOT EXISTS

2s

2s

LEFT OUTER JOIN

4s

3s

EXCEPT

2s

2s

Todos os comandos foram executados duas vezes porque a primeira vez o plano de execução não está em memória e naturalmente o acesso é mais lento. Na segunda execução, por se tratar exatamente do mesmo comando, o plano de execução já está em memória (cachê) e o acesso é mais rápido. Mesmo assim, embora o NOT IN tenha empatado com o LEFT OUTER JOIN (que foi um dos acessos mais rápidos na ausência do índice) ainda ficou entre os mais lentos sendo 50% (1 segundo) mais lento que os mais rápidos. Isso significa que na melhor das hipóteses, se houver índices, o NOT IN ainda tende a ficar mais lento.

Alguns podem achar que ainda assim, a lentidão não é razão suficiente para deixar de utilizar o NOT IN já que mesmo mais lento, ele apresentou um desempenho aceitável. Não acho isso um argumento muito válido, visto que as demais construções também são bem simples e que se cada consulta rodar um pouco mais lenta que as demais, um sistema com múltiplos usuários irá ter seu desempenho total reduzido. Se essa não for uma razão forte o bastante, vejamos outras limitações do uso do NOT IN.

Os scripts anteriores foram apenas para mensurar o desempenho das consultas com um volume razoável de registros. Os próximos scripts não levaram necessariamente o desempenho, por isso utilizarei outras tabelas. As tabelas anteriores podem ser excluídas para liberar recursos (opcionalmente o serviço pode ser reiniciado).

— Cria as tabelas necessárias
CREATE TABLE T1 (ID INT, NOME VARCHAR(50))
CREATE TABLE T2 (ID INT, NOME VARCHAR(50))

— Insere os registros
INSERT INTO T1 (ID, NOME) VALUES (1, ‘Sandro’)
INSERT INTO T1 (ID, NOME) VALUES (2, ‘Dimitri’)
INSERT INTO T1 (ID, NOME) VALUES (3, ‘Gilvan’)

INSERT INTO T2 (ID, NOME) VALUES (1, ‘Sandro’)
INSERT INTO T2 (ID, NOME) VALUES (2, ‘Dimitri’)

Visivelmente podemos perceber que T1 possui um registro a mais que T2 cujo nome é Gilvan. Aplicar o NOT ou qualquer uma das demais alternativas deve retornar esse nome, pois, é o único registro que está em T1 e não está em T2. De fato, uma consulta com o NOT IN irá retornar (como já era esperado) o nome Gilvan. O que aconteceria no entanto, se aparecesse um registro com o ID Null em T2 ?

INSERT INTO T2 (ID, NOME) VALUES (NULL, ‘Ninguém’)

— Uso do NOT IN
SELECT ID, NOME FROM T1
WHERE ID NOT IN (SELECT ID FROM T2)

— Uso do NOT EXISTS
SELECT ID, NOME FROM T1
WHERE NOT EXISTS (
    SELECT ID FROM T2
    WHERE T1.ID = T2.ID)

— Uso do LEFT OUTER JOIN
SELECT T1.ID, T1.NOME FROM T1
LEFT OUTER JOIN T2 ON T1.ID = T2.ID
WHERE T2.ID IS NULL

— Uso do EXCEPT (Somente 2005 e superiores)
SELECT ID, NOME FROM T1 WHERE ID IN (
    SELECT ID FROM T1 EXCEPT SELECT ID FROM T2)

Ao contrário do esperado, o NOT IN não retornou nenhum registro enquanto que as demais alternativas retornaram o registro Givan conforme o esperado. Qual seria a razão para que o NOT IN não retornasse o nome Gilvan já que visivelmente ele não está em T2 ? A razão é bem simples. Toda vez que o NULL é comparado (salvo raríssimas exceções), a comparação é avaliada como falsa. Comparar um valor nulo com qualquer outro valor retornará falso. O valor nulo é algo desconhecido e se compararmos qualquer coisa com algo desconhecido não saberemos se o resultado é ou não verdadeiro (e por isso que o padrão é falso). Assim sendo, ao comparar não somente o nome Gilvan como os demais com o valor NULL a comparação é falsa e por isso nenhum registro é retornado.

Enquanto o NOT IN pode potencialmente retornar resultados indesejados, o mesmo não acontece com as demais alternativas. Seus métodos de comparação passam por etapas e avaliações que não utilizaram a mesma lógica do NOT IN e o nome Gilvan será retornado em todas as demais alternativas. Isso significa que além do desempenho, o NOT pode retornar resultados não esperados (não podemos dizer "errados" já que é uma questão de interpretação da lógica de três valores (verdadeiro, falso e desconhecido)).

Alguns argumentaram que esse tipo de situação é bastante raro visto que dificilmente aparecerá um ID nulo. Concordo que é uma situação difícil de aparecer visto que na maioria das vezes o NOT IN será realizado contra colunas NOT NULL (tipicamente as chaves primárias), mas a situação descrita é uma preocupação a mais ao se utilizar o operador NOT IN enquanto que as demais alternativas dispensam essa preocupação. Mostrarei a seguir outra limitação do uso do NOT IN. As tabelas T1 e T2 podem ser eliminadas.

— Cria tabelas de empregados
CREATE TABLE Empregados (
    IDDepartamento INT,
    IDEmpregado INT,
    Nome VARCHAR(50),
    CONSTRAINT PK_EMP PRIMARY KEY(IDDepartamento, IDEmpregado))

CREATE TABLE EmpregadosDesligados (
    IDDepartamento INT,
    IDEmpregado INT,
    Nome VARCHAR(50),
    CONSTRAINT PK_EMPDesl PRIMARY KEY(IDDepartamento, IDEmpregado))

— Insere os registros necessários
INSERT INTO Empregados VALUES (1,11,‘Rogério’)
INSERT INTO Empregados VALUES (11,1,‘Débora’)
INSERT INTO Empregados VALUES (1,2,‘Júlio’)
INSERT INTO Empregados VALUES (1,3,‘Yves’)

INSERT INTO EmpregadosDesligados VALUES (1,11,‘Rogério’)

Se há uma tabela contendo todos os empregados e uma tabela contendo os empregados desligados, para descobrir os empregados atuais basta apenas realizar uma consulta de todos os registros que estejam em empregados que não estejam em empregados desligados. Assim, todo empregado que não esteja desligado representa uma empregado do quadro da empresa. O operador NOT IN seria uma escolha certa, mas agora há uma limitação. O operador NOT IN é utilizado sobre um campo específico e nesse caso, como a chave primária consiste em duas colunas, ele é limitado. Alguns SGBDs conseguem transpor essa limitação através de extensões. Ex:

SELECT Nome FROM Empregados
WHERE (IDDepartamento, IDEmpregado)
    NOT IN (
        SELECT IDDepartamento, IDEmpregado
        FROM EmpregadosDesligados)

Cabe ressaltar que a construção acima é proprietária e não é ANSI Compliance. Isso significa que há uma chance enorme do comando acima não funcionar na maioria dos SGBDs. O SQL Server por exemplo não contempla a possibilidade acima e nesse caso a limitação do NOT IN em trabalhar com chaves compostas, ou melhor dizendo mais de uma coluna fica ainda mais aparente. A solução normal é tentar a concatenação.

SELECT Nome FROM Empregados
WHERE (
    CAST(IDDepartamento AS VARCHAR(2)) +
    CAST(IDEmpregado AS VARCHAR(2))) NOT IN (
        SELECT
            CAST(IDDepartamento AS VARCHAR(2)) +
            CAST(IDEmpregado AS VARCHAR(2))       
        FROM EmpregadosDesligados)

Ao executar essa consulta, apenas os empregados Júlio e Yves foram recuperados. De fato Rogério havia sido desligado e não deveria ser mostrado, mas e quanto a Débora ? Ela não está cadastrada na tabela de empregado desligados, mas ainda não assim não foi recuperada junto com os demais. Isso se deve ao fato de que a combinação Departamento e Empregado de Rogério (1, 11) resulta na string "111" e a combinação Departamento e Empregado de Débora (11, 1) também resulta na string "111" e como as strings são iguais, Débora não será recuperada. Essa é mais é um preocupação que o NOT IN necessita e que é dispensada pelas demais construções (O EXCEPT também possui a mesma limitação visto que depende do operador IN).

— Uso do NOT EXISTS
SELECT Nome FROM Empregados AS E
WHERE NOT EXISTS (
    SELECT * FROM EmpregadosDesligados AS ED
    WHERE E.IDDepartamento = ED.IDDepartamento AND
    E.IDEmpregado = ED.IDEmpregado)

— Uso do LEFT OUTER JOIN
SELECT E.Nome FROM Empregados AS E
LEFT OUTER JOIN EmpregadosDesligados AS ED
    ON E.IDDepartamento = ED.IDDepartamento AND
    E.IDEmpregado = ED.IDEmpregado
WHERE
    ED.IDDepartamento IS NULL AND ED.IDEmpregado IS NULL

Uma forma de contornar a possível ambiguidade existente no NOT IN é utilizar um delimitador. Ex:

SELECT Nome FROM Empregados
WHERE (
    CAST(IDDepartamento AS VARCHAR(2)) + ‘_’ +
    CAST(IDEmpregado AS VARCHAR(2))) NOT IN (
        SELECT
            CAST(IDDepartamento AS VARCHAR(2)) + ‘_’ +
            CAST(IDEmpregado AS VARCHAR(2))       
        FROM EmpregadosDesligados)

Dessa vez, como esperado, os empregados Júlio, Yves e Débora foram retornados. O uso do caractér "_" desfez a ambiguidade, visto que a string de Rogério é "1_11" e a de Débora é "11_1". Uma vez que essas strings são diferentes, Débora não será excluída da consulta. O problema é que uma construção dessas dificilmente será performática, pois, para cada registro em Empregados será necessário fazer a concatenação para posterior comparação. O mesmo será feito em EmpregadosDesligados. Em outras palavras se Empregados possui X registros e Empregados Desligados possuir Y registros será necessário X * Y comparações. Mesmo que existam índices sobre IDDepartamento e IDEmpregado, os mesmos serão ignorados, pois, o que é comparado é a concatenação e não as colunas. A última vez que presenciei isso, o tempo de execução de uma consulta aumentou de 14 segundos para 5 minutos (foi no Access, mas o problema era exatamente o mesmo).

Acredito que essa limitação do NOT IN aliada ao problema do NULL juntamente com os problemas de desempenho sejam razões suficientes para simplesmente não utilizar esse operador em consultas de alto desempenho que retornem os dados de forma "esperada". As construções NOT EXISTS e LEFT OUTER JOIN contornam os problemas e limitações além de terem na esmagadora maioria dos casos um desempenho superior. O operador EXCEPT sobre certas circunstância também pode ser utilizado para todos os casos (há limitações, mas o conhecimento de como esse operador funciona pode superá-las).

E quando usar o NOT IN então ?

O único ponto positivo do NOT IN em relação às demais construções é que ele é de entendimento muito simples (praticamente óbvio). Eu consideraria utilizá-lo em duas únicas situações: Consultas Seletivas e utilização de Valores Fixos (de preferência as duas em conjunto).

Quando uma consulta já possui outras condições na cláusula WHERE que sejam bastante seletivas, ou seja que retornem poucos valores, a perda de desempenho do NOT IN não fará diferença. E nesse caso (desde que não incorra nos problemas de resultados inesperados ou nas comparações de chaves compostas) o NOT IN pode ser utilizado. No exemplo abaixo, se Campo1 e Campo2 já filtrarem bastante os registros, o NOT IN pode ser utilizado.

SELECT <Campos> FROM Tabela
WHERE Campo1 = <Valor> AND Campo2 = <Valor> AND
Campo3 NOT IN (SELECT Campo FROM Tabela)

O uso do NOT IN para valores fixos também é uma utilização que pode ser considerada. Ex:

SELECT <Campos> FROM Tabela
WHERE Campo NOT IN (1,2)

Se o NOT IN só possuísse desvantagens era bem provável que fosse retirado ou extremamente desaconselhado na documentação. Isso não ocorre porque ele possui seus pontos positivos e é importante saber explorá-los. A recomendação não é simplesmente evitá-lo, mas sim conhecer suas claras desvantagens e evitar sua utilização indiscriminada (principalmente com subqueries).

Agora vou para casa finalmente…

[ ]s,

Gustavo

Piores práticas – Geração de seqüênciais baseados no MAX + 1

Boa Noite Pessoal,

Hoje falarei de mais uma prática ruim relacionada a banco de dados. Digo relacionada a banco de dados, porque o problema que irei descrever não é restrito ao SQL Server. Trata-se de um problema de concorrência que pode ocorrer em qualquer banco de dados e muitas vezes pode passar despercebido. Trata-se da geração de seqüênciais baseada no MAX + 1.

A utilização da técnica MAX + 1 é uma tradução lógica de um algoritmo de incremento. Uma vez que uma tabela tenha o último registro cujo ID seja igual a 3 por exemplo, bastaria capturar o último ID incrementá-lo de uma unidade obtendo-se assim o próximo ID que no exemplo seria o ID 4. O processo é repetido múltiplas vezes e obtêm-se o ID 5, 6, 7, 8 e assim sucessivamente.

Não há nada de errado com esse algoritmo. É uma forma bem prática de obter-se o próximo ID e assim incrementar automaticamente a coluna de ID, código ou qualquer outra que seja autonumerada. O empecilho está na concorrência. Utilizar esse recurso puramente da forma descrita é supor que apenas um usuário por vez utilizará a tabela. Isso é era bem verdade nos anos 80 e início dos anos 90 na qual muitas aplicações eram locais e monousuários (enquanto escrevo essa frase escuto aqui o single Save a Prayer do Duran Duran e acho que estou exatamente nessa época). Se uma aplicação está isolada e destinada à utilização por apenas um usuário, não há nenhum problema de concorrência e a lógica descrita funcionará perfeitamente. A questão é que não estamos mais nos anos 80 e hoje dificilmente se utiliza uma aplicação local e monousuário (durante essa frase já estou ouvindo o remix de Give It To Me da Madonna feito pelo Paul Oakenfold). As aplicações estão disponíveis em diversos pontos de acesso (estações de trabalho, servidores, dispositivos móveis, etc) e com uma quantidade cada vez maior de usuários simultâneos além de online fora de períodos comerciais. Em ambientes como esses, o MAX + 1 definitivamente não tem espaço (mesmo com as devidas adaptações).

Quem já passou pelos problemas do MAX + 1 sabe exatamente do que estou falando. Na verdade nem precisa passar por eles. Basta se perguntar o seguinte: "Se o próximo seqüencial é obtido através do MAX + 1, o que acontece se dois usuários lerem o mesmo registro exatamente na mesma hora ? Será que eles não vão obter o mesmo seqüencial ? Será que isso não vai dar nenhum problema ?". Simulemos então os problemas do MAX + 1 e apontemos às soluções. Nada melhor do que um script. Utilizarei duas conexões que chamarei respectivamente de C1 e C2. Essas conexões podem ser janelas do SQL Server Management Studio, SQLCMD ou alguma aplicação que acesse uma base SQL Server 2005. O primeiro passo é a criação de uma tabela que precise de um seqüencial. Irei utilizar uma fictícia tabela de pessoas.

— Criação da tabela tblPessoas
CREATE TABLE tblPessoas (IDPessoa INT NOT NULL, NomePessoa VARCHAR(50))
ALTER TABLE tblPessoas ADD CONSTRAINT PKPessoa PRIMARY KEY (IDPessoa)

Como há uma chave primária na coluna IDPessoa não podemos utilizar uma trigger tradicional para gerar o ID. Uma vez que a trigger é disparada após o INSERT ter sido realizado, o seqüencial precisa ser gerado antes do INSERT e não depois através da trigger. As saídas para isso é gerar uma procedure de inserção ou a utilização de um trigger INSTEAD OF já que o SQL Server ainda não possui triggers BEFORE. Utilizarei a trigger INSTEAD OF por entender que a geração do seqüencial deve ser garantida para todo o INSERT.

— Criação da trigger de geração de seqüenciais
CREATE TRIGGER trgSeqIDPessoa ON tblPessoas
INSTEAD OF INSERT
AS
BEGIN
    — Obter o maior ID (no caso do primeiro registro retornar zero)
    DECLARE @IDPessoa INT
    SET @IDPessoa = ISNULL((SELECT MAX(IDPessoa) FROM tblPessoas),0)
    SET @IDPessoa = @IDPessoa + 1

    — Obter o nome da Pessoa
    DECLARE @NomePessoa VARCHAR(50)
    SET @NomePessoa = (SELECT NomePessoa FROM INSERTED)

    — Gravar o registro na tabela de pessoas
    INSERT INTO tblPessoas VALUES (@IDPessoa, @NomePessoa)
END

Uma simples instrução INSERT executada algumas vezes pode demonstrar que a trigger funciona perfeitamente e que o campo IDPessoa é autoincrementado através das ações da trigger. Ex:

INSERT INTO tblPessoas (NomePessoa) VALUES (‘Pessoa 1’)
INSERT INTO tblPessoas (NomePessoa) VALUES (‘Pessoa 2’)
INSERT INTO tblPessoas (NomePessoa) VALUES (‘Pessoa 3’)

SELECT IDPessoa, NomePessoa FROM tblPessoas

O que aconterá por exemplo se dois usuários acessarem simultaneamente a base para inserir um novo registro ? Como será que a trigger irá se comportar ? Não há como simular duas inserções simultâneas já que entre as trocas de janela da primeira conexão para a segunda conexão o INSERT já terá sido feito. Para tornar essa simulação possível, o código da trigger será alterado para provocar um DELAY proposital de 5 segundos após a captura do seqüencial. Durante esse DELAY será possível trocar as conexões e simular dois usuários simultâneos.

— Alteração da trigger de geração de seqüenciais
ALTER TRIGGER trgSeqIDPessoa ON tblPessoas
INSTEAD OF INSERT
AS
BEGIN

    — Obter o maior ID (no caso do primeiro registro retornar zero)
    DECLARE @IDPessoa INT
    SET @IDPessoa = ISNULL((SELECT MAX(IDPessoa) FROM tblPessoas),0)
    SET @IDPessoa = @IDPessoa + 1

    — Obter o nome da Pessoa
    DECLARE @NomePessoa VARCHAR(50)
    SET @NomePessoa = (SELECT NomePessoa FROM INSERTED)

    — Força um DELAY proposital de 5 segundos
    WAITFOR DELAY ’00:00:05′

    — Gravar o registro na tabela de pessoas
    INSERT INTO tblPessoas VALUES (@IDPessoa, @NomePessoa)
END

Na conexão C1, execute o seguinte comando para realizar uma inserção.

INSERT INTO tblPessoas (NomePessoa) VALUES (‘Pessoa 4’)

Enquanto a conexão C1 está executando, alterne para a conexão C2 e execute o comando abaixo para realizar outra inserção. Como há o DELAY proposital, o efeito é de como se os usuários estivessem simultâneos.

INSERT INTO tblPessoas (NomePessoa) VALUES (‘Pessoa 5’)

Após aguardar 5 segundos da execução da conexão C2, um erro foi gerado.

Msg 2627, Level 14, State 1, Procedure trgSeqIDPessoa, Line 19
Violation of PRIMARY KEY constraint ‘PKPessoa’. Cannot insert duplicate key in object ‘dbo.tblPessoas’.

The statement has been terminated.

Qual seria a razão para um erro de violação de chave primária ? Se a coluna IDPessoa é chave primária e a trigger é responsável por gerar o seqüencial qual seria a explicação para uma violação de chave primária ? A resposta é simples. Quando a conexão C1 realizou o INSERT, a trigger pesquisou o maior ID que era 3 e o armazenou na variável @IDPessoa. Quando C2 realizou o INSERT, C1 ainda não havia realizado o INSERT, então C2 pesquisou o maior ID que continuava sendo igual a três. Tanto C1 quanto C2 com base no ID 3 produziram o ID igual a 4. Como C1 realizou o INSERT primeiro, ela conseguiu realizar a inserção com o ID 4. C2 realizou o INSERT depois e como o ID 4 já estava cadastrado, houve uma violação de chave primária, pois, o ID não tolera dados em repetição. Se o ID não fosse chave primária, haveria dois registros com o ID igual a 4 (basta retirar a chave primária e fazer um novo teste).

A chave primária conseguiu impedir a presença de registros duplicados, mas um erro foi produzido e o INSERT de C2 foi cancelado. Alguns argumentaram que isso é aceitável já que a chance de dois registros simultâneos aparecerem é pequena e o erro de Primary Key pode ser tratado na aplicação. Tais argumentos podem fazer sentido para pequenas aplicações corporativas mas aplicações de missão crítica certamente irão desbancar tais argumentos. Imagine que um site de comércio eletrônico como a Amazon ou ainda a receita federal (na época da liberação do imposto de renda) mantenham um seqüencial de visitas ao site ? Certamente que a implementação da trigger não funcionaria. Ainda que o erro fosse tratado na aplicação e ressubmetido, essa definitivamente seria uma forte limitação à escalabilidade do site.

A solução parece ser bem simples. É preciso bloquear o MAX + 1 até que o INSERT tenha sido concluído. Se o MAX + 1 for bloqueado não haverá como C1 e C2 pegarem o mesmo ID. A idéia é interessante. Uma prova de conceito envolve a alteração da trigger.

— Alteração da trigger de geração de seqüenciais
ALTER TRIGGER trgSeqIDPessoa ON tblPessoas
INSTEAD OF INSERT
AS
BEGIN
    — Obter o maior ID (no caso do primeiro registro retornar zero)
    — O ID é bloqueado exclusivamente

    DECLARE @IDPessoa INT
    SET @IDPessoa = ISNULL((SELECT MAX(IDPessoa) FROM tblPessoas WITH (XLOCK)),0)
    SET @IDPessoa = @IDPessoa + 1

    — Obter o nome da Pessoa
    DECLARE @NomePessoa VARCHAR(50)
    SET @NomePessoa = (SELECT NomePessoa FROM INSERTED)

    — Força um DELAY proposital de 5 segundos
    WAITFOR DELAY ’00:00:05′

    — Gravar o registro na tabela de pessoas
    INSERT INTO tblPessoas VALUES (@IDPessoa, @NomePessoa)
END

O HINT XLOCK representa um bloqueio exclusivo. Uma vez que o bloqueio exclusivo seja colocado não haverá como duas conexões simultâneas capturarem o mesmo ID. A primeira conexão irá obter o ID, bloqueá-lo e só irá liberar o bloqueio após a realização do INSERT evitando potenciais duplicades e (ou) erros retornados para a aplicação. O teste pode ser feito com novos cadastrados. Na conexão, execute o script abaixo:

INSERT INTO tblPessoas (NomePessoa) VALUES (‘Pessoa 5’)

Enquanto a conexão C1 está executando, alterne para a conexão C2 e execute o comando abaixo para realizar outra inserção. Como há o DELAY proposital, o efeito é de como se os usuários estivessem simultâneos.

INSERT INTO tblPessoas (NomePessoa) VALUES (‘Pessoa 6’)

Após aguardar 5 segundos da execução da conexão C2, tudo parece funcionar perfeitamente. Uma pesquisa na tabela irá demonstrar que os IDs 5 e 6 foram inseridos corretamente e o código funcionou perfeitamente. Os seqüenciais foram gerados conforme desejado. Para uma prova de conceito digna de um ambiente de alta concorrência, um novo teste será realizado. Enquanto a conexão C1 estiver um inserindo um registro, tentaremos consultar um registro já inserido.

INSERT INTO tblPessoas (NomePessoa) VALUES (‘Pessoa 7’)

Enquanto a conexão C1 está executando, alterne para a conexão C2 e execute o comando abaixo para realizar uma atualização. Como há o DELAY proposital, o efeito é de como se os usuários estivessem simultâneos.

UPDATE tblPessoas SET NomePessoa = ‘6a Pessoa’ WHERE IDPessoa = 6

Embora o registro 7 esteja sendo gerado, ao executar o comando acima, foi necessário esperar para que o update pudesse ser realizado. Essa espera ocorre porque a trigger lançou um bloqueio sobre o registro 6 (esse era o maior registro quando o INSERT de C1 foi realizado). Enquanto o INSERT de C1 não finalizar, o UPDATE irá ter que aguardar já que ele atua sobre o registro 6.

Embora seja muito pouco provável que alguém queira atualizar um registro recém inserido está bem claro que utilizar uma técnica do tipo MAX + 1 pode provocar duplicidades e caso seja utilizado o XLOCK para inibir essas duplicidades o resultado obtido é uma fila. Toda vez que uma inserção for feita, as demais terão que aguardar. Se ocorrer uma situação de 10 usuários simultâneos, as inserções serão realizadas de forma seqüencial, ou seja, o primeiro que chegar será atendido e os demais usuários terão de esperar para serem atendidos na ordem em que chegaram até que toda a fila seja atendida. Quanto mais usuários simultâneos houver, maior tenderá a ser a fila e mais os usuários irão aguardar já que os INSERTs não ocorrerão mais em paralelo mas sim de forma seqüencial.

Pode-se argumentar que a duração de um INSERT é de apenas 3ms, mas quando 3ms são responsáveis por gerar uma requisição de forma enfileirada, mil usuários simultâneos serão capazes de gerar uma espera de 3 segundos. Se o INSERT não for tão simples, mas estiver em uma stored procedure com dezenas de outras tarefas, validações e gravações, e demorar apenas 1 segundo para executar, 100 usuários serão capaz de gerar uma fila de 100s. Uma fila de 100 segundos é suficiente para gerar múltiplos timeouts e se os usuários tentarem novamente uma nova fila se formará gerando mais bloqueios, espera e insatisfação.

A solução mais elementar para evitar é utilizar a propriedade Identity do SQL Server. A autonumeração controlada pelo SQL Server é infinitamente mais otimizada para lidar com a concorrência do que uma solução baseada em MAX + 1. Aos interessados já discorri sobre o Identity e seus efeitos em um artigo postado no site do Plugmasters.

SQL Server: Geração de seqüenciais de forma automática
http://www.plugmasters.com.br/sys/materias/836/1/SQL-Server%3A-Gera%E7%E3o-de-seq%FCenciais-de-forma-autom%E1tica

O Identity tem seus pontos positivos, mas tem também suas falhas e pode levar a situações de "furos" na seqüência. Idealmente o "furo" na seqüência não deveria ser um problema uma vez que os objetivos principais do seqüencial são meramente garantir unicidade e que o registro subseqüente terá um valor superior ao registro anterior. Se por uma razão estética é necessário manter uma seqüência sem furos, o Identity não será suficiente. Para essas situações o MAX + 1 talvez fosse uma solução, mas não acredito que manter uma seqüência contínua justifique sacrificar a concorrência. Se isso é realmente necessário, a construção de uma tabela de seqüenciais pode ser uma melhor solução, pois, reduz os efeitos negativos sobre a concorrência. Alguns problemas permanecem, mas as restritições são menores que a abordagem MAX + 1.

Acredito que há razões fortes para não utilizar o MAX + 1 e sinceramente não o recomendo. Pode ser que os implementadores que o utilizam nunca tenham se deparado com aplicações de forte concorrência, mas ainda que a aplicação seja pequena não acho interessante o uso do MAX + 1. Se nas pequenas aplicações o MAX + 1 é utilizado, pode ser difícil largar esse vício quando aplicações maiores aparecerem.

[ ]s,

Gustavo

Piores Práticas – Utilizar o prefixo sp_ no nome de uma stored procedure

Oi pessoal,

Hoje falarei de uma prática muito comum. A utilização do prefixo sp_ no nome de uma stored procedure. Essa é sabidamente uma péssima prática presente inclusive na documentação do produto (Books OnLine). Basta procurar pelo comando CREATE PROCEDURE nessa documentação e a mensagem é bem clara:

We strongly recommend that you not use the prefix sp_ in the procedure name. This prefix is used by SQL Server to designate system stored procedures. For more information, see Creating Stored Procedures (Database Engine).

Talvez uma messagem em inglês não seja suficientemente clara, mas o Books OnLine do SQL Server 2008 tem a tradução em português do comando CREATE PROCEDURE e a messagem continua muito clara:

Recomendamos fortemente que você não use o prefixo sp_ no nome de procedimento. Este prefixo é usado pelo SQL Server para designar procedimentos armazenados de sistema. Para obter mais informações, consulte Criando procedimentos armazenados (Mecanismos de Banco de Dados).

Basta navegar pelo google digitando "sql server stored procedures sp_" (sem as aspas) e aparecerão dezenas de links informando para que não sejam criadas stored procedures com o prefixo sp. Se existe tal recomendação por que então é tão comum a presença de stored procedures que se iniciam com o prefixo sp_ ?

Há duas razões muito triviais para que stored procedures ainda sejam criadas com o prefixo sp_. A primeira é que sp é um acrônimo perfeito para Stored Procedures. Da mesma forma que utilizamos acrônimos como tb e tbl para tabelas, vw ou viw para views, fn para functions, etc é muito natural utilizar sp_ para designar stored procedures. Outra forte razão para vermos stored procedures com o prefixo sp_ é que o SQL Server dispõe de várias procedures internas com o prefixo sp_ (ex: sp_help, sp_databases, sp_attach_db, etc). Juntando essas duas razões com o desconhecimento dos malefícios desse prefixo, é natural (principalmente entre iniciantes) construir stored procedures com o prefixo sp_ (ex: sp_cliente, sp_notas, sp_atualiza, etc).

E qual é a razão pela qual esse prefixo é desaconselhável ? Por que não usar um acrônimo tão natural para designar uma stored procedure ? E se há malefícios por que o SQL Server inicia suas procedure com sp_ ?

A razão é bem simples. O prefixo sp_ é reservado para que o SQL Server saiba que aquela procedure é inicialmente utilizada por ele e que se trata de uma procedure interna. Assim, toda vez que o SQL Server encontra uma procedure com o prefixo sp_ ele entende que essa é uma procedure própria do produto e que ela lhe concede algumas características especiais.

Quando utilizamos a procedure sp_helptext podemos ter informações sobre o código de um objeto como stored procedure, function, etc. Esse comando funcionará em qualquer banco de dados. É tentador pensar que essa procedure existe em qualquer banco de dados, mas a verdade é que essa procedure não existe em nenhum banco de dados criados por nós. Basta fazer uma simples consulta para perceber que não existe a procedure sp_helptext em nosso banco de dados.

SELECT * FROM SYS.OBJECTS WHERE NAME = ‘sp_helptext’

Esse comando não retornará nenhum resultado, pois, essa stored procedure não existe em nenhum banco de dados. Na verdade até existe, mas não é tão notório assim. Se o SQL Server for o 2000, essa procedure estará no banco de dados MASTER. Se for 2005 ou superior, essa stored procedure estará no banco de dados Resource. Independente de estar no MASTER ou no Resource o fato é que essa procedure está em um local reservado a procedures de sistema. É essa característica que faz com que uma stored procedure que não exista em nenhum banco possa ser executada e ainda retorna o resultado correto. Esse mesmo fenômeno ocorre com outros objetos como as views Information_Schema.

Isso significa que toda vez que o SQL Server encontrar uma stored procedure com o prefixo sp_, ele irá supor que se trata de uma procedure de sistema e irá procurar por ela no local onde normalmente ela deveria estar. Isso pode ser devidamente comprovado. Utilizemos um script:

— Muda o contexto do banco de dados
USE MASTER;

— Cria uma procedure com o prefixo sp_
CREATE PROCEDURE sp_retorna_nome_banco
AS
PRINT
DB_NAME()

Se qualquer banco de dados for utilizado, basta executar a sp_retorna_nome_banco e a mesma irá retornar o nome do banco selecionado, ainda que você não tenha criada a stored procedure em outros bancos de dados. Isso ocorreu porque essa procedure tem o prefixo sp_ e por isso o SQL Server deduziu que se tratava de uma stored procedure de sistema que pode ser chamada a partir de qualquer banco de dados.

Essa pode parecer uma vantagem, mas esse efeito possui conseqüências. Como a procedure utiliza o prefixo sp_ (caracterizando-se como uma procedure de sistema), sempre que ela for executada, o SQL Server irá procurar se essa procedure existe nos bancos MASTER e RESOURCE e depois no banco de dados de onde ela foi chamada. O que aconteceria se no MASTER houvesse uma procedure com o prefixo sp_ e em um banco de dados houvesse uma procedure com o mesmo nome ? E se existisse uma procedure chamada sp_retorna_nome_banco em uma base SQL Server chamada BD ? Nesse caso, por padrão, a procedure que existisse no banco seria utilizada ao invés da sp_retorna_nome_banco no MASTER.

O fato é independente de haver conflitos de nomes, stored procedures com o prefixo sp_ sempre irão fazer uma visita aos bancos de dados de sistema para posteriormente irem no banco de dados de origem e executarem. Stored Procedures de negócio como sp_cliente, sp_notas e sp_atualiza deverão existir no banco de dados de negócio mas não no MASTER ou no Resource. Como possuem esse prefixo, toda vez que forem executadas, o SQL Server irá verificar se existem nos bancos de sistemas e não encontrando-as irá procurar no banco de dados de origem. Isso levará a uma ida desnecessária no banco de dados MASTER sempre que a procedure for chamada.

Qual é a razão plausível para utilizar uma stored procedure que irá primeiro em um banco de sistema, procurar por algo que não existe, voltar ao banco original e executar ? Seria muito mais eficiente se ela simplesmente executasse sem todos esses passos desnecessários. Não há razão do ponto de vista de desempenho em utilizar stored procedures com o prefixo sp_ já que elas degeneram o desempenho. A recomendação é que não utilizem stored procedures com o prefixo sp_ em hipótese nenhuma. Ainda que o efeito da sp_retorna_nome_banco possa ser interessante, não há garantia desse efeito em edições posteriores.

[ ]s,

Gustavo