Práticas recomendadas do SQL Server para otimizar o desempenho no Azure Stack Hub

Este artigo fornece as práticas recomendadas do SQL Server para otimizar o SQL Server e melhorar o desempenho em VMs (máquinas virtuais) do Microsoft Azure Stack Hub. Ao executar o SQL Server em VMs do Azure Stack Hub, use as mesmas opções de ajuste de desempenho do banco de dados aplicáveis ao SQL Server em um ambiente de servidor local. O desempenho de um banco de dados relacional em uma nuvem do Azure Stack Hub depende de muitos fatores, incluindo o tamanho da família de uma VM e a configuração dos discos de dados.

Ao criar imagens do SQL Server, considere provisionar suas VMs no portal do Azure Stack Hub. Baixe a Extensão de IaaS do SQL do Gerenciamento do Marketplace no portal do administrador do Azure Stack Hub e baixe suas imagens de VM do SQL Server de sua escolha. Isso inclui SQL Server 2016 SP1, SQL Server 2016 SP2 e SQL Server 2017.

Observação

Embora o artigo descreva como provisionar uma VM do SQL Server usando o portal global do Azure, as diretrizes também se aplicam ao Azure Stack Hub com as seguintes diferenças: O SSD não está disponível para o disco do sistema operacional e há pequenas diferenças na configuração de armazenamento.

Nas imagens de VM, para SQL Server, você só pode usar BYOL (traga sua própria licença). Para o Windows Server, o modelo de licença padrão é o pagamento conforme o uso (PAYG). Para obter informações detalhadas sobre o modelo de licença do Windows Server na VM, consulte o artigo Perguntas frequentes sobre o Windows Server no Azure Stack Hub Marketplace.

Obter o melhor desempenho para o SQL Server em VMs do Azure Stack Hub é o foco deste artigo. Se a sua carga de trabalho tem menos demanda, talvez você não precise realizar todas as otimizações recomendadas. Considere suas necessidades de desempenho e padrões de carga de trabalho ao avaliar essas recomendações.

Observação

Para obter diretrizes de desempenho para SQL Server em VMs do Azure, consulte este artigo.

Lista de verificação das práticas recomendadas do SQL Server

A lista de verificação a seguir é para o desempenho ideal do SQL Server em VMs do Azure Stack Hub:

Área	Otimizações
Tamanho da VM	DS3 ou superior para SQL Server Enterprise Edition. DS2 ou superior para SQL Server Standard Edition e Web Edition.
Armazenamento	Use uma família de VMs que dê suporte ao armazenamento Premium.
Discos	Use no mínimo dois discos de dados (um para arquivos de log e outro para arquivo de dados e TempDB) e escolha o tamanho do disco com base em suas necessidades de capacidade. Defina os locais de arquivo de dados padrão para esses discos durante a instalação do SQL Server. Evite usar o sistema operacional ou discos temporários para armazenamento de banco de dados ou registro em log. Distribua vários discos de dados do Azure para obter maior taxa de transferência de E/S usando Espaços de Armazenamento. Formate com os tamanhos de alocação documentados.
E/S	Habilite a inicialização instantânea de arquivos para arquivos de dados. Limite o crescimento automático nos bancos de dados com incrementos fixos razoavelmente pequenos (64 MB-256 MB). Desabilite a redução automática no banco de dados. Configure locais padrão de backup e arquivo de banco de dados em discos de dados, não no disco do sistema operacional. Habilite as páginas bloqueadas. Aplique service packs e atualizações cumulativas do SQL Server.
Recursos específicos	Faça backup diretamente no armazenamento de blobs (se houver suporte na versão do SQL Server em uso).

Para obter mais informações sobre como e por que fazer essas otimizações, examine os detalhes e as diretrizes fornecidas nas seções a seguir.

Diretrizes de tamanho de VM

Para aplicativos sensíveis ao desempenho, os seguintes tamanhos de VM são recomendados:

• SQL Server Enterprise Edition: DS3 ou superior

• SQL Server Standard Edition e Web Edition: DS2 ou superior

Com o Azure Stack Hub, não há diferença de desempenho entre as séries de família de VMs DS e DS_v2.

Orientação de armazenamento

As VMs da série DS (juntamente com a série DSv2) no Azure Stack Hub fornecem o disco máximo do sistema operacional e a taxa de transferência do disco de dados (IOPS). Uma VM da série DS ou DSv2 fornece até 1.000 IOPS para o disco do sistema operacional e até 2.300 IOPS por disco de dados, independentemente do tipo ou tamanho do disco escolhido.

A taxa de transferência do disco de dados é determinada exclusivamente com base na série da família de VMs. Você pode consultar este artigo para identificar a taxa de transferência do disco de dados por série de família de VMs.

Observação

Para cargas de trabalho de produção, selecione uma VM da série DS ou DSv2 para fornecer o máximo possível de IOPS no disco do sistema operacional e nos discos de dados.

Ao criar uma conta de armazenamento no Azure Stack Hub, a opção de replicação geográfica não tem efeito porque essa funcionalidade não está disponível no Azure Stack Hub.

Diretrizes de discos

Há três tipos de disco principais em uma VM do Azure Stack Hub:

• Disco do sistema operacional: quando você cria uma VM do Azure Stack Hub, a plataforma anexa pelo menos um disco (rotulado como a unidade C ) à VM para o disco do sistema operacional. Cada disco é um VHD armazenado como um blob de páginas no armazenamento.

• Disco temporário: as VMs do Azure Stack Hub contêm outro disco chamado disco temporário (rotulado como a unidade D ). É um disco localizado no nó que pode ser usado como espaço de rascunho.

• Discos de dados: você pode anexar discos adicionais à sua VM como discos de dados, e esses discos são armazenados no armazenamento como blobs de páginas.

As seções a seguir descrevem as recomendações para usar esses diferentes discos.

Disco do sistema operacional

Um disco do sistema operacional é um VHD que pode ser inicializado e montado como uma versão em execução de um sistema operacional e é rotulado como a unidade C .

Disco temporário

A unidade de armazenamento temporário, rotulada como a unidade D , não é persistente. Não armazene nenhum dado que você não esteja disposto a perder na unidade D . Isso inclui os arquivos de banco de dados do usuário e os arquivos de log de transações do usuário.

É recomendável armazenar o TempDB em um disco de dados, pois cada disco de dados fornece um máximo de até 2.300 IOPS por disco de dados.

Discos de dados

• Use discos de dados para arquivos de dados e de log. Se você não estiver usando a distribuição de disco, use dois discos de dados de uma VM que dê suporte ao armazenamento Premium, em que um disco contém os arquivos de log e o outro contém os dados e os arquivos TempDB. Cada disco de dados fornece um número de IOPS, dependendo da família de VMs, conforme descrito em Tamanhos de VM com suporte no Azure Stack Hub. Se você estiver usando uma técnica de distribuição de disco, como Espaços de Armazenamento, coloque todos os dados e arquivos de log na mesma unidade (incluindo TempDB). Essa configuração fornece o número máximo de IOPS disponíveis para o SQL Server consumir, independentemente do arquivo que precisa deles em um determinado momento.

Observação

Quando você provisiona uma VM do SQL Server no portal, tem a opção de editar sua configuração de armazenamento. Dependendo da configuração, o Azure Stack Hub configura um ou mais discos. Vários discos são combinados em um único pool de armazenamento. Tanto os dados quanto os arquivos de log residem juntos nessa configuração.

• Distribuição de disco: para obter mais taxa de transferência, você pode adicionar discos de dados adicionais e usar a distribuição de disco. Para determinar o número de discos de dados necessários, analise o número de IOPS necessários para seus arquivos de log e para seus dados e arquivos TempDB. Observe que os limites de IOPS são por disco de dados com base na família de séries de VMs e não com base no tamanho da VM. Os limites de largura de banda de rede, no entanto, são baseados no tamanho da VM. Consulte as tabelas sobre tamanhos de VM no Azure Stack Hub para obter mais detalhes. Use as seguintes diretrizes:

  • Para Windows Server 2012 ou posterior, use Espaços de Armazenamento com as seguintes diretrizes:

    1. Defina a intercalação (tamanho da distribuição) como 64 KB (65.536 bytes) para cargas de trabalho OLTP (processamento de transações online) e 256 KB (262.144 bytes) para cargas de trabalho de data warehouse para evitar impacto no desempenho devido ao desalinhamento da partição. Isso deve ser definido com o PowerShell.

    2. Defina a contagem de colunas = número de discos físicos. Use o PowerShell ao configurar mais de oito discos (não a interface do usuário do Gerenciador do Servidor).

       Por exemplo, o PowerShell a seguir cria um novo pool de armazenamento com o tamanho da intercalação definido como 64 KB e o número de colunas como 2:

       $PoolCount = Get-PhysicalDisk -CanPool $True
       $PhysicalDisks = Get-PhysicalDisk | Where-Object {$_.FriendlyName -like "*2" -or $_.FriendlyName -like "*3"}
       
       New-StoragePool -FriendlyName "DataFiles" -StorageSubsystemFriendlyName "Storage Spaces*" -PhysicalDisks $PhysicalDisks | New-VirtualDisk -FriendlyName "DataFiles" -Interleave 65536 -NumberOfColumns 2 -ResiliencySettingName simple -UseMaximumSize |Initialize-Disk -PartitionStyle GPT -PassThru |New-Partition -AssignDriveLetter -UseMaximumSize |Format-Volume -FileSystem NTFS -NewFileSystemLabel "DataDisks" -AllocationUnitSize 65536 -Confirm:$false
       

• Determine o número de discos associados ao seu pool de armazenamento com base nas suas expectativas de carga. Tenha em mente que tamanhos de VM diferentes permitem quantidades diferentes de discos de dados anexados. Para obter mais informações, consulte Tamanhos de VM com suporte no Azure Stack Hub.

• Para obter o máximo de IOPS possível para discos de dados, a recomendação é adicionar o número máximo de discos de dados compatíveis com o tamanho da VM e usar a distribuição de disco.

• Tamanho da unidade de alocação NTFS: ao formatar o disco de dados, recomendamos que você use um tamanho de unidade de alocação de 64 KB para arquivos de dados e log, bem como TempDB.

• Práticas de gerenciamento de disco: ao remover um disco de dados, interrompa o serviço SQL Server durante a alteração. Além disso, não altere as configurações de cache nos discos, pois isso não fornece melhorias de desempenho.

Aviso

A falha ao interromper o Serviço SQL durante essas operações pode causar corrupção do banco de dados.

Diretrizes de E/S

• Considere a habilitação da inicialização instantânea de arquivo a fim de reduzir o tempo necessário para alocação inicial do arquivo. Para aproveitar a inicialização instantânea de arquivos, conceda a conta de serviço do SQL Server (MSSQLSERVER) com SE_MANAGE_VOLUME_NAME e adicione-a à política de segurança Executar Tarefas de Manutenção de Volume . Se você estiver usando uma imagem da plataforma SQL Server para o Azure, a conta de serviço padrão (NT Service\MSSQLSERVER) não será adicionada à política de segurança Executar Tarefas de Manutenção de Volume . Em outras palavras, a inicialização instantânea de arquivos não está habilitada em uma imagem da plataforma SQL Server Azure. Depois de adicionar a conta de serviço do SQL Server à política de segurança Executar Tarefas de Manutenção de Volume , reinicie o serviço do SQL Server. Talvez existam considerações de segurança sobre a utilização desse recurso. Para obter mais informações, consulte Inicialização de arquivos de bancos de dados.

• O crescimento automático é uma contingência para um crescimento inesperado. Não gerencie seus dados e registre o crescimento no dia-a-dia com o crescimento automático. Se o crescimento automático for usado, pré-cresça o arquivo usando a opção Tamanho .

• Verifique se a redução automática está desabilitada a fim de evitar uma sobrecarga desnecessária que pode afetar negativamente o desempenho.

• Configure os locais do arquivo de banco de dados e backup padrão. Use as recomendações neste artigo e faça as alterações na janela Propriedades do servidor. Para obter instruções, confira Exibir ou alterar os locais padrão de arquivos de log e de dados (SQL Server Management Studio). A captura de tela a seguir mostra onde fazer essas alterações:

  

• Estabeleça páginas bloqueadas a fim de reduzir a ES e quaisquer atividades de paginação. Para saber mais, confira Habilitar a opção Bloquear Páginas na Memória (Windows).

• Considere compactar todos os arquivos de dados ao transferir para dentro/fora do Azure Stack Hub, incluindo backups.

Diretriz específica do recurso

Algumas implantações podem obter outros benefícios de desempenho usando técnicas mais avançadas de configuração. A lista a seguir destaca alguns recursos do SQL Server que podem ajudá-lo a obter um melhor desempenho:

• Faça backup no armazenamento do Azure. Ao fazer backups para o SQL Server em execução em VMs do Azure Stack Hub, você pode usar o Backup do SQL Server na URL. Esse recurso foi disponibilizado a partir do SQL Server 2012 SP1 CU2 e é recomendado para fazer o backup em discos de dados anexados.

  Ao fazer backup ou restaurar usando o armazenamento do Azure, siga as recomendações fornecidas em Backup do SQL Server para práticas recomendadas de URL e Solução de problemas e restauração de backups armazenados no Microsoft Azure. Você também pode automatizar esses backups usando o Backup Automatizado para SQL Server em VMs do Azure.

• Faça backup no armazenamento do Azure Stack Hub. Você pode fazer backup no armazenamento do Azure Stack Hub de maneira semelhante ao backup no Armazenamento do Azure. Ao criar um backup dentro do SSMS (SQL Server Management Studio), você precisa inserir as informações de configuração manualmente. Você não pode usar o SSMS para criar o contêiner de armazenamento ou a Assinatura de Acesso Compartilhado. O SSMS só se conecta a assinaturas do Azure, não a assinaturas do Azure Stack Hub. Em vez disso, você precisa criar a conta de armazenamento, o contêiner e a Assinatura de Acesso Compartilhado no portal do Azure Stack Hub ou com o PowerShell.

  

  Observação

  A Assinatura de Acesso Compartilhado é o token SAS do portal do Azure Stack Hub, sem o '?' à esquerda na cadeia de caracteres. Se você usar a função de cópia do portal, precisará excluir o '?' à esquerda para que o token funcione no SQL Server.

  Depois de ter o Destino de Backup instalado e configurado no SQL Server, você poderá fazer backup no armazenamento de blobs do Azure Stack Hub.

Próximas etapas

Usando serviços ou criando aplicativos para o Azure Stack Hub