É uma melhor prática separar a infraestrutura de ETL do nosso ambiente e o banco de dados para que consigamos aproveitar o máximo de desempenho dos nossos produtos e evitar a concorrência de memória e processamento das diferentes plataformas.
Contudo, no SQL Server 2012 a Microsoft disponibilizou o Catálogo do SSIS que adicionou uma grande melhoria no gerenciamento de projetos e pacotes de ETL e, dado esse cenário, é muito comum vermos servidores de ETL com uma instância do Database Engine apenas para hospedar o SSISDB.
Uma vez que esse banco de dados não tende a consumir muito espaço em disco, ele geralmente passa despercebido pelo administrador do SSIS e não impõe nenhuma degradação do ambiente, entretanto, em alguns cenários extremos, onde temos a execução de milhares de pacotes por mês esse banco pode crescer de maneira descontrolada e precisaremos dedicar uma atenção especial a ele.
Cenário
Imagine que você administra um servidor de SQL Server Integration Services com centenas de projetos e milhares de pacotes com periodicidade de execuções bastante intensas. A instalação dos produtos do SQL Server foram feitas mantendo as configurações padrão.
Um belo dia, você nota que que o seu SQL Server Agent está parado, uma vez que o startup type desse serviço está habilitado como manual. Dessa forma, você nota que o job SSIS Server Maintenance não executa há meses e o banco SSISDB se tornou muito maior do que você inicialmente havia previsto. Você toma as devidas ações de subir o SQL Agent e alterar seu startup type para Automático. Após essas ações, você tenta iniciar o job manualmente, mas nota o temido erro “9002 the transaction log is full”.
O quê eu faço agora?
Aqui você deve ter em mãos a excelente solução fornecida pelo grande Tim Mitchell no seu blog.
Nesse post ele fornece um script muito sofisticado e que com certeza faz o expurgo dos dados do SSISDB de maneira mais eficiente e sem causar tamanho dano ao transaction log da base. Assim, você consegue limpar os dados e posteriormente retomar o espaço em disco e seus problemas estão resolvidos, certo?
Não tão rápido!! Se não foram tomadas as atenções necessárias na configuração do parâmetro de Max Server Memory, após a execução desse script você vai notar que alguns pacotes falham devido à falta de espaço em disco (mesmo já tendo recuperado o espaço ocupado pelo banco SSISDB).
A mensagem de erro nos relatórios de execução do seu pacote que você verá será muito semelhante ao abaixo:
Description: The buffer manager cannot extend the file “C:\Users\SQLSER~1\AppData\Local\Temp\DTS{3AA45D52-FFD2-479F-AB52-0F0F82402850}.tmp” to length 3670016 bytes. There was insufficient disk space.
Sem uma análise mais aprofundada você pode ficar tentado a alterar a propriedade BufferTempStoragePath do seu pacote conforme descrito aqui e aqui. Mas investigando melhor o problema, você identifica que o SSIS está utilizando esses arquivos temporários pois simplesmente esgotou a quantidade de memória do servidor para a execução dos seus pacotes. Dessa forma, são criados arquivos temporários de swap na home do usuário que faz a chamado do pacote.
Olhando o maior vilão no consumo de memória do servidor você nota que é o processo sqlservr.exe que está consumindo quase 100%, alguma ação precisa ser feita!
Configurando o Max Server Memory
Por padrão o valor do max server memory será definido como 2,147,483,647 megabytes (MB). Enquanto o script executado acima foi muito eficiente na limpeza do seu SSISDB, ele fez com que o SQL Server alocasse bastante dados em memória e uma vez que o max server memory não foi configurado corretamente, essa memória não será devolvida para o Sistema Operacional tão facilmente e você sofrerá as consequências de um ambiente de ETL lento e com a possibilidade de muitas falhas.
Para corrigir isso, basta setar a configuração do max server memory para a sua instância SQL para um valor que deixe bastante memória livre no SO de maneira que seus pacotes tenham bastante capacidade para a execução. Afinal, a prioridade é a execução de processos do SSIS e o SQL Server Database Engine está lá para apoiar essa missão!
No cenário acima, o ambiente possuía 100 GB de memória como um todo então, você ficaria muito bem servido deixando 10 GB de memória para o seu banco de dados e o restante para o processamento de ETL. Para fazer essa mágica, basta executar os comandos abaixo:
EXEC sys.sp_configure N'min server memory (MB)', N'10000'
GO
RECONFIGURE
GO
Agora estamos prontos para ter um melhor desempenho do nosso servidor e evitamos falhas devido ao swap de processamento de dados da memória para o disco.
Conclusão
Nesse artigo foi mostrado como definir e configurar um valor para o max server memory de um servidor dedicado para o processamento de ETL de maneira que desenvolvedores e administradores tirem o máximo proveito do catálogo do SSIS sem impactar na execução dos pacotes.
Adicionalmente, também mencionamos uma solução estratégica para fazer expurgo do banco de dados SSISDB de maneira eficiente fornecida pelo Tim Mitchell.
Data 4all 