Como aumentar a velocidade de restauração da base de dados SQL usando compressão de banco de dados e discos de estado sólido há alguns meses atrás. Eu bloguei sobre como acelerar um backup completo do banco de dados, e o que sobre a velocidade de restauração. De fato, também é um tópico interessante para investigar. Então eu executei todos os tipos de testes e descobriu que é possível restaurar um banco de dados pelo menos 3 vezes mais rápido, desde que você tenha suficientes CPUs e capacidade de disco IO disponíveis. Os testes de restauração são executados no mesmo hardware que os posts anteriores de um servidor Unisys ES7000 de 96 núcleos com uma unidade de armazenamento DSI 3500 e DSI 3600 em estado sólido anexada. O banco de dados a restaurar é um banco de dados de 750 GB com 642 GB de dados espalhados por 96 arquivos e 96 grupos de arquivos. Monitorando uma operação de restauração O SQL Server fornece poucas informações sobre a velocidade de restauração quando uma restauração de banco de dados completo está em andamento, portanto, confie nos contadores de perfônicos do Windows regulares, como 8211 Disk ReadWrite Bytessec para medir a taxa de transferência 8211 Bytes de disco médio por ReadWrite para determinar o bloqueio de IO 8211 Tempo do processador do processo SQLServer. Etapa 1) O desempenho da restauração fora da caixa O primeiro passo é determinar a largura de banda do IO de hardware efetiva disponível no servidor tanto para ler o (s) arquivo (s) de backup quanto para restaurar os arquivos do banco de dados. A maneira mais fácil de medir isso é usando o utilitário SQLIO. O comando restore é o uso de IOs grandes variando de 512 KB a mais de 1 MByte (até 4), então vamos executar o teste SQLIO com grandes blocos também. O teste SQLIO mostra que temos uma banda de leitura aleatória disponível com mais de 3 GBytesec e uma largura de banda de Random Write, que é sustentada em 2,74 GBsec. Isso deve ser mais do que suficiente para nossas experiências de restauração. Etapa 2) Introdução: criando um backup de banco de dados completo em um único arquivo de destino Para fazer backup do banco de dados completo com compactação (84267834 páginas ou 642 GB) leva 2198 segundos, o que é 36 minutos e 38 segundos. (Eu capturai as durações exatas com SQLProfiler monitorando o banco de dados Master). O backup cria um arquivo plano de 317 GB. Impressão da velocidade de backup: o banco de dados está sendo lido com 290 MBsec e escrito para o arquivo de destino com 143 MBsec. SQLServer está usando aprox. 4 CPUs: Etapa 3) Restaurando o banco de dados Leitura de um único arquivo Para verificar rapidamente o conteúdo de um arquivo de backup, você pode usar a GUI do SSMS ou usar o seguinte comando: 8212 Para exibir o conteúdo de um conjunto de backup: RESTORE FILELISTONLY FROM DISK N8217c : DSI3601LUN01backupTPCH1TBFull1File8217 COM FILE 1 Através da GUI do SSMS ou com a seguinte declaração, você pode iniciar a operação de restauração: RESTORE DATABASE TPCH1TB FROM DISK N8217C: DSI3601LUN01BACKUPTPCH1TBFull1File8217 COM ARQUIVO 1. NOUNLOAD. SUBSTITUIR. ESTATÍSTICAS 10 GO Restaurar a partir de um único arquivo Para restaurar o banco de dados inteiro leva 19 minutos e 46 segundos. A restauração do banco de dados é significativamente mais rápida do que o backup (que demorou 36 min.38 seg.) Os dados são lidos a partir do arquivo plano com 1 MByte IOs e escritos em SSD com 512 KB IOs usando 6-7 núcleos Eu registrei o Disco Médio Escreva os dados do Bytessec em um log Perfmon e os contadores mostram que não atingimos um limite de hardware, mas a taxa de transferência de escrita é uma linha plana constante que (normalmente) indica que a taxa de transferência é limitada pelo próprio aplicativo. Tempo para investigar as nossas opções Restore Dica de otimização de desempenho 1 Escrever o conjunto de backup em vários arquivos de destino (dispositivos de despejo) acelerará o processo de backup e restauração significativamente. Para BACKUP a DATABASE para 9 arquivos mostra que as páginas 84267834 foram copiadas em 605,411 segundos (1087,430 MBsec). Restaurar o banco de dados enquanto lê de 9 arquivos de backup está acelerando o processo de restauração. A velocidade de gravação aumenta significativamente, em média, de 600 MBs para 1479 MBsec e completa em 445.191 segundos usando 18 CPUs. Isso é 2,5 vezes mais rápido. 8211Restaurar de 9 arquivos com opções RESTORE DATABASE TPCH1TB FROM DISK N8217c: DSI3601LUN01backupTPCH1TB-Full8217. DISK N8217C: DSI3601LUN01backupFile28242. DISK N8217C: DSI3601LUN01backupFile38242. DISK N8217C: DSI3601LUN00backupFile48242. DISK N8217C: DSI3601LUN01backupFile58242. DISK N8217c: DSI3601LUN01backupFile68242. DISK N8217C: DSI3601LUN01backupFile78242. DISK N8217C: DSI3601LUN01backupFile88242. DISK N8217C: DSI3601LUN00backupFile98242 COM ARQUIVO 1. NOUNLOAD. REPLACE. STATS 10 GO Impressão de restaurar o db completo, lendo de 9 arquivos de backup: Dica de restauração de otimização de desempenho 2 Para acelerar o throughput, experimente ainda mais a especificação do valor BUFFERCOUNT. Isso trará o throughput máximo para o nosso teste. Correndo Velocidade total a restauração completa em apenas 5 minutos 40 segundos RESTORE DATABASE processou com êxito 84267834 páginas em 340.330 segundos (1934.423 MBsec). Especificar o valor BUFFERCOUNT traz-nos mais 454 MB de tempo extra de restauração velocidade DBCC traceon (3605. 3004. 3014. 3213. 8211 1) ir RESTORE DATABASE TPCH1TB FROM DISK N8217c: DSI3601LUN01backupTPCH1TB-Full8217. DISK N8217C: DSI3601LUN01backupFile28242. DISK N8217C: DSI3601LUN01backupFile38242. DISK N8217C: DSI3601LUN00backupFile48242. DISK N8217C: DSI3601LUN01backupFile58242. DISK N8217c: DSI3601LUN01backupFile68242. DISK N8217C: DSI3601LUN01backupFile78242. DISK N8217C: DSI3500LUN01backupFile88242. DISK N8217C: DSI3500LUN00backupFile98242 COM ARQUIVO 1. NOUNLOAD. REPLACE. BUFFERCOUNT 2200 GO DBCC traceoff (3605. 3004. 3014. 3213. 8211 1) GO Impressão das várias métricas ao restaurar a velocidade máxima: Observações Se você gostaria de reunir mais informações sobre as operações de backup e restauração que você pode habilitar (em sua Ambiente de teste apenas, é claro), os seguintes sinalizadores de rastreamento: DBNCC traceon (3605, 3004, 3014, 3213, -1). As informações serão registradas no SQL Errorlog. Opção BLOCKSIZE do conjunto de mídia Durante as experiências de restauração, notei que usar a opção BLOCKSIZE do conjunto de mídia é um assassino de desempenho sério. Além disso, esta opção não aparece no Log, eu estava supondo que essa opção de alguma forma afetaria algumas das configurações, como o MinMaxtranferSize, mas não. Com o BLOCKSIZE configurado para 65536, a restauração leva de repente mais de 8 minutos e a taxa média de processamento é de até 1400 MBsec - (com o BLOCKSIZE configurado para 32768, a restauração leva mais de 10 minutos para ser concluída. Neste artigo PSS, eu li Que o SQL2005 SP2 é capaz de escrever IOs de 1 MB durante as operações de restauração para reduzir as necessidades de extensão de arquivos e a manutenção de metadados NTFS. Aumentar a opção MAXTRANSFERSIZE parece afetar o tamanho de bloco de escrita IO, ele mostra um aumento de média de 600 a 800 KB para 1MB Ou mais, mas durante os meus experimentos, a quilometragem variou. Amostragem do SQL sys. dmoswaitstats por 20 segundos: BACKUPIO: Ocorre quando uma tarefa de backup está aguardando dados ou está aguardando um buffer no qual armazenar dados: infelizmente, aumentando ainda mais o valor do buffer Não ajuda a reduzir este número 1. espere. Talvez a adição de mais LUNs ajudará. DISPATCHERQUEUESEMAPHORE: Ocorre quando um segmento do grupo de despachantes está aguardando mais trabalho para processar. Desligar o log log É no início da restauração ocorre extremamente rápido com gravações de 4-5 MByte muito grandes: Restauração em um nível de Arquivo ou Grupo de Arquivos Além da opção de restauração do banco de dados completo, você também pode restaurar em um arquivo ou GroupGroup. Executei alguns testes para ver se essas opções serão restauradas ainda mais rápido. Restauração Single FileGroup Para restaurar um único Grupo de arquivos (páginas 6.7 GB 877928) leva 7 minutos e 7 segundos (17.3 MBsec). Com Buffercount1100 especificado, leva apenas 3 minutos e 12 segundos (41,2 MBsec). Uma única restauração do grupo de arquivos está usando até 17 CPUs, de modo que não seria uma opção para restaurar vários grupos de arquivos em paralelo para acelerar a operação de restauração. Também porque você não pode iniciar 2 tarefas de restauração do FG em paralelo: quando você inicia várias tarefas de restauração do Grupo de Arquivos, você recebe a mensagem O acesso exclusivo não pôde ser obtido porque o banco de dados está em uso. Restaurar todos na sequência 1 por 1 teria demorado quase 5 horas. Mas você pode especificar All FileGroups para restaurá-los todos de uma vez. Restaurar todos os 97 grupos de arquivos (incluindo o primário) fornece um desempenho semelhante a uma restauração DB Full regular: melhor resultado. 5 minutos e 39 segundos. (RESTORE DATABASE 8230 FILEltnamegt processou com sucesso 84267834 páginas em 339.174 segundos (1941.016 MBsec)). Você pode verificar o status do grupo de arquivos depois com o seguinte comando: SELECT name, reportedesc FROM sys. databasefiles Se você esqueceu de restaurar um ou mais grupos de arquivos, o status Statedesc será RESTORING em vez de ONLINE. Os truques para acelerar um backup de banco de dados também se aplicam para aumentar a velocidade de restauração simplesmente adicione mais arquivos de destino de backup e especifique um valor de Buffercount alto. Isso irá levá-lo a restaurações até 3.3x mais rápidas. Em meus testes, o tempo de restauração de um banco de dados de 462 GB diminuiu de aproximadamente 20 minutos para menos de 6 minutos. Desde que você tenha CPU suficientes (em média, 23, até 34 CPUs são utilizadas pelo SQLServer) e rápido armazenamento de estado sólido para acompanhar a taxa de transferência de IO, o SQL Server 2008R2 aproveitará as CPUs disponíveis e a largura de banda IO para restaurar bancos de dados rapidamente . Como aumentar a velocidade de restauração da base de dados SQL usando compressão de banco de dados e discos de estado sólido. 5.0 de 5 com base em 14 avaliaçõesShowdown-bcp vs. DTS Qual bomba de dados do SQL Server recebe dados dentro e fora de maneira mais rápida Cada gerenciador de aplicativos do SQL Server precisa mover uma grande quantidade de dados para dentro ou fora de um banco de dados do SQL Server pelo menos uma vez, então O SQL Server possui várias ferramentas para operações de transferência de dados em massa. Neste artigo, comparo dois programas de utilidade conhecidos do SQL Server, programa de cópias de problemas (bcp) e Serviços de transformação de dados (DTS) em um cenário comum: movendo dados para e de um arquivo de texto ASCII. Comparo o desempenho e as capacidades desses utilitários com a instrução T-SQL BULK INSERT, que, de acordo com a Microsoft, é o método de cópia em massa mais rápido (veja Arquitetura Data ImportExport no SQL Server 2000 Books Online BOL para obter mais informações). Arquitetura de cópia em massa O SQL Server possui várias APIs subjacentes. Alguns deles, a API de biblioteca de banco de dados original, OLE DB Provider para SQL Server e o driver ODBC incluem um conjunto de funções de cópia em massa. No SQL Server 6.5 e versões anteriores, o utilitário bcp usa a API da biblioteca de banco de dados. No SQL Server 7.0, a Microsoft introduziu uma nova versão do bcp que usa a API de cópia em massa ODBC em vez disso para suportar novos tipos de dados, que DB-Library não suporta. (A Microsoft estabilizou a DB-Library no seu nível atual de funcionalidade, de modo que a empresa não incluirá novos recursos). Em última análise, o mecanismo de armazenamento do SQL Servers lida com chamadas de cada uma dessas APIs. Figura 1. página 38, mostra uma visão geral da arquitetura de cópia em massa do SQL Server 2000s. Dentro do mecanismo de armazenamento, a instrução T-SQL BULK INSERT, bcp e a tarefa DTS Bulk Insert executam o mesmo código. A instrução T-SQL BULK INSERT pode potencialmente ser mais rápida do que as outras porque BULK INSERT é executado completamente no processo do SQL Server. Bcp e DTS, programas de utilidade que executam em seus próprios processos, devem suportar a sobrecarga das comunicações interprocesso para passar dados do arquivo de texto de origem para o SQL Server. Quando você executa bcp ou DTS em um computador cliente, em vez de no sistema SQL Server, a necessidade de copiar os dados através de uma conexão de rede adiciona sobrecarga significativa e retarda ainda mais a operação de cópia em massa. Nos resultados do teste, a vantagem potencial da declaração T-SQL BULK INSERT nem sempre se traduz em um carregamento mais rápido. De acordo com o Microsoft Product Support Services (PSS), a vantagem é significativa com tabelas muito grandes (a Microsoft rotineiramente teste com tabelas de 50 milhões de linhas), quando você carrega várias tabelas em um banco de dados em paralelo e quando você usa grupos de arquivos para segregar Diferentes volumes de disco as tabelas que você planeja carregar em paralelo. Outros fatores também influenciam o desempenho geral de uma operação de cópia em massa: os campos de ponto flutuante são mais rápidos do que os campos de caracteres, e os arquivos criados para armazenar dados no formato de dados binários interno nativo do SQL Servers são mais rápidos do que os arquivos no formato de caracteres ASCII externo. Criando arquivos de teste Eu criei o banco de dados e definei todos os objetos para o meu teste usando um script T-SQL no SQL Server Query Analyzer. Então, eu fiz backup desse banco de dados vazio para criar um ponto de partida reprodutível para meus testes. Usando um gerador de dados de teste para uma tabela com linhas de 608 bytes (que consistem em uma combinação de tipos de dados varchar e inteiro), eu gere tabelas de 5000 linhas, 100.000 linhas e 1 milhão de linhas. Usei o DTS ImportExport Wizard para exportar cada tabela para arquivos de texto no formato de valores separados por vírgulas (CSV) e no formato de campo fixo. Esses arquivos de texto se tornaram meu padrão de dados para teste DTS. Como o bcp não cria diretamente arquivos de texto no mesmo formato CSV que o DTS pode gerar (ou seja, o bcp não oferece uma maneira simples de criar automaticamente arquivos de dados de texto com campos delimitados por vírgulas e campos de texto citados), criei um segundo conjunto de arquivos de texto para Bcp e teste BULK INSERT. Eu usei DTS para carregar os arquivos de dados do formato CSV e o bcp para exportá-los para o formato de campo de largura variável bcps e um formato de campo fixo. Eu usei os arquivos de formato de campo de largura variável para testes bcp e BULK INSERT. O sistema de teste consistiu no SQL Server 2000 Enterprise Edition executado no Windows 2000 Advanced Server com o Service Pack 1 (SP1). Minha plataforma de teste foi um Compaq ProLiant 7000 com dois processadores Intel Pentium II de 500MHz e 512MB de DRAM. Meu sistema incluiu um Compaq Smart Array 3100ES Controller. Eu coloco os dados do SQL Server em uma matriz RAID de 12 unidades e coloco arquivos de log em uma matriz separada de 3 unidades RAID 0. O DTS e sua GUI fizeram cópias em massa entre o SQL Server e um arquivo de texto fácil. O assistente SQL Servers DTS usa a tarefa Transform Data para simplificar os dados em movimento. O DTS Package Designer, que fornece recursos DTS que o assistente não expõe, permite criar procedimentos complexos. A tarefa DTS Transform Data permite modificar o formato do campo de dados durante o processo de importação ou exportação. DTS também expõe a funcionalidade da declaração T-SQL BULK INSERT mais eficiente na tarefa DTS Bulk Insert. Eu usei o assistente DTS para a maioria dos meus testes DTS, envolvendo a tarefa de Inspeção em massa somente para verificar se o desempenho era equivalente a usar a instrução T-SQL BULK INSERT no Query Analyzer. O assistente DTS torna a exportação de uma tabela fácil, basta selecionar o banco de dados e a tabela de origem, clicar em Arquivo de texto, fornecer um nome de arquivo e escolher se deseja um arquivo de saída fixo ou com delimitação por vírgula. Importar os dados do arquivo delimitado por vírgulas foi tão fácil quanto exportar porque o formato do arquivo de texto corresponde ao formato da tabela. Quando a primeira linha no arquivo de texto contém os nomes das colunas da tabela de destino (uma seleção de caixa de seleção), a ordem das colunas no arquivo de texto torna-se sem importância. Sob essas condições, a importação tolerará colunas que faltam que permitem NULLs e colunas definidas com uma restrição padrão. A importação de dados de um arquivo de formato de campo fixo, que você cria usando DTS para exportar a tabela em um formato de campo fixo, foi apenas um pouco mais complexa. O DTS detecta as posições das colunas que contêm dados precedidos de um espaço em branco e coloca as quebras de coluna padrão nessas posições. Meus dados continham algumas dessas posições de coluna que incluíam o início de novas colunas de dados e, por padrão, definiram mais colunas de dados no arquivo de entrada do que a tabela de saída. Eu clique duas vezes sobre as quebras de coluna estranhas para removê-las, e a importação prosseguiu sem erro. O Assistente DST ImportExport habilita a opção Usar carga rápida por padrão. Esta opção, disponível somente quando seu sistema usa o provedor OLE DB SQL Server (SQLOLEDB), chama os provedores IRowsetFastLoad API para gerenciar de forma mais eficiente operações simples de cópia em massa. O DTS tem muitas funções que o bcp não fornece, incluindo instalações para mover dados entre bancos de dados SQL Server, entre bancos de dados SQL Server e não-SQL Server e entre duas fontes de dados ODBC. Além disso, o DTS contém funções para modificar e transformar campos de dados durante a operação de cópia em massa. Você pode escolher entre dois modos de operação do bcp: interativos e não interativos. No modo interativo, uma série de prompts pede que você descreva o formato de arquivos de texto para o bcp. A operação bcp pode salvar essas informações em um arquivo de formato baseado em texto para uso futuro. Além disso, o bcp suporta vários formatos de arquivos padrão: arquivos de dados de caracteres ASCII padrão, arquivos de dados de caracteres Unicode, formatos de dados nativos do SQL Server e uma combinação dos dois últimos. Ao usar um desses tipos de dados padrão, o bcp não solicita informações que usa um conjunto de valores padrão e, opcionalmente, cria um arquivo de formato correspondente. Da mesma forma, quando você especifica um arquivo de formato que descreve o layout dos arquivos de dados de texto, você pode executar o bcp no modo não interativo. O arquivo de formato descreve o layout do arquivo de dados que deseja importar ou exportar e define a correspondência entre os campos do arquivo de dados e as colunas da tabela do SQL Server. O arquivo de formato oferece muito poder e flexibilidade no formato físico dos arquivos de dados e no agrupamento. (Para obter mais informações sobre o layout dos arquivos de dados, consulte a Visão geral do arquivo de formato da barra lateral, página 44.) Depois de ter um arquivo de formato de trabalho, o bcp é fácil de usar. Eu criei uma série de arquivos. bat simples, um para cada um dos meus testes, porque o bcp é executado como um utilitário de linha de comando. Na linha de comando, eu especificava o banco de dados e os nomes das tabelas, bem como IN ou OUT para designar a direção da cópia. Eu também forneci o nome do arquivo de formato, o nome do arquivo de dados e uma ID e senha do usuário para autenticar o acesso à tabela do SQL Server. A operação bcp possui opções que eu não usei, incluindo a capacidade de especificar dicas de cópia em massa do SQL Server. Usando T-SQL BULK INSERT A instrução BULK INSERT expõe a funcionalidade bcps em uma declaração T-SQL. BULK INSERT move dados de um arquivo para uma tabela do SQL Server. No entanto, BULK INSERT não pode exportar dados de uma tabela do SQL Server para um arquivo. A instrução T-SQL depende de um arquivo de formato estilo bcp para determinar a estrutura dos arquivos de dados. No meu teste BULK INSERT, usei os mesmos arquivos de dados e formatos que eu usei para testar o bcp. Com essas entradas, descobri que usar a instrução BULK INSERT do Query Analyzer não era mais difícil do que usar o bcp. Então, eu criei um script T-SQL para executar a instrução BULK INSERT e calculo quanto tempo demorou para ser executado. T-SQL BULK INSERT suporta a maioria das mesmas opções e o arquivo de formato que o bcp faz. BULK INSERT não possui a capacidade do bcps para criar um arquivo de erro contendo as linhas que não foram inseridas na tabela. A instrução T-SQL não é compatível com arquivos de dados que as versões anteriores do bcp criaram esses arquivos de dados podem conter formatos de campo que o SQL Server 2000 não suporta. BULK INSERT também não possui capacidade de usar Bcps para usar formatos regionais quando copiar em massa dados de moeda, data e hora. Finalmente, BULK INSERT não possui a capacidade do bcps para definir o tamanho do pacote de rede para operações de cópia em massa para sistemas SQL Server remotos. Resultados do teste Uma vez que o período de tempo que qualquer operação particular de importação ou exportação leva para completar depende tanto da configuração de hardware dos Servidores SQL, Ive escolheu representar os tempos nos gráficos de resultados do teste como uma porcentagem do tempo de importação ou exportação mais longo (ou seja, teste de importação Os resultados são relativos ao período de tempo que o DTS levou para importar 1 milhão de registros preenchidos com ANSI e os resultados dos testes de exportação são relativos ao período de tempo que DTS levou para exportar 1 milhão de registros preenchidos com ANSI). Registrei o tempo relativo que as várias operações de cópia em massa exigidas para exportar 5000, 100,000 e 1 milhão de registros Gráfico 1. página 42, mostra a comparação de exportação para 1 milhão de registros. Eu também registrei o tempo relativo que as várias operações de cópia em massa exigidas para importar 5000, 100,000 e 1 milhão de registros Gráfico 2. página 42, mostra a comparação de importação para 1 milhão de registros. (Os gráficos mostrados são representativos de todos os resultados.) Como o BOL afirma que o T-SQL BULK INSERT é o mais rápido dos métodos de cópia em massa, eu esperava que BULK INSERT ganhasse a corrida em meus testes. Mas bcp e BULK INSERT terminaram em um calor morto virtual e o DTS não estava muito para trás. Algumas tendências dos resultados são óbvias (por exemplo, mais bytes que o SQL Server deve processar, quanto mais demorar). O que me surpreendeu foi quanto tempo o SQL Server levou para importar o arquivo de dados de comprimento fixo em que cada campo varchar é preenchido com espaços em branco para o comprimento total da coluna de 50 bytes que demorou para importar o arquivo de dados de comprimento variável. Embora o número de registros, colunas e chaves que o SQL Server precisasse inserir permaneceu constante, o número de bytes que ele processou correlaciona fortemente com o tempo necessário para o SQL Server para completar a tarefa. Por exemplo, usando o banco de dados de 1 milhão de linhas, o arquivo de dados de formato fixo DTS era 2,7 vezes o tamanho do arquivo CSV e demorou 2,5 vezes para importar. Ao calcular a média dos tempos de importação Bcp e T-SQL BULK INSERT juntas, descobri que o arquivo de dados de formato fixo era 3 vezes o tamanho do arquivo de comprimento variável e demorou 2,7 vezes quanto a importar. Monitoramento de desempenho Depois de capturar dados de temporização para os testes de 1 milhão de linhas, executei novamente a importação com o Win2K Performance Monitor ativo, registrando a CPU e as estatísticas de utilização do disco. O Gráfico 3 mostra a utilização da CPU que eu observei e o Gráfico 4 exibe as estatísticas de OI. O perfil de utilização da CPU foi interessante. Tanto para o Bcp quanto para o DTS, a utilização da CPU durante a importação ocorreu em três fases distintas de duração aproximadamente igual. Durante a Fase 1, que se correlaciona grosseiramente com o tempo que a operação de cópia em massa estava lendo o arquivo de entrada e escrevendo para tempdb, a utilização da CPU era de 44%. Durante a Fase 2, que abrange as atividades de limpeza, a utilização da CPU caiu para apenas 6%. Durante a Fase 3, que reflete o lote concluído sendo cometido de tempdb para a tabela de banco de dados de destino, a utilização da CPU foi de 46%, com uma CPU funcionando em 74% e a outra em 18%. Ao usar o T-SQL BULK INSERT, a Fase 1 ocorre no processo do SQL Server e, de acordo com minhas observações, leva apenas 82% dos ciclos de CPU que o Bcp ou o DTS requer. Observe o uso intenso de uma CPU durante a Fase 3. Eu suspeito que o caso mais robusto (ou seja, quando você carrega várias tabelas simultaneamente para diferentes grupos de arquivos em diferentes dispositivos de disco) usaria vários processadores mais fortemente. Além disso, observe a redução na utilização da CPU da Fase 3 durante uma operação de INSCRIBIDA BULK minimamente registrada de 44% a 27% da CPU. Eu também observei a utilização do disco visualizando o disco lido e escrevendo IOs por segundo e o comprimento médio da fila do disco para o volume do sistema, uma matriz RAID 0 de unidade de disco e as duas matrizes RAID que hospedavam o arquivo de log de transação do SQL Server e os bancos de dados arquivo de dados. A métrica IOs por segundo mostrou atividade de IO bastante pesada contra o volume do sistema (cerca de 90 IOs por segundo) ao longo da operação de cópia em massa, exceto para uma breve paralisação em aproximadamente a marca de três quartos. Esta matriz RAID 0 também hospedou o banco de dados tempdb e os arquivos de dados ASCII. Todos os IO nos dados do banco de dados e os arrays do registro de transações ocorreram na Fase 3. Em geral, um comprimento médio da fila de IO do disco de mais de duas vezes o número de unidades de disco em uma matriz sinaliza um potencial gargalo (ou seja, um recurso que limita a operação de completar Mais rápido). O volume do sistema (com o banco de dados tempdb e os arquivos ASCII de entrada) teve um comprimento médio da fila de disco de 1,4 e, apesar do tráfego constante de IO, meus testes não enfatizaram fortemente a fila. Meu teste também não estressou o disco com o arquivo de log de transação do banco de dados. O comprimento médio da fila do disco nunca atingiu 1. Meus testes utilizaram o volume que contém o arquivo de dados dos bancos de dados, com comprimentos de fila média IO durante as operações DTS, bcp e BULK INSERT de 39.6 , 67,7 e 31,9 eventos, respectivamente, como mostra o Gráfico 4. Esses comprimentos de fila de IO indicam um gargalo de performance nesta matriz porque ele tinha 12 unidades. A maioria das operações de computador tem um embotellamento no subsistema de IO. Ao identificar a fonte dos problemas de desempenho, você saberá quais os recursos a serem atendidos se precisar acelerar a carga. Algumas Surpresas Fiquei surpreendida algumas vezes durante o teste (para ajudar a evitar surpresas, veja a barra lateral Some Gotchas to Remember, página 39). Primeiro, importar os dados do arquivo de formato de campo fixo levou consideravelmente mais tempo do que importar os mesmos dados do arquivo de formato CSV e, após a importação, o banco de dados ocupava muito mais espaço em disco quase três vezes. Os espaços em branco nos campos varchar no arquivo de formato de campo fixo causaram esse consumo de espaço desordenado. Você especifica espaços em branco, configurando ANSIPADDING para ON. ON não é o padrão para o SQL Server 2000. ANSIPADDING é uma das sete opções de SET que o SQL Server requer quando você trabalha com visualizações indexadas, de modo que os administradores costumam ativá-lo. O valor de ANSIPADDING em vigor quando você define o campo determina se deve truncar os espaços em branco dos campos varchar. O valor em vigor quando você adiciona ou atualiza o campo não é relevante. Executei um script no Query Analyzer para definir o banco de dados. Os bancos de dados Propriedades de Conexão são padrão para a configuração ANSIPADDING para ON, então a opção estava ativada quando eu executei o script de definição do banco de dados. Se você não estiver usando visualizações indexadas e quiser economizar espaço em disco e reduzir o tempo de processamento, defina ANSIPADDING como OFF antes de criar ou adicionar campos à sua tabela. Executando operações de cópia em massa Minha segunda surpresa veio quando eu percebi o quão complexas as operações de cópia em massa foram minimamente logadas. Quando você importa um grande número de registros em um banco de dados, o registro de atualização completa pode preencher rapidamente o log de transações. No entanto, quando você define operações de cópia em massa para registro mínimo, o SQL Server registra apenas alocações de extensão (SQL Servers 64KB, unidades de 8 páginas de espaço em disco), e não os dados inseridos. A extensão da informação de alocação permite ao SQL Server reverter a transação, mas a quantidade de informações não é suficiente para rolar a transação para a frente durante as operações de recuperação do banco de dados. O SQL Server 7.0 forneceu a opção de banco de dados Select intobulk para suportar operações T-SQL BULK INSERT minimamente registradas. O SQL Server 2000 possui dois modelos de recuperação, simples e armazenados em massa, que podem resultar em operações BULK INSERT minimamente registradas. No entanto, essas configurações por si só não são suficientes para manter o SQL Server de iniciar completamente as operações BULK INSERT. Para obter um registro mínimo, você deve garantir que nada replica a tabela de destino, a tabela de destino não possui gatilhos, a tabela de destino está vazia ou não tem índices definidos e você usou a dica TABLOCK para que a operação BULK INSERT use tabela - Nível, em vez de nível de linha, bloqueio. Com grandes operações de cópia em massa, você também deve considerar a configuração BATCHSIZE em vigor. Por padrão, todo o arquivo de entrada é um lote que o SQL Server considera que é uma transação. Até que você cometer essa transação, o SQL Server não divulgará o espaço de log de transações que contenha as informações de reversão. (Para obter mais informações sobre os modelos de recuperação, consulte Kalen Delaney, Inside SQL Server, Database Recovery Models, junho de 2000.) Usando a TABLOCK Bulk Copy Hint A dica TABLOCK é necessária para que o SQL Server registre minimamente as operações T-SQL BULK INSERT. A dica TABLOCK também é uma das condições que o SQL Server requer quando você carrega dados em uma tabela de vários clientes ao mesmo tempo, uma operação paralela de carga de dados. Quando eu testei a opção TABLOCK, descobri que o SQL Server usava minimamente a transação logonly 13MB em uso depois de carregar a tabela de 1 milhão de registros com a opção TABLOCK, em comparação com mais de 370MB depois de carregar a tabela sem a opção. No entanto, depois de carregar a tabela com a opção TABLOCK, a tabela ocupava quase 2 GB de espaço em disco. Carregando a tabela do mesmo arquivo de entrada sem a opção TABLOCK usou apenas 228MB de espaço em disco. O comando DBCC SHOWCONTIG, que informa se seus índices estão fragmentados, mostraram ambas as versões com uma densidade de varredura de 99% mais, indicando que ambas as tabelas tinham muito pouca fragmentação externa. (Para informações sobre vários tipos de fragmentação, veja Kalen Delaney, Mantenha o SQL Server em Up and Running, dezembro de 2000.) A diferença entre as tabelas foi na densidade de sua página, a quantidade média de espaço usada para cada página de 8KB atribuída à tabela. A densidade média da página da tabela com TABLOCK era apenas 11% da tabela sem a opção TABLOCK tinha uma densidade de página de 99%. Eu criei a tabela com uma restrição de chave primária em cluster na coluna IDENTITY e um FILLFACTOR de 10 por cento. O SQL Server usa o FILLFACTOR somente quando ele constrói um índice SQL Server ignora o FILLFACTOR quando ele modifica o índice. (Para mais informações sobre FILLFACTOR, consulte Kalen Delaney, Inside SQL Server, The Fill-Factor Truth, página 29.) Assim, quando você omite a sugestão TABLOCK, o SQL Server parece tratar a operação T-SQL BULK INSERT como atualizações para o base de dados. Quando você especifica a dica TABLOCK, o SQL Server parece tratar a operação BULK INSERT como uma carga de tabela inicial. Observe que o comprimento da fila IO dos discos de dados durante as operações T-SQL BULK INSERT minimamente logadas caiu para eventos 3.7, o que especifica o número de operações de gravação seqüencial aguardando e não implica nenhum gargalo de IO de disco. Testes adicionais com o TABLOCK efetivamente revelaram que o SQL Server usou o FILLFACTOR durante o carregamento da tabela apenas ao carregar uma nova tabela. O SQL Server ignorou o FILLFACTOR quando executei BULK INSERT com TABLOCK em uma tabela vazia que anteriormente continha registros. Fontes na Microsoft explicaram esse comportamento da seguinte forma: Em troca de desativar atualizações simultâneas com o BULK INSERT, o TABLOCK permite algumas otimizações. Imagino que você tenha um índice em cluster. Se TABLOCK estiver configurado, usamos o mesmo algoritmo internamente que usamos para preencher o índice durante a criação do índice, e é por isso que presta atenção ao fator de preenchimento. Isso resulta em tempos de carregamento mais rápidos, mas apresenta algumas inconsistências no comportamento. Qual usar para carregar tabela simples quando o Bcp ou T-SQL BULK INSERT irá fazer o trabalho, escolha o que melhor se adapta à maneira como você trabalha. Você não pode bater as capacidades de DTS e facilidade de uso, mas você paga uma penalidade de desempenho quando usa o Assistente de ImportExport de Servidores SQL que invoca os Dados de Transformação DTS, especialmente para tabelas maiores (por exemplo, o custo foi de cerca de 15% em meu teste de 1 milhão de linhas). Para combinar o desempenho do T-SQL BULK INSERT com a conveniência de um pacote DTS, use a tarefa DTSs Bulk Insert. Quando você importa um grande número de linhas em um banco de dados, a forma como você projeta a operação afeta não apenas a velocidade, mas também o sucesso final das operações. O tamanho do lote determina a frequência com que o SQL Server libera espaço de registro durante a operação de cópia em massa e ajuda você a garantir que você não ficará sem espaço de log. Cumprir os requisitos para registrar minimamente a operação de cópia em massa tem um enorme impacto nos requisitos de disco do espaço de registro e algum impacto no desempenho com uma pontuação de 3% mais rápida no meu teste de registro de comprimento variável de 1 milhão de linhas.
No comments:
Post a Comment