TroubleShooting - DEADLOCK, como acontecem!

/* Olá Senhores, Senhoras e Senhoritas DBAS!!!
 

      Existe um erro muito comum no SQL , mas que poucas pessoas entendem o que significa. Ele se chama DEADLOCK.
   
      Geralmente é assim que ele se apresenta

      Msg 1205, Level 13, State 45, Line 2

      Transaction (Process ID 194) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

      OU

      Msg 1205, Level 13, State 45, Line 2

      A transação (ID do processo 194) entrou em deadlock em bloquear recursos com outro processo e foi escolhida como a vítima do deadlock. Execute a transação novamente.

-- O que é o DEADLOCK?

      Basicamente significa que a transação A requisitou um recurso que está sendo utilizado pela Transação B e que a transação B está requisitando  um recurso trancado pela transação A. Ou seja, entramos em um Loop, onde A está esperando B concluir e B só conclui quando A terminar.

      Quando o SQL Server detecta essa situação ele mata o processo com rollback menos custoso ou pela transação mais velha.
      Mas nada melhor para entender do que um exemplinho.

*/


-- Abra uma query no seu Managment Studio e rode o seguinte código


use TabelasTemporarias


CREATE TABLE Tabela1 (Valor int)

CREATE TABLE Tabela2 (Valor int)


-- Agora vamos fazer a transação A. Iniciando uma transação e inserindo um valor -- na tabela 1. Não commite



BEGIN TRAN

INSERT INTO Tabela1 VALUES (0)

 

-- Abra uma nova query e crie a transação B, inserindo um valor na tabela 2.
-- Não comite.



BEGIN TRAN

INSERT INTO Tabela2 VALUES (0)


-- Volte para a primeira query e execute um SELECT na tabela 2


SELECT * FROM Tabela2

-- Você vai notar que a query vai ficar executando sem concluir, pois ela está 
-- esperando pelo primeiro processo. (vide query abaixo)

SELECT
      r.session_id [SessionID da Tran A],
      r.blocking_session_id [SessionID da Tran B],
      r.wait_type
FROM
      sys.dm_tran_locks l
inner join sys.dm_exec_requests r on l.request_session_id = r.session_id
WHERE
      request_status = 'WAIT'

/*
SessionID da Tran A SessionID da Tran B wait_type
------------------- ------------------- -----------------------------------------
194                 391           LCK_M_S

*/   


-- Ou seja, ja estamos no ponto onde A depende de B.
-- Por fim, vamos fazer B depender de A.
-- Na query da transação B, execute um select na Tabela 1.


SELECT * FROM Tabela1


-- E Voilá! Em poucos segundos você terá um erro matando a transação A.

  

/* O intuito aqui é só mostrar o que acontece, pois acredito que entender o problema é a melhor forma de resolve-la, para formas de reduzir o Deadlock e entende-lo profundamente seguem dois links oficiais






Se tiver duvidas, sugestões e reclamações me mande um email!


Abraços e Feliz Natal!!!

*/

T-SQL Tips - Checar existência de Tabela Temporária!

/*

Senhores,


      Como faz meses que não coloco algo novo no blog, vou oferecer para vocês uma dica bem rápida.
    

      Como checar a existencia de uma tabela temporária!!!
    

      Achava que era impossivel ou extremamente complexo. Mas na verdade é bem simples!!
    

*/   


      CREATE TABLE #MeuTesteTemporario (Coluna1 int, Coluna2 varchar(100))

    

-- E aqui está o pulo do gato!! 
-- Ao invés de um EXISTS na sys.objects, use a função OBJECT_ID   

      IF OBJECT_ID('TempDb.dbo.#MeuTesteTemporario') IS NOT NULL

            DROP TABLE #MeuTesteTemporario


/*

      Simples, fácil, descomplicado!
    

      Obrigado Simon Sabin!

           

*/   

Crônica Corporativa - O Militar e o telefone.

            Há muito tempo atrás estava jogando conversa fora com um amigo cujo pai era militar. Ele me contava que achava muito estranho o jeito que o pai dele atendia ao telefone quando estava em serviço. A conversa era seca e sem rodeios, ele já atendia falando o próprio nome, pulava todos os “bons dias” e “como vais” tão comuns em nosso dia-a-dia e após responder perguntas desligava às vezes sem nem dizer um tchau.

A princípio quando você se depara com uma situação assim, a primeira impressão é de que você está falando com uma pessoa bem mal–educada, mas se analisarmos com quem estamos falando, não daria para esperar outra coisa.

Vendo um exército como uma corporação, é logico esperar que a cultura organizacional seja baseada em um espirito de combate. Em um combate, o telefone não é um canal para conversar e fazer amizades, ele é um meio de comunicação e na guerra comunicação é recurso. E recurso, principalmente em situações extremas, deve ser usado com economia e eficiência. Identifique-se, passe a mensagem, libere o canal. Sem desperdício, sem perda de tempo.

Da mesma maneira funciona o combate real, o caso extremo, os soldados estão atirando uns nos outros e se você é um cabo com meia dúzia de soldados ao seu comando e vê o inimigo sorrateiramente se aproximando, você não vai virar para um deles e dizer:

- Com licença soldado?

- Pois não senhor. - responderia o soldado solícito.

- Como está tudo bem? – você perguntará com um sorriso.

- Tudo ótimo, senhor, obrigado por perguntar. Em que posso ser útil?

- Soldado, você poderia, por favor, atirar naquele soldado inimigo que está chegando para nos matar.

E o soldado não responderia, pois ele já estaria morto. E provavelmente você também.



Muito disso acontece no nosso mundo corporativo. Não estamos em guerra, mas estamos o tempo todo competindo com outras organizações. E isso é levado muito a sério, pois se a organização perde a competição, nós perdemos nosso emprego e com ele nosso sustento, ou seja, não é a morte, mas é bem ruim.

E quando pensa especificamente no universo da TI, com seus servidores de missão crítica, alta disponibilidade, milhares de usuários e milhões de transações e isso tudo parado dependendo da sua habilidade para corrigir um bug inesperado já dá pra sentir um calafrio na espinha e o corpo se preparando para uma briga do tipo “faca nos dentes”.

E é nessas horas, quando o circo pega fogo, que vejo algumas pessoas se melindrando porque o chefe falou mais alto, porque o e-mail veio sem assinatura ou bom dia, porque o colega pediu sem falar “por favor”.

Vamos lá né amigo! Agora estamos na guerra cara! Graças a deus não é assim todo dia e logo que a coisa esfriar, o servidor subir e o ultimo soldado inimigo estiver morto e enterrado, eu volto a sorrir e te darei todos os “por favor”, “meu caro”  e “se não for muito incomodo” que você quiser, mas enquanto isso não chega:



- ATIRA CARA!!! ATIRA NELE!!! ATIIIIRAAAAAAA!!!!



Atenciosamente,

Felipe Antunes

T-SQL Tips - Agendando um Trace com SQL Profiler

/*
      Olá amigos!

      Esse post é bem simples. Uma receita de bolo para voce agendar a execução do seu SQL Server Profiler e retornar isso depois em uma consulta T-SQL. Ótimo para relatórios de Performance e analise de problemas.

      Então mãos a obra!

-- ## PASSO 1 - Criar o Script Tracer Definition.

      O Script Tracer Definition é um arquivo .SQL que contém todas as regras sobre o que será capturado, quanto tempo e quais critérios de filtro. Como ele é bem extenso e pouco intuitivo para ser feito na mão, o primeiro passo é utilizar o SQL Profiler para fazer o trabalho para você.


1 - Abra o SQL Profiler, crie um novo Trace.

     

2 - Selecione a checkbox SaveToFile e escolha um diretório na sua máquina para armazenarmos o resultado.

     

      Obs: Esse diretório na verdade deve ser um diretório do servidor onde voce executará o Job. Mas é possivel alterarmos essa configuração depois.

      Obs2: A opção SaveToTable não é possivel no job agendado (Checar)

3 - Selecione a checkbox "Enable Trace Stop Time". Isso dirá ao Profiler que hora ele deve parar de processar o trace.

      No meu exemplo, quero que o profiler rode das 09:00 até as 11:00.

     

      Obs: A configuração de Start fica por conta do schedule do job como veremos adiante.

4 - Configure os eventos que voce deseja. Em geral eu capturo as StoreProcedures e Batchs T-SQL que finalizaram.

     

     

5 - Clique em Run e na sequencia para a captura. Vá File/Export/Script Trace Definition/ For SQL Server 2005-2008

     

     

6 - Abra o Script no SQL Management Studio. Abaixo o script que foi gerado. Fiz comentários grifados em cada ponto importante.
    
*/
     

/****************************************************/

/* Created by: SQL Server 2008 Profiler             */

/* Date: 16/09/2011  17:52:36         */

/****************************************************/

-- Create a Queue

declare @rc int

declare @TraceID int

declare @maxfilesize bigint

declare @DateTime datetime

set @DateTime = '2011-09-19 11:00:00.000' --<< Aqui é o Stop Time que definimos.

set @maxfilesize = --<<---- Aqui é o tamanho maximo do arquivo TRC em Mb.


-- Please replace the text InsertFileNameHere, with an appropriate

-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension

-- will be appended to the filename automatically. If you are writing from

-- remote server to local drive, please use UNC path and make sure server has

-- write access to your network share

exec @rc = sp_trace_create @TraceID output, 0, N'F:\Traces\Trace_Agendado', @maxfilesize, @Datetime

/*<<-- Onde está em amarelo voce coloca o diretório do servidor e nome do arquivo desejado (sem o .trc) .>> */

if (@rc != 0) goto error

-- Client side File and Table cannot be scripted

-- Set the events
declare @on bit

set @on = 1

exec sp_trace_setevent @TraceID, 10, 15, @on

exec sp_trace_setevent @TraceID, 10, 16, @on

exec sp_trace_setevent @TraceID, 10, 1, @on

exec sp_trace_setevent @TraceID, 10, 9, @on

exec sp_trace_setevent @TraceID, 10, 17, @on

exec sp_trace_setevent @TraceID, 10, 10, @on

exec sp_trace_setevent @TraceID, 10, 18, @on

exec sp_trace_setevent @TraceID, 10, 11, @on

exec sp_trace_setevent @TraceID, 10, 12, @on

exec sp_trace_setevent @TraceID, 10, 13, @on

exec sp_trace_setevent @TraceID, 10, 6, @on

exec sp_trace_setevent @TraceID, 10, 14, @on

exec sp_trace_setevent @TraceID, 12, 15, @on

exec sp_trace_setevent @TraceID, 12, 16, @on

exec sp_trace_setevent @TraceID, 12, 1, @on

exec sp_trace_setevent @TraceID, 12, 9, @on

exec sp_trace_setevent @TraceID, 12, 17, @on

exec sp_trace_setevent @TraceID, 12, 6, @on

exec sp_trace_setevent @TraceID, 12, 10, @on

exec sp_trace_setevent @TraceID, 12, 14, @on

exec sp_trace_setevent @TraceID, 12, 18, @on

exec sp_trace_setevent @TraceID, 12, 11, @on

exec sp_trace_setevent @TraceID, 12, 12, @on

exec sp_trace_setevent @TraceID, 12, 13, @on

-- Set the Filters

declare @intfilter int

declare @bigintfilter bigint



exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - c16c42f1-c66c-4152-8fc1-d92a44fef880'

exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - fd8af8d3-5b18-49b2-93d4-048a74902059'

-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1



-- display trace id for future references
select TraceID=@TraceID

-- Loop para aguardar a conclusão do job

DECLARE @Running int = 1

WHILE @Running = 1
     BEGIN
            WAITFOR DELAY '00:01:00'

            IF EXISTS(SELECT * FROM sys.traces WHERE ID = @TraceID)

                  SET @Running = 1
            else
                  SET @Running = 0 
      END


goto finish

error:
select ErrorCode=@rc

finish:
go


--################ FIM DO SCRIPT ##############################

-- OBSERVAÇAO: Voce pode fazer um teste do script no próprio SSMS. É só executar -- o script, se ele foi bem sucessido ele retorna um TraceID que pode ser
-- consultado na tabela sys.trace

/*

-- ## PASSO 2 - Criação de Job.

Vamos criar um Job com dois passos

      1 - No primeiro será o da execução do trace.

     

      2 - Vamos acrescentar um pequeno loop T-SQL que checa se o Trace conclui logo depois do "select TraceID=@TraceID". Veja abaixo.

*/

(…)

      select TraceID=@TraceID
   

      DECLARE @Running int = 1

      WHILE @Running = 1

            BEGIN

                  WAITFOR DELAY '00:01:00'

                  IF EXISTS(SELECT * FROM sys.traces WHERE ID = @TraceID)

                        SET @Running = 1

                  else

                        SET @Running = 0 

            END

(…)

/*   

      3 - O segundo passo é colocar o conteudo do trace em uma tabela usando a function fn_trace_gettable

*/   

      SELECT TextData, CPU,Reads,Writes,Duration
      into SuaBaseDeDados.dbo.SuaTabela
       -- Coloque acima a base e tabela de sua referencia
      FROM fn_trace_gettable ( N'F:\Traces\Trace_Agendado.trc' , DEFAULT )


     


--    4 - E pronto só agendar o Job e brincar com sua tabelinha.

/*

## BEGIN BONUS ##

        Se voce chegou até aqui é porque o assunto realmente te interessou. Existe um pequeno truque para você tornar o Stop Time dinamico.

       É bem simples e torna o job ainda mais versátil. Se quiser saber como faz, ou se voce descobriu como fazer me mande um email.

## END BONUS ##

      Gostou da dica? Achou algum bug? Tem alguma pergunta?? Fique a vontade para me contactar e comentar abaixo!

      email: fjantunes@gmail.com

      E Muito Obrigado pela visita!

*/

Modelagem - Função para calcular dias úteis em T-SQL

/*#######################################################################
ATENÇÃO: ESTE POST TODO É UM SCRIPT! SE VOCE NÃO TIVER PACIENCIA PARA LER, BASTA COPIA-LO EM SEU
MANAGEMENT STUDIO E EXECUTA-LO NA SUA BASE DE PREFERENCIAS
VOCE TERÁ UMA TABELA DATAS E A FUNÇÃO FN_DIASUTEIS PRONTA PRA USO!
#######################################################################*/

/* Olá caro leitor!

   Esses dias no fórum da MSDN um colega perguntou se era possivel criar uma função que calcula-se quantos dias uteis tivemos entre uma data e outra. Não existe nenhuma função nativa no SQL que faça esse calculo, no entanto com alguns calculos e uma tabela do tamanho de um MP3 (3Mb) conseguimos montar essa função cobrindo um período de dois séculos !

   Esse tipo de tabela também é bem comum entre o pessoal que trabalha com Data Warehouse.

  Então mãos a obra!

*/ 

/* ## Passo 1 - CONSTRUINDO E POPULANDO A TABELA ------------------------

      Nesse passo, montamos uma estrutura bem simples, que armazenará todas os dias de 1900 a 2099, incluindo alguns atributos que podem ser interessantes.

      Na sequencia com um WHILE para popularmos os dados.     

      Obs: Antes que voce questione. Existe uma razão para o campo DiaUtil ser Default 1 e tipo tinyint (explicações adiante)
 
*/

-- Tabela

CREATE TABLE Datas (
      Data datetime NOT NULL CONSTRAINT PK_Datas PRIMARY KEY,
      DiaSemana varchar(13) NOT NULL,
      DiaSemanaNum tinyint NOT NULL,
      Dia tinyint  NOT NULL,
      Mes tinyint NOT NULL,
      NomeMes varchar(9) NOT NULL,
      Ano smallint NOT NULL,
      DiaUtil tinyint NOT NULL DEFAULT (1)
      )

GO

--- Preenchimento da tabela

DECLARE @DataInicial DATETIME = '01/01/1900'
DECLARE @DataFinal DATETIME = '31/12/2099'
 
WHILE @DataInicial <= @DataFinal
      BEGIN
            INSERT INTO Datas
            SELECT
                  @DataInicial,
                  DATENAME(WEEKDAY,@DataInicial),
                  DATEPART(WEEKDAY,@DataInicial),
                  DATEPART(DAY,@DataInicial),
                  MONTH(@DataInicial), 
                  DATENAME(MONTH,@DataInicial),
                  YEAR(@DataInicial),
                  1

            SET @DataInicial = DATEADD(DAY,1,@DataInicial)
      END
      GO

/*
      Notou como a tabela ficou pequena???
    
      Execute um space used e você ficará impressionado.

*/

--- Espaço ocupado

      sp_spaceused 'Datas'
      GO

/*
rows        reserved        data               index_size         unused
----------- --------------- ------------------ ------------------ -------
73049       3344 KB         3312 KB            24 KB              8 KB
*/

/* ## Passo 2 - Marcando os dias inuteis (na visão de seu chefe)

      OK. Dados prontos, só precisamos marcar os tres tipos de dias não úteis que possuimos

      1 - Finais de Semana

      2 - Feriados Nacionais Fixos (dependendo do seu cenário, fique a vontade para incluir os feriados estaduais e municipais)

      3 - Feriados Nacionais variáveis (basicamente o Carnaval (EBA!) e o Corpus Christi (???). Pois ambos seguem o Calculo Eclesiástico quem em 325 o Concilio de Nicea assim os definiu (SQL Server também é cultura!!!)

*/


/*  Fins de Semana: Basta zerar a coluna DiaUtil onde o dia da semana numerico for igual a 7 - Sabádo e 1 – Domingo */

      UPDATE Datas SET DiaUtil = 0 WHERE DiaSemanaNum IN (7,1)


/*  Feriados Nacionais Fixos: Para cada combinação de dia e mês (o ano não importa) um update nos feriados.
  Inclua aqui dias 24/12 ou 31/12 conforme seu cenário exigir */

    

      UPDATE Datas
      SET DiaUtil = 0
      WHERE
            (Dia = 1 AND Mes = 1)   -- Dia da Confraternização Mundial
      OR
            (Dia = 21 AND Mes = 4) -- Dia de Tiradentes
      OR
            (Dia = 1 AND Mes = 5)   -- Dia do Trabalho
      OR
            (Dia = 7 AND Mes = 9)   -- Independencia do Brasil
      OR
            (Dia = 12 AND Mes = 10)   -- Dia de Nossa Senhora Aparecida
      OR
            (Dia = 2 AND Mes = 11)   -- Finados
      OR
            (Dia = 15 AND Mes = 11)   -- Proclamação da Republica
      OR
            (Dia = 25 AND Mes = 12)   -- Natal


/* ## Passo 3 - Feriados Nacionais variaveis (Carnaval e Corpus Christi).

      Aqui a coisa complica um pouco, pois temos que converter em T-SQL as fórmulas que o Concilio de Nicea decidiu a uns 1700 anos atrás (será que o
Papa da época imaginou isso um dia?)


      Minha referência veio do site http://inf.ufrgs.br/~cabral/Pascoa.html. Do professor da UFRGS - Profº Roberto Cabral de Mello Borges.

      Lá ele explica o calculo mais detalhadamente e dá exemplos. Além de uma tabela completa com datas ja calculadas.

      Basicamente aqui, farei um outro Loop com WHILE passeando por cada ano e usando a formula para identificar o Domingo de Pascoa. Através dele chegaremos na Terça de Carnaval (Domingo de Pascoa menos 47 dias) e no Corpus Christi (Domingo de Pascoa + 60 dias)
*/

-- Constantes X e Y e variaveis de calculo
DECLARE @X int = 24
DECLARE @Y int = 5
DECLARE @a int ,@b int, @c int,@d int,@e int

-- Variaveis de data
DECLARE @Ano smallint = 1900
DECLARE @Dia tinyint
DECLARE @Mes tinyint
DECLARE @DomingoPascoa datetime


WHILE @Ano <= 2099
      BEGIN
      --- Calculo de Dia e Mes
            SET @a = @Ano % 19
            SET @b = @Ano % 4
            SET @c = @Ano % 7
            SET @d = (19 * @a + @X) % 30
            SET @e = (2 * @b + 4 * @c + 6 * @d + @Y) % 7

            IF (@d + @e) > 9
                  BEGIN
                        SET @Dia = @d + @e - 9
                        SET @Mes = 4
                  END
            else
                  BEGIN
                        SET @Dia = @d + @e + 22
                        SET @Mes = 3
                  END
              

/* Correção dos casos particulares que ocorrem duas vezes por século (2049 e 2076 no século 21) */

            IF @Ano IN (2049,2076)
                  BEGIN
                        IF @Dia = 26
                             SET @Dia = 19
                        else
                             SET @Dia = 18                     
                  END  

            -- Obtém o domingo de pascoa            
            SET @DomingoPascoa = CAST(CAST(@Dia as varchar) + '/' + CAST(@Mes as varchar) + '/' + CAST(@Ano as varchar) as datetime)

            -- Marca a terça feira de carnaval (pascoa - 47 dias)
            UPDATE Datas
            SET DiaUtil = 0
            WHERE  Data = DATEADD(day,-47,@DomingoPascoa)

            -- Marca a quinta feira Corpus Christi (pascoa + 60 dias)
            UPDATE Datas
            SET DiaUtil = 0
            WHERE  Data = DATEADD(day,60,@DomingoPascoa)


            SET @Ano = @Ano + 1
      END

GO



/* ## Passo 4 - Enfim a função!!!

      Eis a razão de ter escolhido TinyInt no campo DiaUtil. Se tivesse escolhido o campo Bit, não conseguiria aplicar o SUM. 
      O campo data como PK também garante uma performance excepcional no calculo.

*/

      CREATE FUNCTION dbo.fn_DiasUteis (@DataInicial datetime,
                                        @DataFinal datetime)
      RETURNS int
      AS

            BEGIN

                  DECLARE @DiasUteis int

                  SELECT
                             @DiasUteis = SUM(DiaUtil)
                  FROM
                        Datas
                  WHERE
                        Data BETWEEN      @DataInicial AND @DataFinal


                  RETURN @DiasUteis
            END  
GO


/* ## E Pronto!

      Essa funçãozinha ficará eternamente ativa, não exigirá manutenção nos próximos 88 anos. O que é tempo suficiente para voce relaxar e descobrir e refletir sobre quantos dias uteis ocorreram desde o dia que nasceu!
   
*/
 

SELECT dbo.fn_DiasUteis ('21/04/1982','18/08/2011') 
 

/*
      Gostou da dica? Achou algum bug? Tem alguma pergunta?? Fique a vontade para me contactar e comentar abaixo!

      email: fjantunes@gmail.com


*/