Práticas recomendadas para instâncias do SQL Server

Você pode aplicar diversas práticas recomendadas para otimizar instâncias do Compute Engine que executam o Microsoft SQL Server. Para saber como configurar uma instância do SQL Server de alto desempenho, leia Criando uma instância do SQL Server de alto desempenho .

Configurando o Windows

Esta seção aborda tópicos de configuração sobre como otimizar o sistema operacional Microsoft Windows para desempenho do SQL Server durante a execução no Compute Engine.

Configurando 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 seu ambiente SQL Server para que ele possa se conectar ao banco de dados a partir 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 esta regra de firewall, é uma boa prática especificar o endereço IP das máquinas clientes. 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, observe que o caminho do parâmetro do program pode mudar dependendo da versão do SQL Server usada.

A imagem do aplicativo SQL Server inclui uma regra de firewall SQL Server Windows. Esta regra é bastante irrestrita, portanto considere desativá-la antes que seu sistema entre em produção.

Ajustando conexões de rede

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

As configurações de rede padrão na maioria dos sistemas operacionais são definidas para conexões em computadores pequenos conectados a redes moderadamente rápidas. Tais 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áquinas virtuais (VM) são anexadas a uma rede projetada pelo Google que oferece alta capacidade e desempenho. Os servidores físicos que executam as instâncias do Compute Engine são altamente otimizados para aproveitar essa capacidade de rede. Os drivers de rede virtual nas suas instâncias também são otimizados, o que torna os valores padrão suficientes para a maioria dos casos de uso.

Instalando antivírus

Prática recomendada: siga as orientações da Microsoft para software antivírus.

Se você estiver executando o Windows, deverá estar executando algum software antivírus. Malware e vírus de software apresentam um risco significativo para qualquer sistema conectado a uma rede, e o software antivírus é uma etapa simples de mitigação que você pode usar para proteger seus dados. No entanto, se o software antivírus não estiver configurado corretamente, poderá impactar negativamente o desempenho do seu banco de dados. A Microsoft fornece conselhos sobre como escolher software antivírus .

Otimizando para desempenho e estabilidade

Esta seção fornece informações sobre como otimizar o desempenho do SQL Server no Compute Engine e descreve atividades operacionais para ajudar a mantê-lo funcionando perfeitamente.

Movendo arquivos de dados e arquivos de log para um novo disco

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

Por padrão, a imagem pré-configurada para SQL Server vem com tudo instalado no disco permanente de inicialização, que é montado como a unidade `C:`. Considere anexar um disco permanente SSD secundário e mover os arquivos de log e de dados para o novo disco.

Usando um SSD local para melhorar IOPS

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

A natureza efêmera da tecnologia SSD local a torna uma má candidata para uso com bancos de dados críticos e arquivos importantes. No entanto, o arquivo tempdb e o arquivo de paginação do Windows são arquivos temporários, portanto, ambos são ótimos candidatos para migrar para um SSD local. Isso descarrega um número significativo de operações de E/S dos discos permanentes SSD. Para obter mais informações sobre como configurar isso, consulte Configurando o TempDB .

Processamento de consulta paralela

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

A configuração padrão recomendada para max degree of parallelism é combiná-la com o número de CPUs no servidor. No entanto, chega um ponto em que dividir uma consulta em 16 ou 32 partes, executá-los todos em vCPUs diferentes e depois consolidar tudo em um único resultado leva muito mais tempo do que se apenas uma vCPU tivesse executado a consulta. Na prática, 8 funciona como um bom valor padrão.

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

Essa configuração anda de mãos dadas com max degree of parallelism . Cada unidade representa uma combinação de trabalho de CPU e E/S necessário para executar uma consulta com um plano de execução serial antes de ser considerada para um plano de execução paralelo. O valor padrão é 5. Embora não forneçamos nenhuma recomendação específica para alterar o valor padrão, vale a pena ficar de olho e, se necessário, aumentá-lo gradativamente em 5 durante o teste de carga. Um indicador importante de que esse valor pode precisar ser aumentado é a presença de esperas CXPACKET . Embora a presença de esperas CXPACKET não indique necessariamente que essa configuração deva ser alterada, é um bom ponto de partida.

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

Bancos de dados individuais podem ter diferentes necessidades de paralelismo. Você pode definir essas configurações globalmente e definir Max DOP no nível do banco de dados individual. Você deve observar suas cargas de trabalho exclusivas, monitorar as esperas e, em seguida, ajustar os valores adequadamente.

O site SQLSkills oferece um guia de desempenho útil que cobre estatísticas de espera dentro do banco de dados. Seguir este guia pode ajudá-lo a entender o que está esperando e como mitigar os atrasos.

Tratamento de logs de transações

Prática recomendada: monitore o crescimento do log de transações em seu sistema. Considere desativar o crescimento automático e definir seu arquivo de log para um tamanho fixo, com base no acúmulo médio diário de logs.

Uma das fontes mais negligenciadas de perda de desempenho e lentidão intermitente é o crescimento descontrolado do log de transações. Quando seu banco de dados estiver configurado para usar o modelo de recuperação Full , você poderá realizar uma restauração para qualquer momento, mas seus logs de transações serão preenchidos mais rapidamente. Por padrão, quando o arquivo de log de transações 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 ele termine. O SQL Server aumenta cada arquivo de log com base no tamanho máximo do arquivo e na configuração de crescimento do arquivo .

Quando o arquivo atinge seu limite máximo de tamanho e não pode crescer, o sistema emite um erro 9002 e coloca o banco de dados no modo somente leitura. Se o arquivo puder crescer, o SQL Server expandirá o tamanho do arquivo e zerará o espaço vazio. A configuração para Crescimento de arquivo é padronizada como 10% do tamanho atual do arquivo de log. Esta não é uma boa configuração padrão para desempenho porque quanto maior o arquivo cresce, mais tempo leva para criar o novo espaço vazio.

Prática recomendada: agende backups regulares do log de transações.

Independentemente do tamanho máximo e das configurações de crescimento, agende backups regulares do log de transações , que, por padrão, truncam entradas de log antigas e permitem que o sistema reutilize o espaço de arquivo existente. Esta simples tarefa de manutenção pode ajudar a evitar quedas de desempenho em horários de pico de tráfego.

Otimizando Arquivos de Log Virtuais

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

O arquivo de log de transações físicas é segmentado em Virtual Log Files (VLF). Novos VLFs são criados sempre que o arquivo de log de transações físicas precisa crescer. Se você não desativou o crescimento automático e o crescimento estiver acontecendo com muita frequência, muitos VLFs serão criados. Essa atividade pode resultar na fragmentação do arquivo de log, 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 criar durante o crescimento automático. Geralmente, se o crescimento for inferior a 1/8 do tamanho do arquivo de log atual, o SQL Server cria um VLF nesse novo segmento. Anteriormente, seriam criados 8 VLFs para crescimento entre 64 MB e 1 GB e 16 VLFs para crescimento acima de 1 GB. Você pode usar o script TSQL abaixo para verificar quantos VLFs seu banco de dados possui atualmente. Se houver milhares de arquivos, considere reduzir e redimensionar manualmente o arquivo de log.

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

Você pode ler mais sobre VLFs no site de Brent Ozar .

Evitando a fragmentação do índice

Prática recomendada: desfragmente regularmente os índices nas tabelas mais modificadas.

Os índices nas suas tabelas podem ficar fragmentados, o que pode levar a um desempenho insatisfatório de quaisquer consultas que utilizem esses índices. Um cronograma de manutenção regular deve incluir a reorganização dos índices nas tabelas mais modificadas. Você pode executar o seguinte script Transact-SQL em seu banco de dados para mostrar os índices e sua porcentagem de fragmentação. Você pode ver nos 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 seus índices estão muito fragmentados, você pode reorganizá-los usando um script ALTER básico. Aqui está um exemplo de script que imprime as instruções ALTER que você pode executar para cada um dos índices de suas 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 que possuem a maior fragmentação e execute essas instruções de forma incremental. Considere agendar este ou um script semelhante como um de seus trabalhos de manutenção regulares.

Formatando 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 extensões . As extensões têm tamanho de 64 KB e são compostas de oito páginas de memória contíguas que também têm tamanho 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 procura 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

Fazendo backup

Prática recomendada: faça backup dos seus dados regularmente usando as soluções de backup e recuperação de desastres do Google para obter proteção ideal. Recomendamos fazer backup de seus dados pelo menos 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 contínuo eficiente com recuperação pontual real que ajuda a realizar backup em menos tempo do que os backups convencionais, ao mesmo tempo que reduz o impacto nos servidores de produção. Ele também reduz o consumo de largura de banda e de armazenamento para objetivos 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 baixo RTO.
  • 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.

Saber mais:

Monitoramento

Prática recomendada: use o Cloud Monitoring.

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

Ao usar recursos de coleta de dados , você pode ajustar as informações que deseja monitorar e enviá-las para o data warehouse de gerenciamento integrado . O data warehouse de gerenciamento pode ser executado no mesmo servidor que você está monitorando ou os dados podem ser transmitidos para outra instância do SQL Server que esteja executando o warehouse.

Dados de carregamento em massa

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

É provável que você precise carregar grandes quantidades de dados em seu sistema pelo menos uma vez, se não regularmente. Esta é uma operação que consome muitos recursos e você pode atingir o limite de IOPS do disco permanente ao fazer 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 de seus trabalhos em lote. A solução é criar um banco de dados completamente separado que use o modelo de recuperação Simple e, em seguida, usar 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. Você também pode colocar esse novo banco de dados em uma unidade SSD local, se tiver espaço suficiente. Usar um SSD local para o banco de dados de recuperação reduz o consumo de recursos de suas operações em massa e o tempo necessário para concluir os trabalhos. O benefício final é que sua tarefa de backup dos dados de produção não precisará fazer backup de todas as operações em massa no log de transações e, portanto, será menor e executada mais rapidamente.

Validando sua configuração

Prática recomendada: teste sua configuração para validar se ela funciona conforme o esperado.

Sempre que você configurar um novo sistema, planeje validar a configuração e executar alguns testes de desempenho. Este procedimento armazenado é um ótimo recurso para avaliar a configuração do SQL Server. Reserve algum tempo para ler sobre os sinalizadores de configuração e execute o procedimento.

Otimizando o SQL Server Enterprise Edition

O SQL Server Enterprise Edition possui uma longa lista de recursos adicionais em relação à Standard Edition. Se você estiver migrando uma licença existente paraGoogle Cloud, existem algumas opções de desempenho que você pode considerar implementar.

Usando tabelas compactadas

Prática recomendada: habilite a compactação de tabela e índice.

Pode parecer contra-intuitivo que a compactação de tabelas possa acelerar o desempenho do sistema, mas, na maioria dos casos, é isso que acontece. A desvantagem é usar uma pequena quantidade de ciclos de CPU para compactar os dados e eliminar a E/S de disco extra necessária para ler e gravar os blocos maiores. Geralmente, quanto menos E/S de disco seu sistema usar, melhor será seu desempenho. As instruções para estimar e ativar a compactação de tabelas e índices estão no site do MSDN .

Ativando a extensão do buffer pool

Melhor prática: Use a extensão do buffer pool para acelerar o acesso aos dados.

O buffer pool é onde o sistema armazena páginas limpas . Em termos simples, ele armazena cópias dos seus dados, refletindo sua aparência no disco. Quando os dados mudam na memória, isso é chamado de página suja . As páginas sujas devem ser descarregadas no disco para salvar as alterações. Quando seu banco de dados é maior que a memória disponível, isso pressiona o buffer pool e páginas limpas podem ser descartadas. Quando as páginas limpas são eliminadas, o sistema deve ler o disco na próxima vez que acessar os dados eliminados.

O recurso de extensão do buffer pool 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 troca , e dá acesso às páginas limpas no SSD local, o que é mais rápido do que ir ao disco normal para buscar os dados.

Essa técnica não é tão rápida quanto ter memória suficiente, mas pode proporcionar um aumento modesto no rendimento quando a memória disponível estiver baixa. Você pode ler mais sobre extensões de buffer pool e revisar alguns resultados de benchmarking no site de Brent Ozar .

Otimizando o licenciamento do SQL Server

Multithreading simultâneo (SMT)

Prática recomendada: definir o número de threads por núcleo como 1 para a maioria das cargas de trabalho do SQL Server

Multithreading simultâneo (SMT), comumente conhecido como Tecnologia Hyper-Threading (HTT) em processadores Intel, é um recurso que permite que um único núcleo da CPU seja compartilhado logicamente como dois threads. No Compute Engine, o SMT é ativado na maioria das VMs por padrão, o que significa que cada vCPU na VM é executada em um único thread e cada núcleo físico da CPU é compartilhado por duas vCPUs.

No Compute Engine, você pode configurar o número de threads por núcleo , o que efetivamente desativa o SMT. Quando o número de threads por núcleo é definido como 1, as vCPUs não compartilham núcleos físicos da CPU. Essa configuração impacta significativamente os custos de licenciamento do Windows Server e do SQL Server. Quando o número de threads por núcleo é definido como 1, o número de vCPUs em 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, configurar o número de threads por núcleo também afeta o desempenho da carga de trabalho. Os aplicativos escritos para serem multithread podem aproveitar esse recurso dividindo o trabalho de computação em partes menores paralelizáveis ​​que são agendadas em vários núcleos lógicos. Essa paralelização do trabalho geralmente aumenta o rendimento geral do sistema, utilizando melhor os recursos principais disponíveis. Por exemplo, quando um thread está paralisado, o outro thread pode utilizar o núcleo.

O impacto exato no desempenho do SMT no SQL Server depende das características da carga de trabalho e da plataforma de hardware usada, pois a implementação do SMT difere entre as gerações de hardware. Cargas de trabalho com um alto volume de pequenas transações, por exemplo, cargas de trabalho OLTP, muitas vezes podem aproveitar as vantagens do SMT e se beneficiar de um maior aumento de desempenho. Por outro lado, cargas de trabalho menos paralelizáveis, por exemplo, cargas de trabalho OLAP, beneficiam-se menos do SMT. Embora esses padrões tenham sido observados em geral, considere avaliar o impacto no desempenho do SMT por carga de trabalho para determinar o impacto da definição do número de threads por núcleo como 1.

A configuração mais econômica para a maioria das cargas de trabalho do SQL Server envolve definir o número de threads por núcleo como 1. Qualquer queda no desempenho pode ser compensada pela utilização de uma VM maior. Na maioria dos casos, a redução de 50% no custo de licenciamento é maior que o aumento do custo 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árias 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

Após seguir as etapas para desabilitar o SMT no SQL Server a nova configuração é:

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

Agora que apenas 8 núcleos estão visíveis no sistema operacional, o servidor requer apenas 8 vCPUs para execução do Windows Server e do SQL Server.

O que vem a seguir