Práticas recomendadas para instâncias do SQL Server

Siga várias práticas recomendadas para otimizar as instâncias do 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.

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.

Como configurar o firewall do Windows

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

O firewall avançado do Windows é um componente de segurança importante no Windows Server. Ao configurar o ambiente do SQL Server para se conectar ao banco de dados de outras máquinas cliente, defina o firewall para permitir o 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
    

Ao usar essa regra de firewall, é uma boa prática especificar o endereço IP das suas máquinas 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, o caminho do parâmetro program pode mudar dependendo da versão do SQL Server usada.

A imagem do aplicativo SQL Server inclui uma regra de firewall SQL Server do Windows. Essa regra é muito ampla, então, convém desativá-la antes do 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 pequenos 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. O software antivírus é uma etapa simples de prevenção que pode ser usada para proteger seus dados. No entanto, se o software antivírus não estiver configurado corretamente, ele poderá afetar 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 ajudar a mantê-lo funcionando de modo consistente.

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 de 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 faz com que ela seja uma opção inadequada para uso em bancos de dados essenciais e arquivos importantes. Porém, 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 permanentes SSD. 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 é correspondê-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 dessas partes em diferentes vCPUs e a consolidação delas em um único resultado levam muito mais tempo do que se apenas uma vCPU executasse a consulta. Na prática, o número 8 funciona como um bom valor padrão.

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

Essa configuração é compatível com 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. Não há recomendação específica para alterar esse valor, mas convém observá-lo e, se necessário, aumentá-lo de maneira incremental em cinco durante o teste de carga. Um indicador-chave de que esse valor pode precisar ser aumentado é a presença de esperas de CXPACKET. A presença de esperas de CXPACKET não indica, necessariamente, que essa configuração precisa mudar, mas é um excelente ponto de partida.

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

Bancos de dados individuais têm diferentes necessidades de paralelismo. É possível definir essas configurações globalmente e definir Max DOP no nível do banco de dados individual. Observe as cargas de trabalho únicas, monitore as esperas e ajuste os valores.

No site SQLSkills (em inglês), há um guia útil sobre desempenho que abrange as estatísticas de espera dentro do banco de dados. Para entender a espera e como minimizar os atrasos, siga esse guia.

Como 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 registro diária.

Uma das fontes mais negligenciadas de perda de desempenho e lentidão intermitente é o crescimento não gerenciado de registros de transações. Quando o banco de dados é configurado para usar o modelo de recuperação Full, é possível restaurar o banco a qualquer momento. No entanto, os registros de transação são preenchidos com maior rapidez. 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 no modo somente leitura. Se o arquivo crescer, o SQL Server irá expandir o tamanho do arquivo e zerar 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, truncam entradas de registro antigas e fazem com que o sistema reutilize o espaço do arquivo existente. Essa tarefa de manutenção simples pode ajudar 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 pode resultar na fragmentação do arquivo de registros, que é semelhante à fragmentação do disco e pode afetar negativamente o desempenho.

O SQL Server 2014 introduziu um algoritmo mais 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 <YOUR_DB>
    DECLARE @vlf_count INT
    DBCC LOGINFO
    SET @vlf_count = @@ROWCOUNT
    SELECT VLFs = @vlf_count
    

Leia mais sobre VLFs no site de Brent Ozar (em inglês).

Evitar fragmentação de índice

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

Os índices nas tabelas ficam fragmentados, o que pode levar a 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. Veja nos resultados do exemplo que o índice PK_STOCK está 95% fragmentado. Na instrução "SELECT" a seguir, 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 de maneira incremental. Programe esse script ou um semelhante como um de seus jobs de manutenção regular.

Como formatar discos secundários

Prática recomendada: formatar discos secundários com uma unidade de alocação de 64 KB.

O SQL Server armazena dados em unidades de armazenamento chamadas de extensões. As extensões têm 64 KB e são compostas por oito páginas de memória contíguas que têm 8 KB. Formatar um disco com a unidade de alocação de 64 KB permite que o SQL Server leia e grave extensões com mais eficiência, o que aumenta o desempenho de E/S do disco.

Para formatar discos secundários com uma unidade de locação de 64 KB, execute o seguinte comando do PowerShell, que pesquisa todos os discos novos e não inicializados em um sistema e formata os discos com a unidade de alocação de 64 KB:

    Get-Disk | Where-Object {$_.PartitionStyle -eq 'RAW'} | Initialize-Disk -PartitionStyle GPT -PassThru | New-Partition -AssignDriveLetter -UseMaximumSize | Format-Volume -FileSystem NTFS -AllocationUnitSize 65536 -Confirm:$FALSE
    

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, tenha 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 do Stackdriver Monitoring para Microsoft Windows para enviar vários pontos de dados de monitoramento para o sistema do Stackdriver Monitoring.

Use os recursos de coleta de dados para ajustar as informações que você quer monitorar e enviá-las para o armazenamento de dados de gerenciamento integrado. O armazenamento de dados de gerenciamento pode ser executado no mesmo servidor que você está monitorando ou o streaming de dados pode ser feito para outra instância do SQL Server que esteja executando o armazenamento.

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á que carregar grandes volumes de dados no seu 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 a E/S de disco e o consumo de CPU de operações de carregamento em massa, com o benefício adicional de acelerar o tempo de execução dos seus jobs em lote. A solução aqui é criar um banco de dados completamente separado que use o modelo de recuperação Simple e usar esse banco de dados para preparar e transformar o conjunto de dados em lote antes de inseri-lo no banco de dados de produção. É possível também colocar esse novo banco de dados em uma unidade SSD local, se houver espaço suficiente. Isso reduz o consumo de recursos das suas operações em massa, bem como o tempo de conclusão dos jobs. O benefício final é que o job de backup dos dados de produção não terá que 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. Reserve um tempo depois para ler sobre as sinalizações de configuração e executar o procedimento.

Como otimizar o SQL Server Enterprise Edition

O SQL Server Enterprise Edition tem uma longa lista de recursos extra em comparação com a Standard Edition. Se você estiver migrando de uma licença atual para o GCP, há algumas opções de desempenho que podem ser implementadas.

Como usar tabelas compactadas

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

O fato de as tabelas de compactação agilizarem o desempenho do sistema pode não parecer lógico, mas, na maioria dos casos, é isso que acontece. A compensação está usando uma pequena quantidade de ciclos de CPU para compactar dados e eliminar a E/S de disco extra necessária para ler e gravar blocos maiores. Geralmente, quanto menos E/S de disco for usada pelo sistema, melhor será o desempenho. Veja instruções de como calcular e ativar a compactação em tabelas e índices no site da MSDN.

Como ativar a extensão de pool de buffer

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, ele armazena 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, o que precisa ser liberado 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 podem ser 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 precisarem ser 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 e também concede acesso a páginas limpas na SSD local, o que é mais rápido do que acessar o disco regular para buscar os dados.

Essa técnica nem de perto é tão rápida quanto ter memória suficiente, mas oferece um pequeno aumento 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.

A seguir