Criando pacotes SSIS programaticamente usando o ManagedDTS

The control flow of the package created using ManagedDTS


Nos artigos publicados anteriormente nesta série, explicamos como usar o Biml para criar e gerenciar SQL
    Pacotes do SSIS (Server Integration Services). Neste artigo, falaremos sobre a primeira alternativa dessa linguagem de marcação, que é o modelo de objeto gerenciado do Integration Services (ManagedDTS e assemblies relacionados) fornecido pela Microsoft.

Neste artigo, ilustraremos primeiro como criar, salvar e executar pacotes SSIS usando o ManagedDTS em C # e, em seguida,
    faremos uma pequena comparação com o Biml.

Modelo de objeto do Integration Services

Para explicar o modelo de objeto dos serviços de integração, primeiro precisamos dar uma olhada na arquitetura do SSIS. Como mostrado em
    a figura abaixo (Referência: Visão Geral da Programação do Integration Services)

Arquitetura SSIS

Figura 1 – Arquitetura SSIS

Podemos observar que os principais componentes do SSIS são:

  1. Serviço de Serviços de Integração: É um serviço do Windows que monitora a execução de pacotes, mas também gerencia o armazenamento de pacotes no SQL Server
  2. Mecanismo de tempo de execução do Integration Services: Controla o gerenciamento e a execução de pacotes, implementando a infraestrutura que permite a ordem de execução, o log, as variáveis ​​e a manipulação de eventos
  3. O mecanismo de fluxo de dados: Ele gerencia as tarefas de fluxo de dados em um pacote, pois é especializada para mover dados entre diferentes origens e destinos e pode conter componentes adicionais

Conforme mostrado na arquitetura, o tempo de execução e o mecanismo de fluxo de dados têm um modelo de objeto que permite que eles sejam totalmente gerenciados a partir de aplicativos personalizados, designer do SSIS ou assistente de Importação e Exportação do SQL Server. Além disso, ambos são escritos em código nativo e podem ser acessados ​​usando utilitários de linha de comando (DTExec) ou aplicativos personalizados.

O modelo de objeto permite que os desenvolvedores desenvolvam e gerencia pacotes SSIS usando linguagens compatíveis com .NET.

Desenvolvimento de pacotes SSIS usando o modelo de objeto gerenciado

Assembléias de desenvolvimento do SSIS

Os seguintes assemblies são aqueles que você pode precisar para desenvolver, gerenciar e executar pacotes SSIS do .NET
    formulários:

Nome

Nome Qualificado

Descrição

ManagedDTS

Microsoft.SqlServer.ManagedDTS.dll

Mecanismo de tempo de execução gerenciado

PipelineHost

Microsoft.SqlServer.PipelineHost.dll

Mecanismo de fluxo de dados gerenciados

DTSRuntimeWrap

Microsoft.SqlServer.DTSRuntimeWrap.dll

Wrapper para o mecanismo de tempo de execução nativo

DTSPipelineWrap

Microsoft.SqlServer.DTSPipelineWrap.dll

Wrapper para mecanismo de fluxo de dados nativo

Antes do SQL Server 2019, esses assemblies eram localizados em : Arquivos de Programas Microsoft SQL Server SDK AssembliesCaminho No SQL Server 2019, eles estão localizados no global
    cache de montagem com os assemblies da estrutura .NET.

Criando um novo pacote usando o modelo de objeto

Para criar um pacote SSIS a partir do seu código .Net, você deve adicionar o assembly ManagedDTS como uma referência no seu projeto. Então você deve importar o Microsoft.SqlServer.Dts.Runtime montagem da seguinte forma:

O código a seguir cria um pacote vazio e o salva como arquivo .dtsx:

Observe que você pode salvar o pacote no SQL Server (banco de dados Msdb) usando SaveToSqlServer () e SaveToSqlServerAs () métodos, ou você pode salvá-lo em um repositório de pacotes SSIS usando SaveToDtsServer () método. Para obter mais informações, consulte: Salvando um pacote programaticamente.

Carregando pacote de um arquivo existente

Para carregar um pacote existente, você pode usar o método Application.LoadPackage () da seguinte maneira:

Adicionando gerenciadores de conexões

Para adicionar um gerenciador de conexões, você deve usar a classe Application.ConnectionManager e especificar o
    tipo de conexão e as informações necessárias com base nesse tipo. Como exemplo, o código a seguir adiciona um banco de dados OLE
    gerenciador de conexões:

Adicionando tarefas

Usando ManagedDTS, as tarefas são adicionadas à propriedade Executables na classe Package. Existem diferentes abordagens
    para adicionar uma tarefa ao fluxo de controle do pacote:

  1. Adicionando a tarefa explicitamente, fornecendo as informações de montagem manualmente

    Nesta abordagem, você deve inserir o nome do assembly, o nome qualificado, a versão, a cultura e o token da chave pública no método Add (). Por exemplo:

  2. Usando o AssemblyQualifiedName

    Nessa abordagem, você deve adicionar o assembly relacionado à tarefa como referência e importá-lo em seu código; em seguida, você
        deve recuperar o nome qualificado de montagem da classe relacionada à tarefa (localizada em : Arquivos de Programas Microsoft SQL Server DTS Tasks ”) Como exemplo, se você precisar adicionar uma tarefa Executar SQL, deverá
        primeiro adicione Microsoft.SqlServer.SQLTask.dll como referência. Então você deve usar o seguinte código:

  3. Usando o moniker STOCK

    Você pode consultar a documentação a seguir para obter uma lista completa dos identificadores de estoque de tarefas. Então você deve usá-lo da seguinte maneira:

Adicionando uma tarefa de fluxo de dados

Como mencionamos acima, a tarefa de fluxo de dados é uma tarefa especial do SSIS onde podemos adicionar componentes. Por esse motivo, nós
    falará sobre isso em uma seção separada. Como mencionamos na primeira seção, você deve adicionar PipelineHost e
    Assemblies DTSPipelineWrap além do ManagedDTS para trabalhar com o mecanismo de fluxo de dados. O código a seguir é criar
    um pacote, adicione um gerenciador de conexões OLE DB, adicione uma tarefa de fluxo de dados que contenha uma fonte OLE DB e OLE DB
    destino e configure-os para importar dados de [Person].[Person] mesa em
[Person].[Person_temp]:

Adicionando componentes Tarefas de Fluxo de Dados

Você pode adicionar componentes da tarefa de fluxo de dados usando o Nome da Criação ou o nome qualificado da montagem. A segunda abordagem
    requer a importação do conjunto relacionado ao componente (localizado em : Arquivos de Programas Microsoft SQL Server DTS PipelineComponents ”) O exemplo a seguir ilustra como adicionar componentes de origem e destino do OLE DB e criar um mapeamento entre eles:

Após a execução do aplicativo, o pacote é criado com sucesso. As seguintes capturas de tela mostram o pacote
    Tarefa Controle de fluxo e fluxo de dados:

O fluxo de controle do pacote criado usando o ManagedDTS

Figura 2 – Fluxo de controle de pacote criado

A tarefa de fluxo de dados do pacote criado usando o ManagedDTS

Figura 3 – Tarefa de fluxo de dados criada

Executando pacote programaticamente

Após criar um pacote, podemos executá-lo usando o método Execute () da seguinte maneira:

Recursos online

Nas seções anteriores, explicamos brevemente como criar e executar pacotes usando o ManagedDTS, mas há muito mais instruções que você precisa saber, como mapear tarefas, configurar propriedades de tarefas, manipulação de erros …
    por esse motivo, a Microsoft forneceu documentação útil onde a maioria dessas operações é explicada; Documentação do desenvolvedor do Integration Services.

Comparando com Biml

Simplicidade

Após criar o pacote usando o ManagedDTS, nós o converteremos em um script Biml, conforme explicado em Convertendo pacotes SSIS em scripts Biml. Você pode verificar como o Biml substitui um script C # complexo e facilita o processo de leitura e edição
    pois é mais simples e não requer conhecimento de programação.

Contexto

Por outro lado, é mais preferível usar o ManagedDTS ao criar e executar os pacotes dentro de
    um aplicativo e está relacionado a outras tarefas, pois o Biml é gerenciado em uma solução SSIS e não é
    integrado em um aplicativo.

Tratamento de erros no desenvolvimento

A manipulação de erros é um desastre ao usar o ManagedDTS, pois não mostra uma mensagem de erro clara, como mostra a imagem abaixo. Enquanto estiver no Biml, a ferramenta “Verificar erros no Biml” retorna todos os erros encontrados durante a validação do script.

Exceção clara ao criar pacote usando o ManagedDTS

Figura 4 – Lançou uma exceção ao criar o pacote SSIS programaticamente

Recursos

Faltam artigos e guias relacionados para as duas tecnologias. Mas a capacidade de converter pacotes SSIS em scripts Biml facilita a aprendizagem, pois simplifica o processo de aprendizado caso você esteja familiarizado com o SSIS.

Conclusão

Neste artigo, explicamos uma das alternativas para criar pacotes SSIS usando o Biml, que é o modelo de objeto gerenciado do mecanismo de tempo de execução do serviço de integração. Além disso, fizemos uma pequena comparação entre as duas abordagens para ilustrar a principal diferença.

Índice

Hadi Fadlallah
Últimas mensagens de Hadi Fadlallah (ver todos)

Atualizado Kit Primeiro Respondente e Kit de Ferramentas Consultor para março de 2020


Nas palavras imortais de “Orange” Julius Caesar, como escrito pelo colega de Shake, “Cuidado com os insetos de março”. Levamos 2.064 anos, mas finalmente corrigimos esses bugs e adicionamos alguns novos recursos.

Para obter a nova versão:

Alterações no kit de ferramentas do consultor

Atualizado o Kit do primeiro respondente com as atualizações de script deste mês.

Alterações sp_AllNightLog

  • Correção: ignore os bancos de dados em que ignore_database <> 0 na tabela backup_worker. (# 2308, obrigado Maxiwheat.)

Alterações no sp_Blitz

  • Correção: não erro quando alguns bancos de dados têm a rotatória numérica ativada, e um erro de arredondamento normalmente gera um erro. (# 2302, obrigado DJH.)
  • Correção: erro na verificação completa do backup: se o msdb.backupset contivesse backups feitos em outros servidores, nós os incluiríamos como backups bem-sucedidos quando eles realmente não estavam. (# 2313, obrigado Solomon Rutzky.)
  • Correção: o script de desinstalação agora funciona em agrupamentos que diferenciam maiúsculas de minúsculas. (# 2307, obrigado Protiguous.)

Alterações no sp_BlitzCache

  • Melhoria: adicionada uma nova verificação para o cache UserStore_TokenPerm ser> 10% do buffer pool. Estou vendo problemas fora do comum em que esse cache substituiu a memória e fez com que o SQL Server não conseguisse armazenar em cache nenhum plano de execução. Esse foi um problema há mais de uma década no SQL 2005, mas eu tenho um cliente do SQL Server 2016/2017 com um caso aberto com a Microsoft. Adicionaremos essa mesma verificação para sp_Blitz e sp_BlitzFirst. (# 2134, obrigado Erik Darling.)

Alterações sp_BlitzFirst

  • Correção: o @FilterPlansByDatabase estava lançando um erro se você passasse em uma lista de bancos de dados (em vez de apenas “usuário” para os bancos de dados do usuário.) (Nº 2311, obrigado Sam Carey.)

Alterações sp_BlitzLock

  • Melhoria: agora também captura deadlocks de paralelismo de consulta única. (# 2286, obrigado Adrian Buckman.)
  • Correção: agora funciona com o Amazon RDS SQL Server Express Edition, ignorando a consulta sysjobssteps, como fazemos com o Azure SQL DB. (# 2317, obrigado Ernesto-Ibanez.)

sp_BlitzWho Alterações

Para suporte

Quando você tiver dúvidas sobre como as ferramentas funcionam, converse com a comunidade no canal #FirstResponderKit Slack. Se você precisar de um convite grátis, pressione SQLslack.com. Seja paciente – ele é formado por voluntários que têm empregos diurnos.

Quando você encontrar um bug ou quiser algo alterado, leia o arquivo contribut.m.md.

Quando você tiver alguma dúvida sobre o que os scripts encontraram, primeiro leia o URL “Mais detalhes” para qualquer aviso que encontrar. Colocamos muito trabalho na documentação e não queremos que alguém grite com você para ler o manual. Depois disso, quando você ainda tiver dúvidas sobre como algo funciona no SQL Server, poste uma pergunta no DBA.StackExchange.com e a comunidade (que nos inclui!) Ajudará. Inclua erros exatos e quaisquer capturas de tela aplicáveis, o número da versão do SQL Server (incluindo o número da compilação) e a versão da ferramenta com a qual você está trabalhando.

Movendo sua carga de trabalho SQL para a nuvem – Uma visão do SQLEspresso

Estou falando no Pass Summit 2019 - Uma foto do SQLEspresso


Todos os dias, mais organizações de TI decidem mover seus bancos de dados do SQL Server para o Azure. De fato, mais de um milhão de bancos de dados locais do SQL Server foram movidos para o Azure. Há um blog interessante sobre como a Microsoft é mais rápida e barata que seus concorrentes que vale a pena ler. Para ajudar na sua mudança para o Azure, a Microsoft oferece várias ferramentas e serviços de migração para tornar essa mudança o mais suave possível, o que acho que atribui ao sucesso deles. Duas dessas opções estão abaixo com alguns links informativos.

Se você estiver migrando várias instâncias grandes do SQL Server, o Serviço de Migração de Banco de Dados do Azure é a melhor maneira de migrar bancos de dados para o Azure em escala.

Você também pode automatizar as migrações de banco de dados usando os comandos do PowerShell do Serviço de Migração de Banco de Dados do Azure.

O Serviço de Migração de Banco de Dados do Azure (DMS) é um serviço totalmente gerenciado para migrar várias fontes de banco de dados para plataformas de dados do Azure em escala. Ele suporta o SQL Server 2005 através do SQL Server 2019, bem como uma variedade de outros pares de origem e destino. O Azure DMS permite uma migração perfeita com o mínimo de tempo de inatividade ou esforço.

Vamos ver como isso funciona.

Trabalhando com o Serviço de Migração de Banco de Dados do Azure

Uma migração de banco de dados típica usando o Serviço de Migração de Banco de Dados do Azure consiste nas seguintes etapas:

  1. Execute uma avaliação de migração usando o Data Migration Assistant (DMA). Observe todos os bloqueadores de migração relatados pelo DMA e execute as correções recomendadas.
  2. Se você precisar avaliar todo o conjunto de dados e encontrar a disponibilidade relativa dos bancos de dados migrando para o Azure SQL, siga as etapas fornecidas aqui.
  3. Depois de encontrar o destino SQL do Azure, use o Serviço de Migração de Banco de Dados do Azure para encontrar o SKU ideal de destino SQL do Azure que atenda às suas necessidades de desempenho.
  4. Crie seus bancos de dados de destino.
  5. Crie uma instância do Serviço de Migração de Banco de Dados do Azure.
  6. Crie um projeto de migração especificando os bancos de dados de origem, bancos de dados de destino e as tabelas a serem migradas.
  7. Inicie a carga completa. Nota: selecione Conectados para incluir backups do log de transações.
  8. Escolha a validação subsequente.
  9. Execute uma transição manual do seu ambiente de produção para o novo banco de dados do Azure.

Migração offline vs. online

A quantidade de tempo de inatividade necessária para migrar para a nuvem é sempre um aspecto importante na decisão de migrar para a nuvem ou não. O Serviço de Migração de Banco de Dados do Azure permite que você faça uma migração offline ou online. A diferença é a quantidade de tempo de inatividade. Com uma migração offline, o tempo de inatividade começa ao mesmo tempo em que a migração é iniciada. Com uma migração online, o tempo de inatividade é limitado ao tempo necessário para passar para o novo ambiente no final da migração.

A Microsoft recomenda testar uma migração offline. Se você não puder tolerar o tempo de inatividade, mude para uma migração online. É importante observar que uma migração on-line requer uma instância do nível de preços Premium. No entanto, eles facilitam o processo, oferecendo-o gratuitamente nos primeiros seis meses.

Sumário

O Serviço de Migração de Banco de Dados do Azure oferece uma idéia muito melhor do que esperar ao migrar grandes instâncias de banco de dados em escala. Ele migra seus bancos de dados com facilidade usando as práticas recomendadas da Microsoft. Ainda melhor é que a Microsoft investe continuamente em migrações trabalhando para melhorar a confiabilidade e o desempenho, além de adicionar pares de origem / destino. Eu posso ver facilmente por que eles continuam liderando as tecnologias em nuvem.

Para saber mais sobre como mover seus bancos de dados para a nuvem, aqui está um ótimo recurso. “Faça uma infraestrutura de dados à prova do futuro com o Azure: um caso de negócios para administradores de banco de dados.

Removendo duplicatas em uma planilha do Excel usando scripts Python

Remove duplicates by keeping maximum and minimum value


No artigo, scripts Python para formatar dados no Microsoft Excel, usamos scripts Python para criar um excel e fazer várias formatações de dados. Python é uma linguagem de programação interessante de alto nível. Você pode passar por vários casos de uso do Python no SQLShack.

Neste artigo, examinaremos a remoção de dados duplicados do Excel usando o Python.

Uma rápida recapitulação da remoção de linhas duplicadas no Microsoft Excel

Suponha que tenhamos os seguintes dados em uma planilha do Excel. Queremos nos livrar dos valores duplicados nesta planilha.

linhas duplicadas no Microsoft Excel

No Microsoft Excel, usamos o Remover duplicatas botão no menu Dados. Esta opção verifica valores duplicados e retém o PRIMEIRO valor exclusivo e remove outros valores.

Vamos clicar em Remover duplicatas e selecionar todas as colunas.

Remover duplicatas

Clique em ok e ele remove os valores duplicados 3 valores duplicados e retém 5 valores exclusivos.

Confirmação de remoção duplicada

Temos os seguintes dados após a remoção de duplicatas.

Dados do Excel após remover duplicatas

Suponha que você esteja trabalhando no Excel usando a linguagem Python. Se esse excel contiver valores duplicados, talvez não desejemos usar as funcionalidades do Excel. Nosso script deve ser capaz de lidar com esses dados duplicados e remover de acordo com nossos requisitos, como remover todas as duplicatas, remover todas, exceto a última duplicada, remover todas, exceto a primeira duplicada.

Vamos ver a maneira Python de lidar com dados duplicados no Excel.

Scripts Python para remover duplicatas em um excel

Antes de começarmos com o Python, verifique os pré-requisitos especificados no artigo, scripts Python para formatar dados no Microsoft Excel.

Inicie o SQL Notebook no Azure Data Studio e verifique a existência de pacotes de pandas e NumPy. Você pode clicar em Gerenciar extensões no Azure Data Studio para isso.

Cadernos SQL do Azure Data Studio

Depois de clicar em Gerenciar pacotes, ele fornece uma lista dos pacotes instalados. Aqui, podemos ver ambos pandas e NumPy pacote junto com o utilitário pip.

Gerenciar pacotes

Usamos a função pandas read_excel () para importar um arquivo do Excel. Crie um novo bloco de código no SQL Notebook e execute o código. Aqui, a instrução print imprime o quadro de dados que consiste em dados da folha do Excel.

Primeiro, importamos o pandas biblioteca para ler e escrever as planilhas do excel.

Nestes dados, poucas colunas contêm NaN na coluna de comentários. Python exibe NaN para as células que não possuem nenhum valor / texto.

Na saída, também vemos valores de índice para linhas individuais. A primeira linha começa com o ID de índice 0 e aumenta em 1 a cada nova linha.

Ler dados do excel

Usamos a função drop_duplicates () para remover registros duplicados de um quadro de dados nos scripts Python.

Sintaxe de drop_duplicates () em scripts Python

DataFrame.drop_duplicates (subconjunto = Nenhum, mantenha = ‘primeiro’, local = Falso)

  • Subconjunto: Neste argumento, definimos a lista de colunas a considerar para identificar linhas duplicadas. Se considerar todas as colunas no caso, não especificaremos nenhum valor
  • Manter: Aqui, podemos especificar os seguintes valores:
    • Primeiro: remova todas as linhas duplicadas, exceto a primeira

    • Última: remova todas as linhas duplicadas, exceto a última

    • Falso: remova todas as linhas duplicadas

  • No lugar: Por padrão, o Python não altera o quadro de dados de origem. Podemos especificar esse argumento para alterar esse comportamento

Exemplo 1: use drop_duplicates () sem argumentos

Na consulta a seguir, ele chama a função drop.duplicates () para [data] quadro de dados.

Na saída, podemos ver que ele remove linhas com os IDs de índice 1,5 e 7. É o comportamento padrão da função drop_duplicate (). Ele mantém o primeiro valor e remove outras duplicatas.

Use drop_duplicates () sem argumentos

Exemplo 2: use drop_duplicates () junto com os nomes das colunas

Por padrão, o Pandas cria um quadro de dados para todas as colunas disponíveis e verifica se há dados duplicados. Suponhamos que queremos excluir as colunas Comentários para verificar duplicatas. Isso significa que se a linha contiver valores semelhantes no restante das colunas, ela deverá ser uma linha duplicada. Temos poucos registros em nossa planilha do Excel que contêm mais valores duplicados se não considerarmos a coluna de comentários.

Nos seguintes scripts Python, especificamos nomes de colunas no argumento do subconjunto. Os pandas verificam essas colunas e removem os valores duplicados. Exclui a coluna de comentários nesse caso.

Exemplo 2a: manter = argumento “primeiro”

Também especificamos outro argumento keep = first para instruir o Python a manter o primeiro valor e remover outras duplicatas. São os comportamentos padrão para que possamos excluir esse parâmetro aqui também.

Veja a saída e temos apenas três registros disponíveis. Ele removeu todas as linhas duplicadas das colunas especificadas. Temos linhas com os índices de índice 0,2 e 4 na saída usando o primeiro valor no argumento keep.

Use drop_duplicates () junto com os nomes das colunas

Exemplo 2b: Manter = “último” argumento

Nós podemos mudar o argumento keep = last. Ele mantém a última linha das duplicatas e remove as linhas duplicadas anteriores. Vamos mudar o argumento e ver a saída.

Nesse caso, a saída muda e temos linhas com o ID do índice 1,2,7 na saída.

Manter =

Exemplo 2c: Manter = argumento “false”

Anteriormente, mantivemos a primeira ou a última linha e removemos outras linhas duplicadas. Suponha que desejemos remover todos os valores duplicados na planilha do Excel. Podemos especificar o valor False no parâmetro keep para ele.

Se executarmos o Script Python acima, obteremos a seguinte mensagem de erro.

ValueError: keep deve ser “first”, “last” ou False

Manter = argumento

Para o primeiro e o último valor, usamos aspas duplas, mas precisamos especificar o valor False sem aspas. Vamos remover a cotação e executar o código.

Na saída, não obtemos nenhuma linha porque não temos nenhuma linha exclusiva na planilha do Excel.

Saída do argumento Keep =

Para testar o código acima, vamos adicionar uma nova linha no excel e executar o código acima. Devemos obter a linha na saída. O script funciona e obtemos linhas únicas na saída.

Inserir uma nova linha

Exemplo 3: Remova duplicatas mantendo o valor máximo e mínimo

Agora, suponha que tenhamos uma nova coluna Age na planilha do excel. Alguém entrou na idade errada para os funcionários. Queremos remover os valores duplicados, mas manter a linha que tem o valor máximo de idade para um funcionário. Por exemplo, Rohan tem duas entradas nesta planilha. Ambas as linhas parecem semelhantes; no entanto, uma linha mostra 22 anos, enquanto outra tem o valor 23. Queremos remover a linha com uma idade mínima. Nesse caso, a linha com 22 anos de idade para Rohan deve ser removida.

Remova duplicatas mantendo o valor máximo e mínimo

Para esse requisito, usamos uma função de script Python adicional sort_values ​​(). No código a seguir, classificamos os valores da idade em ordem crescente usando a função data.sort_values ​​(). Na ordem crescente, os dados são classificados da idade mínima para a máxima, para que possamos manter o último valor e remover outras linhas de dados.

Na saída, podemos ver que possui linhas com idade máxima para cada funcionário. Por exemplo, Rohan mostra 23 anos que é a idade máxima disponível nos dois registros.

Remova duplicatas mantendo o valor máximo

Da mesma forma, podemos alterar a classificação dos dados em ordem decrescente e remover as duplicatas com valores mínimos de idade.

Remova duplicatas mantendo o valor mínimo

Exemplo 4: função drop_duplicate () usando argumento inplace

Por padrão, o Pandas retorna um novo quadro de dados e não altera o quadro de dados de origem. Podemos especificar o argumento inplace = True, e ele também altera o quadro de dados de origem.

Execute a seguinte consulta e chame o quadro de dados no final; retorna o conteúdo do quadro de dados de origem.

argumento local

Vamos alterar o valor padrão do argumento inplace e visualizar a alteração na saída.

inplace = argumento verdadeiro

Destaque valores duplicados com códigos de cores personalizados

Em muitos casos, queremos apenas verificar os dados duplicados em vez de removê-los. Em vez disso, precisamos destacar os valores duplicados e enviá-los à equipe apropriada para correção. Pode ser possível no caso de estarmos recebendo dados de terceiros.

Podemos usar formatação condicional e forneça um estilo visual (código de cores) para duplicar linhas. No código a seguir, definimos uma função de script Python para destacar valores duplicados na cor de fundo laranja. Abordaremos mais sobre a formatação condicional nos próximos artigos.

Ele nos fornece a seguinte saída e podemos interpretar facilmente quais linhas contêm valores duplicados. É útil, especialmente quando temos um grande número de linhas. Não podemos passar por cada linha nesse caso, e o código de cores nos ajuda a identificar os valores duplicados.

Realçar valores duplicados

Como alternativa, podemos usar outras funções, como GROUPBY e conte as linhas duplicadas.

Se qualquer contagem de linhas for maior que 1, será uma linha duplicada. Na saída a seguir, podemos observar que Dolly apareceu 4 vezes na planilha do Excel, o que significa que é uma linha duplicada. Rajendra não contém nenhuma linha duplicada, portanto, sua contagem é 1 na saída.

Contagem de linhas duplicadas

Conclusão

Neste artigo, exploramos o processo para remover linhas duplicadas em uma planilha do Excel usando scripts Python. Gostei da maneira de lidar com arquivos do Excel usando Python. Abordaremos scripts mais úteis nos próximos artigos. Fique ligado!

Rajendra Gupta
Últimas mensagens de Rajendra Gupta (ver todos)

Função agregada SQL Server MAX ()

Max() function example 7


O SQL Server nos fornece várias funções agregadas que podem ser usadas para executar diferentes tipos de cálculos
    em um conjunto de valores e retorne um único valor que resumiu o conjunto de dados de entrada. Esses agregados do SQL Server
    As funções incluem AVG (), COUNT (), SUM (), MIN () e MAX ().

Neste artigo, examinaremos o uso da função agregada MAX () e forneceremos vários exemplos do MAX ()
    uso da função.

Uso

A função agregada SQL MAX () é usada para retornar o valor máximo da expressão numérica fornecida ou do
    valor mais alto na sequência de intercalação da expressão de caractere fornecida.

Sintaxe

A sintaxe T-SQL para a função agregada MAX () é como:

Onde a opção ALL, que é a opção padrão, indica que a função agregada MAX () será aplicada a
    todos os valores de expressão e a opção DISTINCT indicam que cada valor exclusivo será considerado no
    função agregada, que não é aplicável à função MAX ().

o Expressão aqui pode ser passado para a função agregada MAX () pode ser qualquer caractere numérico,
    coluna uniqueidentifier ou DateTime da tabela, considerando que o tipo de dados BIT não pode ser usado com o MAX ()
    função agregada.

A função agregada MAX () também pode levar um valor constante ou qualquer expressão aritmética ou de string, levando em consideração que a função agregada MAX () ignorará qualquer valor NULL na expressão de entrada.

O valor retornado da função agregada SQL MAX () será o mesmo que o valor Expressão
tipo de dados e o valor retornado será NULL quando a expressão de entrada não tiver linhas para ler.

Exemplos de função agregada SQL MAX ()

Para fins de demonstração da função agregada SQL MAX (), vamos criar uma nova tabela de teste que contém colunas com
    tipos de dados diferentes, como tipos de dados INT, DECIMAL, NVARCHAR, DATETIME e BIT, usando o comando CREATE TABLE T-SQL
    declaração abaixo:

Depois que a tabela for criada, preencheremos essa tabela com os dados de teste do Microsoft AdventureWorks2017
    testando tabelas de banco de dados, usando o script T-SQL abaixo:

O ambiente de teste agora está pronto com uma tabela de 316 registros.

Para obter o valor máximo da coluna ID, que mostra o último funcionário adicionado, e usá-lo para mais
    análise ou operações, usaremos a função agregada SQL MAX () dentro de uma instrução SELECT, como no T-SQL
    declaração abaixo:

E o valor retornado em nossa demonstração aqui será 316, que é o último ID de funcionário adicionado, como mostrado abaixo:

Exemplo 1 da função Max ()

Outro cenário em que precisamos obter o nome do funcionário localizado no final do pedido alfabético.
    Isso pode ser alcançado aplicando a função agregada MAX () na coluna de caracteres EmpName, em que MAX ()
    A função classificará os valores da coluna EmpName com base nesse agrupamento de colunas e retornará o último valor, como no
    Instrução T-SQL abaixo:

No nosso caso aqui, a instrução SELECT com a função agregada SQL MAX () retornará o último funcionário, após
    classificando os nomes dos funcionários. Você pode imaginá-lo classificando os nomes em ordem decrescente e obtendo o primeiro nome do funcionário,
    como mostrado abaixo:

Exemplo 2 da função Max ()

A função agregada SQL MAX () também pode ser usada com o tipo de dados DateTime, onde classificará o DateTime
    valores e retorne o último valor dos resultados classificados. Em nosso cenário, precisamos obter o funcionário mais jovem com
    a data de nascimento mais próxima. Para isso, passaremos a coluna EmpDateOfBirth para a função agregada MAX (), como
    na instrução T-SQL SELECT abaixo:

A instrução SELECT anterior com a função agregada SQL MAX () classificará a data de nascimento dos funcionários e retornará
    o último valor da data de nascimento. Você pode imaginar a função agregada MAX () como classificar os valores da data de nascimento dos funcionários
    descendo e obtendo a primeira data de nascimento, conforme mostrado abaixo:

Exemplo 3 da função Max ()

A reformulação da escala salarial dos funcionários pode ser verificada com diferentes tipos de cálculos e agregada
    operações incluindo a função agregada SQL MAX (). Isso pode ser feito passando a coluna EmpSalary para o MAX ()
    , que classificará todos os valores salariais dos funcionários e retornará o maior valor da coluna, como na instrução T-SQL Select abaixo:

A execução da instrução SELECT anterior com a função agregada MAX () será semelhante ao resultado retornado
    de classificar os valores dos salários dos funcionários em ordem decrescente e retornar o salário Top 1, com o maior valor, conforme mostrado
    abaixo:

Exemplo de função Max () 4

Se tentarmos aplicar a função agregada SQL MAX () à coluna EmpIsActive, com um tipo de dados BIT, como no
    Instrução T-SQL SELECT abaixo:

A execução da instrução SELECT falhará, com uma mensagem de erro que mostra que o bit do tipo de dados não pode ser usado
    com o operador MAX, em que não é possível ver o valor máximo de uma coluna de bits, que já possui
    apenas dois valores, 1 e 0. A mensagem de erro será como abaixo:

Erro de exemplo da função Max () 5

A função agregada SQL MAX () também pode ser usada na cláusula WHERE de uma consulta SELECT simples. Isso pode ser
    obtido comparando um valor específico da coluna com o resultado retornado de uma subconsulta que retornou o valor máximo
    valor dessa coluna, conforme mostrado na consulta T-SQL SELECT abaixo:

A consulta SELECT anterior retornará todas as informações sobre o funcionário cujo salário é o maior salário dentro de
    salário de todos os funcionários, onde calculará o valor máximo dos salários dos funcionários e comparará cada
    salário do funcionário com esse valor, conforme mostrado abaixo:

Exemplo de função Max () 6

Também podemos usar a função agregada SQL MAX () para retornar o valor máximo de uma coluna específica com outra coluna
    valores, onde todas as colunas não agregadas devem ser listadas na cláusula GROUP BY. Por exemplo, a consulta SELECT
    abaixo, listará todos os valores de data de nascimento e salário de todos os funcionários e, ao lado de cada valor, retornará o valor máximo,
    para comparar cada valor com o valor máximo, conforme mostrado abaixo:

E o resultado, que pode ser facilmente comparado, será o seguinte:

Exemplo de função Max () 7

Também podemos filtrar o resultado da consulta anterior definido para retornar apenas os funcionários com uma variação salarial muito grande
    dos valores salariais máximos, fornecendo uma condição adicional usando a função agregada SQL MAX () dentro
    a cláusula HAVING, como mostrado abaixo:

E o resultado da execução da consulta anterior será como:

Exemplo de função Max () 8

Vamos adicionar uma nova coluna à tabela de demonstração sem preencher nenhum valor nessa coluna, usando o ALTER TABLE T-SQL
    declaração abaixo:

Após adicionar a nova coluna, se tentarmos executar a consulta abaixo para obter o valor máximo permitido de toda a empresa
    funcionários que usam a função agregada SQL MAX ():

O resultado retornado será NULL, pois a tabela não possui valor nessa coluna, conforme mostrado abaixo:

Exemplo de função Max () 9

Conclusão

É claro, a partir desses exemplos acima, como podemos usar a função agregada SQL MAX () para atingir um objetivo específico em
    cenários diferentes, com diferentes tipos de dados e em qualquer consulta T-SQL.

Ahmad Yaseen
Últimas mensagens de Ahmad Yaseen (ver todos)

Erros ilógicos podem ser um fator do plano – SQLBlog

Erros ilógicos podem ser um fator do plano - SQLBlog


Erros ilógicos podem ser um fator do plano - SQLBlog 1 Eu já falei sobre erros ilógicos antes. Em várias respostas sobre administradores de banco de dados (um, dois, três), mostro como você pode usar um CASE expressão ou TRY_CONVERT para solucionar um erro em que um valor não numérico, que deveria ter sido filtrado por uma cláusula de junção ou outra, ainda leva a um erro de conversão. Erland Sommarskog criou um item de Conexão mais de uma década atrás, ainda não endereçado, chamado “SQL Server não deve gerar erros ilógicos”.

Recentemente, tivemos um cenário em que uma consulta estava falhando em um servidor, mas não em outro. Mas isso foi um pouco diferente; não havia números envolvidos. Imagine este cenário: uma tabela de origem possui uma coluna que é varchar(20). Uma consulta cria uma variável de tabela com uma coluna que é varchar(10)e insere linhas da tabela de origem, com um filtro no lugar que expõe apenas valores com 10 caracteres ou menos.

Em muitos casos, é claro, esse cenário é perfeitamente adequado e tudo funciona como esperado.

CREATE TABLE dbo.src(i int, a varchar(20));
 
INSERT dbo.src VALUES(1,'aaaaa'),(2,'bbbbbbbbbbbbbbb');
 
DECLARE @t table(a varchar(10));
 
INSERT @t SELECT a FROM dbo.src WHERE i = 1;
 
DROP TABLE dbo.src;

Se você mudar isso para WHERE i = 2;, porém, você receberá esta * mensagem de erro:

Msg 2628, Nível 16, Estado 1
Os dados de sequência ou binários seriam truncados na tabela ‘tempdb.dbo. # A60B3C91’, coluna ‘a’. Valor truncado: ‘bbbbbbbbbb’.

Não há surpresas aqui; é assim que deve funcionar.

Um exemplo mais complexo

Quando o esquema e as consultas ficam mais complicadas, o plano se torna importante. Aqui está um cenário um pouco mais complicado, mas mantido o mais simples possível, para demonstrar o problema:

CREATE TABLE dbo.t1(id int NOT NULL, s varchar(5) NOT NULL);
CREATE TABLE dbo.t2(id int NOT NULL);
 
INSERT dbo.t1 (id, s) VALUES (1, 'l=3'), (2, 'len=5'), (3, 'l=3');
INSERT dbo.t2 (id)    VALUES (1), (3), (4), (5);
GO
 
DECLARE @t table(dest varchar(3) NOT NULL);
 
INSERT @t(dest) SELECT t1.s 
  FROM dbo.t1 
  INNER JOIN dbo.t2 ON t1.id = t2.id;
GO
 
DROP TABLE dbo.t1, dbo.t2;

Isso gera um erro semelhante ao que vimos anteriormente:

Msg 2628, Nível 16, Estado 1
Dados binários ou de string seriam truncados na tabela ‘tempdb.dbo. # AC65D70E’, coluna ‘dest’. Valor truncado: ‘len’.

Embora devamos ter recuperado apenas linhas com valores que cabem na coluna de destino (id é 1 ou 3, pois essas são as únicas duas linhas que correspondem aos critérios de junção), a mensagem de erro indica que a linha em que id também foi retornado 2, mesmo sabendo que não poderia ter sido. Vamos olhar para o estimado plano (com desculpas por essa terminologia a Grant, Hugo e Erin):

Plano derivado de Plano derivado de “Explain” no Azure Data Studio

Você pode ver o CONVERT_IMPLICIT lá, o que você acha que deveria ter tornado esse erro impossível. Você também pode ver que a correspondência de hash tem apenas duas linhas saindo, mas algo que falta nos diagramas é quantas linhas vão para dentro um operador e, nesse caso, a folha de propriedades do operador revela que a tabela de origem é usada para materializar o lado da construção. É aqui que o erro ocorre, porque a conversão implícita precisa ser avaliada.

Posso mostrar algumas maneiras pelas quais, sem alterar a consulta, podemos fazer o erro desaparecer (ou reaparecer).

Dados diferentes

Com apenas uma alteração nos dados de origem:

CREATE TABLE dbo.t1(id int NOT NULL, s varchar(5) NOT NULL);
CREATE TABLE dbo.t2(id int NOT NULL);
 
INSERT dbo.t1 (id, s) VALUES (1, 'l=3'), (2, 'len=5'), (3, 'l=3');
INSERT dbo.t2 (id)    VALUES (1), (3), (4);--, (5);
GO
 
DECLARE @t table(dest varchar(3) NOT NULL);
 
INSERT @t(dest) SELECT t1.s 
  FROM dbo.t1 
  INNER JOIN dbo.t2 ON t1.id = t2.id;
GO
 
DROP TABLE dbo.t1, dbo.t2;

Desta vez, a correspondência de hash não gera erroDesta vez, a correspondência de hash não gera erro

Ainda temos uma correspondência de hash, mas observe que o lado da construção e o lado do probe foram trocados, um efeito colateral de dados ligeiramente diferentes. Como a tabela de hash não precisa mais materializar o resultado da conversão implícita, nenhum erro ocorre.

Esquema diferente

Outra “solução” é adicionar um índice em cluster ao dbo.t2 (e volte a inserir as 4 linhas originais):

CREATE TABLE dbo.t1(id int NOT NULL, s varchar(5) NOT NULL);
CREATE TABLE dbo.t2(id int PRIMARY KEY CLUSTERED);
 
INSERT dbo.t1 (id, s) VALUES (1, 'l=3'), (2, 'len=5'), (3, 'l=3');
INSERT dbo.t2 (id)    VALUES (1), (3), (4), (5);
GO
 
DECLARE @t table(dest varchar(3) NOT NULL);
 
INSERT @t(dest) SELECT t1.s 
  FROM dbo.t1 
  INNER JOIN dbo.t2 ON t1.id = t2.id;
GO
 
DROP TABLE dbo.t1, dbo.t2;

Aqui está como o plano muda:

Agora obtemos uma junção de loops aninhados e nenhum erroAgora obtemos uma junção de loops aninhados e nenhum erro

o CONVERT_IMPLICIT ainda está lá no escalar de computação, mas a avaliação não é forçada antes que a linha ruim seja eliminada, porque a junção de loops aninhados não precisa materializar nada.

Consulta diferente

Você também pode alterar a consulta; por exemplo, você pode aplicar funções como LEFT, SUBSTRINGou um explícito CONVERT (ou CAST) para a coluna:

CREATE TABLE dbo.t1(id int NOT NULL, s varchar(5) NOT NULL);
CREATE TABLE dbo.t2(id int NOT NULL);
 
INSERT dbo.t1 (id, s) VALUES (1, 'l=3'), (2, 'len=5'), (3, 'l=3');
INSERT dbo.t2 (id)    VALUES (1), (3), (4), (5);
GO
 
DECLARE @t table(dest varchar(3) NOT NULL);
 
INSERT @t(dest) SELECT s = LEFT(t1.s, 3) -- or SUBSTRING(t1.s, 1, 3)
                                         -- or CONVERT(varchar(3), t1.s)
  FROM dbo.t1                            -- or CAST(t1.s AS varchar(3))
  INNER JOIN dbo.t2 ON t1.id = t2.id;
GO
 
DROP TABLE dbo.t1, dbo.t2;

A transformação subjacente aqui é implementada como um SUBSTRING, e isso fornece ao otimizador informações suficientes para saber que nenhum valor pode ter mais de três caracteres:

Nenhum erro ao aplicar LEFT ()Nenhum erro ao aplicar LEFT ()

Você também pode aplicar um WHERE cláusula para eliminar as linhas explicitamente. Isso funciona nesse caso específico, mas, como demonstrado nas postagens acima, isso nem sempre funciona (nem usa uma CTE ou tabela derivada):

CREATE TABLE dbo.t1(id int NOT NULL, s varchar(5) NOT NULL);
CREATE TABLE dbo.t2(id int NOT NULL);
 
INSERT dbo.t1 (id, s) VALUES (1, 'l=3'), (2, 'len=5'), (3, 'l=3');
INSERT dbo.t2 (id)    VALUES (1), (3), (4), (5);
GO
 
DECLARE @t table(dest varchar(3) NOT NULL);
 
INSERT @t(dest) SELECT t1.s
  FROM dbo.t1 
  INNER JOIN dbo.t2 ON t1.id = t2.id
  WHERE LEN(t1.s) <= 3;
GO
 
DROP TABLE dbo.t1, dbo.t2;

O plano é agora:

Nenhum erro com um filtro explícitoNenhum erro com um filtro explícito

Nesse caso, a linha incorreta é filtrada com antecedência suficiente para que se torne irrelevante a maneira como a correspondência de hash opera.

Você também pode tentar sugerir uma junção de loop para evitar a correspondência de hash e, provavelmente, meia dúzia de outras maneiras de alterar a consulta para forçar a filtragem anterior ou eliminar a materialização.

Observe que algumas dicas podem trazer o erro de volta. Por exemplo, neste caso, OPTION (FORCE ORDER) (ou qualquer dica que preserve a ordem) traz t1 de volta ao lado da compilação da combinação de hash e o erro retorna:

CREATE TABLE dbo.t1(id int NOT NULL, s varchar(5) NOT NULL);
CREATE TABLE dbo.t2(id int NOT NULL);
 
INSERT dbo.t1 (id, s) VALUES (1, 'l=3'), (2, 'len=5'), (3, 'l=3');
INSERT dbo.t2 (id)    VALUES (1), (3), (4), (5);
GO
 
DECLARE @t table(dest varchar(3) NOT NULL);
 
INSERT @t(dest) SELECT t1.s
  FROM dbo.t1 
  INNER JOIN dbo.t2 ON t1.id = t2.id
  OPTION (FORCE ORDER);
GO
 
DROP TABLE dbo.t1, dbo.t2;

Um grama de prevenção …

Uma solução mais lógica é alterar a variável da tabela para combinar tipos de dados ou, se dados mais longos não fizerem sentido na tabela de origem, conserte na fonte. Isso pode ter efeitos de gotejamento, mas, geralmente, o problema decorre da origem, permitindo valores muito grandes. Corrija a fonte e é improvável que declarações muito grandes em outros lugares causem problemas, a menos que sejam preenchidas por outras fontes.

Enquanto isso, verifique se não há dados errados que estejam causando explicitamente o erro, fazendo você seguir o seu caminho. Não seria a primeira vez que o código seria responsabilizado quando dados defeituosos (ou melhor, um esquema permissivo demais) estavam realmente com defeito.

Conclusão

Meu objetivo aqui não é explicar como solucionar o erro, é mais que você pode ou não ver o erro de truncamento, mesmo quando não faz sentido. E você pode ver o inverso acontecer – um código que funcione com facilidade pode ser interrompido repentinamente quando alguém adiciona dados, atualiza estatísticas, cria ou reconstrói um índice ou ocorre um failover ou a reinicialização do serviço.

O esquema e os dados subjacentes podem desempenhar um papel no plano que você obtém e outros fatores podem fazer com que você obtenha planos diferentes em servidores diferentes ou mesmo no mesmo servidor em momentos diferentes, mesmo quando o esquema e os dados são os mesmos .

Enorme grito para Paul White, cuja contribuição inestimável tornou esta postagem muito melhor do que poderia ter sido.



Semana gratuita de Noções básicas sobre ajuste de consulta: Parte 3, Como os parâmetros influenciam os planos em cache


/ *

Fundamentos do ajuste de consulta: como os parâmetros afetam os planos

v1.0 – 2019-06-30

https://www.BrentOzar.com/go/queryfund

Esta demonstração requer:

* Qualquer versão suportada do SQL Server

* Qualquer banco de dados Stack Overflow: https://www.BrentOzar.com/go/querystack

Este primeiro RAISERROR é apenas para garantir que você não acerte F5 acidentalmente.

execute o script inteiro. Você não precisa executar isso:

* /

RAISERROR(N‘Opa! Não, nãobasta pressionar F5. Execute essas demos uma de cada vez., 20, 1) COM REGISTRO;

IR

/ * Estou usando o banco de dados Stack médio de 50 GB: * /

USAR StackOverflow2013;

IR

/ * E este procedimento armazenado descarta todos os índices não clusterizados: * /

DropIndexes;

IR

/ * No entanto, deixa os índices agrupados no lugar.

Crie alguns índices para apoiar nossas consultas: * /

CRIO ÍNDICE IX_Localização EM dbo.Comercial(Localização);

CRIO ÍNDICE IX_UserId EM dbo.Comentários(ID do usuário);

CRIO ÍNDICE IX_CreationDate EM dbo.Comentários(Data de criação);

IR

CONJUNTO ESTATISTICAS IO EM;

/ *

Começaremos com a mesma consulta que usamos no primeiro módulo.

Ative os planos de consulta reais e execute o seguinte:

* /

SELECT você.Nome em Exibição, você.Eu iria COMO ID do usuário, c.Eu iria COMO CommentId, c.Ponto, c.Texto

A PARTIR DE dbo.Comercial você

INTERIOR JUNTE-SE dbo.Comentários c EM você.Eu iria = c.ID do usuário

ONDE você.Localização = ‘Helsinki, Finlândia’

E c.Data de criação ENTRE ’01/08/2013′ E ’30/08/2013′

ORDEM POR c.Ponto DESC;

IR

/ *

Coisas para pensar:

* Qual tabela foi processada primeiro? E em segundo lugar?

* Como acessamos essas tabelas?

Vamos tentar um local mais popular: Índia.

* /

SELECT você.Nome em Exibição, você.Eu iria COMO ID do usuário, c.Eu iria COMO CommentId, c.Ponto, c.Texto

A PARTIR DE dbo.Comercial você

INTERIOR JUNTE-SE dbo.Comentários c EM você.Eu iria = c.ID do usuário

ONDE você.Localização = ‘Índia’

E c.Data de criação ENTRE ’01/08/2013′ E ’30/08/2013′

ORDEM POR c.Ponto DESC;

IR

/ *

Isso mudou:

* A forma do plano?

* Nossas estimativas de linha em cada tabela?

* A memória concede?

* Como o SQL Server escolheu acessar cada tabela?

Vamos tentar mais um local: Estados Unidos.

* /

SELECT você.Nome em Exibição, você.Eu iria COMO ID do usuário, c.Eu iria COMO CommentId, c.Ponto, c.Texto

A PARTIR DE dbo.Comercial você

INTERIOR JUNTE-SE dbo.Comentários c EM você.Eu iria = c.ID do usuário

ONDE você.Localização = ‘Estados Unidos’

E c.Data de criação ENTRE ’01/08/2013′ E ’30/08/2013′

ORDEM POR c.Ponto DESC;

IR

/ *

Pontos a considerar ao mudar de local:

* Quantos planos diferentes vimos até agora?

* Quantos planos podem existir para todos os locais diferentes?

* Existe talvez um plano que funcione bem para todos?

* Temos alguns dados discrepantes que precisam de planos diferentes?

Vamos ver os principais locais:

* /

SELECT TOPO 100 Localização, CONTAGEM(*) COMO recs

A PARTIR DE dbo.Comercial

GRUPO POR Localização

ORDEM POR CONTAGEM(*) DESC;

IR

SELECT CONTAGEM(*) A PARTIR DE dbo.Comercial ONDE Localização = ‘Helsinki, Finlândia’;

IR

/ *

E estamos apenas mudando a localização – nem mesmo as datas!

Vamos facilitar nossos testes criando um procedimento armazenado:

* /

CRIO OU ALTERAR PROC dbo.usp_SearchComments

@Localização NVARCHAR(200),

@Data de início DATA HORA,

@Data final DATA HORA COMO

INÍCIO

SELECT você.Nome em Exibição, você.Eu iria COMO ID do usuário, c.Eu iria COMO CommentId, c.Ponto, c.Texto

A PARTIR DE dbo.Comercial você

INTERIOR JUNTE-SE dbo.Comentários c EM você.Eu iria = c.ID do usuário

ONDE você.Localização = @Localização

E c.Data de criação ENTRE @Data de início E @Data final

ORDEM POR c.Ponto DESC;

FIM

IR

/ *

Normalmente, você não precisa limpar o cache do plano, mas isso ajudará na

algo que estou prestes a mostrar em um minuto:

* /

DBCC FREEPROCCACHE;

IR

EXEC usp_SearchComments ‘Índia’, ’01/08/2013′, ’30/08/2013′;

IR

EXEC usp_SearchComments ‘Helsinki, Finlândia’, ’01/08/2013′, ’30/08/2013′;

IR

/ *

Quando você tem uma consulta reutilizável e parametrizada, o SQL Server cria o plano

com base no primeiro conjunto de parâmetros que são usados.

Isso é chamado de sniffing de parâmetro.

O primeiro conjunto de parâmetros é detectado e usado para criar o plano em cache.

Você pode ver os planos em cache:

* /

SELECT TOPO 100 * A PARTIR DE sys.dm_exec_query_stats;

SELECT * A PARTIR DE sys.dm_exec_query_plan(0x05000400A8F9B90D709D72C38901000001000000000000000000000000000000000000000000000000000000);

IR

/ *

Ou da maneira moderna – sp_BlitzCache do nosso kit de código aberto First Responder:

http://FirstResponderKit.org

Desative os planos reais antes de executar isso, porque você não deseja ver o

planos espetacularmente grandes envolvidos na análise de suas consultas:

* /

EXEC sp_BlitzCache;

IR

/ *

Veja a coluna “Parâmetros de execução em cache” em sp_BlitzCache. Isto é

construído com o primeiro conjunto de parâmetros do plano, o conjunto compilado.

Esses eventos (e outros) podem fazer com que o plano em cache desapareça:

* Reiniciando o SQL Server

* DBCC FREEPROCCACHE

* Reconstruindo índices em tabelas na consulta

* Atualizando estatísticas sobre tabelas na consulta

Vamos fazer um desses:

* /

ALTERAR MESA dbo.Comercial RECONSTRUIR;

IR

/ * E tente a consulta novamente, mas desta vez execute Helsinque primeiro: * /

EXEC usp_SearchComments ‘Helsinki, Finlândia’, ’01/08/2013′, ’30/08/2013′;

IR

EXEC usp_SearchComments ‘Índia’, ’01/08/2013′, ’30/08/2013′;

IR

/ *

Quando você tem uma consulta lenta:

* Se não tiver parâmetros, é fácil investigar.

* Se ele possui parâmetros, e SEMPRE é lento, também é fácil.

* Se tiver parâmetros, e algumas vezes é lento, isso é realmente complicado.

Não vou entrar em detalhes sobre o parâmetro sniffing aqui, mas vou

dar-lhe alguns recursos sobre como identificar quando está acontecendo, como reagir

parametrizar farejar emergências e como ajustar suas consultas para que sejam menos

suscetível à detecção de parâmetros:

Vídeo gratuito sobre a detecção de parâmetros: https://BrentOzar.com/go/sniff

Artigo longo: Lento no aplicativo, rápido no SSMS por Erland Sommarskog

http://www.sommarskog.se/query-plan-mysteries.html

Também coberto na minha aula de treinamento de três dias, Mastering Query Tuning:

https://www.brentozar.com/product/mastering-query-tuning/

A razão pela qual estou mencionando o parâmetro sniffing, no entanto, é que eu só preciso de você

para entender que você pode ajustar uma consulta para que ela funcione bem com alguns

parâmetros, mas não outros.

Quando você tem uma consulta orientada a parâmetros, precisa:

* Colete um conjunto de parâmetros para ajuste:

* Normalmente chamados de usuários preocupados com

* Outliers com conjuntos de dados muito pequenos

* Outliers com conjuntos de dados muito grandes

* Outliers onde o SQL Server estima linhas incorretamente

* Armado com isso, então você:

* Ajuste índices ou consultas para que um plano funcione melhor para todos, ou

* Execute a consulta com cada conjunto de parâmetros, obtendo seu plano

* Medir o desempenho dos diferentes planos com diferentes entradas

Para obter o conjunto de parâmetros comuns, você pode:

* Pergunte aos usuários

* Consultar as tabelas (procurando outliers)

* Verifique o cache do plano (mas estes são apenas os parâmetros compilados)

* Execute um rastreamento do Profiler ou uma sessão de Eventos Estendidos, capturando-os enquanto eles são executados

Ou altere seu código para registrá-los temporariamente:

* /

CRIO MESA dbo.Debug_ParameterLog

(Eu iria INT IDENTIDADE(1,1) PRIMARY CHAVE CLUSTERED,

ProcName NVARCHAR(200),

RunDate DATETIME2 PADRÃO GETDATE(),

Params NVARCHAR(MAX));

IR

CRIO OU ALTERAR PROC dbo.usp_SearchComments

@Localização NVARCHAR(200),

@Data de início DATA HORA,

@Data final DATA HORA,

@Debug_ParameterLog MORDEU = 0 0 COMO

INÍCIO

E SE @Debug_ParameterLog = 1

INSERIR PARA DENTRO dbo.Debug_ParameterLog (ProcName, Params)

VALORES (‘usp_SearchComments’,

N‘@ Localização = N’ + @Localização + ‘,’ +

N‘@ StartDate = N’ + FUNDIDA(@Data de início COMO NVARCHAR(30)) + N‘,’ +

N‘@ EndDate = N’ + FUNDIDA(@Data final COMO NVARCHAR(30)) + N‘;’);

SELECT você.Nome em Exibição, você.Eu iria COMO ID do usuário, c.Eu iria COMO CommentId, c.Ponto, c.Texto

A PARTIR DE dbo.Comercial você

INTERIOR JUNTE-SE dbo.Comentários c EM você.Eu iria = c.ID do usuário

ONDE você.Localização = @Localização

E c.Data de criação ENTRE @Data de início E @Data final

ORDEM POR c.Ponto DESC;

FIM

IR

/ * Normalmente, você deixaria @Debug_ParameterLog = 0.

Mas quando você precisar começar a capturar parâmetros, basta alterar o padrão para 1: * /

CRIO OU ALTERAR PROC dbo.usp_SearchComments

@Localização NVARCHAR(200),

@Data de início DATA HORA,

@Data final DATA HORA,

@Debug_ParameterLog MORDEU = 1 COMO

INÍCIO

E SE @Debug_ParameterLog = 1

INSERIR PARA DENTRO dbo.Debug_ParameterLog (ProcName, Params)

VALORES (‘usp_SearchComments’,

N‘@ Localização = N’ + @Localização + ‘,’ +

N‘@ StartDate = N’ + FUNDIDA(@Data de início COMO NVARCHAR(30)) + N‘,’ +

N‘@ EndDate = N’ + FUNDIDA(@Data final COMO NVARCHAR(30)) + N‘;’);

SELECT você.Nome em Exibição, você.Eu iria COMO ID do usuário, c.Eu iria COMO CommentId, c.Ponto, c.Texto

A PARTIR DE dbo.Comercial você

INTERIOR JUNTE-SE dbo.Comentários c EM você.Eu iria = c.ID do usuário

ONDE você.Localização = @Localização

E c.Data de criação ENTRE @Data de início E @Data final

ORDEM POR c.Ponto DESC;

FIM

IR

EXEC usp_SearchComments ‘Helsinki, Finlândia’, ’01/08/2013′, ’30/08/2013′;

IR

EXEC usp_SearchComments ‘Índia’, ’01/08/2013′, ’30/08/2013′;

IR

SELECT * A PARTIR DE dbo.Debug_ParameterLog;

/ *

Acertar seus parâmetros pode ser complicado aqui, e você pode tecnicamente

pressione injeção SQL – então é claro que você não gostaria apenas de pegar os parâmetros

e execute-os como estão.

Isso é deixado como um exercício para o leitor.

Falando em exercícios para o leitor, vamos fazer um!

Esta consulta estava no cache do seu plano como uma das mais intensivas em recursos:

* /

CRIO OU ALTERAR PROC dbo.usp_CommentBattles

@UserId1 INT,

@UserId2 INT COMO

INÍCIO

COM Batalhas COMO (SELECT c1.PostId, c1.Ponto COMO User1Score, c2.Ponto COMO User2Score

A PARTIR DE dbo.Comentários c1

INTERIOR JUNTE-SE dbo.Comentários c2 EM c1.PostId = c2.PostId E c1.Eu iria <> c2.Eu iria

ONDE c1.ID do usuário = @UserId1

E c2.ID do usuário = @UserId2

)

SELECT User1Victories = COALESCE(SOMA(CASO QUANDO b.User1Score > b.User2Score ENTÃO 1 OUTRO 0 0 FIM),0 0),

User2Victories = COALESCE(SOMA(CASO QUANDO b.User1Score < b.User2Score ENTÃO 1 OUTRO 0 0 FIM),0 0),

Empates = COALESCE(SOMA(CASO QUANDO b.User1Score = b.User2Score ENTÃO 1 OUTRO 0 0 FIM),0 0)

A PARTIR DE Batalhas b;

FIM

IR

/ * Sua missão: encontre valores externos para @ UserId1 e @ UserId2. * /

/ *

Licença: Creative Commons Attribution-ShareAlike 3.0 Unported (CC BY-SA 3.0)

Mais informações: https://creativecommons.org/licenses/by-sa/3.0/

Você é livre para:

* Compartilhar – copie e redistribua o material em qualquer meio ou formato

* Adapte – remixe, transforme e desenvolva o material para qualquer finalidade, inclusive

comercialmente

Sob os seguintes termos:

* Atribuição – você deve dar o crédito apropriado, fornecer um link para a licença,

e indicar se foram feitas alterações.

* ShareAlike – Se você remixar, transformar ou desenvolver o material, deverá

distribua suas contribuições sob a mesma licença que o original.

* /

Um guia para iniciantes sobre os Databricks do Azure

Azure Databricks integration with other services.


Este artigo serve como um guia completo para os Databricks do Azure para iniciantes. Aqui, você mostrará os conceitos básicos dos Databricks no Azure, como criá-lo no portal do Azure e vários componentes e componentes internos relacionados a ele.

Os sistemas estão trabalhando com grandes quantidades de dados em petabytes ou mais e ainda estão crescendo a uma taxa exponencial. O big data está presente em todos os lugares e vem de diferentes fontes, como sites de mídia social, vendas, dados de clientes, dados transacionais etc. E acredito firmemente que esses dados só têm seu valor se pudermos processá-los de maneira interativa e rápida.

O Apache Spark é um sistema de computação de cluster rápido e de código aberto e uma estrutura altamente popular para análise de big data. Essa estrutura processa os dados em paralelo, o que ajuda a aumentar o desempenho. Está escrito em Scala, uma linguagem de alto nível, e também suporta APIs para Python, SQL, Java e R.

Agora a pergunta é:

O que é o Azure Databricks e como ele está relacionado ao Spark?

Simplificando, o Databricks é a implementação do Apache Spark no Azure. Com clusters Spark totalmente gerenciados, é usado para processar grandes cargas de trabalho de dados e também ajuda na engenharia de dados, na exploração de dados e também na visualização de dados usando o Machine Learning.

Enquanto eu trabalhava em databricks, acho essa plataforma analítica extremamente amigável para o desenvolvedor e flexível, com facilidade para usar APIs como Python, R etc. Para explicar um pouco mais, digamos que você criou um quadro de dados em Python, com Databricks do Azure, você pode carregar esses dados em um modo de exibição temporário e pode usar Scala, R ou SQL com um ponteiro referente a esse modo de exibição temporário. Isso permite que você codifique em vários idiomas no mesmo notebook. Este foi apenas um dos recursos interessantes.

Por que os Databricks do Azure?

Evidentemente, a adoção do Databricks está ganhando importância e relevância em um mundo de big data por duas razões. Além do suporte a vários idiomas, esse serviço nos permite integrar facilmente com muitos serviços do Azure, como Blob Storage, Data Lake Store, banco de dados SQL e ferramentas de BI como Power BI, Tableau, etc. É uma ótima plataforma colaborativa que permite que os profissionais de dados compartilhem clusters e espaços de trabalho, o que leva a maior produtividade.

Esboço

Antes de começarmos a cavar Databricks no Azure, gostaria de dedicar um minuto aqui para descrever como esta série de artigos será estruturada. Pretendo abordar os seguintes aspectos dos Databricks no Azure nesta série. Observe – este esboço pode variar aqui e ali quando eu realmente começar a escrever sobre eles.

  1. Como acessar o Armazenamento de Blob do Azure nos Databricks do Azure

  2. Processando e explorando dados nos Azure Databricks

  3. Conectando bancos de dados SQL do Azure com os Databricks do Azure

  4. Carregar dados no SQL Data Warehouse do Azure usando os Databricks do Azure

  5. Integrando os Databricks do Azure ao Power BI

  6. Execute um Bloco de Dados do Azure Databricks no Azure Data Factory e muito mais…

Neste artigo, falaremos sobre os componentes dos Databricks no Azure e criaremos um serviço Databricks no portal do Azure. Avançando, criaremos um cluster Spark neste serviço, seguido pela criação de um notebook no cluster Spark.

A captura de tela abaixo é o diagrama apresentado pela Microsoft para explicar os componentes do Databricks no Azure:

Integração do Azure Databricks com outros serviços.

Existem alguns recursos que vale a pena mencionar aqui:

  • Área de Trabalho Databricks – Oferece um espaço de trabalho interativo que permite que cientistas, engenheiros e empresas de dados colaborem e trabalhem em conjunto em notebooks e painéis
  • Databricks Runtime – Incluindo o Apache Spark, eles são um conjunto adicional de componentes e atualizações que garantem melhorias em termos de desempenho e segurança de cargas de trabalho e análises de big data. Essas versões são lançadas regularmente
  • Como mencionado anteriormente, ele se integra profundamente a outros serviços, como os serviços do Azure, Apache Kafka e Hadoop Storage, e você pode publicar ainda mais os dados no aprendizado de máquina, análise de fluxo, Power BI, etc.

  • Por ser um serviço totalmente gerenciado, vários recursos como armazenamento, rede virtual etc. são implantados em um grupo de recursos bloqueados. Você também pode implantar esse serviço em sua própria rede virtual. Veremos isso mais adiante neste artigo

  • Sistema de arquivos Databricks (DBFS) – Esta é uma camada de abstração sobre o armazenamento de objetos. Isso permite montar objetos de armazenamento, como o Armazenamento de Blob do Azure, que permite acessar dados como se estivessem no sistema de arquivos local. Vou demonstrar isso em detalhes no próximo artigo desta série

Agora que temos um entendimento teórico dos Databricks e seus recursos, vamos ao portal do Azure e o vemos em ação.

Criar um serviço de Databricks do Azure

Como para qualquer outro recurso no Azure, você precisaria de uma assinatura do Azure para criar Databricks. Caso não tenha, você pode ir aqui para criar um gratuitamente.

Entre no portal do Azure e clique em Crie um recurso e tipo databricks na caixa de pesquisa:

Crie Databricks no portal do Azure. 1/3

Clique no Crio como mostrado abaixo:

Crie Databricks no portal do Azure. 2/3

Você será levado para a tela a seguir. Providencie a seguinte informação:

  • Assinatura – selecione sua assinatura

  • Grupo de recursos – estou usando o que já criei (azsqlshackrg), você pode criar um novo também para esse

  • Nome da área de trabalho – é o nome (azdatabricks) que você deseja fornecer para o serviço databricks

  • Localização – selecione a região onde deseja implantar o serviço de dados, Leste dos EUA

  • Nível de preço – Estou selecionando Premium – 14 dias grátis de DBUs para esta demonstração. Para saber mais detalhes sobre as camadas Standard e Premium, clique aqui

Depois, bata no Revisar + Criar para revisar os valores enviados e, finalmente, clique no Crio para criar este serviço:

Crie Databricks no portal do Azure. 3/3

Depois de criado, clique na opção “Ir para o recurso” na guia de notificação para abrir o serviço que você acabou de criar:

Serviço de banco de dados implantado com êxito no Azure.

Você pode ver várias informações específicas, como URL, detalhes de preços, etc. sobre o serviço de dados no portal.

Clique em Iniciar espaço de trabalho abrir o portal do Azure Databricks; é aqui que criaremos um cluster:

Iniciar espaço de trabalho databricks no Azure

Você será solicitado a entrar novamente para iniciar o Databricks Workspace.

A captura de tela a seguir mostra a página inicial do Databricks no portal do Databricks. No Área de trabalho Na guia, você pode criar cadernos e gerenciar seus documentos. o Dados A guia abaixo permite criar tabelas e bancos de dados. Você também pode trabalhar com várias fontes de dados como Cassandra, Kafka, Azure Blob Storage, etc. Clique em Clusters na lista vertical de opções:

Portal de Databricks do Azure.

Criar um cluster Spark nos Databricks do AzureClusters em databricks no Azure são criados em um ambiente Apache spark totalmente gerenciado; você pode aumentar ou diminuir a escala automaticamente com base nas necessidades da empresa. Clique em Criar cluster abaixo no Clusters página:

Criar um cluster de spark nos databricks no Azure

A captura de tela a seguir mostra várias opções de configuração para criar um novo cluster de bancos de dados. Estou criando um cluster com o tempo de execução 5.5 (um mecanismo de processamento de dados), versão Python 2 e a série Standard_F4s configurada (o que é bom para baixas cargas de trabalho). Como é uma demonstração, não estou habilitando o auto-scaling e também a opção para encerrar este cluster se ele estiver ocioso por 120 minutos.

Por fim, gire-o com um clique no Criar cluster botão no Novo cluster página:

Criar um cluster Spark nos databricks no Azure

Basicamente, você pode configurar seu cluster como desejar. Várias configurações de cluster, incluindo Opções avançadas, são descritas em detalhes aqui nesta página de documentação da Microsoft.

Você pode ver o status do cluster como Pendente na captura de tela abaixo. Isso levará algum tempo para criar um cluster:

Cluster Spark no estado pendente nos bancos de dados no Azure

Agora nosso cluster está ativo e em execução:

Cluser de faísca no estado em execução nos bancos de dados no Azure

Por padrão, o Databricks é um serviço totalmente gerenciado, o que significa que os recursos associados ao cluster são implantados em um grupo de recursos bloqueados, databricks-rg-azdatabricks-3… como mostrado abaixo. Para o serviço Databricks, azdatabricks, VM, disco e outros serviços relacionados à rede são criados:

Recursos criados com databricks no Azure 1/2

Você também pode observar que uma conta de armazenamento dedicada também é implantada no grupo de recursos fornecido:

Recursos criados com databricks no Azure 2/2

Crie um bloco de anotações no cluster Spark

Um notebook no cluster spark é uma interface baseada na Web que permite executar código e visualizações usando diferentes idiomas.

Depois que o cluster estiver em funcionamento, você poderá criar blocos de anotações nele e também executar tarefas do Spark. Na guia Área de trabalho, na barra de menus vertical esquerda, clique em Crio e selecione Caderno:

Bloco de anotações em bancos de dados no Azure.

No Crio Caderno caixa de diálogo, forneça o nome do Notebook, selecione o idioma (Python, Scala, SQL, R), o nome do cluster e pressione o Crio botão. Isso criará um bloco de anotações no cluster Spark criado acima:

Crie um bloco de notas nos databricks.

Como exploraremos diferentes facetas dos Databricks Notebooks em meus próximos artigos, vou colocar um ponto final neste post aqui.

Conclusão

Tentei explicar o básico dos Azure Databricks da maneira mais compreensível aqui. Também abordamos como você pode criar Databricks usando o Portal do Azure, seguido pela criação de um cluster e um bloco de anotações nele. A intenção deste artigo é ajudar os iniciantes a entender os fundamentos dos Databricks no Azure. Fique atento aos artigos do Azure para descobrir mais sobre essa ferramenta poderosa.

Gauri Mahajan
Últimas mensagens de Gauri Mahajan (ver todos)

Usando arquivos de script C # externos no Biml

Adding a C# file to the solution


No artigo publicado anteriormente, Estendendo o Biml com scripts C #, explicamos como usar scripts VB ou C # dentro do código Biml para impedir o trabalho repetitivo de desenvolvimento. Mas nesta solução, os scripts e classes C # estão disponíveis apenas em um único arquivo, enquanto podemos precisar usá-los em muitos.

Neste artigo, explicaremos como armazenar esses scripts VB ou C # em arquivos externos e usá-los nos scripts Biml. Além disso, mencionaremos como criar métodos de extensão dentro desses scripts C #.

Adicionando scripts VB ou C #

Para adicionar scripts externos, precisamos ir para a faixa de menu “Extensões”, “BimlExpress” e clicar em “Adicionar novo arquivo C #” ou “Adicionar novo arquivo VB” com base no idioma que precisamos usar:

Adicionando um arquivo C # à solução

Figura 1 – Adicionando um novo arquivo C #

Depois de clicar nesse botão, um novo arquivo C # é adicionado à solução:

Arquivo C # incluído dentro do Solution Explorer

Figura 2 – arquivo C # adicionado na solução

Definindo um método dentro da classe C #

Neste exemplo, usaremos essa classe C # para definir um método que gera um identificador global (GUID) a ser usado dentro de uma transformação de coluna derivada. Abrimos o arquivo e importamos o espaço para nome do sistema. Em seguida, adicionamos o seguinte código:

Usando o arquivo C # no Biml

Para começar, usaremos o código Biml que desenvolvemos no artigo Extending Biml with C # scripts.

Primeiro, precisamos importar o arquivo C # usando um bloco de diretiva colocado fora do principal elemento da seguinte forma:

Então, quando precisarmos usar qualquer método definido neste arquivo, devemos escrever o nome da classe e do método (ou seja, MyClass.GetNewGuid ()).

Voltar ao código Biml que desenvolvemos anteriormente. Se precisarmos usar o método C # na expressão Coluna Derivada, devemos usar um bloco de texto onde implementamos este método:

Se dermos uma olhada na janela de código compilado, podemos ver que um novo identificador global é avaliado para a transformação da coluna derivada (observe que esses exemplos podem não funcionar no mundo real, pois o GUID gerado não será alterado para todas as linhas importadas e não é gerado por linha).

mostrando como o método auxiliar é avaliado ao usar dentro de uma expressão de colum derivada

Figura 3 – Mostrando o valor retornado pelo método após a compilação

Como podemos ver no código, adicionamos manualmente o nome do esquema nos componentes de origem e destino do OLE DB, pois o “con.GetDatabaseSchema ()” retorna apenas o nome da tabela.

Agora, adicionaremos um script C # que usa o TableNode como parâmetro e retornar um nome completo (esquema + tabela):

Agora, implementaremos esse método no código Biml da seguinte maneira:

Se verificarmos a janela de código compilado, veremos que obtemos o nome completo da tabela:

mostrando como o método auxiliar é avaliado no código Biml compilado

Figura 4 – Método retornando o nome completo da tabela

Criando um método de extensão

No exemplo anterior, criamos um método relacionado ao objeto TableNode em uma classe diferente. Mas e se adicionarmos vários arquivos de script externos, os métodos de rastreamento nas classes serão mais difíceis cada vez que uma nova classe for adicionada. Para resolver esse problema, podemos criar métodos externos que aparecerão na classe TableNode (método de extensão). Se você não conhece os métodos de extensão, pode consultar um dos seguintes artigos para saber mais sobre essa tecnologia:

Para criar um método de extensão, devemos adicionar a palavra-chave “this” antes do tipo de dados do parâmetro do método, da seguinte maneira:

O código inteiro deve se parecer com o abaixo:

Agora, dentro do código Biml, podemos chamar esse método usando a seguinte linha de bloco de texto:

Na captura de tela abaixo, podemos ver como esse método é avaliado após a compilação:

mostrando como o método de extensão é avaliado no código Biml compilado

Figura 5 – Avaliação do método de extensão

Conclusão

Neste artigo, explicamos como usar scripts C # externos no código Biml (usando classes e métodos Helper), também mencionamos como converter esses métodos auxiliares em métodos de extensão.

Por enquanto, acho que este é o último artigo sobre o uso do Biml para gerar e controlar pacotes do SQL Server Integration Services (SSIS) nesta série. Se você está procurando uma boa referência para aprender o Biml, pode consultar os links que fornecemos no artigo Introdução ao Biml ou o incrível blog de Catherine Wilhelmsen, que é um dos principais especialistas neste domínio.

Índice

Hadi Fadlallah
Últimas mensagens de Hadi Fadlallah (ver todos)

Monitorando atividades usando sp_WhoIsActive no SQL Server

sp_whoisactive embedded documentation


Neste artigo, falaremos sobre o procedimento armazenado sp_WhoIsActive e como podemos usá-lo para monitorar atualmente
    executando atividades no SQL Server.

Introdução

Os administradores de banco de dados (DBAs) estão constantemente verificando as operações atualmente em execução em uma instância do SQL Server, especialmente quando o servidor está mais lento.

Em geral, a Microsoft forneceu dois procedimentos armazenados do sistema chamados “sp_who” e “sp_who2” para recuperar todos os
    executando processos na instância, mas eles não possuem muitas informações úteis que possam facilitar o desempenho
    processo de monitoramento e análise, também mostram muita informação inútil (processos do sistema).

Por esse motivo, Adam Machanic (MVP da Microsoft desde 2004) desenvolveu um procedimento armazenado mais poderoso chamado “sp_whoisactive” para preencher a lacuna entre as necessidades reais dos DBAs e os
    procedimentos atualmente fornecidos (sp_who e sp_who2).

Nas seções a seguir, falaremos brevemente sobre o procedimento armazenado sp_who e sp_who2 e ilustraremos
    como baixar e usar o procedimento armazenado sp_whoisactive.

sp_Who e sp_Who2

Como mencionamos anteriormente, a Microsoft forneceu procedimentos armazenados sp_Who e so_Who2 para o monitoramento de atividades no SQL
    Servidor. Nesta seção, explicaremos quais são as informações retornadas por cada procedimento armazenado e quais são as diferenças entre eles.

Conforme descrito na documentação oficial, sp_who “Fornece informações sobre usuários, sessões e processos atuais em uma instância do Microsoft SQL Server Database Engine. A informação pode ser
        filtrado para retornar apenas os processos que não estão ociosos, que pertencem a um usuário específico ou que pertencem a um
        sessão específica “.

sp_who retorna informações como o ID do processo da sessão (SPID), o ID do contexto de execução (ECID), o processo
    status, o ID da sessão de bloqueio, o nome do banco de dados, o logon e o nome do host associados a esse processo e os
    tipo de comando

saída do procedimento sp_who

Figura – saída sp_who

sp_Who2 é semelhante ao sp_Who, mas não está documentado nem é suportado, mas retorna mais informações e desempenho
    contador dos processos atuais, como o nome do programa que executa o comando Disk IO, CPU Time, last batch
    tempo de execução.

saída do procedimento sp_who2

Figura – saída sp_who2

Conforme mostrado nas capturas de tela acima, a saída desses procedimentos está mostrando todos os processos do sistema e do usuário em execução que não são necessários o tempo todo e o usuário pode filtrar apenas usando o nome de login ou o ID da sessão enquanto pode precisar ocultar os processos do sistema. Além disso, as saídas não contêm nenhuma informação sobre o comando SQL em execução no momento, como tempo de execução, duração da execução, informações de WAIT e mais informações.

Baixe e instale sp_whoisactive

Para baixar esse procedimento, você deve ir para a página de downloads do site e selecionar a versão relevante ou pode fazê-lo no repositório do GitHub.

Após a conclusão do download, você deve abrir o arquivo who_is_active.sql usando o SQL Server Management Studio e
    execute o script.

Usando sp_Whoisactive

Após a instalação do procedimento, se o executarmos, podemos ver que ele retorna apenas os processos do usuário em execução por padrão
    e fornece as seguintes informações para cada processo:

Coluna

Descrição

Mostrado por sp_who

Mostrado por sp_who2

dd hh: mm: ss.mss

Tempo decorrido do processo

Não

Não

identificação de sessão

O ID da sessão do processo

sim

sim

sql_text

O comando SQL atualmente em execução

Não

Não

Nome de acesso

O nome de login associado ao processo

sim

sim

wait_info

As informações de espera do processo (agregadas)

Não

sim

CPU

O tempo de CPU

Não

sim

tempdb_allocations

Número de gravações Tempdb concluídas

Não

Não

tempdb_current

Número de páginas Tempdb alocadas atualmente

Não

Não

blocking_session_id

O ID da sessão de bloqueio

sim

sim

número de leituras realizadas

Não

E / S de disco

escreve

número de gravações feitas

Não

E / S de disco

leituras físicas

número de leituras físicas feitas

Não

E / S de disco

memoria usada

a quantidade de memória usada

Não

Não

status

O status do processo

sim

sim

open_tran_count

o número de transações usadas

Não

Não

percent_complete

a porcentagem de conclusão da consulta

Não

Não

nome de anfitrião

O nome da máquina host

sim

sim

nome do banco de dados

O nome do banco de dados em que a consulta é executada

sim

sim

nome do programa

O aplicativo que executou a consulta

Não

sim

start_time

A hora de início do processo

Não

sim

login_time

A hora do login

Não

Não

Identificação do Pedido

O ID da solicitação

sim

sim

collection_time

A hora em que essa última seleção foi executada

Não

Não

parte um da saída padrão sp_whoisactive

Figura – Saída do procedimento, parte 1

parte dois da saída padrão sp_whoisactive

Figura – saída do procedimento parte 2

Como mostrado abaixo, para mostrar os processos do sistema, você deve executar o seguinte comando:

mostrando processos do sistema usando sp_whoisactive

Figura – Mostrando processos do sistema

Você pode visualizar mais informações deste procedimento passando parâmetros adicionais, como
    @get_additional_info, @get_locks, @get_avg_time e outros parâmetros.

Uma das coisas surpreendentes sobre esse procedimento é que ele está bem documentado e todas as informações relacionadas podem ser
    obtido executando o seguinte comando:

documentação incorporada sp_whoisactive

Figura – Documentação incorporada do procedimento

Conforme mostrado na imagem acima, o comando help contém três seções:

  1. Informação geral: onde informações gerais como versão, site, e-mail do criador são
            forneceu
  2. Descrição dos parâmetros: onde uma lista de todos os parâmetros disponíveis com sua descrição é
            forneceu
  3. Descrição das colunas de saída: uma lista completa de todas as colunas de saída disponíveis com seus
            descrições

Salvando dados históricos

Se precisarmos salvar periodicamente as informações dos processos em execução para análises adicionais, e como todas as colunas de saída
    Como tipos e dados de dados podem ser encontrados na documentação incorporada do procedimento, podemos criar um trabalho do agente SQL que
    periodicamente executa o procedimento sp_whoisactive dentro de um comando INSERT, como:

Em seguida, podemos nos referir aos dados armazenados posteriormente para uma análise mais aprofundada.

Recursos

Se você deseja aprender mais sobre esse procedimento armazenado, pode consultar os seguintes links:

Conclusão

Neste artigo, descrevemos brevemente os procedimentos sp_who, sp_who2 e sp_whoisactive e explicamos por que
    sp_whoisactive é mais poderoso e mais necessário para administradores de banco de dados.

Hadi Fadlallah
Últimas mensagens de Hadi Fadlallah (ver todos)