Práticas recomendadas para instâncias do SQL Server

Siga várias práticas recomendadas para otimizar as instâncias do Google Compute Engine que executam o Microsoft SQL Server. Para saber como configurar uma instância de alto desempenho do SQL Server, leia Como criar uma instância de alto desempenho do SQL Server.

Como configurar o Windows

Nesta seção, confira os tópicos de configuração sobre como otimizar o sistema operacional Microsoft Windows para melhorar o desempenho do SQL Server quando executado no Compute Engine.

Configurar o firewall do Windows

Prática recomendada: use o firewall avançado do Windows Server e especifique os endereços IP dos seus computadores cliente.

O firewall do Windows com segurança avançada é um componente de segurança importante no Windows Server. Quando você configura o ambiente do SQL Server para se conectar com o banco de dados de outras máquinas cliente, defina o firewall para possibilitar tráfego de entrada:

netsh advfirewall firewall add rule name="SQL Access" ^
dir=in action=allow ^
program="%programfiles%\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn\sqlservr.exe" ^
remoteip=[LOCAL_SUBNET]

Quando você usa essa regra de firewall, é uma boa prática especificar o endereço IP dos seus computadores cliente. Especifique uma lista delimitada por vírgulas de endereços IP sem espaços em branco para o parâmetro remoteip em vez de [LOCAL_SUBNET]. Além disso, observe que o caminho para o parâmetro program pode mudar, dependendo da versão do SQL Server usada.

A imagem do aplicativo SQL Server inclui uma regra de firewall do Windows no SQL Server. Essa regra é muito ampla, então convém desativá-la antes de o sistema entrar em produção.

Ajustar conexões de rede

Prática recomendada: use as configurações de rede padrão do sistema operacional.

Na maioria dos sistemas operacionais, as configurações de rede padrão são definidas para conexões em computadores menos robustos conectados a redes moderadamente rápidas. Essas configurações geralmente são suficientes. Além disso, padrões conservadores garantem que o tráfego de rede não sobrecarregue a rede e os computadores conectados.

No Compute Engine, as instâncias da máquina virtual são conectadas a uma rede projetada pelo Google, que oferece alta capacidade e desempenho. Os servidores físicos que executam instâncias do Compute Engine são altamente otimizados para aproveitar a capacidade da rede. Os drivers de rede virtual nas instâncias também são otimizados, o que torna os valores padrão suficientes para a maioria dos casos de uso.

Instalar antivírus

Prática recomendada: siga a orientação da Microsoft para software antivírus.

Se você está usando o Windows, é necessário executar algum software antivírus. Vírus de malware e software apresentam um risco significativo para qualquer sistema conectado a uma rede, e o software antivírus é uma etapa simples de prevenção que pode ser usada para proteger seus dados. No entanto, se o antivírus não está configurado corretamente, ele impacta negativamente o desempenho do banco de dados. A Microsoft fornece recomendações para escolha de software antivírus.

Otimizar desempenho e estabilidade

Esta seção contém informações sobre como otimizar o desempenho do SQL Server no Compute Engine e as atividades operacionais para mantê-lo funcionando consistentemente.

Mover arquivos de dados e de registros para um novo disco

Prática recomendada: use um disco permanente separado de SSD para arquivos de dados e registros.

Por padrão, a imagem pré-configurada do SQL Server é fornecida com tudo instalado no disco permanente de inicialização, que é ativado como a unidade "C:\". Anexe um disco permanente SSD secundário e mova os arquivos de registros e dados para o novo disco.

Usar uma SSD local para melhorar a IOPS

Prática recomendada: crie novas instâncias do SQL Server com um ou mais SSDs locais para armazenar arquivos tempdb e de paginação do Windows.

A natureza temporária da tecnologia SSD local torna-a uma opção inadequada para uso em bancos de dados críticos e arquivos importantes. No entanto, tanto o arquivo tempdb quanto o de paginação do Windows são temporários, portanto são ótimos candidatos para passar para uma SSD local. Isso descarrega diversas operações de E/S dos seus discos de SSD permanentes. Leia mais sobre como fazer essa configuração aqui.

Processamento paralelo de consulta

Prática recomendada: defina max degree of parallelism como 8.

A configuração padrão recomendada para max degree of parallelism é combiná-lo com o número de CPUs no servidor. No entanto, há um ponto em que a quebra de uma consulta em 16 ou 32 partes, a execução em diferentes CPUs e a consolidação em um único resultado levam muito mais tempo do que se apenas uma vCPU executasse a consulta. Na prática, o número oito funciona como um bom valor padrão.

Prática recomendada: monitore as esperas de CXPACKET e aumente o cost threshold for parallelism gradualmente.

Essa configuração acompanha o max degree of parallelism. Cada unidade representa uma combinação de CPU e trabalho de E/S necessária para fazer uma consulta com um plano de execução em série, antes de ser considerada para um plano de execução paralela. O valor padrão é cinco. Embora não haja recomendação específica para alterar esse valor, convém observá-lo e, se necessário, aumentá-lo de maneira incremental por cinco durante o teste de carga. Um indicador-chave de que esse valor precisa ser aumentado é a presença de esperas de CXPACKET. A presença de CXPACKET não indica, necessariamente, que essa configuração deve mudar, mas é um excelente ponto de partida.

Prática recomendada: monitore os diferentes tipos de espera e ajuste as configurações do processamento paralelo global ou configure-os no banco de dados individual.

Bancos de dados individuais têm diferentes necessidades de paralelismo. Defina essas configurações globalmente e indique Max DOP no banco de dados individual. Observe as cargas de trabalho únicas, monitore a espera e ajuste os valores.

No site SQLSkills, há um guia de desempenho útil que cobre as estatísticas de espera dentro do banco de dados. Para entender a espera e como minimizar os atrasos, siga esse guia.

Processar registros de transação

Prática recomendada: monitore o crescimento de registros de transação no sistema. Desative o crescimento automático e defina o arquivo de registros em um tamanho fixo, com base na média de acumulação de registo diário.

Uma das fontes mais negligenciadas de perda de desempenho e lentidão intermitente é o crescimento não gerenciado de registros de transações. Quando seu banco de dados está configurado para usar o modelo de recuperação Full, execute uma restauração pontual, mas seus registros de transação são preenchidos mais rápido. Por padrão, quando o arquivo de registros da transação está cheio, o SQL Server aumenta o tamanho do arquivo para adicionar mais espaço vazio a fim de gravar mais transações, e bloqueia todas as atividades no banco de dados até que o processo seja finalizado. O SQL Server aumenta cada arquivo de registros com base no tamanho máximo de arquivo e na configuração de crescimento do arquivo.

Quando o arquivo atinge o limite máximo de tamanho e não cresce mais, o sistema emite um erro 9002 e coloca o banco de dados em modo somente leitura. Se o arquivo crescer, o SQL Server expande o tamanho do arquivo e zera o espaço vazio. A configuração dos padrões de crescimento do arquivo é definida, por padrão, como 10% do tamanho atual do arquivo de registros. Essa não é a configuração ideal em termos de desempenho, porque quanto mais o arquivo cresce, mais tempo leva para um novo espaço vazio ser criado.

Prática recomendada: programe backups regulares do arquivo de transação.

Independentemente das configurações de tamanho máximo e crescimento, programe backups regulares do registro de transação que, por padrão, trunca entradas de registro antigas e faz com que o sistema reutilize o espaço do arquivo existente. Essa tarefa de manutenção simples ajuda a evitar quedas de desempenho nos momentos de pico de tráfego.

Otimizar arquivos de registros virtuais

Prática recomendada: monitore o crescimento do arquivo de registros virtuais e tome medidas para evitar a fragmentação dele.

O arquivo de registros de transações físicas é segmentado em arquivos de registros virtuais (VLF, na sigla em inglês). Novos VLFs são criados cada vez que o arquivo de registros de transações físicas precisa crescer. Se você não desativa o crescimento automático e esse crescimento é frequente, muitos VLFs são criados. Essa atividade gera a fragmentação do arquivo de registros e do disco e afeta negativamente o desempenho.

O SQL Server 2014 introduziu um algoritmo eficiente para determinar quantos VLFs são criados durante o crescimento automático. Geralmente, quando o crescimento é inferior a 1/8 do tamanho do arquivo de registros atual, o SQL Server cria um VLF dentro desse novo segmento. Antes, isso criaria 8 VLFs para crescimento entre 64 MB e 1 GB, e 16 VLFs para crescimento acima de 1 GB. Use o script TSQL abaixo para verificar quantos VLFs seu banco de dados tem atualmente. Se ele tiver milhares de arquivos, diminua e redimensione manualmente o arquivo de registros.

--Check VLFs substitute your database name below
USE 
DECLARE @vlf_count INT
DBCC LOGINFO
SET @vlf_count = @@ROWCOUNT
SELECT VLFs = @vlf_count

Leia mais sobre VLFs no site de Brent Ozar.

Evitar fragmentação de índice

Prática recomendada: desfragmente regularmente os índices nas tabelas com modificação intensa.

Os índices nas tabelas ficam fragmentados provocando um desempenho insatisfatório de consultas que usam esses índices. Uma programação de manutenção regular precisa incluir a reorganização dos índices nas tabelas mais modificadas. Execute o script T-SQL a seguir no banco de dados para mostrar os índices com a porcentagem de fragmentação. Consulte os resultados do exemplo que o índice PK_STOCK está 95% fragmentado. Na seguinte instrução 'SELECT', substitua '[YOUR_DB]' pelo nome do seu banco de dados:

SELECT stats.index_id as id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(N'[YOUR_DB]'), NULL, NULL, NULL, NULL) AS stats
    JOIN sys.indexes AS indx ON stats.object_id = indx.object_id
      AND stats.index_id = indx.index_id AND name IS NOT NULL;

RESULTS
-------------------------------
Id    name          avg_fragmentation_in_percent
-------------------------------
1 ORDERS_I1 0
2 ORDERS_I2 0
1 ORDER_LINE_I1 0.01
1 PK_STOCK95.5529819557039
1 PK_WAREHOUSE0.8

Quando os índices estiverem muito fragmentados, reorganize-os com um simples script ALTER. Aqui está um exemplo de script que imprime as instruções ALTER que podem ser executadas para cada índice das tabelas.

SELECT
'ALTER INDEX ALL ON ' + table_name + ' REORGANIZE;
GO'
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG = 'YOUR_DB'

Escolha as tabelas do conjunto de resultados com a maior fragmentação. Depois, execute essas instruções incrementalmente. Programe esse script ou um semelhante como uma das suas tarefas de manutenção regular.

Fazer backup

Prática recomendada: defina um plano e faça backups regularmente.

No site de Ola Hallengren, há um bom ponto de partida para compreender como implementar um plano sólido de backup e manutenção.

Durante backups regulares do banco de dados, cuidado para não consumir muitas IOPS de disco permanente. Use a SSD local para organizar seus backups e enviá-los por push para um intervalo do Cloud Storage.

Monitorar

Prática recomendada: use o Stackdriver Monitoring.

É possível instalar o agente Beta do Stackdriver Monitoring para que o Microsoft Windows envie vários pontos de dados de monitoramento para o sistema Stackdriver.

Use os recursos de coleta de dados para ajustar as informações que você quer monitorar e as envie para o armazenamento de dados de gerenciamento integrado. É possível executar esse data warehouse no mesmo servidor que você está monitorando, ou fazer o streaming de dados para outra instância do SQL Server que execute o data warehouse.

Carregar dados em massa

Prática recomendada: use um banco de dados separado para organizar e transformar dados em massa antes de movê-los para os servidores de produção.

Você, provavelmente, terá de carregar grandes volumes de dados no sistema pelo menos uma vez, se não regularmente. Essa é uma operação de uso intensivo de recursos e, talvez, você atinja o limite de IOPS do disco permanente durante carregamentos em massa.

Existe uma maneira fácil de reduzir E/S de disco e consumo de CPU de operações de carregamento em massa, com o benefício adicional de acelerar o tempo de execução das suas tarefas em lote. A solução aqui é criar um banco de dados completamente separado que usa o modelo de recuperação Simple. Depois use esse banco de dados para preparar e transformar o conjunto de dados em massa antes de inseri-lo no banco de dados de produção. Também é possível colocar esse novo banco de dados em uma unidade SSD local, se tiver espaço suficiente. Isso reduz o consumo de recursos das suas operações em massa, bem como o tempo de conclusão das tarefas. O benefício final é que a tarefa de backup dos dados de produção não terá de fazer backup de todas essas operações em massa no registro de transações e, portanto, será menor e terá execução mais rápida.

Validar sua configuração

Prática recomendada: teste sua configuração para verificar se ela é executada como previsto.

Sempre que você instalar um novo sistema, planeje a validação da configuração e a execução de alguns testes de desempenho. Esse procedimento armazenado é um excelente recurso para avaliar a configuração do SQL Server. Em algum momento, leia sobre as sinalizações de configuração e execute o procedimento.

Otimizar o SQL Server Enterprise Edition

O SQL Server Enterprise Edition tem uma longa lista de recursos adicionais da Standard Edition. Se você estiver migrando de uma licença existente para o GCP, há algumas opções de desempenho para implementação.

Usar tabelas compactadas

Prática recomendada: ative a compactação de tabelas e índices.

O fato de as tabelas de compactação melhorarem o desempenho do sistema pode parecer intuição, mas na maioria dos casos, isso é o que acontece. A compensação é o uso de uma pequena quantidade de ciclos de CPU para compactar dados e eliminar a E/S de disco extra, necessários para ler e gravar blocos maiores. Geralmente, quanto menos E/S de disco for usada pelo sistema, melhor será o desempenho. Instruções para estimativa e ativação de compactação de tabelas e índices são encontradas no site do MSDN.

Ativar extensão de pool de buffers

Prática recomendada: use a extensão de pool de buffers para agilizar o acesso aos dados.

O pool de buffers é local onde o sistema armazena as páginas limpas. Basicamente, onde são armazenadas cópias dos seus dados espelhando o que existe no disco. Quando os dados são alterados na memória, eles são chamados de página suja e são liberados para o disco para que as alterações sejam salvas. Quando o banco de dados é maior do que a memória disponível, isso pressiona o pool de buffers e as páginas limpas são descartadas. Quando as páginas limpas são descartadas, a leitura do sistema é feita a partir do disco na próxima vez que os dados descartados são acessados.

Com o recurso de extensão de pool de buffers, é possível enviar por push as páginas limpas para uma SSD local, em vez de descartá-las. Isso funciona nos mesmos moldes da memória virtual, ou seja, por meio de trocas. Além de conceder acesso a páginas limpas na SSD local, o que é mais rápido do que acessar o disco regular para buscar dados.

Essa técnica, nem de perto é tão rápida quanto ter memória suficiente, mas oferece um aumento modesto de capacidade quando a memória disponível está baixa. Leia mais sobre extensões de pool de buffers e analise alguns resultados de comparativos de mercado no site de Brent Ozar.

Próximos passos

Esta página foi útil? Conte sua opinião sobre:

Enviar comentários sobre…

Documentação do Compute Engine