Práticas recomendadas para instâncias do SQL Server

Pode aplicar 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 do SQL Server de alto desempenho, leia o artigo Criar uma instância do SQL Server de alto desempenho.

Use o Workload Manager para avaliar e implementar o SQL Server

A avaliação do SQL Server no Workload Manager permite-lhe analisar as suas implementações do SQL Server com um conjunto de recomendações Trusted Cloud by S3NS predefinidas para um desempenho ideal diretamente a partir da Trusted Cloud consola. Para mais informações, consulte as instruções de configuração do agente para o SQL Server.

A ferramenta Automatização da implementação guiada no Workload Manager permite-lhe configurar e implementar aplicações empresariais no Trusted Cloud by S3NS. Também pode usar a automatização da implementação guiada para configurar uma implementação para a sua carga de trabalho e, em seguida, gerar infraestrutura como código (IaC) do Terraform e Ansible que pode exportar para uma maior personalização ou usar num pipeline de implementação existente. Para mais informações, consulte o artigo Automatização da implementação guiada.

Configurar o Windows

Esta secção aborda tópicos de configuração sobre como otimizar o sistema operativo Microsoft Windows para o desempenho do SQL Server quando executado no Compute Engine.

Configurar a firewall do Windows

Prática recomendada: use a Firewall avançada do Windows Server e especifique os endereços IP dos computadores cliente.

A Firewall avançada do Windows é um componente de segurança importante no Windows Server. Quando configurar o ambiente do SQL Server para que possa estabelecer ligação à base de dados a partir de outros computadores cliente, configure a 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

Quando usa esta regra de firewall, é uma boa prática especificar o endereço IP dos computadores cliente. Especifique uma lista de endereços IP delimitada por vírgulas sem espaços em branco para o parâmetro remoteip em vez de LOCAL_SUBNET. Tenha também em atenção que o caminho do parâmetro program pode mudar consoante a versão do SQL Server que usa.

A imagem da aplicação SQL Server inclui uma SQL Serverregra de firewall do Windows. Esta regra é relativamente sem restrições, por isso, considere desativá-la antes de o seu sistema entrar em produção.

Ajustar as ligações de rede

Prática recomendada: use as predefinições de rede do sistema operativo.

As predefinições de rede na maioria dos sistemas operativos estão configuradas para ligações em computadores pequenos que estão ligados a redes moderadamente rápidas. Normalmente, estas definições são suficientes. Além disso, as predefinições conservadoras garantem que o tráfego de rede não sobrecarrega a rede nem os computadores ligados.

No Compute Engine, as instâncias de máquinas virtuais (VMs) estão associadas a uma rede concebida pela Google que oferece elevada capacidade e desempenho. Os servidores físicos que executam as suas instâncias do Compute Engine estão altamente otimizados para tirar partido desta capacidade de rede. Os controladores de rede virtual nas suas instâncias também são otimizados, o que torna os valores predefinidos suficientes para a maioria dos exemplos de utilização.

Instalar antivírus

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

Se estiver a usar o Windows, deve ter algum software antivírus em execução. O software malicioso e os vírus de software representam um risco significativo para qualquer sistema ligado a uma rede, e o software antivírus é um passo de mitigação simples que pode usar para proteger os seus dados. No entanto, se o software antivírus não estiver configurado corretamente, pode afetar negativamente o desempenho da base de dados. A Microsoft oferece aconselhamento sobre como escolher software antivírus.

Otimizar o desempenho e a estabilidade

Esta secção fornece informações sobre como otimizar o desempenho do SQL Server no Compute Engine e descreve as atividades operacionais para ajudar a mantê-lo em funcionamento sem problemas.

Mover ficheiros de dados e ficheiros de registo para um novo disco

Prática recomendada: use um disco persistente SSD separado para ficheiros de registo e de dados.

Por predefinição, a imagem pré-configurada para o SQL Server é fornecida com tudo instalado no disco persistente de arranque, que é montado como a unidade "C:". Considere anexar um disco persistente SSD secundário e mover os ficheiros de registo e os ficheiros de dados para o novo disco.

Usar um SSD local para melhorar as IOPS

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

A natureza efémera da tecnologia SSD local torna-a uma má candidata para utilização com as suas bases de dados críticas e ficheiros importantes. No entanto, o ficheiro de paginação do tempdb e do Windows são ficheiros temporários, pelo que ambos são ótimos candidatos para mover para um SSD local. Isto transfere um número significativo de operações de I/O dos seus discos persistentes de SSD. Para mais informações sobre como configurar esta opção, consulte o artigo Configurar a TempDB.

Processamento de consultas paralelas

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

A predefinição recomendada para max degree of parallelism é igualá-la ao número de CPUs no servidor. No entanto, há um ponto em que dividir uma consulta em 16 ou 32 partes, executá-las todas em vCPUs diferentes e, em seguida, consolidá-las novamente num único resultado demora muito mais tempo do que se apenas uma vCPU tivesse executado a consulta. Na prática, 8 funciona como um bom valor predefinido.

Prática recomendada: monitorize as CXPACKETesperas e aumente-as gradualmentecost threshold for parallelism.

Esta definição está intimamente relacionada com a funcionalidade max degree of parallelism. Cada unidade representa uma combinação de trabalho de CPU e de E/S necessário para executar uma consulta com um plano de execução em série antes de ser considerada para um plano de execução em paralelo. O valor predefinido é 5. Embora não façamos nenhuma recomendação específica para alterar o valor predefinido, vale a pena manter-se atento e, se necessário, aumentá-lo gradualmente em 5 durante os testes de carga. Um indicador fundamental de que este valor pode ter de ser aumentado é a presença de CXPACKET esperas. Embora a presença de esperas do CXPACKET não indique necessariamente que esta definição deve ser alterada, é um bom ponto de partida.

Prática recomendada: monitorize diferentes tipos de espera e ajuste as definições de processamento paralelo globais ou defina-as ao nível da base de dados individual.

As bases de dados individuais podem ter necessidades de paralelismo diferentes. Pode definir estas definições globalmente e definir Max DOP ao nível da base de dados individual. Deve observar as suas cargas de trabalho únicas, monitorizar as esperas e, em seguida, ajustar os valores em conformidade.

O site SQLSkills oferece um guia de desempenho útil que abrange estatísticas de espera na base de dados. Seguir este guia pode ajudar a compreender o que está em espera e como mitigar os atrasos.

Processamento de registos de transações

Prática recomendada: monitorize o crescimento do registo de transações no seu sistema. Considere desativar o crescimento automático e definir o ficheiro de registo para um tamanho fixo, com base na acumulação média diária de registos.

Uma das fontes mais negligenciadas de perda de desempenho e abrandamentos intermitentes é o crescimento não gerido do registo de transações. Quando a base de dados está configurada para usar o modelo de recuperação Full, pode fazer um restauro para qualquer ponto no tempo, mas os registos de transações são preenchidos mais rapidamente. Por predefinição, quando o ficheiro de registo de transações está cheio, o SQL Server aumenta o tamanho do ficheiro para adicionar mais espaço vazio para escrever mais transações e bloqueia toda a atividade na base de dados até terminar. O SQL Server aumenta cada ficheiro de registo com base no respetivo tamanho máximo do ficheiro e na definição de aumento do ficheiro.

Quando o ficheiro atinge o limite máximo de tamanho e não pode aumentar, o sistema emite um erro 9002 e coloca a base de dados no modo só de leitura. Se o ficheiro puder aumentar, o SQL Server expande o tamanho do ficheiro e preenche o espaço vazio com zeros. A predefinição da definição File Growth é 10% do tamanho atual do ficheiro de registo. Esta não é uma boa definição predefinida para o desempenho, porque quanto maior for o ficheiro, mais tempo demora a criar o novo espaço vazio.

Prática recomendada: agende cópias de segurança regulares do registo de transações.

Independentemente das definições de tamanho máximo e crescimento, agende regularmente cópias de segurança do registo de transações, que, por predefinição, truncam as entradas de registo antigas e permitem que o sistema reutilize o espaço de ficheiros existente. Esta simples tarefa de manutenção pode ajudar a evitar diminuições no desempenho durante os períodos de pico de tráfego.

Otimizar ficheiros de registo virtuais

Prática recomendada: monitorize o crescimento do ficheiro de registo virtual e tome medidas para evitar a fragmentação do ficheiro de registo.

O ficheiro de registo de transações físico é segmentado em ficheiros de registo virtuais (VLF). Os VLFs são criados sempre que o ficheiro de registo de transações físico tem de aumentar. Se não desativou o crescimento automático e o crescimento ocorre com demasiada frequência, são criados demasiados FVLs. Esta atividade pode resultar na fragmentação do ficheiro de registo, 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 ficheiro de registo atual, o SQL Server cria um VLF nesse novo segmento. Anteriormente, criava 8 VLFs para o crescimento entre 64 MB e 1 GB e 16 VLFs para o crescimento superior a 1 GB. Pode usar o script TSQL abaixo para verificar quantos VLFs a sua base de dados tem atualmente. Se tiver milhares de ficheiros, considere reduzir e redimensionar manualmente o ficheiro de registo.

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

Pode ler mais sobre os VLFs no Website de Brent Ozar.

Evitar 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 mau desempenho de quaisquer consultas que usem estes índices. Uma agenda de manutenção regular deve incluir a reorganização dos índices nas tabelas mais modificadas. Pode executar o seguinte script Transact-SQL para a sua base de dados para mostrar os índices e a respetiva percentagem de fragmentação. Pode ver nos resultados de exemplo que o índice PK_STOCK está fragmentado em 95%. Na seguinte declaração "SELECT", substitua "YOUR_DB" pelo nome da sua base 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 seus índices estão demasiado fragmentados, pode reorganizá-los através de um script ALTER básico. Segue-se um script de exemplo que imprime as declarações que pode executar para cada um dos índices das suas tabelas:ALTER

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 têm a fragmentação mais elevada e, em seguida, execute essas declarações de forma incremental. Considere agendar este ou um script semelhante como uma das suas tarefas de manutenção regulares.

Formatar discos secundários

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

O SQL Server armazena dados em unidades de armazenamento denominadas extensões. Os intervalos têm 64 KB e são compostos por oito páginas de memória contíguas que também têm 8 KB. A formatação de um disco com uma unidade de alocação de 64 KB permite que o SQL Server leia e escreva extensões de forma mais eficiente, 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 num 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

A fazer uma cópia de segurança

Prática recomendada: faça regularmente uma cópia de segurança dos seus dados através das soluções de cópia de segurança e recuperação de desastres da Google para uma proteção ideal. Recomendamos que faça uma cópia de segurança dos seus dados, pelo menos, uma vez por dia.

As soluções de cópia de segurança e recuperação de desastres da Google oferecem as seguintes vantagens para o Microsoft SQL Server:

  • Cópia de segurança incremental eficiente para sempre com recuperação real num determinado momento que ajuda a fazer cópias de segurança em menos tempo do que as cópias de segurança convencionais, ao mesmo tempo que reduz o impacto nos servidores de produção. Também reduz o consumo de largura de banda e armazenamento para um objetivo de ponto de recuperação (OPR) e um custo total de propriedade (TCO) baixos.
  • Montagem e migração de recuperações (M&M) para cópias de segurança armazenadas no Cloud Storage para um RTO baixo.
  • Integração abrangente com as capacidades do SQL Server, incluindo suporte para clusters de grupos de disponibilidade do SQL Server e várias opções de recuperação em vários cenários.
  • Painel de gestão central, incluindo capacidades de monitorização, alertas e relatórios dedicadas para todas as suas cópias de segurança.

Saiba mais:

Monitorização

Prática recomendada: use o Cloud Monitoring.

Pode instalar o agente do Cloud Monitoring para Microsoft Windows para enviar vários pontos de dados de monitorização para o sistema Cloud Monitoring.

Ao usar as capacidades de recolha de dados, pode ajustar as informações que quer monitorizar e enviá-las para o armazém de dados de gestão incorporado. O data warehouse de gestão pode ser executado no mesmo servidor que está a monitorizar ou os dados podem ser transmitidos para outra instância do SQL Server que execute o data warehouse.

Carregamento de dados em massa

Prática recomendada: use uma base de dados separada para preparar e transformar dados em massa antes de os mover para servidores de produção.

É provável que tenha de carregar grandes quantidades de dados no seu sistema, pelo menos, uma vez, se não for regularmente. Esta é uma operação que requer muitos recursos e pode atingir o limite de IOPS do disco persistente quando faz carregamentos em massa.

Existe uma forma fácil de reduzir o I/O do disco e o consumo da CPU das operações de carregamento em massa, com a vantagem adicional de acelerar o tempo de execução dos seus trabalhos em lote. A solução consiste em criar uma base de dados completamente separada que use o Simplemodelo de recuperação e, em seguida, usar essa base de dados para preparar e transformar o conjunto de dados em massa antes de o inserir na base de dados de produção. Também pode colocar esta nova base de dados num disco SSD local, se tiver espaço suficiente. A utilização de um SSD local para a base de dados de recuperação reduz o consumo de recursos das suas operações em massa e o tempo necessário para concluir as tarefas. A vantagem final é que a tarefa de cópia de segurança dos dados de produção não tem de fazer uma cópia de segurança de todas essas operações em massa no registo de transações e, por isso, será mais pequena e executada mais rapidamente.

A validar a configuração

Prática recomendada: teste a configuração para validar se tem o desempenho esperado.

Sempre que configurar um novo sistema, deve planear validar a configuração e executar alguns testes de desempenho. Este procedimento armazenado é um excelente recurso para avaliar a configuração do SQL Server. Dedique algum tempo mais tarde a ler acerca das flags de configuração e execute o procedimento.

Otimizar o SQL Server Enterprise Edition

O SQL Server Enterprise Edition tem uma longa lista de capacidades adicionadas em comparação com o Standard Edition. Se estiver a migrar uma licença existente para o Trusted Cloud, existem algumas opções de desempenho que pode considerar implementar.

Usar tabelas comprimidas

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

Pode parecer pouco intuitivo que a compressão de tabelas possa fazer com que o seu sistema tenha um desempenho mais rápido, mas, na maioria dos casos, é isso que acontece. A contrapartida é usar uma pequena quantidade de ciclos da CPU para comprimir os dados e eliminar a E/S de disco adicional necessária para ler e escrever os blocos maiores. Geralmente, quanto menos E/S de disco o seu sistema usar, melhor é o respetivo desempenho. As instruções para estimar e ativar a compressão de tabelas e índices estão no Website MSDN.

Ativar a extensão do conjunto de buffers

Prática recomendada: use a extensão do conjunto de buffers para acelerar o acesso aos dados.

O conjunto de buffers é onde o sistema armazena páginas limpas. Em termos simples, armazena cópias dos seus dados, espelhando o aspeto que têm no disco. Quando os dados mudam na memória, chama-se página suja. As páginas sujas têm de ser descarregadas para o disco para guardar as alterações. Quando a base de dados é maior do que a memória disponível, isso exerce pressão sobre o conjunto de buffers, e as páginas limpas podem ser eliminadas. Quando as páginas limpas são ignoradas, o sistema tem de ler a partir do disco da próxima vez que aceder aos dados ignorados.

A funcionalidade de extensão do conjunto de buffers permite enviar páginas limpas para um SSD local, em vez de as rejeitar. Isto funciona de forma semelhante à memória virtual, ou seja, através da troca, e dá-lhe acesso às páginas limpas no SSD local, que é mais rápido do que aceder ao disco normal para obter os dados.

Esta técnica não é tão rápida como ter memória suficiente, mas pode dar-lhe um aumento modesto no débito quando a memória disponível é baixa. Pode ler mais acerca das extensões do conjunto de buffers e rever alguns resultados de testes de referência no site de Brent Ozar.

Otimizar o licenciamento do SQL Server

Multithreading simultâneo (SMT)

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

O multithreading simultâneo (SMT), conhecido como tecnologia Hyper-Threading (HTT) nos processadores Intel, é uma funcionalidade que permite que um único núcleo da CPU seja partilhado logicamente como dois threads. No Compute Engine, a SMT está ativada na maioria das VMs por predefinição, o que significa que cada vCPU na VM é executada num único segmento e cada núcleo da CPU física é partilhado por duas vCPUs.

No Compute Engine, pode configurar o número de threads por núcleo, o que desativa efetivamente a SMT. Quando o número de threads por núcleo está definido como 1, as vCPUs não partilham núcleos de CPU físicos. Esta configuração afeta significativamente os custos de licenciamento do Windows Server e do SQL Server. Quando o número de threads por núcleo está definido como 1, o número de vCPUs numa VM é reduzido para metade, o que também reduz para metade o número de licenças do Windows Server e do SQL Server necessárias. Isto pode diminuir significativamente o custo total da carga de trabalho.

No entanto, a configuração do número de threads por núcleo também afeta o desempenho da carga de trabalho. As aplicações escritas para serem multithread podem tirar partido desta funcionalidade dividindo o trabalho de computação em partes paralelizadas mais pequenas que são agendadas em vários núcleos lógicos. Esta paralelização do trabalho aumenta frequentemente o débito geral do sistema através de uma melhor utilização dos recursos de processadores disponíveis. Por exemplo, quando um processo está parado, o outro processo pode usar 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, uma vez que a implementação da SMT difere entre as gerações de hardware. As cargas de trabalho com um volume elevado de pequenas transações, por exemplo, cargas de trabalho OLTP, podem tirar partido da SMT e beneficiar de um aumento de desempenho maior. Por outro lado, as cargas de trabalho menos paralelizadas, por exemplo, as cargas de trabalho OLAP, beneficiam menos da SMT. Embora estes padrões tenham sido observados geralmente, considere avaliar o impacto no desempenho da SMT com base em cada carga de trabalho para determinar o impacto da definição do número de threads por núcleo como 1.

A configuração mais rentável para a maioria das cargas de trabalho do SQL Server envolve definir o número de threads por núcleo como 1. Qualquer diminuição do desempenho pode ser compensada com a utilização de uma VM maior. Na maioria dos casos, a diminuição de 50% no custo de licenciamento é superior ao aumento do custo da VM maior.

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

Por predefinição, o número de núcleos visíveis no sistema operativo é 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

Depois de seguir os passos para desativar a 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 operativo, o servidor só precisa de 8 vCPUs para o Windows Server e o SQL Server serem executados.

O que se segue?