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.

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.

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 clientes.

O firewall avançado do Windows é um componente de segurança importante no Windows Server. Ao configurar o ambiente do SQL Server para que ele possa se conectar ao banco de dados de outras máquinas clientes, configure 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 no lugar de LOCAL_SUBNET. Além disso, o caminho do parâmetro program pode mudar dependendo da versão do SQL Server que você usa.

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 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 de máquina virtual (VM) são anexadas 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 sobre 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 estiver configurado corretamente, é possível que ele impacte negativamente o desempenho do banco de dados. A Microsoft fornece recomendações para escolha de software antivírus.

Como otimizar o desempenho e a estabilidade

Nesta seção, confira informações sobre como otimizar o desempenho do SQL Server no Compute Engine e veja as atividades operacionais que ajudarão a mantê-lo funcionando sem problemas.

Como mover arquivos de dados e de registros para um novo disco

Prática recomendada: use um disco permanente SSD separado 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, montado como a unidade "C:". Convém anexar um disco permanente secundário de SSD e mover os arquivos de registros e dados para o novo disco.

Como 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. Para mais informações sobre configuração, consulte Como configurar o TempDB.

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 em diferentes vCPUs 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 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 é 5. 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 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 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 realize as configurações 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 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.

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 registo 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 para qualquer momento. No entanto, os registros de transação são preenchidos rapidamente. 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 para 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. Por padrão, a configuração de crescimento do arquivo é definida como 10% do tamanho atual do arquivo de registros. Em termos de desempenho, essa não é a configuração ideal, porque quanto mais o arquivo cresce, mais tempo leva para um novo espaço vazio ser criado.

Prática recomendada: programe backups regulares dos registros de transação.

Independentemente das configurações de crescimento e tamanho máximo, programe backups regulares do registro de transação (em inglês). Por padrão, esses backups truncam entradas de registro antigas e permitem 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 dos arquivos de registros virtuais e tome medidas para evitar a fragmentação deles.

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. Anteriormente, isso criaria 8 VLFs para crescimento entre 64 MB e 1 GB, além de 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.

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 Transact-SQL a seguir no banco de dados para que ele exiba os índices com a porcentagem de fragmentação. Consulte os 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 usando um script ALTER básico. Veja 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.

Como formatar discos secundários

Prática recomendada: formate 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 de oito páginas de memória contíguas de 8 KB. A formatação de um disco com uma 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 alocaçã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

Como fazer backup

Prática recomendada: faça backup dos seus dados regularmente usando as soluções de backup e de recuperação de desastres do Google para uma proteção ideal. Recomendamos fazer backup dos dados uma vez por dia.

As soluções de backup e recuperação de desastres do Google oferecem os seguintes benefícios para o Microsoft SQL Server:

  • Backup incremental permanente eficiente com recuperação pontual real que ajuda a realizar o backup em menos tempo do que os backups convencionais, ao mesmo tempo que reduz o impacto nos servidores de produção. Além disso, reduz o consumo de largura de banda e de armazenamento. para um objetivo de ponto de recuperação (RPO) e custo total de propriedade (TCO) baixos.
  • Monte e migre recuperações (M&M) para backups armazenados no Cloud Storage para ter um RTO baixo.
  • Integração abrangente com recursos do SQL Server, incluindo suporte para clusters de grupos de disponibilidade do SQL Server e diversas opções de recuperação em vários cenários.
  • Painel central de gerenciamento, incluindo recursos dedicados de monitoramento, alertas e relatórios para todos os seus backups.

Saiba mais:

Monitoramento

Prática recomendada: use o Cloud Monitoring.

É possível instalar o agente do Cloud Monitoring para Microsoft Windows para enviar vários pontos de dados de monitoramento ao sistema do Cloud Monitoring.

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 (links em inglês). É 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 a E/S de disco e consumo de CPU de operações de carregamento em massa, com o benefício adicional de acelerar o ambiente de execução dos seus jobs em lote. A solução é criar um banco de dados completamente separado que use o modelo de recuperação Simple e, depois, usar o 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. O uso de uma SSD local para o banco de dados de recuperação reduz o consumo de recursos das suas operações em massa e o tempo necessário para concluir os jobs. A vantagem final é que seu job de backup para os dados de produção não precisará fazer backup de todas essas operações em massa nos registros de transação. Portanto, ele será menor e mais rápido.

Como validar sua configuração

Prática recomendada: teste sua configuração para validar 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.

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 uma licença atual para o Google Cloud, considere implementar algumas opções de desempenho.

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 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á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 estimar e ativar compactação em tabelas e índices no site da MSDN (em inglês).

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, 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. Essas páginas precisam ser liberadas 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 buffer e as páginas limpas podem ser descartadas. Quando as páginas limpas forem descartadas, o sistema precisará ler o disco na próxima vez que os dados descartados forem acessados.

O recurso de extensão do pool de buffers permite enviar páginas limpas para um SSD local, em vez de descartá-las. Isso funciona da mesma forma que a memória virtual, ou seja, por meio de trocas. Além disso, esse processo concede acesso às páginas limpas do 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 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.

Como otimizar o licenciamento do SQL Server

Multissegmentação simultânea (SMT)

Prática recomendada: defina o número de linhas de execução por núcleo como 1 para a maioria das cargas de trabalho do SQL Server.

A multissegmentação simultânea (SMT), conhecida como Hyper-Threading Technology (HTT) de processadores Intel, é um recurso que permite que um único núcleo de CPU seja compartilhado de maneira lógica como duas linhas de execução. No Compute Engine, a SMT fica ativada na maioria das VMs por padrão, o que significa que cada vCPU da VM é executada em uma única linha de execução e cada núcleo físico da CPU é compartilhado por duas vCPUs.

No Compute Engine, é possível configurar o número de linhas de execução por núcleo, o que desativa o SMT. Quando o número de linhas de execução por núcleo é definido como 1, as vCPUs não compartilham os núcleos da CPU física. Essa configuração afeta significativamente os custos de licenciamento do Windows Server e do SQL Server. Quando o número de linhas de execução por núcleo é definido como 1, o número de vCPUs de uma VM é reduzido pela metade, o que também reduz pela metade o número de licenças necessárias do Windows Server e do SQL Server. Isso pode diminuir significativamente o custo total da carga de trabalho.

No entanto, a configuração do número de linhas de execução por núcleo também afeta o desempenho da carga de trabalho. Os aplicativos criados para serem multissegmentados podem aproveitar esse recurso dividindo o trabalho de computação em blocos menores carregáveis em paralelo, programados em vários núcleos lógicos. Esse paralelismo de trabalho geralmente aumenta a capacidade geral do sistema utilizando melhor os recursos principais disponíveis. Por exemplo, quando uma linha de execução é interrompida, a outra pode utilizar o núcleo.

O impacto exato no desempenho da SMT no SQL Server depende das características da carga de trabalho e da plataforma de hardware usada, já que a implementação da SMT é diferente entre as gerações de hardware. As cargas de trabalho com um alto volume de pequenas transações, como cargas de trabalho de OLTP, costumam aproveitar o SMT e se beneficiar de um aumento maior de desempenho. Em contrapartida, cargas de trabalho com menos carregamento em paralelo, como cargas de trabalho OLAP, se beneficiam menos do SMT. Embora esses padrões tenham sido observados de modo geral, avalie o impacto no desempenho da SMT por carga de trabalho para determinar o impacto de definir o número de linhas de execução por núcleo como 1.

A configuração mais econômica para a maioria das cargas de trabalho do SQL Server é definir o número de linhas de execução por núcleo como 1. Qualquer queda de desempenho pode ser compensada usando uma VM maior. Na maioria dos casos, a redução de 50% do custo de licenciamento é mais cara do que o custo aumentado da VM maior.

Exemplo: considere que um SQL Server está implantado na configuração n2-standard-16

Por padrão, o número de núcleos visíveis no sistema operacional é 16, o que significa que são necessários 16 vCPUs do Windows Server e 16 vCPUs de licenças do SQL Server para executar o servidor.

PS C:\> Get-WmiObject -Class Win32_processor | Select-Object NumberOfCores, @{Name="Thread(s) per core";Expression={$_.NumberOfLogicalProcessors/$_.NumberOfCores}}

NumberOfCores Thread(s) per core
------------- ------------------
            8                  2

Depois de seguir as etapas para desativar a SMT no SQL Server, a nova configuração será:

PS C:\> Get-WmiObject -Class Win32_processor | Select-Object NumberOfCores, @{Name="Thread(s) per core";Expression={$_.NumberOfLogicalProcessors/$_.NumberOfCores}}

NumberOfCores Thread(s) per core
------------- ------------------
            8                  1

Como apenas oito núcleos ficam visíveis no sistema operacional, o servidor precisa de apenas 8 vCPUs do Windows Server e do SQL Server para ser executado.

A seguir