Migrando Blog https://luizflsilva.wordpress.com/

Curtindo cada vez mais a criar conteudo que possa levar aprendizado a amantes e profissionais da area de banco de dados, estou migrando o blog para https://luizflsilva.wordpress.com/

Porque um simples select count as vezes demora as vezes é rapido

Ola pessoal, porque será que uma uma mesma consulta como um "simples select count" hora é lento, hora rapido, oque acontece ?

Nesse video vamos fazer um teste em uma base onde configurei o SQL Server para rodar com no maximo 2Gb de memória ("não se assuste, foi intencional !!!").

Demo efetuado com SQL Server 2019 Dev para treinamentos, em anexo segue os scripts para os interessador em fazer a demo.

Criei duas tabelas sendo uma com 1Gb de dados e outra com 2Gb de dados, vamos ver oque acontece quando consultar essas tabelas ?, lembrando que o SQL está configurado para rodar com no máximo 2 Gbs de memória.


Para quem quiser ver o link direto no youtube: https://www.youtube.com/watch?v=phLGBYSz-hY&feature

Intalando SMSS versão em inglês

Vamos falar hoje sobre a instalação do Microsoft SQLServer Management Studio, nas ultimas disponibilizações para download o arquivo de setup tende a baixar com a linguagem utilizada no windows no meu caso com a linguagem padrão em português, da ainda para mudar a linguagem nas configurações porem fica ainda uma tradução e as mensagens de retorno no result também traduzidas.

Porem quem está acostumado a trabalhar com banco de dados, mesmo que não saiba nada de inglês vai preferir usar a ferramenta em inglês ao invés de português.

Quando entrar no link para baixar o setup Ssmsja vem como PTB, mas para burlar esse download automático no site da microsoft tem o link para versões anteriores 

Na Imagem abaixo, validando o download padrão e o arquivo de instalação em ptb ( que ja instala com a linguagem padrão em português)

Na próxima imagem, note que mesmo que mudar o link do site de download para (en) o download vai permanecer em português:

Contornando essa situação de maneira simples, percorra pelo site do download até Idiomas Disponíveis, e baixa a versão  Inglês (Estados Unidos), após o download, inicie a instalação normal, nesse caso podem ocorrer dois erros que bloqueiam a instalação com a mensagem Setup Blocked como na imagem abaixo:

Caso venha acontecer esse bloqueio na instalação você pode analisar o logs as alternativas que me ocorreram foi necessário desinstalar versão do SSMS anterior 18.> e excluir a chave de registro da instalação anterior:

Windows+R: reg consulta "HKLM\SOFTWARE\WOW6432Node\Microsoft\Microsoft SQL Server Management Studio" /reg:32 /s

Após essas etapas é só curtir a nova versão totalmente me inglês.

É isso ai pessoal, espero que esse post ajude alguém, até a próxima

tese

Configurar DataBaseMail via Script

Fala galera, voltando hoje para demonstrar como configurar o DatabaseMail do SQL Server totalmente via script.

No script abaixo, siga os passos descristos e você terá o seu DatabaseMail configurado em poucos minutos:

-- Passo 1 -- Validar se o serviço está Ativo
 EXECUTE msdb.dbo.sysmail_help_status_sp
 -- Se estiver parado (STOPPED) rodar o script abaixo para iniciar o serviço

 EXEC msdb.dbo.sysmail_start_sp;

 -- Valida o status para ver se o retorno é (STARTED )

-- Passo 2 -- Criar uma conta ( Account )
 /*
  Nesse passo ( Criar uma conta ) que é definido as configurações de envio do email como:
  Servidor de e-mail e-mail de envio: @mailserver_name
  Porta de Comunicação ( Padrão do Servidor de e-mai ): @port
  habilitar SSL (criptografia durante o envio de dados): @enable_ssl
  Descrição da Conta: @description
  Nome da conta dentro do SQL: @account_name
  Nome que aparecerá como remetente do e-mail: @display_name
  E-mail de envio: @email_address
  Usuario do e-mail: @username
  Senha do e-mail: @password

 */
 -- Adicionar conta de e-mail
 execute msdb.dbo.sysmail_add_account_sp
  -- Dados fíxos
  @mailserver_name = 'smtp.gmail.com', -- endereço do servidor de envio de e-mails
  @port = 587, -- porta de comunicação
  @enable_ssl = 1, -- habilitar SSL (criptografia durante o envio de dados)
  -- Dados da sua conta
  @description = 'Teste de Envio de e-mail',
  @account_name = 'Conta Teste', -- nome da conta dentro do SQL
  @display_name = 'E-mail enviado pela Conta Teste',   -- Nome que aparecerá como remetente do e-mail
  @email_address = 'conta_teste@seudominio.com',
  @username = 'conta_teste@seudominio.com',
  @password = 'SuaSenha'

-- Passo 3 -- Criar um Profile de e-mail

 execute msdb.dbo.sysmail_add_profile_sp
  @profile_name = 'Profile Teste',
  @description = 'Profile para Teste de e-mail'

-- Passo 4 -- Vincular a Conta ao Profile
 /*
  Nesse passo vinculamos a conta com o profile

 */
 execute msdb.dbo.sysmail_add_profileaccount_sp
  @profile_name = 'Nome do Profile que cricou no Passo 3',
  @account_name = 'Nome da Conta que cricou no Passo 2',
  @sequence_number = 1


-- Passo 5 -- Enviar um e-mail de Teste

 execute msdb.dbo.sp_send_dbmail 
  @profile_name = 'Profile Teste', -- Nome do Profile definido no Passo 3
  @recipients = 'lfernandolsilva@gmail.com', -- Destinatário do e-mail
  @subject = 'Assunto - Teste Database Mail',
  @body = 'Teste nova conta de e-mail'



-- Passo 6 -- Consulta o stado das filas de envio:
-- A consulta abaixo, retorna o status dos ultimos 1000 emails enviado

 SELECT TOP 1000
  P.name   AS "Profile de Envio",
  C.name   AS "Conta de Envio",
  a.send_request_date AS DataEnvio,
  a.sent_date AS DataEntrega,
  (CASE
   WHEN a.sent_status = 0 THEN '0 - Aguardando envio'
   WHEN a.sent_status = 1 THEN '1 - Enviado'
   WHEN a.sent_status = 2 THEN '2 - Falhou'
   WHEN a.sent_status = 3 THEN '3 - Tentando novamente'
  END) AS Situacao,
  a.from_address AS Remetente,
  A.recipients AS Destinatario,
  a.subject AS Assunto,
  a.reply_to AS ResponderPara,
  a.body AS Mensagem,
  a.body_format AS Formato,
  a.importance AS Importancia,
  a.file_attachments AS Anexos,
  a.send_request_user AS Usuario,
  B.description AS Erro,
  B.log_date AS DataFalha
 FROM  msdb.dbo.sysmail_mailitems   A WITH(NOLOCK)
  JOIN msdb.dbo.sysmail_profile   P WITH(NOLOCK) ON A.PROFILE_ID = P.PROFILE_ID
  JOIN msdb.dbo.sysmail_account   C WITH(NOLOCK) ON A.sent_account_id = c.account_id
  LEFT JOIN msdb.dbo.sysmail_event_log B WITH(NOLOCK) ON A.mailitem_id = B.mailitem_id
 ORDER BY send_request_date DESC

Funções LAG e LEAD

Ola pessoal passando hoje aqui para falar um pouco sobre as funções LAG e LEAD

Essas funções pode ser muito bem aproveitas em variados relatórios, analise de Inconsistências em consultas analiticas e muitas outras...

LAG - Acessa os dados de uma linha anterior no mesmo conjunto de resultados sem usar uma autojunção começando com SQL Server 2012 (11.x). LAG fornece acesso a uma linha a um determinado deslocamento físico que antecede a linha atual. Use essa função analítica em uma instrução SELECT para comparar valores na linha atual com valores em uma linha anterior.

Fonte: https://docs.microsoft.com/pt-br/sql/t-sql/functions/lag-transact-sql?view=sql-server-ver15

LEAD - Acessa os dados de uma linha seguinte no mesmo conjunto de resultados sem o uso de uma autojunção começando pelo SQL Server 2012 (11.x). LEAD fornece acesso a uma linha a um determinado deslocamento físico que segue a linha atual. Use essa função analítica em uma instrução SELECT para comparar valores na linha atual com valores em uma linha seguinte.

Fonte: https://docs.microsoft.com/pt-br/sql/t-sql/functions/lead-transact-sql?view=sql-server-ver15

Mas Luiz, explica melhor esse negocio de LAG e LEAD ai, bom vamos la galera:

No exemplo abaixo tenho duas tabelas "TB_CLIENTE" ( Cadastro de Clientes ) e "TB_CONTATO" que vai armazenar o Historico de quantas vezes o cliente  recebeu uma ligação ou que a empresa ligou para esse cliente.

Observe a imagem abaixo, que na tabela "TB_CONTATO" ha 6 registros para o cliente Luiz

Descrição das colunas:

"Ordem Contato"-- Ordem do registro em que foi contactado ( usei um ROW_NUMBER()  da coluna do ID_CLIENTE ordenando pela Data Inicio do Contato

"Nome Cliente"-- Nome do Cliente ( Tabela Cliente )

"Data Hora Inico Contato"- Coluna DT_INICIO_CONTATO que representa o Horario de Inico do Contato desse registro

"Data Hora fim Contato"- Coluna DT_FIM_CONTATO que representa o Horario  Final do Contato desse registro

-- LAG  ( Circulado em Verde na imagem abaixo )

"Data Hora Inico Contato ( Anterior )"- Coluna DT_INICIO_CONTATO porem usando a função LAG para retornar a Data Hora Inicio do Contato do registro Anterior ( Note que caso o registro "Data Hora Inico Contato" seja o primeiro contato com o cliente essa coluna vai retornar NULL )

"Data Hora fim Contato ( Anterior )"- Coluna DT_FIM_CONTATO porem usando a função LAG para retornar a Data Hora Final do Contato do registro Anterior

-- LEAD  ( Circulado em Azul na imagem abaixo )

"Data Hora Inico Contato ( Proximo )"- Coluna DT_INICIO_CONTATO porem usando a função LEAD para retornar a Data Hora Inicio do Contato do proximo registro de contato com o cliente( Note que caso esse "Data Hora Inico Contato" seja o ultimo contato com o cliente essa coluna vai retornar NULL )

"Data Hora fim Contato ( Proximo )"- Coluna DT_FIM_CONTATO porem usando a função LEAD para retornar a Data Hora Final do Contato do proximo registro de contato com o cliente

Script utilizado nessa demo:

--DROP TABLE TB_CLIENTE

CREATE TABLE TB_CLIENTE (
 ID_CLIENTE INT IDENTITY(1,1) PRIMARY KEY,
 NM_CLIENTE VARCHAR(80)
)

--DROP TABLE TB_CONTATO

CREATE TABLE TB_CONTATO (
 ID_CONTATO INT IDENTITY PRIMARY KEY,
 DT_INICIO_CONTATO DATETIME,
 DT_FIM_CONTATO DATETIME,
 ID_CLIENTE INT
)
TRUNCATE TABLE TB_CLIENTE

INSERT INTO TB_CLIENTE ( NM_CLIENTE ) VALUES ('LUIZ FERNANDO')


INSERT INTO TB_CONTATO ( DT_INICIO_CONTATO,DT_FIM_CONTATO,ID_CLIENTE) VALUES ('20200130 10:59:59','20200130 11:03:30',1)
INSERT INTO TB_CONTATO ( DT_INICIO_CONTATO,DT_FIM_CONTATO,ID_CLIENTE) VALUES ('20200130 14:10:00','20200130 14:14:30',1)
INSERT INTO TB_CONTATO ( DT_INICIO_CONTATO,DT_FIM_CONTATO,ID_CLIENTE) VALUES ('20200201 09:05:03','20200201 09:06:30',1)
INSERT INTO TB_CONTATO ( DT_INICIO_CONTATO,DT_FIM_CONTATO,ID_CLIENTE) VALUES ('20200202 13:11:03','20200202 13:17:50',1)
INSERT INTO TB_CONTATO ( DT_INICIO_CONTATO,DT_FIM_CONTATO,ID_CLIENTE) VALUES ('20200203 10:01:11','20200203 10:04:31',1)
INSERT INTO TB_CONTATO ( DT_INICIO_CONTATO,DT_FIM_CONTATO,ID_CLIENTE) VALUES ('20200203 19:07:31','20200203 19:10:25',1)

SET DATEFORMAT DMY

SELECT
 ROW_NUMBER() OVER(PARTITION BY CO.ID_CLIENTE ORDER BY CO.DT_INICIO_CONTATO ASC) AS "Ordem Contato",

 C.NM_CLIENTE AS "Nome Cliente",
 CO.DT_INICIO_CONTATO  AS "Data Hora Inico Contato",
 CO.DT_FIM_CONTATO   AS "Data Hora fim Contato",


 LAG(CO.DT_INICIO_CONTATO) OVER (ORDER BY CO.ID_CLIENTE)  AS "Data Hora Inico Contato ( Anterior )",
 LAG(CO.DT_FIM_CONTATO) OVER (ORDER BY CO.ID_CLIENTE)  AS "Data Hora fim Contato ( Anterior )",

 LEAD (CO.DT_INICIO_CONTATO) OVER (ORDER BY CO.ID_CLIENTE)  AS "Data Hora Inico Contato ( Proximo )",
 LEAD (CO.DT_FIM_CONTATO) OVER (ORDER BY CO.ID_CLIENTE)  AS "Data Hora fim Contato ( Proximo )"

FROM TB_CLIENTE C
JOIN TB_CONTATO CO ON C.ID_CLIENTE = CO.ID_CLIENTE

#FicaDica

Deus lhe ajuda a carregar os fardos que são pesados demais para você. Ele está com você para lhe sustentar e fortalecer. Entregue seus problemas a Deus e ele vai lhe ajudar

sp_who3

Veterana sp_who, ainda muito util para uma breve analise no SQL Server

Não é nativa do SQL, segue portanto link para Download: https://bit.ly/39PsmGn

sp_who3 quem está ativo;

Parâmetros de consulta:

sp_who3 1 - quem está consumindo a memória;

sp_who3 2 - que possui planos em cache que consumiram a CPU mais cumulativa (10 principais);

sp_who3 3 - quem está conectado e quantas sessões possui;

sp_who3 4 - quem está ocioso que possui transações abertas;

sp_who3 5 - quem está executando tarefas que usam tempdb (top 5);

sp_who3 6 - quem está bloqueando.



Lógico que tem muitas outras rotinas que retorna informações ainda mais completas que a sp_who3 como:

sp_whoisactive desenvolvida por Adam Machanic (http://whoisactive.com);

sp_BlitzWho desenvolvida por Blent Ozar - (https://www.brentozar.com/first-aid/sp_blitzwho/);

Monitorar Processo Criação de Index (SQL Server)

Dale galera hoje vamos falar um pouco de uma rotina para monitorar o processo de criação de Index

Quem que nunca precisou de criar um index em horário de produção e ficou se cagando todo !!!

Bom a menos que a versão do SQL Server seja 2016 ou superior e Enterprise, que possibilita utilizar a opção de criar um index ONLINE ( dai já é outra historia ).

Para criar um index em horário de operação tem que acima de tudo entender a regra de negócio aplicada assim como validar os impactos que esse pode causar com a alocação da tabela. Para possibilitar a analise do tempo de criação e progresso criei a view VW_PROGRESS_SESSION.

Para que seja possível acompanhar o progresso na criação de um Index, é preciso setar o parâmetro SET STATISTICS PROFILE ON, e executar junto com o comando de Create do Index , como no exemplo abaixo:

SET STATISTICS PROFILE ON

GO

CREATE INDEX [IX_nome_index] ON [Nome_Database].[dbo].[Nome_Tabela] ([Coluna_index])
INCLUDE ([Colunas_include])
WITH(FILLFACTOR= 80, ONLINE=ON); -- Hint ONLINE disponível somente na versão Enterprise do SQL Server

Após rodar o script de create do index, abra uma nova aba do Management Studio e rode a consulta da View, como no exemplo abaixo:

SELECT
*
FROM VW_PROGRESS_SESSION
WHERE COMMAND = 'CREATE INDEX'

Será retornado um resultado do progresso com as colunas abaixo:

command,  -- Comando que esta sen executado No caso ( 'CREATE INDEX')
session_id, --  Id da Seção
start_time,  --  Data hora que iniciou a execução
text,            -- Script que está rodando na seção
Ds_Operador_Atual,
Qt_Linhas_Total,
Qt_Linhas_Processadas,
Qt_Linhas_Restantes,
Vl_Percentual_Completado,
Qt_Segundos_Decorridos,
Qt_Segundos_Restantes,
Dt_Prevista,
host_name,
program_name

Imagem de exemplo:

Bom saber que a estimativa prevista do percentual completado, e Data previsa para termino é calculado conforme o total de linhas processadas, ou seja, é uma estimativa


Script de criação da view VW_PROGRESS_SESSION:

/*
SELECT
 * 
FROM VW_PROGRESS_SESSION
WHERE COMMAND = 'CREATE INDEX'
--WHERE COMMAND = 'UPDATE'
--'CREATE INDEX'
--'ALTER INDEX'

*/

CREATE VIEW VW_PROGRESS_SESSION
AS
WITH CTE_SESSION AS 
(
    SELECT
        B.session_id,
  C.command ,
        B.login_time,
        B.[host_name],
        B.[program_name],
        B.nt_user_name,
        B.original_login_name,
        D.[text],
        MIN(C.start_time) AS start_time,
        SUM(A.[row_count]) AS Qt_Linhas_Processadas,
        SUM(A.[estimate_row_count]) AS [Qt_Linhas_Total],
        MAX(A.last_active_time) - MIN(A.first_active_time) AS [Qt_Tempo_Decorrido_MS],
        MAX(IIF(A.[close_time] = 0 AND A.[first_row_time] > 0, [physical_operator_name], N'<Transition>')) AS [Ds_Operador_Atual]
    FROM
        sys.dm_exec_query_profiles A
        JOIN sys.dm_exec_sessions B ON B.session_id = A.session_id
        JOIN sys.dm_exec_requests AS C WITH (NOLOCK) ON B.session_id = C.session_id
        CROSS APPLY sys.dm_exec_sql_text(C.[sql_handle]) D
    --WHERE    C.command = 'CREATE INDEX'
    GROUP BY
        B.session_id,
  C.command ,
        B.login_time,
        B.[host_name],
        B.[program_name],
        B.nt_user_name,
        B.original_login_name,
        D.[text]
),
CTE_RESULT AS 
(
    SELECT
         *,
         ( [Qt_Linhas_Total] - Qt_Linhas_Processadas ) AS [Qt_Linhas_Restantes],
         ( [Qt_Tempo_Decorrido_MS] / 1000.0 ) AS [Qt_Segundos_Decorridos]
     FROM
         CTE_SESSION
)
SELECT
 command ,
    session_id,
    start_time,
    [text],
    [Ds_Operador_Atual],
    [Qt_Linhas_Total],
    Qt_Linhas_Processadas,
    [Qt_Linhas_Restantes],
    CONVERT(DECIMAL(5, 2), (( Qt_Linhas_Processadas * 1.0 ) / [Qt_Linhas_Total] ) * 100) AS [Vl_Percentual_Completado],
    [Qt_Segundos_Decorridos],
    (( [Qt_Segundos_Decorridos] / Qt_Linhas_Processadas ) * [Qt_Linhas_Restantes] ) AS [Qt_Segundos_Restantes],
    DATEADD(SECOND, (( [Qt_Segundos_Decorridos] / Qt_Linhas_Processadas ) * [Qt_Linhas_Restantes] ), GETDATE()) AS [Dt_Prevista],
    [host_name],
    [program_name],
    nt_user_name,
    original_login_name
FROM
    CTE_RESULT