Arquivo do autor:Gustavo Maia Aguiar

O cálculo do uptime, do downtime e da disponibilidade em T-SQL

Boa Noite Pessoal,

Olhando aqui a longa lista de emails vi uma newsletter bem interessante. No corpo dela havia um desafio em TSQL para calcular o tempo de uptime e downtime a partir de um registro de log. Achei o desafio bem interessante e pertinente. É muito comum em determinadas situações termos um arquivo com os vários momentos em que um sistema foi iniciado, parado e reestabelecido. Esses dados são importantes para efetuar o cálculo de indicadores importantes como o uptime, o downtime e a disponibilidade de uma solução. Normalmente tais indicadores são utilizados para negociação e contratação serviços, estipulação de SLAs, etc.

Para demonstrar um exemplo de como fazer esse cálculo a partir de uma lista de observações, vou utilizar uma situação hipotética de uma espécie de monitor de serviços. Esse monitor fará consultas esporádicas e anotará em uma lista o momento da consulta e o status do serviço (1 para Online e 0 para Offline). Em termos de script, segue a situação que descrevi:

– Cria uma tabela de Disponibilidade
CREATE TABLE LogDisponibilidade (DataRegistro SMALLDATETIME, OnLine BIT)

– Insere alguns registros de Log
INSERT INTO LogDisponibilidade VALUES (‘20100213 08:00′,1)
INSERT INTO LogDisponibilidade VALUES (‘20100213 14:53′,1)
INSERT INTO LogDisponibilidade VALUES (‘20100213 19:12′,1)
INSERT INTO LogDisponibilidade VALUES (‘20100213 23:15′,0)
INSERT INTO LogDisponibilidade VALUES (‘20100214 09:25′,0)
INSERT INTO LogDisponibilidade VALUES (‘20100214 09:30′,1)
INSERT INTO LogDisponibilidade VALUES (‘20100214 17:49′,0)
INSERT INTO LogDisponibilidade VALUES (‘20100214 22:13′,1)
INSERT INTO LogDisponibilidade VALUES (‘20100215 17:25′,0)
INSERT INTO LogDisponibilidade VALUES (‘20100215 19:44′,1)
INSERT INTO LogDisponibilidade VALUES (‘20100215 20:42′,0)
INSERT INTO LogDisponibilidade VALUES (‘20100215 23:50′,1)
INSERT INTO LogDisponibilidade VALUES (‘20100216 10:37′,1)
INSERT INTO LogDisponibilidade VALUES (‘20100216 17:00′,0)
INSERT INTO LogDisponibilidade VALUES (‘20100216 21:43′,0)
INSERT INTO LogDisponibilidade VALUES (‘20100216 22:59′,0)
INSERT INTO LogDisponibilidade VALUES (‘20100217 08:00′,1)

A lista pode ser exibida conforme a tabela abaixo:

Data da Coleta Status
13/02/2010 08:00:00 Online
13/02/2010 14:53:00 Online
13/02/2010 19:12:00 Online
13/02/2010 23:15:00 Offline
14/02/2010 09:25:00 Offline
14/02/2010 09:30:00 Online
14/02/2010 17:49:00 Offline
14/02/2010 22:13:00 Online
15/02/2010 17:25:00 Offline
15/02/2010 19:44:00 Online
15/02/2010 20:42:00 Offline
15/02/2010 23:50:00 Online
16/02/2010 10:37:00 Online
16/02/2010 17:00:00 Offline
16/02/2010 21:43:00 Offline
16/02/2010 22:59:00 Offline
17/02/2010 08:00:00 Online

Às 8h do dia 13 o serviço estava disponível e manteve-se assim até às 23:15 daquele dia. É verdade que houve duas coletas às 14:53 e 19:12, mas isso apenas constatou que o serviço continuava disponível. Como o serviço manteve-se de pé entre 08:00 até às 23:15, podemos confirmar a duração de 15:15 minutos de uptime. Às 23:15 do dia 13 houve uma queda no serviço que se manteve indisponível até às 09:30 do dia 14/12. Isso representa 10:15 minutos de downtime. Houve uma medição intermediária às 09:25 do dia 14, mas ela apenas constatou que o serviço continuava indisponível até aquele momento.

A visualização completa dos períodos de uptime e downtime poderia ser melhor visualizada da seguinte maneira:

Início Fim Status Duração (Min)
13/02/2010 08:00:00 13/02/2010 23:15:00 Online 915
13/02/2010 23:15:00 14/02/2010 09:30:00 Offline 615
14/02/2010 09:30:00 14/02/2010 17:49:00 Online 499
14/02/2010 17:49:00 14/02/2010 22:13:00 Offline 264
14/02/2010 22:13:00 15/02/2010 17:25:00 Online 1152
15/02/2010 17:25:00 15/02/2010 19:44:00 Offline 139
15/02/2010 19:44:00 15/02/2010 20:42:00 Online 58
15/02/2010 20:42:00 15/02/2010 23:50:00 Offline 188
15/02/2010 23:50:00 16/02/2010 17:00:00 Online 1030
16/02/2010 17:00:00 17/02/2010 08:00:00 Offline 900

Com essa visualização fica muito fácil calcular o tempo de uptime e de downtime. A tabela trouxe os dados de forma detalhada, mostrando todos os períodos de uptime e downtime, mas não seria difícil efetuar uma soma para chegar aos totais de 60:54 minutos e 35:06 minutos de uptime e downtime respectivamente. O problema é como chegar nesse resultado a partir dos registros da tabela de disponibilidade ? Vejamos o passo a passo de como fazer sem incorrer em uso de cursores, tabelas temporárias, loops, etc.

Tomando-se por base a lista dos períodos de uptime e downtime e a própria tabela "LogDisponibilidade", se for observado com atenção os primeiros registros da , é possível definir alguns comportamentos ocultos:

  • O "início" da primeira linha da lista corresponde a data do primeiro registro (marcação em verde)
  • O "início" de uma linha da lista (que não seja a primeira) será sempre igual ao "fim" da linha anterior (marcação em roxo)
  • O "fim" da última linha da lista corresponde a data do último registro (marcação em marrom)

Para ficar mais claro, mostro novamente a lista dos períodos de uptime e downtime com as marcações:

Início Fim Status Duração (Min)
13/02/2010 08:00:00 13/02/2010 23:15:00 Online 915
13/02/2010 23:15:00 14/02/2010 09:30:00 Offline 615
14/02/2010 09:30:00 14/02/2010 17:49:00 Online 499
14/02/2010 17:49:00 14/02/2010 22:13:00 Offline 264
14/02/2010 22:13:00 15/02/2010 17:25:00 Online 1152
15/02/2010 17:25:00 15/02/2010 19:44:00 Offline 139
15/02/2010 19:44:00 15/02/2010 20:42:00 Online 58
15/02/2010 20:42:00 15/02/2010 23:50:00 Offline 188
15/02/2010 23:50:00 16/02/2010 17:00:00 Online 1030
16/02/2010 17:00:00 17/02/2010 08:00:00 Offline 900

É importante lembrar dessas regras, pois, podem clarear várias das soluções possíveis.

Voltando à tabela "LogDisponibilidade", pode-se perceber que ela possui vários registros mostrando que em alguns períodos há observações semelhantes (o uptime é seguido de outro uptime e o downtime é seguido de outro downtime) e observações diferentes (o uptime é seguido de um downtime ou vice-versa). O que interessa mesmo são as observações que diferem, pois, é justamente na troca de uma observação de uptime para downtime que há definição de um intervalo seja de uptime ou de downtime. Para facilitar a explicação, irei me restringir aos seis primeiros registros da tabela "LogDisponibilidade" que são suficientes para visualizar essas alternâncias (criei uma view para facilitar o trabalho).

CREATE VIEW vTOP6 As SELECT TOP(6) DataRegistro, OnLine FROM LogDisponibilidade

DataRegistro OnLine
13/02/2010 08:00:00 1
13/02/2010 14:53:00 1
13/02/2010 19:12:00 1
13/02/2010 23:15:00 0
14/02/2010 09:25:00 0
14/02/2010 09:30:00 1

Do primeiro ao terceiro registro há uma continuação do uptime, ou seja, o serviço está de pé. O quarto registro mostra uma alternância, pois, parte-se de um período de uptime para um período de downtime. O período de downtime se mantém no quinto registro. No sexto registro há novamente uma alternância encerrando o downtime fixado entre o quarto e o quinto registro. Para cada data de registro, a primeira tentativa seria recuperar a primeira alternância subsequente. Isso pode ser feito com a consulta abaixo:

SELECT
    DataRegistro As Inicio, (
        SELECT MIN(DataRegistro) FROM vTOP6 As TInt
        WHERE TInt.DataRegistro > TOut.DataRegistro AND TOut.OnLine != TInt.OnLine)
    As Fim, OnLine FROM vTOP6 As TOut

Inicio Fim OnLine
13/02/2010 08:00:00 13/02/2010 23:15:00 1
13/02/2010 14:53:00 13/02/2010 23:15:00 1
13/02/2010 19:12:00 13/02/2010 23:15:00 1
13/02/2010 23:15:00 14/02/2010 09:30:00 0
14/02/2010 09:25:00 14/02/2010 09:30:00 0
14/02/2010 09:30:00 NULL 1

A clásula TInt.DataRegistro > TOut.DataRegistro AND TOut.OnLine != TInt.OnLine impõe que para cada data retornada, deve-se pegar as datas imediatamente superiores e que possua um status diferente. A data 13/02/2010 às 08:00 possui várias datas superiores. As datas 13/12/2010 às 14:53 e 19:12 são superiores, mas ambas mostram uma continuidade de uptime, pois, possuem o mesmo valor para a coluna OnLine. Para que a data seja realmente relevante é preciso que haja uma variação de status (por isso a diferença TOut.OnLine != TInt.OnLine). As datas 13/02/2010 às 23:15 e 14/02/2010 às 09:25 atendem essa condição, mas o interessante é pegar a menor delas e por isso a função MIN é utilizada. Assim conclui-se que às 23:15 do dia 13/02/2010 e 14/02/2010 houve mudança de status. A data de 14/02/2010 às 09:30 é a última data e por isso não há registro que atenda às condições impostas no predicado da subquery.

A coluna "Fim" possui dois registros duplicados (13/02/2010 às 23:15 e 14/02/2010 às 09:30). Isso ocorre, porque em períodos de continuidade, seja de uptime ou de downtime, a referência será sempre a próxima data imediatamente superior em que haja uma mudança de status. Se o "Fim" é conhecido, para identificar o "início" correto, basta procurar a menor data que se relacione ao "fim".

SELECT MIN(Inicio) As Inicio, Fim FROM (
    SELECT
        DataRegistro As Inicio, (
            SELECT MIN(DataRegistro) FROM vTOP6 As TInt
            WHERE TInt.DataRegistro > TOut.DataRegistro AND TOut.OnLine != TInt.OnLine)
        As Fim, OnLine FROM vTOP6 As TOut) As Res
GROUP BY Fim
ORDER BY Inicio

Inicio Fim
13/02/2010 08:00:00 13/02/2010 23:15:00
13/02/2010 23:15:00 14/02/2010 09:30:00
14/02/2010 09:30:00 NULL

Essa consulta não tem nada novo. Ela apenas faz uma subquery na consulta anterior. Como a coluna "Fim" estava em repetição, ela mantém a coluna "Fim" e recupera a menor data possível para a coluna "Início" em relação à coluna "Fim". Com essa consulta, já é possível visualizar que há dois períodos. O primeiro período é de 08:00 do dia 13/02/2010 até 13/02/2010 às 23:15 e o segundo é de 23:15 do dia 13/02/2010 até às 09:30 do dia 14/02/2010. O terceiro registro apareceu porque 14/02/2010 às 09:30 é o último registro da view e como não possui data superior teve a coluna "Fim" com o valor NULL. De forma a corrigir esse detalhe e deixar a consulta menos poluída, a seguir mostro a CTE equivalente.

;WITH Res (Inicio, Fim, OnLine) As (
SELECT
    DataRegistro As Inicio, (
        SELECT MIN(DataRegistro) FROM vTOP6 As TInt
        WHERE TInt.DataRegistro > TOut.DataRegistro AND TOut.OnLine != TInt.OnLine)
    As Fim, OnLine FROM vTOP6 As TOut),

Intervalos (Inicio, Fim) As (
    SELECT MIN(Inicio) As Inicio, Fim FROM Res
    WHERE Fim IS NOT NULL
    GROUP BY Fim)

SELECT Inicio, Fim FROM Intervalos
ORDER BY Inicio

Inicio Fim
13/02/2010 08:00:00 13/02/2010 23:15:00
13/02/2010 23:15:00 14/02/2010 09:30:00

Com essa última CTE, os períodos estão corretamente definidos. O problema é que não está contemplada a informação de disponibilidade sobre o período, ou seja, se naquele período o serviço estava ou não online. Embora a primeira CTE (Res) tenha a coluna de OnLine, o uso da função MIN na segunda CTE (Intervalos) acabou deixando essa coluna de fora. Ela até pode ser contemplada e participar da cláusula GROUP BY, mas é preciso lembrar que colunas do tipo BIT não podem participar de funções de agregação e por isso ela precisará ser convertida.

;WITH Res (Inicio, Fim, OnLine) As (
SELECT
    DataRegistro As Inicio, (
        SELECT MIN(DataRegistro) FROM vTOP6 As TInt
        WHERE TInt.DataRegistro > TOut.DataRegistro AND TOut.OnLine != TInt.OnLine)
    As Fim, CAST(OnLine As TINYINT) FROM vTOP6 As TOut),

Intervalos (Inicio, Fim, OnLine) As (
    SELECT MIN(Inicio) As Inicio, Fim, OnLine FROM Res
    WHERE Fim IS NOT NULL
    GROUP BY Fim, OnLine)

SELECT Inicio, Fim, OnLine FROM Intervalos
ORDER BY Inicio

Inicio Fim OnLine
13/02/2010 08:00:00 13/02/2010 23:15:00 1
13/02/2010 23:15:00 14/02/2010 09:30:00 0

Agora que a CTE está formada, basta retirar a referência à view vTOP6 e utilizar a própria tabela "LogDisponibilidade". Acrescentei uma CTE a mais para formatar a coluna OnLine e renomeá-la.

;WITH Res (Inicio, Fim, OnLine) As (
SELECT
    DataRegistro As Inicio, (
        SELECT MIN(DataRegistro) FROM LogDisponibilidade As TInt
        WHERE TInt.DataRegistro > TOut.DataRegistro AND TOut.OnLine != TInt.OnLine)
    As Fim, CAST(OnLine As TINYINT) FROM LogDisponibilidade As TOut),

Intervalos (Inicio, Fim, OnLine) As (
    SELECT MIN(Inicio) As Inicio, Fim, OnLine FROM Res
    WHERE Fim IS NOT NULL
    GROUP BY Fim, OnLine)

SELECT Inicio, Fim,
    CASE OnLine WHEN 1 THEN ‘OnLine’ ELSE ‘OffLine’ END As Status
FROM Intervalos
ORDER BY Inicio

Inicio Fim Status
13/02/2010 08:00:00 13/02/2010 23:15:00 OnLine
13/02/2010 23:15:00 14/02/2010 09:30:00 OffLine
2010-02-14 09:30:00 2010-02-14 17:49:00 OnLine
2010-02-14 17:49:00 2010-02-14 22:13:00 OffLine
2010-02-14 22:13:00 2010-02-15 17:25:00 OnLine
2010-02-15 17:25:00 2010-02-15 19:44:00 OffLine
2010-02-15 19:44:00 2010-02-15 20:42:00 OnLine
2010-02-15 20:42:00 2010-02-15 23:50:00 OffLine
2010-02-15 23:50:00 2010-02-16 17:00:00 OnLine
2010-02-16 17:00:00 2010-02-17 08:00:00 OffLine

Se os intervalos estão corretamente definidos, não há muita dificuldade em calcular a diferença entre as datas de início e de fim para obter a duração dos mesmos.

;WITH Res (Inicio, Fim, OnLine) As (
SELECT
    DataRegistro As Inicio, (
        SELECT MIN(DataRegistro) FROM LogDisponibilidade As TInt
        WHERE TInt.DataRegistro > TOut.DataRegistro AND TOut.OnLine != TInt.OnLine)
    As Fim, CAST(OnLine As TINYINT) FROM LogDisponibilidade As TOut),

Intervalos (Inicio, Fim, OnLine) As (
    SELECT MIN(Inicio) As Inicio, Fim, OnLine FROM Res
    WHERE Fim IS NOT NULL
    GROUP BY Fim, OnLine),

IntervalosDuracao (Inicio, Fim, Status, Duracao) As (
    SELECT Inicio, Fim,
        CASE OnLine WHEN 1 THEN ‘OnLine’ ELSE ‘OffLine’ END,
        DATEDIFF(MI,Inicio, Fim)
    FROM Intervalos)

SELECT Inicio, Fim, Status, Duracao
FROM IntervalosDuracao
ORDER BY Inicio

Início Fim Status Duração (Min)
13/02/2010 08:00:00 13/02/2010 23:15:00 Online 915
13/02/2010 23:15:00 14/02/2010 09:30:00 Offline 615
14/02/2010 09:30:00 14/02/2010 17:49:00 Online 499
14/02/2010 17:49:00 14/02/2010 22:13:00 Offline 264
14/02/2010 22:13:00 15/02/2010 17:25:00 Online 1152
15/02/2010 17:25:00 15/02/2010 19:44:00 Offline 139
15/02/2010 19:44:00 15/02/2010 20:42:00 Online 58
15/02/2010 20:42:00 15/02/2010 23:50:00 Offline 188
15/02/2010 23:50:00 16/02/2010 17:00:00 Online 1030
16/02/2010 17:00:00 17/02/2010 08:00:00 Offline 900

Agora que os intervalos foram corretamente delimitados e as durações devidamente calculadas, não é difícil calcular o tempo total de uptime, downtime e disponibilidade. A fórmula da disponibilidade é calculada dividindo-se o tempo total que um serviço esteja no ar (uptime) pelo tempo total (uptime + downtime) desconsiderando-se as paradas planejadas (que não foram tratadas nesse exemplo).

;WITH Res (Inicio, Fim, OnLine) As (
SELECT
    DataRegistro As Inicio, (
        SELECT MIN(DataRegistro) FROM LogDisponibilidade As TInt
        WHERE TInt.DataRegistro > TOut.DataRegistro AND TOut.OnLine != TInt.OnLine)
    As Fim, CAST(OnLine As TINYINT) FROM LogDisponibilidade As TOut),

Intervalos (Inicio, Fim, OnLine) As (
    SELECT MIN(Inicio) As Inicio, Fim, OnLine FROM Res
    WHERE Fim IS NOT NULL
    GROUP BY Fim, OnLine),

IntervalosDuracao (Inicio, Fim, Status, Duracao) As (
    SELECT Inicio, Fim,
        CASE OnLine WHEN 1 THEN ‘OnLine’ ELSE ‘OffLine’ END,
        DATEDIFF(MI,Inicio, Fim)
    FROM Intervalos),

Indicadores (Uptime, Downtime) As (
    SELECT
        (SELECT CAST(SUM(Duracao) As Decimal)
        FROM IntervalosDuracao WHERE Status = ‘OnLine’),
        (SELECT CAST(SUM(Duracao) As Decimal)
        FROM IntervalosDuracao WHERE Status = ‘OffLine’))

SELECT
    Uptime, Downtime, Uptime / (Uptime + Downtime) As Disponibilidade
FROM Indicadores

Uptime Downtime Disponibilidade
3654 2106 0.634375

Acho que com isso é finalizado o cálculo da disponibilidade. O desafio original não é exatamente igual a esse, mas eu diria que o correto entedimento do cálculo apresentado até então é o caminho das pedras para resolvê-lo. Há maiores detalhes no link abaixo:

http://beyondrelational.com/blogs/tc/archive/2010/02/08/tsql-challenge-23-calculate-the-uptime-and-downtime-of-a-system-by-reading-the-data-from-an-audit-log.aspx

Há um incentivo para quem conseguir resolver o desafio. Os participantes estarão disputando uma licença da ferramenta Red Gate SQL Backup. Além da ferramenta, há ainda alguns bonés, camisetas, etc. Os interessados devem apressar-se, pois, o desafio termina agora no dia 22/02/2010.

[ ]s,

Gustavo

Community Zone 2010

Oi Pessoal,

Desde 2007, todo ano a Microsoft organiza um evento chamado Community Zone. A idéia é montar um evento de em clima de descontração reunindo vários integrantes das comunidades, células acadêmicas, fóruns, etc que disseminam tecnologias Microsoft. Isso inclui MVPs, Student Partners, líderes de grupos de usuários, membros do Ineta e Culminis, influenciadores de uma forma geral e a própria equipe da Microsoft. Nesse evento há palestras, mesas de discussão, dinâmicas de grupo entre outras atividades para que os participantes se conheçam, interajam e troquem experiências profissionais.

A primeira vez que participei do Community Zone foi em 2008. Em dezembro de 2007 voltei a frequentar os fóruns MSDN e TechNet auxiliando com as dúvidas de SQL Server. Comecei a contribuir tão ativamente, que em agosto de 2008 fui convidado para o evento (muito bom por sinal). Além das palestras técnicas que falaram sobre o padrão OpenXML e deram as primeiras pinceladas no assunto Cloud Computing, tive a oportunidade de conhecer alguns MVPs, influenciadores e profissionais de peso em SQL Server como Fabiano Neves Amorin, Diego Nogare, Vladimir Magalhães para citar alguns. Lembro-me do Laércio Freitas e eu perguntando para o Júnior Galvão e o Diego Nogare o que era necessário para se tornar MVP, pois, estávamos bem empenhados em buscar esse reconhecimento. A dinâmica do agente Jack Bauer também foi fantástica.

Essa semana recebi o e-mail de convite para o Community Zone na sua edição 2010. Não pude ir no Community Zone em 2009, pois, na época estava bastante atarefado e alguns compromissos eram inadiáveis. Esse ano já confirmei minha presença. Espero conhecer pessoalmente alguns dos bons nomes que vejo nas comunidades, fóruns, webcasts de SQL Server que ainda não conheço.

[ ]s,

Gustavo

Encontrando índices não utilizados

Boa Noite Pessoal,

Semana passada começou excelente. Na virada do mês, lá estava eu curtindo a apresentação do DJ Paul Van Dyk no último live no Brasil (foi simplesmente fantástico). Foi ótimo para desestressar porém acabei tendo uma segunda-feira puxada e cansativa. Felizmente consegui tocar boa parte dos afazeres de SQL Server (pendências como sempre ainda estão presentes incluindo o post da semana passada). Finalmente consegui entregar o relatório final de um projeto de consultoria iniciado no ano passado. Durante a apresentação, eu relatei uma certa "anomalia" que havia identificado. Uma das tabelas mais utilizadas pelas aplicações possui nada mais que 17 índices. Isso foi sem dúvida algo que me impressionou, pois, em bancos OLTP normais não é nenhum um pouco comum encontrar essa quantidade de índices em um única tabela (se fosse um DW ainda ia). Imagine só uma operação de INSERT ter de escrever em mais 17 lugares além do próprio dado ? Sem dúvida um grande complicador em termos de desempenho. Na ocasião, um dos analista me perguntou se havia uma forma de identificar os índices que não estavam sendo utilizados. Eu não cheguei a levantá-los no relatório, mas não só a esse analista como a todos os demais que tenha essa dúvida, a resposta é sim. É possível levantar os índices que não estão sendo utilizados se o SQL Server for 2005 (ou superior). Não que no 2000 não desse, mas o processo era absurdamente mais trabalhoso. Vejamos como fazer isso.

– Muda o contexto para o TempDB
USE TempDB

– Cria uma tabela de Pessoas
CREATE TABLE Pessoas (CodigoPessoa INT NOT NULL,
    Nome VARCHAR(50) NOT NULL,
    CPF CHAR(11) NOT NULL,
    Telefone CHAR(8) NOT NULL,
    DDD CHAR(3) NOT NULL,
    UF CHAR(2) NOT NULL,
    DataNascimento DATE NOT NULL,
    RowID UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID())

– Insere 5 milhões de registros aleatórios
DECLARE @i INT = 1

WHILE @i <= 5000000
BEGIN
  INSERT INTO Pessoas (
    CodigoPessoa, Nome, CPF, Telefone,
    DDD, UF, DataNascimento) VALUES (@i,
    REPLICATE(CHAR(65 + (@i% 25)),40 + (@i % 10)),
    REPLICATE(CAST((@i % 10) As CHAR(1)),11),
    REPLICATE(CAST((@i % 5) As CHAR(1)),8),‘011’,‘SP’,
    DATEADD(D,ABS(CHECKSUM(NEWID())) / 1000000,‘19700101’))
  SET @i += 1
END

Agora que os registros estão devidamente cadastrados, o próximo passo é a criação de algumas constraints e índices.

– Cria as constraints
ALTER TABLE Pessoas ADD CONSTRAINT PK_Pessoas PRIMARY KEY (CodigoPessoa)
ALTER TABLE Pessoas ADD CONSTRAINT UQ_RowID UNIQUE (RowID)

– Cria os índices
CREATE INDEX IX_Nome ON Pessoas (Nome)
CREATE INDEX IX_RowID ON Pessoas (RowID)
CREATE INDEX IX_Telefone ON Pessoas (Telefone)
CREATE INDEX IX_UF ON Pessoas (UF)
CREATE INDEX IX_DDD ON Pessoas (DDD)
CREATE INDEX IX_DataNasc ON Pessoas (DataNascimento)
CREATE INDEX IX_DataNasc_UF ON Pessoas (DataNascimento, UF)
CREATE INDEX IX_UF_Telefone ON Pessoas (UF, Telefone)
CREATE INDEX IX_DDD_Telefone ON Pessoas (DDD, Telefone)

Visivelmente já existem alguns índices bem "inúteis". Antes de proferir as consultas para saber o que é útil ou não, é necessário cadastrar alguns registros a mais para que os resultados das consultas sejam os mesmos e que os registros aleatórios não interfiram.

– Insere mais cinco registros de pessoas
INSERT INTO Pessoas (CodigoPessoa, Nome, CPF, Telefone, DDD, UF, DataNascimento)
VALUES (5000001,‘Rildo’,‘70901232141’,‘99865455’,‘061’,‘DF’,‘19690314’)

INSERT INTO Pessoas (CodigoPessoa, Nome, CPF, Telefone, DDD, UF, DataNascimento)
VALUES (5000002,‘Gilvan’,‘25987341230’,‘32132234’,‘021’,‘RJ’,‘19680223’)

INSERT INTO Pessoas (CodigoPessoa, Nome, CPF, Telefone, DDD, UF, DataNascimento)
VALUES (5000003,‘Fábio’,‘45312967402’,‘84122932’,‘061’,‘DF’,‘19690314’)

INSERT INTO Pessoas (CodigoPessoa, Nome, CPF, Telefone, DDD, UF, DataNascimento)
VALUES (5000004,‘Renato’,‘56349087124’,‘84531278’,‘083’,‘PB’,‘19970221’)

INSERT INTO Pessoas (CodigoPessoa, Nome, CPF, Telefone, DDD, UF, DataNascimento)
VALUES (5000005,‘Genésio’,‘71234210943’,‘9738200’,‘061’,‘DF’,‘19650628’)

O SQL Server 2005 possui uma DMV própria para monitorar a utilização de índices chamada sys.dm_db_index_usage_stats. Essa view pode ser combinada com a view de catálogo sys.indexes para obter uma visão de utilização dos índices. O script abaixo (considerando que a tabela foi criada no TempDB), mostra essa visão:

– Muda o contexto do banco de dados
USE TEMPDB

– Consulta a utilização dos índices da tabela Pessoas
SELECT
    DB_NAME(database_id) As Banco, OBJECT_NAME(I.object_id) As Tabela, I.Name As Indice,
    U.User_Seeks As Pesquisas, U.User_Scans As Varreduras, U.User_Lookups As LookUps,
    U.Last_User_Seek As UltimaPesquisa, U.Last_User_Scan As UltimaVarredura,
    U.Last_User_LookUp As UltimoLookUp, U.Last_User_Update As UltimaAtualizacao
FROM
    sys.indexes As I
    LEFT OUTER JOIN sys.dm_db_index_usage_stats As U
    ON I.object_id = U.object_id AND I.index_id = U.index_id
WHERE I.object_id = OBJECT_ID(‘Pessoas’)

O resultado é mostrado na tabela abaixo:

Índice Pesquisas Varreduras Lookups Ultima
Pesquisa
Última
Varredura
Último
LookUp
Última
Atualização
PK_Pessoas 0 0 0 NULL NULL NULL 2010-02-08 13:47
UQ_RowID 0 0 0 NULL NULL NULL 2010-02-08 13:47
IX_Nome 0 0 0 NULL NULL NULL 2010-02-08 13:47
IX_RowID 0 0 0 NULL NULL NULL 2010-02-08 13:47
IX_Telefone 0 0 0 NULL NULL NULL 2010-02-08 13:47
IX_UF 0 0 0 NULL NULL NULL 2010-02-08 13:47
IX_DDD 0 0 0 NULL NULL NULL 2010-02-08 13:47
IX_DataNasc 0 0 0 NULL NULL NULL 2010-02-08 13:47
IX_DataNasc_UF 0 0 0 NULL NULL NULL 2010-02-08 13:47
IX_UF_Telefone 0 0 0 NULL NULL NULL 2010-02-08 13:47
IX_DDD_Telefone 0 0 0 NULL NULL NULL 2010-02-08 13:47

A relação mostra todos os índices da tabela Pessoas. Nota-se que os dois primeiros registros não referem-se propriamente a índices, mas sim a constraints. Isso ocorre, porque constraints como primary keys e unique constraints necessitam criar um índice unique para garantir suas restrições de unicidade. Há maiores informações sobre elas em: Será que a opção IGNORE_DUP_KEY permite entradas duplicadas na chave primária e índices únicos ?.

As colunas de pesquisa, varreduras e lookups dão pistas sobre a utilização do índice. Em ordem de utilidade, na maioria das situações é preferível ter mais pesquisas, depois lookups e posteriormente varreduras. Índices que são utilizados somente para scans devem ser revistos, pois, podem significar índices mal projetados ou consultas mal elaboradas. As colunas subsequentes mostram quando o índice teve uma pesquisa, varredura, lookup ou atualização recente. As atualizações em um índice vão ocorrer sempre que houver um INSERT ou um DELETE, pois, a exclusão da linha necessariamente provoca variações no índice. No caso de operações de UPDATE, o índice será atualizado se a coluna que teve o UPDATE fizer parte do índice e alguns outros casos especiais.

Como nenhuma consulta foi realizada desde a criação dos índices, as colunas referentes a consultas (pesquisas, varreduras e lookups estão todas zeradas). A coluna de última atualização foi preenchida, pois, houve a inserção de cinco registros após a criação dos índices. Se essas inserções não tivessem ocorrido, a coluna última atualização não estaria preenchida.

Agora que existem registros "identificados", uma forma de verificar sua real necessidade dos índices sugeridos até então é a elaboração de algumas consultas para descobrir quais são realmente usados para recuperação dos dados. Para tornar os exemplos mais interessantes, vou incluir o plano de execução.

– Consulta 1
– Pesquisar quantos clientes existem em São Paulo

SELECT COUNT(*) FROM Pessoas
WHERE UF = ‘SP’

O resultado da consulta é alterado:

Índice Pesquisas Varreduras Lookups Ultima
Pesquisa
Última
Varredura
Último
LookUp
PK_Pessoas 0 0 0 NULL NULL NULL
UQ_RowID 0 0 0 NULL NULL NULL
IX_Nome 0 0 0 NULL NULL NULL
IX_RowID 0 0 0 NULL NULL NULL
IX_Telefone 0 0 0 NULL NULL NULL
IX_UF 1 0 0 2010-02-08 15:03 NULL NULL
IX_DDD 0 0 0 NULL NULL NULL
IX_DataNasc 0 0 0 NULL NULL NULL
IX_DataNasc_UF 0 0 0 NULL NULL NULL
IX_UF_Telefone 0 0 0 NULL NULL NULL
IX_DDD_Telefone 0 0 0 NULL NULL NULL

Como a consulta utilizou o índice para efetuar uma pesquisa (Seek), a coluna de pesquisas aumentou em uma unidade e a data da última pesquisa realizada com aquele índice foi atualizada deixando de ser nula.

– Consulta 2
– Pesquisar os clientes com DDD 061
SELECT * FROM Pessoas
WHERE DDD = ‘061’

Índice Pesquisas Varreduras Lookups Ultima
Pesquisa
Última
Varredura
Último
LookUp
PK_Pessoas 0 0 1 NULL NULL 2010-02-08 15:32
UQ_RowID 0 0 0 NULL NULL NULL
IX_Nome 0 0 0 NULL NULL NULL
IX_RowID 0 0 0 NULL NULL NULL
IX_Telefone 0 0 0 NULL NULL NULL
IX_UF 1 0 0 2010-02-08 15:03 NULL NULL
IX_DDD 0 0 0 NULL NULL NULL
IX_DataNasc 0 0 0 NULL NULL NULL
IX_DataNasc_UF 0 0 0 NULL NULL NULL
IX_UF_Telefone 0 0 0 NULL NULL NULL
IX_DDD_Telefone 1 0 0 2010-02-08 15:32 NULL NULL

O índice IX_DDD_Telefone foi utilizado para pesquisa, mas como ele não possui todas as colunas necessárias, é preciso fazer um lookup para localizar os dados que estão no índice clustered. Essa operação é chamada de lookup e por isso, a chave primária foi utilizada para lookup.

– Consulta 3
– Pesquisar as pessoas com DDD 061

SELECT * FROM Pessoas
WHERE CONVERT(CHAR(10),DataNascimento,103) = ’23/02/1968′

Índice Pesquisas Varreduras Lookups Ultima
Pesquisa
Última
Varredura
Último
LookUp
PK_Pessoas 0 0 2 NULL NULL 2010-02-08 15:32
UQ_RowID 0 0 0 NULL NULL NULL
IX_Nome 0 0 0 NULL NULL NULL
IX_RowID 0 0 0 NULL NULL NULL
IX_Telefone 0 0 0 NULL NULL NULL
IX_UF 1 0 0 2010-02-08 15:03 NULL NULL
IX_DDD 0 0 0 NULL NULL NULL
IX_DataNasc 0 1 0 NULL 2010-02-08 15:55 NULL
IX_DataNasc_UF 0 0 0 NULL NULL NULL
IX_UF_Telefone 0 0 0 NULL NULL NULL
IX_DDD_Telefone 1 0 0 2010-02-08 15:32 NULL NULL

O índice sobre a coluna data considera um tipo Date. A conversão para o tipo CHAR, "invalida" o índice para pesquisa. Nesse caso é necessário varrer toda a estrutura de índice para converter cada entrada para CHAR(10) e posteriormente fazer a pesquisa. Essa varredura (SCAN) faz com que o índice aumente a quantidade de varreduras de 0 para 1 e atualiza a data da última varredura. Como o índice não possui todas as colunas necessárias, foi necessário buscar os dados presentes no índice clustered aumentando a quantidade de lookups para 2.

– Consulta 4
– Pesquisar os 100 primeiros clientes que iniciam com a letra A

SELECT TOP 100 * FROM Pessoas
WHERE Nome LIKE ‘A%’

Índice Pesquisas Varreduras Lookups Ultima
Pesquisa
Última
Varredura
Último
LookUp
PK_Pessoas 0 1 2 NULL 2010-02-08 16:31 2010-02-08 15:32
UQ_RowID 0 0 0 NULL NULL NULL
IX_Nome 0 0 0 NULL NULL NULL
IX_RowID 0 0 0 NULL NULL NULL
IX_Telefone 0 0 0 NULL NULL NULL
IX_UF 1 0 0 2010-02-08 15:03 NULL NULL
IX_DDD 0 0 0 NULL NULL NULL
IX_DataNasc 0 1 0 NULL 2010-02-08 15:55 NULL
IX_DataNasc_UF 0 0 0 NULL NULL NULL
IX_UF_Telefone 0 0 0 NULL NULL NULL
IX_DDD_Telefone 1 0 0 2010-02-08 15:32 NULL NULL

Ao contrário do que poderia parecer, o índice sobre a coluna Nome sequer foi utilizado (possivelmente por conta da cardinalidade). Como a tabela é clusterizada, ou seja, possui um índice clustered, ela foi simplesmente varrida para que os nome fossem comparados aumentando para 1 a quantidade de scans na chave primária.

– Consulta 5
– Pesquisar um RowID específico

SELECT * FROM Pessoas
WHERE RowID = ‘2E50F357-A814-DF11-BDFB-18A9056DE331′

Índice Pesquisas Varreduras Lookups Ultima
Pesquisa
Última
Varredura
Último
LookUp
PK_Pessoas 0 1 3 NULL 2010-02-08 16:31 2010-02-08 15:32
UQ_RowID 0 0 0 NULL NULL NULL
IX_Nome 0 0 0 NULL NULL NULL
IX_RowID 0 0 0 NULL NULL NULL
IX_Telefone 0 0 0 NULL NULL NULL
IX_UF 1 0 0 2010-02-08 15:03 NULL NULL
IX_DDD 0 0 0 NULL NULL NULL
IX_DataNasc 0 1 0 NULL 2010-02-08 15:55 NULL
IX_DataNasc_UF 0 0 0 NULL NULL NULL
IX_UF_Telefone 0 0 0 NULL NULL NULL
IX_DDD_Telefone 1 0 0 2010-02-08 15:32 NULL NULL

Esse é um detalhe interessante. O índice UQ_RowID é um índice derivado da constraint UQ_RowID e é o mesmo que o índice IX_RowID, mas mesmo assim, o SQL Server optou por escolher o IX_RowID. Eles são idênticos e tanto faz, pois, o resultado é o mesmo. Entretanto, há um duplo overhead, pois, os dois índices precisam ser mantidos. A exclusão do índice IX_RowID permitirá que o índice UQ_RowID seja utilizado.

– Elimina o índice IX_RowID
DROP INDEX Pessoas.IX_RowID

– Consulta 6
– Pesquisar um RowID específico

SELECT * FROM Pessoas
WHERE RowID = ‘2E50F357-A814-DF11-BDFB-18A9056DE331′

Índice Pesquisas Varreduras Lookups Ultima
Pesquisa
Última
Varredura
Último
LookUp
PK_Pessoas 0 1 3 NULL 2010-02-08 16:31 2010-02-08 15:32
UQ_RowID 1 0 0 2010-02-08 17:00 NULL NULL
IX_Nome 0 0 0 NULL NULL NULL
IX_Telefone 0 0 0 NULL NULL NULL
IX_UF 1 0 0 2010-02-08 15:03 NULL NULL
IX_DDD 0 0 0 NULL NULL NULL
IX_DataNasc 0 1 0 NULL 2010-02-08 15:55 NULL
IX_DataNasc_UF 0 0 0 NULL NULL NULL
IX_UF_Telefone 0 0 0 NULL NULL NULL
IX_DDD_Telefone 1 0 0 2010-02-08 15:32 NULL NULL

Com o índice IX_RowID eliminado, o índice UQ_RowID foi utilizado incorrendo no mesmos custos do índice IX_RowID.

A consulta utilizada mostra como verificar a utilização de cada índice. Um pequeno ajuste mostrará os índices completamente não utilizados permitindo uma eventual revisão de sua real necessidade.

– Muda o contexto do banco de dados
USE TEMPDB

;WITH IndicesNaoUtilizados As (
SELECT
    DB_NAME(database_id) As Banco, OBJECT_NAME(I.object_id) As Tabela, I.Name As Indice,
    U.User_Seeks As Pesquisas, U.User_Scans As Varreduras, U.User_Lookups As LookUps,
    U.Last_User_Seek As UltimaPesquisa, U.Last_User_Scan As UltimaVarredura,
    U.Last_User_LookUp As UltimoLookUp, U.Last_User_Update As UltimaAtualizacao
FROM
    sys.indexes As I
    LEFT OUTER JOIN sys.dm_db_index_usage_stats As U
    ON I.object_id = U.object_id AND I.index_id = U.index_id
WHERE database_id = DB_ID())

SELECT
    Banco, Tabela, Indice, Pesquisas, Varreduras, LookUps,
    UltimaPesquisa, UltimaVarredura, UltimoLookUp
FROM IndicesNaoUtilizados
WHERE
    (Pesquisas + Varreduras + LookUps) = 0

Um índice sempre terá a última data de atualização preenchida quando ocorrerem inserts e deletes (e updates em alguns casos). A utilidade de um índice significa que ele é capaz de auxiliar nas consultas recuperando os dados mais rapidamente. Se esse índice não é utilizado para nenhuma operação que envolve recuperação como pesquisa (Seek), varredura (Scan) ou o Lookup ele é um sério candidato a ser eliminado.

Fragilidades do método

Embora essa seja uma abordagem interessante para detectar alguns índices inúteis, ela em por si só não é tão eficaz se não forem observados alguns outros pontos de análise.

Acuracidade dos dados das DMVs

Por padrão, as DMVs iniciam a coleta de dados no momento em que o SQL Server é iniciado. Isso significa que mesmo que os índices existam, se o SQL Server for reiniciado, automaticamente todos os contadores serão zerados. Se um índice for criado e muito utilizado, mas após reiniciar o SQL Server ele não for mais usado, a DMV poderá apontar como zero a soma das pesquisas, varreduras e lookups e isso pode levar a falsas interpretações.

Conhecimento das rotinas de negócio

Muitas vezes um índice pode ser aparentemente desprezado por passar longos períodos de tempo sem utilização. Entretanto é possível que rotinas batch ou ainda relatórios mensais, trimestrais, etc tenham necessidade de utilizá-lo. Sem conhecer essas rotinas, algum índice pode ser excluído indevidamente e provocar problemas de desempenho.

Índices utilizados para restrição

Nem sempre o papel de um índice está relacionado à recuperação rápida de dados. Primary Keys e Unique Constraints dependem de índices, pois, é o mecanismo que elas possuem para garantir a unicidade. Especialmente no caso das Unique Constraints, pode acontecer dos índices serem criados para impedir registros duplicados e talvez ele nunca sejam realmente utilizados para pesquisa. Essa característica é comum também para índices unique criados sem a associação a uma constraint.

Dependência do banco de dados

A consulta que eu elaborei depende da DMV sys.dm_db_index_usage_stats e da view sys.indexes. Por ser uma DMV, a sys.dm_db_index_usage_stats retorna os dados de toda a instância independente do banco executado (desde que haja as devidas permissões para tal). No caso da sys.indexes, é necessário estar logado no banco de dados desejados para que os dados dela possam ser retornados corretamente. Pode-se utilizar somente a sys.dm_db_index_usage_stats, mas não será possível obter alguns dados como o nome do índice ou ainda as colunas participantes (sys.index_columns).

E Por que se preocupar com índices não utilizados ?

Se analisarmos as estruturas de índices, elas representam uma certa redundância. O índice é uma "cópia" de parte dos dados em uma organização própria para facilitar a recuperação. O simples fato de ser uma "cópia" impõe duas desvantagens naturais. A primeira é que será necessário espaço adicional para armazenar essas "cópias parciais". A segunda é que por serem "cópias" utilizadas para pesquisas, elas devem ser mantidas de forma síncrona com os dados reais, ou seja, a gravação de um registro deve necessariamente atualizar todos os índices. Isso significa um custo de I/O adicional. Isso significa que na melhor das hipóteses um índice pode auxiliar a recuperação de dados (não é 100% garantido), mas sempre irá prejudicar a gravação de dados (isso é 100% garantido) e por isso devem ser criteriosamente escolhidos. Eliminar os índices desnecessários já é um ponto positivo na criação de uma proposta de reindexação.

[ ]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

A quantidade de linhas fala por si só ?

Bom Dia Pessoal,

Há alguns dias atrás passei uma tarde em minha cadeira monitorando o ambiente e logo ali ao lado estavam reunidos alguns amigos DBAs, analistas de negócio e a área usuária trabalhando na migração de uma aplicação para um ambiente mais parrudo que o atualmente utilizado. Além das pessoas envolvidas, estava na outra ponta da linha do telefone o fornecedor do software. Durante toda essa iteração escutei várias vezes frases do tipo "tem mais de sete milhões de linhas", "a quantidade de linhas é muito grande", etc.

É impressionante como existem muitas argumentações de carga baseadas puramente na quantidade de linhas. Quantas vezes uma software house não usa a quantidade de linhas como robustez da sua solução ("Meu Software processa mais de um milhão de linhas por dia") ? Quantas vezes um fornecedor não recomenda um servidor mais parrudo para processar muitos registros ("Veja bem, recomendo o servidor XPTO com 103 processadores e 105 GB de RAM, pois, serão mais de 1.000 linhas por segundo") ? Ou ainda decisões precipitadas como "A tabela tem dois milhões de registros. É melhor a gente particioná-la para obter desempenho".

Será mesmo que o número de linhas é uma boa métrica para carga, volume e dimensionamento ? Será que podemos dizer que a solução A é melhor que a solução B apenas porque A trabalha com mais linhas que B ? O número de linhas é um indicador importante com certeza, mas não pode ser utilizado isoladamente da forma como muitos utilizam, pois, pode levar a interpretações equivocadas.

Uma metáfora em relação ao mundo cotidiano

Em uma situação hipotética, um casal possui um casa em um grande terreno e está interessado em adquirir uma piscina. Algumas tendências como aquecimento global, trabalho home office e os três filhos pequenos que pedem para ir ao clube todos os dias fizeram com que essa decisão fosse "acelerada" e eles precisam decidir rapidamente.

Uma dos maiores problemas para quem tem piscina em casa refere-se à manutenção. A limpeza da piscina, por exemplo, pode demandar esvaziá-la e posteriormente enchê-la renovando a água "suja" para uma mais "limpa". Como água é um bem que está se tornando cada vez mais escasso e a conta de água é progressiva (quanto mais água se consome, mais se paga e essa relação não é proporcional, pois, a taxa é crescente com o volume) é imprescindível escolher a piscina que ofereça o lazer necessário, mas com a maior economia de água possível.

O casal vai à casa de piscinas mais especializada da cidade e o vendedor dá as seguintes opções:

  • A piscina de 1,5m de profundidade custa R$ 8.000,00
  • A piscina de 2,0m de profundidade custa R$ 12.000,00
  • A piscina de 2,5m de profundidade custa R$ 16.000,00

Normalmente uma piscina com 1,5m de profundidade está muito adequada para lazer e prática da natação. Considerando que as crianças são pequenas, ter uma piscina com 2,0m ou 2,5m de profundidade é até desaconselhável. Vale a pena lembrar que o principal parâmetro para decisão da compra é a quantidade de água (tem que ser a menor possível).

O raciocínio é bem simples. À medida que a piscina se torna mais funda, mais água será necessária para enchê-la. Então a piscina mais econômica será aquela que tem a menor profundidade. Logo a piscina de 1,5m deve ser a escolhida. Pensando assim, o casal optou pela piscina de 1,5m. Como todo cliente inteligente, antes de fechar a compra, o casal pediu para visualizar as piscinas. O esqueleto das mesmas é parecido com a figura abaixo:

De acordo com o raciocínio anterior, a piscina de menor profundidade deveria ser a que mais economizaria, pois a piscina mais raza é a que gasta menos água. Esse raciocínio parece correto, mas é logicamente inconsistente. Primeiro porque a quantidade de água é calculada em função do volume e não da profundidade. Embora a profundidade faça parte do cálculo do volume (Comprimento * Altura * Largura), ela sozinha não determinará o maior volume, pois, existem outros componentes envolvidos no cálculo.

Quando elaborei a figura coloquei todas com o mesmo comprimento variando apenas a largura e a profundidade. A piscina de profundidade 1,5m possui 6x de largura, a de 2,0m possui 3x de largura e de 2,5m possui 2x de largura. Como todas tem o mesmo comprimento vejamos o cálculo do volume para as piscinas

  Comprimento Largura Altura Volume
Piscina 1 c 6x 1,5m 9cx
Piscina 2 c 3x 2,0m 6cx
Piscina 3 c 2x 2,5m 5cx

As medidas de volume mostram que a piscina de 1,5m que possui a menor profundidade ocupa 50% a mais no volume de água em relação à piscina de profundidade de 2,0m e quase o dobro em relação à piscina de 2,5m de profundidade. A regra da profundidade mostrou-se completamente falsa já que possui um raciocínio inconsistente e levaria uma decisão equivocada. Isso porque a medida a ser analisada é o volume de água e não a profundidade (altura) da piscina em questão. A profundidade até poderia definir a piscina que possui o maior volume de água mas isso só seria verdade se as proporções entre comprimento e altura fossem as mesmas para as três piscinas o que não necessariamente é verdade (nesse caso não foi).

Aplicação em banco de dados

E o que o exemplo das piscinas tem a ver com banco de dados ? Se formos pensar nos bancos de dados relacionais e suas tabelas, não teremos propriamente uma figura em três dimensões como uma piscina, mas para cada tabela haverá um plano bidimensional representado por linhas e colunas. O tamanho de uma tabela irá variar diretamente em função dessas duas dimensões, ou seja, quanto maior forem o número de linhas e o número de colunas maior será o tamanho da tabela.

É importante notar que o tamanho das tabelas normalmente é uma medida de grandeza muito melhor que a quantidade de linhas. Embora a quantidade de linhas seja um componente importante para calcular o tamanho da tabela, o tamanho das colunas é igualmente importante e comumente desprezado (quase ninguém pergunta quantas colunas existem na tabela, mas é muito comum perguntar o quantidade de linhas). Embora normalmente a tabela que tem a maior quantidade de linhas seja mais volumosa que a tabela que tem menos quantidade de linhas, isso não é uma verdade absoluta. O exemplo a seguir (SQL Server 2008), mostra uma situação em que isso não acontece:

– Cria uma tabela de Controle Orçamentário
CREATE TABLE ResumoConsolidado (
    Ano SMALLINT, CentroCusto CHAR(5),
    JanPlanejado MONEY, JanExecutado MONEY, JanRealizado MONEY,
    FevPlanejado MONEY, FevExecutado MONEY, FevRealizado MONEY,
    MarPlanejado MONEY, MarExecutado MONEY, MarRealizado MONEY,
    AbrPlanejado MONEY, AbrExecutado MONEY, AbrRealizado MONEY,
    MaiPlanejado MONEY, MaiExecutado MONEY, MaiRealizado MONEY,
    JunPlanejado MONEY, JunExecutado MONEY, JunRealizado MONEY,
    JulPlanejado MONEY, JulExecutado MONEY, JulRealizado MONEY,
    AgoPlanejado MONEY, AgoExecutado MONEY, AgoRealizado MONEY,
    SetPlanejado MONEY, SetExecutado MONEY, SetRealizado MONEY,
    OutPlanejado MONEY, OutExecutado MONEY, OutRealizado MONEY,
    NovPlanejado MONEY, NovExecutado MONEY, NovRealizado MONEY,
    DezPlanejado MONEY, DezExecutado MONEY, DezRealizado MONEY)

– Cria uma tabela de Lancamentos Contábeis
CREATE TABLE Lancamentos (
    IDLancamento INT IDENTITY(1,1),
    DataLancamento DATE,
    ValorLancamento MONEY,
    CentroCusto CHAR(5))

– Insere 1 milhão de resumos consolidados
INSERT INTO ResumoConsolidado VALUES (2009,‘MARKT’,
    150000000,120000000,100000000,
    150000000,120000000,100000000,
    150000000,120000000,100000000,
    150000000,120000000,100000000,
    150000000,120000000,100000000,
    150000000,120000000,100000000,
    150000000,120000000,100000000,
    150000000,120000000,100000000,
    150000000,120000000,100000000,
    150000000,120000000,100000000,
    150000000,120000000,100000000,
    150000000,120000000,100000000)
GO 1000000

– Insere 10 milhões de lançamentos
INSERT INTO Lancamentos VALUES (‘20091220’,1256.12,‘MARKT’)
GO 10000000

Após a execução do script temos uma tabela ResumoConsolidado com 1 milhão de registros e uma tabela de Lançamentos com 10 milhões de registros. Se formos seguir a "falsa" lógica, certamente a tabela de lançamentos é muito mais volumosa, pois, tem 10 milhões de registros contra apenas 1 milhão da tabela de lançamentos. Vejamos então o que o SQL Server nos revela sobre a volumetria dessas tabelas através do script abaixo:

EXEC sp_spaceused @objname = ‘ResumoConsolidado’, @updateusage = ‘true’
EXEC sp_spaceused @objname = ‘Lancamentos’, @updateusage = ‘true’

Após aproximadamente 50 minutos, o resultado das stored procedures é mostrado na tabela abaixo:

name rows reserved data index_size unused
ResumoConsolidado 1000000 320072 KB 320000 KB 8 KB 64 KB
Lancamentos 10000000 300808 KB 300752 KB 8 KB 48 KB

A primeira conclusão é que número de linhas não significa necessariamente muito espaço. A tabela Lancamentos possui 10 milhões de linhas e ocupa apenas 300MB. Com a quantidade de memória dos servidores atuais beirando as dezenas de GB, uma tabela de 300MB caberia em memória sem maiores dificuldades se fosse necessário (isso considerando a completa ausência de índices para facilitar o acesso). A segunda conclusão (e mais importante) é que ao contrário do que se pensava, a tabela com a maior quantidade de linhas não necessariamente é a mais volumosa. A tabela ResumoConsolidado possui 1 milhão de linhas e ocupa 320MB enquanto que a tabela Lancamentos possui dez vezes mais a quantidade de linhas e ainda ocupa menos espaço ficando em 300MB. Assim como a profundidade da piscina, a quantidade de linhas se analisada isoladamente pode levar à raciocínios e decisões equivocadas. A explicação é bem simples. Embora a tabela ResumoConsolidado tenha a menor quantidade de linhas, o tamanho de cada linha é significativamente maior. Para 320MB e 1 milhão de linhas teremos cerca de 320bytes por linha. No caso da Lancamentos, o tamanho da linha fica em torno de 30bytes (300MB / 10.000.000 de linhas). Se fóssemos representar as tabelas em forma bidimensional (linhas x colunas) teríamos as seguintes "áreas".

Se formos considerar que um retângulo possui a altura de 1 milhão de linhas e a largura de 30bytes, para a tabela ResumoConsolidado seria necessário um pouco mais que dez retângulos colocados horizontalmente. Como cada retângulo possui 30bytes com dez retângulos conseguimos preencher 300 bytes, mas 2/3 de um retângulo consegue-se os 20 bytes restantes e assim se obtêm os 320 bytes por linha. Multiplicando esses 320 bytes pela altura de 1 milhão de linhas chega-se a área ed 320MB retornada pela sp_spaceused. No caso da tabela Lancamentos, é necessário empilhar dez retângulos. Cada retângulo possui a largura de 30 bytes que é correspondente à largura do registro. Como a altura de cada retângulo é de 1 milhão de registros, é preciso dez retângulos para chegar na marca de 1 milhão de registros.

Com a utilização dos retângulos é fácil fazer as devidas comparações. A tabela ResumoConsolidado utiliza dez retângulos e mais um pedaço de um retângulo (2 / 3) enquanto que a tabela Lancamentos utiliza apenas 10 retângulos. A diferença entre elas é justamente a área vermelha que corresponde a 20MB. Como pode ser percebido, a tabela ResumoConsolidado ocupará mais espaço em relação à tabela Lancamentos ainda que a tabela ResumoConsolidado tenha menos linhas (apenas um retângulo de altura).

A relevância da quantidade de linhas

Se o tamanho de uma tabela depende da quantidade de linhas e da quantidade de colunas, uma vez que o tamanho da linha permaneça mais ou menos homogêneo é possível fazer julgamentos a partir da quantidade de linhas. Se uma tabela A possui uma linha de tamanho de 100 bytes com 25 milhões de linhas e uma tabela B possui uma linha de tamanho 150 btyes, mas apenas 1 milhão de linhas seguramente a primeira tabela é muito mais volumosa. Entretanto, dada as infinitas realidades de negócio e seus modelos de dados jamais poderemos obter um tamanho padrão para o tamanho de linhas e por consequência dificilmente a quantidade de linhas será um bom indicador de volumetria. Salvo casos absurdos como bilhões ou trilhões de linhas (que fatalmente produzem tabelas muito grandes) será sempre necessário "desconfiar" quando surgir o argumento "a tabela possui milhões de linhas".

Como podemos observar, o tamanho da tabela é uma métrica muito mais adequada que a quantidade de linhas. Ainda assim, o tamanho da tabela não é por si só uma medida que diga muita coisa sozinha. Uma base de 10TB pode até impressionar pelo tamanho, mas não significa necessariamente algo crítico ou que mereça uma atenção fora do normal. Se formos considerar os dados "vivos", ou seja, aqueles que são realmente acessados se restrijam a 100GB por exemplo, certamente a importância de 10TB ficará muito reduzida. 10TB significam muito espaço, longas janelas de backup, mas administrar 100GB não tem o mesmo peso que administrar 10TB efetivos.

O que é de fato importante visualizar é que criticidade e grandeza de uma base de dados dependem de vários fatores. Quantidade de acessos simultâneos à base de dados, tamanho das transações, quantidade de tabelas, total de aplicações diferentes que acessam a base são por exemplo fatores que podem dizer muito mais sobre uma determinada base de dados do que meramente "minha base tem mais de 10 milhões de linhas".

Quase todo processo de troubleshooting, consultoria, documentação e afins possivelmente perguntará "qual é a quantidade de linhas" ou algo parecido. É bem fácil e intuitivo. Isso não está errado. Está apenas incompleto. A quantidade de linhas é importante, mas sozinha nunca será relevante. É preciso sempre usar esse dado dentro de um contexto para evitar distorções.

[ ]s,

Gustavo

Recuperando o nome dos elementos em um documento XML no SQL Server

Boa Noite Pessoal,

Essa semana apareceu no fórum do MSDN uma thread bem interessante em relação a XML intitulada "Como descobrir qual a Tag "Mãe" de um XML". A pergunta era bem simples. Dado um certo documento XML, como fazer para descobrir o nome da tag root desse documento ? Tenho um certo interesse e facilidade com o assunto XML, mas essa pergunta (aparentemente bem fácil), me deu um certo nó para tentar responder. Depois de muito tentar montar uma expressão XQuery para retornar o nome da tag e procurado o Books OnLine por contas das funções XQuery permitidas (nem todo o padrão é implementado na íntegra), acabei me rendendo e indo buscar ajuda no Google. Minha alegria foi saber que após uma vasta pesquisa no google só encontrei uma solução baseada no CONVERT e no REPLACE. Minha tristeza foi simplesmente não encontrar uma solução baseada em XQuery.

Após postar a solução baseada puramente em TSQL acabei deixando essa questão um pouco de lado (minhas tentativas haviam sido frustadas até então). Eis que essa semana, ao iniciar a leitura de mais um livro de SQL Server (SQL Server MVP Deep Dives) acabei encontrando sem querer uma inspiração para a solução para esse problema. Ainda vou postar no MSDN, mas vamos reproduzir o exemplo.

declare @xml xml
set @xml = ‘<questionario>
    <Cod>0</Cod>
    <cod_item>1</cod_item>
    <cod_questionario>34</cod_questionario>
    <cod_pergunta>98</cod_pergunta>
    <cod_resposta>2</cod_resposta>
    <dat_resposta_text>1/1/1900 00:00:00</dat_resposta_text>
    <cod_tipo_objeto>1</cod_tipo_objeto>
    <cod_pergunta_condicao>0</cod_pergunta_condicao>
    <cod_resposta_condicao>0</cod_resposta_condicao>
  </questionario>’

Select Substring(XML,Charindex(‘<‘, XML)+1, Charindex(‘>’, XML) – Charindex(‘<‘, XML)-1) As Root
From (
 Select
 Cast(@xml as Varchar(Max)) As XML) As Data

Essa solução é capaz de retornar uma coluna root com o conteúdo questionario que é o nome da tag root. Essa solução é adaptada do link http://www.windows-tech.info/15/c0b6c1c5015db804.php. A dúvida era como fazer via XQuery ? Eu tenho algum conhecimento das funções de metadados, mas mesmo assim não consegui fazer funcionar. Adaptando um pouco a solução do livro, o correto seria a consulta abaixo:

declare @xml xml
set @xml = ‘<questionario>
    <Cod>0</Cod>
    <cod_item>1</cod_item>
    <cod_questionario>34</cod_questionario>
    <cod_pergunta>98</cod_pergunta>
    <cod_resposta>2</cod_resposta>
    <dat_resposta_text>1/1/1900 00:00:00</dat_resposta_text>
    <cod_tipo_objeto>1</cod_tipo_objeto>
    <cod_pergunta_condicao>0</cod_pergunta_condicao>
    <cod_resposta_condicao>0</cod_resposta_condicao>
  </questionario>’

SELECT node.value(N’fn:local-name(.[1])’, N’nvarchar(1000)’) As Root
FROM @xml.nodes(N'(//*)[1]‘) T(node)

Avançando um pouco mais na solução, há uma consulta interessante.

declare @xml xml
set @xml = ‘<questionario>
    <Cod>0</Cod>
    <cod_item>1</cod_item>
    <cod_questionario>34</cod_questionario>
    <cod_pergunta>98</cod_pergunta>
    <cod_resposta>2</cod_resposta>
    <dat_resposta_text>1/1/1900 00:00:00</dat_resposta_text>
    <cod_tipo_objeto>1</cod_tipo_objeto>
    <cod_pergunta_condicao>0</cod_pergunta_condicao>
    <cod_resposta_condicao>0</cod_resposta_condicao>
  </questionario>’

SELECT
    node.value(N’fn:local-name(.[1])’, N’nvarchar(1000)’) AS NodeName,
    CAST(node.query(N’./text()’As VARCHAR(100)) As NodeValue
FROM @xml.nodes(N’//*’) T(node)

A consulta revela os elementos e seus respectivos valores conforme a tabela abaixo:

NodeName NodeValue
questionario  
Cod 0
cod_item 1
cod_questionario 34
cod_pergunta 98
cod_resposta 2
dat_resposta_text 1/1/1900 00:00:00
cod_tipo_objeto 1
cod_pergunta_condicao 0
cod_resposta_condicao 0

O crédito da inspiração vai para o MVP Michael Coles. Ele pode ser encontrado no link http://www.sergeantsql.com.

[ ]s,

Gustavo

Criptografia, Autenticadores e Ataques de Inferência no SQL Server

Boa Noite Pessoal,

A primeira vez que fui apresentado aos recursos de criptografia do SQL Server em meados de 2006, ficou uma certa curiosidade sobre o parâmetro authenticator dos métodos EncryptByKey e EncryptByPassPhrase. As formas mais seguras de criptografia utilizam a combinação de chaves públicas e privadas e são representadas pelos métodos EncryptByAsymKey e EncryptByCert que por sua vez dispensam em sua assinatura o uso de autenticadores. Qual será então a utilidade desse parâmetro ? Se os métodos mais fortes o dispensam e os métodos mais fracos o utilizam como argumento opcional em que situação ele é necessário ?

Passei algum tempo sem encontrar a resposta, mas depois de estudar um pouco mais sobre segurança acabei descobrindo a sua real utilidade. Algumas formas de ataque como negação de serviço, sql injection, etc sempre me foram familiares, pois, já trabalhei muito para prevení-las e eventualmente já tive que ajudar algumas vítimas de ataques nessa modalidade. Entretanto, o termo "ataque de inferência" não é um dos que costumo observar no dia a dia e tampouco vejo mencionado nas bibliografias de SQL Server e nem sequer no Books OnLine. O uso de autenticadores nos métodos EncryptByKey e EncryptByPassPhrase serve justamente para prevenir esse tipo de ataque a dados criptografados.

Ao invés de me prender a explicações mais formais desse termo, vejamos em uma situação prática, porque apenas a criptografia sozinha talvez não seja eficaz na garantia da integridade e confidencialidade dos dados. O script a seguir cria uma pequena tabela de empregados com algumas colunas cadastrais clássicas e alguns dados mais sensitivos a exemplo da coluna salário.

– Cria uma tabela de empregados
CREATE TABLE Empregados (
    EmpregadoID INT IDENTITY(1,1) NOT NULL,
    CPF CHAR(11) NOT NULL,
    Nome VARCHAR(50) NOT NULL,
    Funcao VARCHAR(50) NOT NULL,
    Salario VARBINARY(256) NULL)

– Adiciona as constraints
ALTER TABLE Empregados ADD CONSTRAINT PK_Empregado PRIMARY KEY (EmpregadoID)

– Insere três empregados
INSERT INTO Empregados (CPF, Nome, Funcao, Salario)
VALUES (‘70036543921’,‘Ademilda Souza Gonçalves’,‘Diretora de TI’, NULL)

INSERT INTO Empregados (CPF, Nome, Funcao, Salario)
VALUES (‘51034681233’,‘Alexandre Pereira Neto’,‘Analista Sênior’, NULL)

INSERT INTO Empregados (CPF, Nome, Funcao, Salario)
VALUES (‘25341677542’,‘Edmilson Alves Medeiros’,‘Desenvolvedor Júnior’, NULL)

Os dados cadastrais estão concluídos, agora resta informar os salários para cadastro. Podemos utilizar várias opções no próprio SQL Server como certificados, chaves simétricas, as funções pwdEncrypt e pwdCompare, etc. Não entrarei no mérito do método de criptografia mais forte. Para o propósito desse artigo uma criptografia simétrica baseada em frase já é suficiente.

– Declara uma string para montar a chave simétrica
DECLARE @pwd NCHAR(8)
SET @pwd = N’ds-ospc!’

– Declara strings para armazenar os salários (tem que ser texto para atender a criptografia)
DECLARE @Salario1 VARCHAR(8), @Salario2 VARCHAR(8), @Salario3 VARCHAR(8)
SET @Salario1 = ‘10256.78’ – Salário da Ademilda
SET @Salario2 = ‘5360.29’ – Salário do Alexandre
SET @Salario3 = ‘2230.55’ – Salário do Edmilson

– Declara strings para armazenar as senhas criptografadas
DECLARE @Salario1C VARBINARY(256), @Salario2C VARBINARY(256), @Salario3C VARBINARY(256)
SET @Salario1C = ENCRYPTBYPASSPHRASE(@pwd,@Salario1)
SET @Salario2C = ENCRYPTBYPASSPHRASE(@pwd,@Salario2)
SET @Salario3C = ENCRYPTBYPASSPHRASE(@pwd,@Salario3)

– Atualiza as senhas dos clientes
UPDATE Empregados SET Salario = @Salario1C WHERE EmpregadoID = 1
UPDATE Empregados SET Salario = @Salario2C WHERE EmpregadoID = 2
UPDATE Empregados SET Salario = @Salario3C WHERE EmpregadoID = 3

Edmilson é um desenvolvedor que conseguiu uma credencial para acesso a tabela de empregados. Essa credencial possui permissão de SELECT e UPDATE na tabela de Empregados. Ele não sabe exatamente o salário de Ademilda, mas a julgar pela sua posição na empresa, ele sabe que trata-se de um salário muito superior ao seu. Assim sendo, ele efetua um UPDATE para igualar o seu salário ao dela.

UPDATE Empregados SET Salario = (SELECT Salario FROM Empregados WHERE EmpregadoID = 1)
WHERE EmpregadoID = 3

– Verifica as colunas de nome e salário
SELECT Nome, Salario FROM Empregados

Após a execução do comando, o salário de Ademilda e de Edmilson realmente são iguais (ainda que criptografados). Os códigos HASH podem divergir, mas o importante é que os salários da Ademilda e do Edmilson são iguais.

Nome Salário
Ademilda Souza Gonçalves 0x01000000CF13BEDF13CFC59006793E775C19BACA7605877816E31CB8D63DC548CCBDC56A
Alexandre Pereira Neto 0x010000001DCCD902BF968B61514AF6AF0DA3510D4DDD6F001A83D149
Edmilson Alves Medeiros 0x01000000CF13BEDF13CFC59006793E775C19BACA7605877816E31CB8D63DC548CCBDC56A

O que ocorreu foi uma inferência por parte do usuário Edmilson. Ele não sabe o salário da Ademilda, mas baseando-se na sua posição, ele consegue inferir que o salário é superior e portanto efetuou a atualização. No momento que ele visualizar o contracheque, ele conseguirá descobrir o salário da Ademilda. Podemos constatar que eles são iguais através da consulta abaixo:

– Declara uma string para montar a chave simétrica
DECLARE @pwd NCHAR(8)
SET @pwd = N’ds-ospc!’

SELECT Nome,
    CAST(
        CAST(DECRYPTBYPASSPHRASE(@pwd,Salario) As VARCHAR(8))
    As SMALLMONEY) As Salario
FROM Empregados

A consulta retorna os empregados e seus salários descriptografados.

Nome Salário
Ademilda Souza Gonçalves 10256,78
Alexandre Pereira Neto 5360,29
Edmilson Alves Medeiros 10256,78

De fato, a consulta confirma que Edmilson "promoveu-se" aumentando o seu salário. Vejamos agora como o uso de autenticadores pode prevenir esse problema.

– Declara uma string para montar a chave simétrica
DECLARE @pwd NCHAR(8)
SET @pwd = N’ds-ospc!’

– Declara strings para armazenar os salários (tem que ser texto para atender a criptografia)
DECLARE @Salario1 VARCHAR(8), @Salario2 VARCHAR(8), @Salario3 VARCHAR(8)
SET @Salario1 = ‘10256.78’ – Salário da Ademilda
SET @Salario2 = ‘5360.29’ – Salário do Alexandre
SET @Salario3 = ‘2230.55’ – Salário do Edmilson

– Declara strings para armazenar as senhas criptografadas com o uso de autenticadores
DECLARE @Salario1C VARBINARY(256), @Salario2C VARBINARY(256), @Salario3C VARBINARY(256)
SET @Salario1C = (SELECT ENCRYPTBYPASSPHRASE(@pwd,@Salario1,1,CAST(EmpregadoID As SYSNAME)) FROM Empregados WHERE EmpregadoID = 1)
SET @Salario2C = (SELECT ENCRYPTBYPASSPHRASE(@pwd,@Salario2,1,CAST(EmpregadoID As SYSNAME)) FROM Empregados WHERE EmpregadoID = 2)
SET @Salario3C = (SELECT ENCRYPTBYPASSPHRASE(@pwd,@Salario3,1,CAST(EmpregadoID As SYSNAME)) FROM Empregados WHERE EmpregadoID = 3)

– Atualiza as senhas dos clientes
UPDATE Empregados SET Salario = @Salario1C WHERE EmpregadoID = 1
UPDATE Empregados SET Salario = @Salario2C WHERE EmpregadoID = 2
UPDATE Empregados SET Salario = @Salario3C WHERE EmpregadoID = 3

Supondo que Edmilson irá tentar novamente efetuar um ataque por inferência, vejamos a consulta abaixo:

UPDATE Empregados SET Salario = (SELECT Salario FROM Empregados WHERE EmpregadoID = 1)
WHERE EmpregadoID = 3

– Verifica as colunas de nome e salário
SELECT Nome, Salario FROM Empregados

A consulta retorna os empregados e seus salários criptografados (parte do resultado foi truncado na exibição)

Nome Salário
Ademilda Souza Gonçalves 0x010000000D436D519FF7094F209CBA414C63D48C95DBCC868919A2AECEFDEBC53EC67…
Alexandre Pereira Neto 0x0100000041A7183124B568B47C5945C168C13382DBD8577476E3BE800F060F8CE220D…
Edmilson Alves Medeiros 0x010000000D436D519FF7094F209CBA414C63D48C95DBCC868919A2AECEFDEBC53EC67…

Realmente o salário de Ademilda e Edmilson continuam iguais na sua forma criptografada, agora vejamos se é possível descriptografá-los.

– Declara uma string para montar a chave simétrica
DECLARE @pwd NCHAR(8)
SET @pwd = N’ds-ospc!’

SELECT Nome,
    CAST(
        CAST(DECRYPTBYPASSPHRASE(@pwd,Salario,1,CAST(EmpregadoID As SYSNAME)) As VARCHAR(8))
    As SMALLMONEY) As Salario
FROM Empregados

A consulta mostra o resultado abaixo:.

Nome Salário
Ademilda Souza Gonçalves 10256,78
Alexandre Pereira Neto 5360,29
Edmilson Alves Medeiros NULL

De fato embora o salário criptografado seja exatamente o mesmo no momento de efetuar o retorno, o salário de Edmilson não pode ser descriptografado. Isso ocorreu porque embora a chave de criptografia utilizada seja exatamente a mesma (ds-ospc!), houve um autenticador a mais no processo de autenticação. Esse autenticador foi o EmpregadoID. O valor de EmpregadoID para Ademilda é 1 e para Edmilson é 2. Como ele usado como autenticador, não será possível que o salário de Edmilson seja corretamente descriptografado, pois, o EmpregadoID será diferente.

Para que o ataque tivesse êxito, seria necessário que Edmilson atualizasse também o seu valor para EmpregadoID, mas nesse caso, além dele não ter as permissões necessárias, a tabela possui uma unicidade em torno do ID que impediria que ele efetuasse a troca. Mesmo que ele conseguisse fazer isso, trocar o seu ID é semelhante a trocar sua identidade. Se esse ID for utilizado em outras tabelas ou for emitido no contracheque, ou ainda, for utilizado em outras tabelas, haverá vários indícios de fraude e certamente ele seria descoberto muito rapidamente. Imagine por exemplo que o ID seja utilizado para rastrear despesas históricas de viagem, reuniões de compromissos, etc. Nesse caso Edmilson teria que literalmente se responsabilizado por todas as atividades de Ademilda e esse tipo de fraude seria descoberto muito rapidamente.

Esse tipo de ataque não é exclusivo do SQL Server e tampouco de banco de dados. O fato de um dado ser criptografado não necessariamente significa que ele está protegido contra todos os tipos de violações de integridade e confiabilidade. O uso de autenticadores pode reduzir esse tipo de ameça embora não dispense uma boa gestão de permissões e guarda das chaves de criptografia utilizadas.

[ ]s,

Gustavo