Introdução Com o lançamento do SQL Server 2017 Service Pack 1, a tecnologia In-Memory ColumnStore também está disponível nas Edições Standard, Web e Even Express e LocalDB. Além do benefício de apenas uma base de código para manter, essa alteração na política também se tornará uma poupança de espaço de armazenamento de disco clara devido às suas altas taxas de duplicação e compressão de dados e, por último, mas também, também é um desempenho de consulta ad hoc grave Booster A principal diferença entre os sabores SQL é a quantidade de energia e memória da CPU atribuída a tarefas como (re) construção do Índice Clustering ColumnStore. Por exemplo: com a edição padrão, um único núcleo (tempo máximo do processador 100 do processo sqlservr) está sendo usado e a consulta de um CCI acontece com um máximo de 2 CPUs (MAXDOP2), contra o alavancagem de todas as CPUs disponíveis na Enterprise Edition. Criando um Custer (Clustering ColumnStore Index) com o SQL Server 2017 Standard Edition: Criando um CCI com todos os 4 núcleos disponíveis com o SQL Server 2017 Enterprise Edition: os tempos de base para carregar 7,2 GB 60 milhões de linhas de um único arquivo TPCH lineItem não mostra muito de Uma diferença entre os sabores quando Bulk inserindo os dados diretamente em uma tabela de heap ou uma tabela com um CCI, a diferença fica clara quando comparamos o tempo necessário para construir um CCI em uma tabela de pilha ou reconstruindo um CCI: para resumir, o absoluto A maneira mais rápida de ter dados disponíveis em uma tabela com um Índice de ColumnStore Clustered é: carregar em heap construir o CCI depois com SQL 2017 Ent. Ed. Carga direta no CCI Para tabelas com um Índice de ColumnStore Clustered já criado, certifique-se de transmitir diretamente para os Grupos de Linha Comprimidos para maximizar a taxa de transferência. Para fazer isso, o tamanho do lote Inserir deve ser igual ou maior do que 100K linhas (102400 para ser preciso). Os lotes menores serão escritos em tabelas compactadas do delta store antes de serem movidas pela tupla em seus segmentos de grupo de linhas compactados finais, o que significa que o SQL Server deve tocar os dados duas vezes: existem várias opções para carregar dados e vamos superar o uso mais utilizado Como o comando Bulk Insert, BCP e SSIS. Permite ver o que é necessário para obter o melhor desempenho e como monitorar 1) Inserção em massa de T-SQL Vamos começar com o comando BULK INSERT: Verificar o progresso da carga de dados Para verificar o número de linhas que já foram carregadas no CCI, mesmo quando A opção Table Lock está sendo usada, consulte um novo dmv chamado sys. dmdbcolumnstorerowgroupphysicalstats: Este DMV também revelará os possíveis estados do Grupo de Recursos com mais detalhes durante o carregamento. Existem quatro estados do grupo de linhas possíveis ao carregar dados. Quando você vê o estado INVISBILE, como na imagem abaixo, significa que os dados estão sendo compactados em um RowGroup. 0: INVISIBLE (RowGroup está em processo de construção a partir de dados na loja delta) 1: OPEN160160160160160160160 (RowGroup está aceitando novos registros) 2: CLOSED160160160 (o RowGroup está preenchido, mas ainda não está compactado pelo processo de movimentação de tupla) 3: COMPRESSED160 ( RowGroup é preenchido e compactado). 4 TOMBSTONE160 (RowGroup está pronto para ser lixo coletado e removido) Ao especificar o tamanho do lote com um valor de 102400 ou superior, você alcançará o máximo desempenho e os dados serão transmitidos e comprimidos diretamente em seu RG final, este comportamento aparecerá como COMPRIMIDO. Você também pode verificar um DMV que foi introduzido com o SQL2017 para verificar o Estado RowGroup, que é o sys. columnstorerowgroups DMV: Test Result Bulk inserindo dados em uma tabela com CCI através do comando Bulk Insert pode ser melhorado, adicionando o Batchsize102400 e TABLOCK opções. Isso traz uma melhoria de 8 no throughput. 2) BCP. exe O utilitário BCP ainda está sendo usado bastante em muitos ambientes de produção, portanto vale a pena verificá-lo rapidamente: por padrão, o BCP fornece 1000 linhas no momento ao SQL Server. O tempo necessário para carregar 7,2 GB de dados via BCP: 530 segundos. Ou160 113K rowssec O estado RowGroup mostra NVISIBLE, o que significa que, com as configurações padrão, a Delta Store está sendo usada. Para certificar-se de que o comando BCP transmite os dados diretamente nos RGs compactados, você deve adicionar a opção batchsize b com um valor de pelo menos 102400. Eu executei vários testes com tamanhos de lotes maiores: até 1048576, mas o 102400 me deu o melhor resultado. BCP DB. dbo. LINEITEMCCI em F: TPCHlineitem. tbl S. - c - T - tquotquot - b 102400 h tablock O estado do RowGroup agora mostra COMPRIMIDO, o que significa que ignoramos a loja Delta e os fluxos de dados nos RGs compactados: Resultado: o BCP Completado em 457 segundos ou linhas de 133K por segundo ou durante o teste percebi que as configurações padrão do SSIS 2017 usam tamanhos de buffer de memória que também podem potencialmente limitar o tamanho do lote para se tornar menos de 100K linhas. No exemplo abaixo, você vê que os dados desembarcaram nas lojas delta: os estados RG são fechados e os campos deltastorehobiti são preenchidos, o que significa que as lojas delta são alavancadas. Este foi o momento de chegar e verificar com os meus colegas que felizmente perceberam isso e uma solução já existe (consulte: Capacidade de dimensionamento automático do Buffer de fluxo de dados beneficia o carregamento de dados no CCI). Para aproveitar ao máximo as capacidades de transmissão de CCI, é necessário aumentar as configurações do MaxRows do BufferSize da memória padrão: mude estes em valores maiores de 10x: 8211 DefaultMaxBufferRows de 10000 para 1024000 e o mais importante: 8211 DefaultBufferSize de 10485760 para 104857600. Observação: a nova configuração AutoAdjustBufferSize deve ser definida como True quando você carrega linhas de dados muito largas. Altere também os valores para o adaptador de destino: 8211 Linhas por lote: 160 de nenhum em 102400 8211 Tamanho máximo de inserção de inserção: de 2147483647 a 102400 A paridade característica introduzida com o SQL Server 2017 SP1 abre uma nova gama de possibilidades para se beneficiar de Esperançosamente As orientações anteriores ajudam você a maximizar o desempenho de Inserção em massa, BCP e SSIS ao carregar dados em um Índice de ColumnStore em Cluster. O que será a maneira mais rápida e rápida de carregar dados de um arquivo plano em uma tabela no SQL Server 2017 Muito mudou desde a minha inicial Publique neste tópico há muitos anos, como a introdução de tabelas otimizadas em memória e os índices de tabelas de Columnstore atualizáveis. Além disso, a lista de veículos de transporte de dados para escolher está crescendo: além do BCP, o comando T-SQL Bulk Insert, SSIS como ferramenta ETL e PowerShell, alguns novos foram adicionados, como PolyBase, R Script Externo ou ADF. Nesta publicação, vou começar com a verificação de quanto mais rápido o novo amplo durável, as tabelas de memória não duráveis são a configuração da linha de base. Para esses testes, estou usando uma VM padrão DS4V2 Azure com 8 núcleos28 GB de RAM e 2 volumes de disco rígido com armazenamento em cache do host RW habilitado. (Ambos os Luns fornecem 275 MBsec RW throughput embora a GUI indique um limite de 60MBsec). Eu gerei um único arquivo plano de 60 milhões de linhas7.2 gigabytes TPCH lineitem como dados para carregar. Como linha de base para uso para comparação, usaremos o tempo necessário para carregar o arquivo em uma tabela Heap: Este comando regular de Inserção em massa completa em 7 minutos com uma média de 143K rowssec. Habilitando o banco de dados de teste para tabelas otimizadas de memória As tabelas introduzidas em memória (em SQL20172017 Enterprise amp Developer Edition) foram projetadas para OLTP muito rápido com muitas transações pequenas e alta concorrência, que é um tipo de carga de trabalho completamente diferente como inserção em massa, mas apenas Por curiosidades, experimente Existem 2 tipos de mesas em memória: tabelas duráveis e não duráveis. Os duráveis irão persistir dados no disco, os não duráveis não serão. Para habilitar esta opção, devemos fazer algumas tarefas domésticas e atribuir um volume de disco rápido para hospedar esses arquivos. Em primeiro lugar, altere o banco de dados para habilitar a opção Contém MEMORYOPTIMIZEDDATA, seguida de adicionar uma localização de arquivo e um grupo de arquivos que conterão as tabelas otimizadas para a memória: a terceira coisa a fazer é adicionar um pool de memória separado à instância do SQL Server para que possa manter tudo Os dados que vamos carregar em tabelas na memória separadas do seu pool de memória padrão: encadernando um banco de dados a um pool de memória. As etapas para definir um pool de memória separado e para vincular um banco de dados estão listadas abaixo: conjuntos de memória extra são gerenciados através do Governador de recursos SQL. O 4º e último passo é vincular o banco de dados de teste ao novo pool de memória com o comando sys. spxtpbinddbresourcepool.160 Para que a ligação se torne efetiva, devemos retirar o banco de dados offline e trazê-lo novamente. Uma vez ligado, podemos alterar dinamicamente a quantidade de memória atribuída ao seu pool através do comando ALTER RESOURCE POOL PoolHK WITH (MAXMEMORYPERCENT 80). Bulk Insert in Durable In-Memory table Agora, estamos todos configurados com a opção In-memory habilitada, podemos criar uma tabela na memória. Cada tabela otimizada para memória deve ter pelo menos um índice (um índice Range ou Hash) que são completamente (re) integrados na memória e nunca são armazenados no disco. Uma tabela durável deve ter uma chave primária declarada, que pode ser suportada pelo índice requerido. Para suportar uma chave primária, adicionei uma coluna ROWID1 de rownumber adicional à tabela: Especificar um tamanho de lote de 1 (até 5) Milhões de linhas para o comando de inserção em massa ajuda a persistir dados no disco enquanto a inserção em massa está em andamento (em vez de salvar Tudo no final), minimizando a pressão da memória no pool de memória PookHK que criamos. A carga de dados na tabela durável em memória é completada em 5 minutos 28 segundos ou 183K Rowssec. Isso é bom, mas não muito mais rápido do que a nossa linha de base. Olhando para o sys. dmoswaitstats mostra que o waitstat número 1 é IMPPROVIOWAIT que ocorre quando o SQL Server espera que um IO de carga em massa termine. Olhando para o contador de desempenho Bulk Copy Rowssec e Disk Write Bytessec mostra os picos de descarga de disco de 275 MBsec uma vez que um lote entrou (os pontos verdes). Esse é o máximo do que o disco pode entregar, mas não explica tudo. Dado o menor ganho, estacionaremos este para futuras investigações. Monitorando o pool de memória Através do sys. dmresourcegovernorresourcepools dmv podemos verificar se a nossa tabela na memória alavanca o pool de memória PoolHK recém-criado: a saída mostra que este é o caso, os 7.2GB (alguns extra para o Rowid) foram descompactados carregados na memória PoolHk pool: se você tentar carregar mais dados do que a memória disponível para o pool, você receberá uma mensagem adequada como essa: a declaração foi encerrada. Msg 701, Nível 17, Estado 103, Linha 5 Não há memória do sistema insuficiente no pool de recursos 8216PookHK para executar esta consulta. Para procurar um nível mais profundo na alocação de espaço de memória em uma base de tabela por memória, você pode executar a seguinte consulta (tirada do documento SQL Server In-Memory OLTP Internals para o documento SQL Server 2017): os dados que acabamos de carregar são armazenados como um Estrutura de varheap com um índice de hash: até agora tão bom Agora vamos passar e verificar a forma como o encenação em uma tabela não durável executa Inserção em massa na tabela In-Memory não durável Para tabelas IMND, não precisamos de uma chave primária, então nós apenas Adicione e índice de Hash não agrupado e defina DURABILITY SCHEMAONLY. A inserção em massa O carregamento de dados na tabela não durável termina dentro de 3 minutos com uma taxa de transferência de 335K rowssec (vs 7 minutos). Esta é 2,3 vezes mais rápida que a inserção em uma tabela de heap. Para o encaminhamento de dados, esta definitivamente é uma vitória rápida SSIS Single Bulk Insert em uma tabela não durável Tradicionalmente, o SSIS é a maneira mais rápida de carregar um arquivo rapidamente no SQL Server porque o SSIS tratará todo o pré-processamento de dados para que o mecanismo do SQL Server possa Gastar sua CPU na persistência dos dados no disco. Isso ainda será o caso ao inserir os dados em uma tabela não durável. Abaixo de um resumo dos testes que executei com o SSIS para esta publicação: a opção SSIS Fastparse e160 as configurações DefaultBufferMaxRows e DefaultBufferSize são os principais impulsionadores de desempenho. Além disso, o provedor OLE DB (SQLOLEDB.1) nativo executa um pouco melhor do que o SQL Native Client (SQLNCLI11.1). Quando você executa SSIS e SQL Server lado a lado, o aumento do tamanho do pacote de rede não é necessário.160160 Resultado líquido: um pacote SSIS básico que lê uma fonte de arquivo simples e grava os dados diretamente na tabela não durável por meio de um destino OLE DB Executa semelhante ao comando Inserção em massa em uma tabela IMND: as linhas de 60 milhões são carregadas em 2minutos 59seconds ou 335K rowssec, idênticos ao comando Bulk insert. SSIS com Distribuidor de Dados Balanced Mas wait8230160 as mesas em memória são projetadas para trabalhar com o bloqueio do amplificador sem bloqueio, então isso significa que podemos carregar dados também através de múltiplos fluxos. Isso é fácil de alcançar com o SSIS o Balanced Data Distributor irá trazer apenas isso (o BDD Está listado na seção comum da caixa de ferramentas do SSIS) Adicionando o componente BDD e inserindo os dados na mesma tabela não durável com 3 fluxos fornece a melhor taxa de transferência: agora somos até 526000 Rowssec Olhando para esta linha muito plana, com apenas 160 de tempo de CPU usado pelo SQLServer, parece que estamos atingindo algum gargalo: tentei ser criativo, alavancando a função de módulo e adicionando mais 2 fluxos de dados dentro do pacote (cada processamento 13 dos dados) 160, mas isso não está melhorando Muito (1 min52sec), então um ótimo tópico para investigar um futuro post160160 A opção de tabela In-Memory Non-Durable traz alguma melhoria de desempenho séria para o armazenamento temporário de dados 1.5x mais rápido com um inserto em massa T e até 3,6 vezes mais rápido com o SSIS. Esta opção, principalmente projetada para acelerar o OLTP, também pode fazer uma grande diferença para encolher sua janela de lote rapidamente (Para continuar) Atualizar estatísticas de tabela do SQL Server para o desempenho do kick O RANGEHIKEY mostra o valor mais alto para esse intervalo de chaves. O RANGEROWS mostra o número de registros entre o valor RANGEHIKEY e o novo valor RANGEHIKEY. O EQROWS é o número estimado de linhas que correspondem ao valor RANGEHIKEY. O valor DISTINCTRANGEROWS é o número estimado de valores distintos entre o RANGEHIKEY atual e o próximo RANGEHIKEY. O AVGRANGEROWS é o número estimado de valores duplicados entre o RANGEHIKEY atual e o próximo RANGEHIKEY. Se você tem amplos intervalos com poucos registros, talvez seja necessário ajustar a porcentagem de varredura do seu índice. Como você pode ter obtido com esta dica, a atualização estatística e o ajuste podem ser uma tarefa muito complexa. Quando feito corretamente, ele pode fornecer esse bit extra de desempenho que o sistema pode não ter fornecido sob a configuração estatística padrão. Essas técnicas permitem aumentar o desempenho do SQL Server e obter um retorno sobre cada dólar investido em sua infraestrutura do SQL Server. SOBRE O AUTOR Denny Cherry tem mais de uma década de experiência na gestão do SQL Server, incluindo MySpaces mais de 175 milhões de instalação de usuários, uma das maiores do mundo. As áreas de especialização técnica da Dennys incluem arquitetura do sistema, ajuste de desempenho, replicação e solução de problemas. Denny usa essas habilidades regularmente em seu papel atual como Administrador de banco de dados Sr. e Arquiteto da Awareness Technologies. Denny é um membro de longa data da PASS e Quest Softwares Association of SQL Server Experts e escreveu vários artigos técnicos sobre o gerenciamento do SQL Server. Este foi publicado pela última vez em outubro de 2007 Dig Deeper no Microsoft SQL Server Performance Monitoring and Tuning Encontre mais conteúdo PRO e outras ofertas para membros, aqui. E-Handbook Inicie a conversa 0 comentários Sua senha foi enviada para: Ao enviar, você concorda em receber e-mails da TechTarget e seus parceiros. Se você reside fora dos Estados Unidos, você concorda em transferir e processar seus dados pessoais nos Estados Unidos. Privacidade Crie um nome de usuário para comentar.
No comments:
Post a Comment