Criar uma instância do SQL Server de alto desempenho

Criar a VM do Compute Engine com discos

Para criar uma instância do SQL Server de alto desempenho, tem primeiro de criar uma instância de VM com o SQL Server e dois volumes de disco persistente.

Considerações sobre o Persistent Disk

Para selecionar o tipo de volumes de discos persistentes para a sua VM, reveja as seguintes considerações:

• Um disco SSD local oferece uma localização de elevado desempenho para o tempdb e o ficheiro de paginação do Windows.

  Existem algumas considerações importantes a ter em conta quando usar um disco SSD local. Quando encerra a instância a partir do Windows ou a repõe através da API, o disco SSD local é removido. Esta ação torna a instância não inicializável. Para voltar a executar a máquina, tem de desassociar os discos persistentes, criar uma nova instância com os mesmos e, em seguida, definir um novo disco SSD local. Após o arranque, também tem de formatar o novo disco e reiniciar. Por conseguinte, não deve armazenar permanentemente dados críticos num disco SSD local nem desligar a instância, a menos que esteja preparado para a reconstruir.

• Um disco persistente SSD oferece armazenamento de elevado desempenho para os ficheiros da base de dados.

  O desempenho do disco persistente baseia-se num cálculo que usa o número de CPUs e o tamanho do disco. Com 32 vCPUs e um disco de 1 TB, o desempenho atinge um pico de 40 000 operações de leitura por segundo (ops) e 30 000 ops de escrita. A taxa de transferência sustentada total para leituras e escritas é de 800 MB por segundo e 400 MB por segundo, respetivamente. Estas medições representam uma soma de todos os volumes do disco persistente anexados à máquina virtual, incluindo a unidade C:\. Para garantir um desempenho consistente, crie um disco SSD local e transfira todas as IOPS necessárias para o ficheiro de paginação, os dados de preparação e as cópias de segurança.tempdb

Para ler mais sobre o desempenho do disco, consulte o artigo Configure os discos para cumprir os requisitos de desempenho.

Criar uma VM do Compute Engine com discos

Para criar uma VM com o SQL Server 2022 Standard pré-instalado no Windows Server 2022, siga estes passos:

1. Na Trusted Cloud consola, aceda à página Criar uma instância.

   Aceda a Criar uma instância

2. Em Nome, introduza ms-sql-server.

3. Na secção Configuração da máquina, selecione Objetivo geral e, em seguida, faça o seguinte:

   1. Na lista Série, clique em N2.
   2. Na lista Tipo de máquina, clique em n2-highmem-16 (16 vCPU, 128 GB de memória).

4. Na secção Disco de arranque, clique em Alterar e, de seguida, faça o seguinte:

   1. No separador Imagens públicas, clique na lista Sistema operativo e, de seguida, selecione SQL Server no Windows Server.
   2. Na lista Versão, clique em SQL Server 2022 Standard no Windows Server 2022 Datacenter.
   3. Na lista Tipo de disco de arranque, clique em Disco persistente padrão.
   4. No campo Tamanho (GB), defina o tamanho do disco de arranque para 50 GB.
   5. Para guardar a configuração do disco de arranque, clique em Selecionar.

5. Expanda a secção Opções avançadas e faça o seguinte:

   1. Expanda a secção Discos.

   2. Para criar discos locais, clique em Adicionar SSD local e, de seguida, faça o seguinte:

      1. Na lista Interface, selecione o protocolo que cumpre os requisitos de desempenho do seu sistema.
      2. Na lista Capacidade do disco, selecione uma capacidade do disco que suporte o tamanho previsto dos ficheiros tempdb.
      3. Para concluir a criação deste disco, clique em Guardar.

   3. Para criar discos adicionais, clique em Adicionar novo disco.

      1. Mantenha o campo Name inalterado.
      2. Na lista Tipo de origem do disco, selecione Disco em branco.
      3. Na lista Tipo de disco, selecione Disco persistente SSD.
      4. No campo Tamanho, introduza o tamanho do disco que pode acomodar o tamanho da base de dados.
      5. Para terminar de criar o segundo disco, clique em Guardar.

6. Para criar a VM, clique em Criar.

Configurar o Windows

Agora que tem uma instância funcional com o SQL Server, ligue-se à sua instância e configure o sistema operativo Windows. Depois disso, vai aprender a configurar o SQL Server numa secção futura.

Associe-se à sua instância

1. Na Trusted Cloud consola, aceda à página Instâncias de VM.

   Aceder às instâncias de VM

2. Na coluna Nome, clique no nome da sua instância, ms-sql-server.

3. Na parte superior da página de detalhes da instância, clique no botão Definir palavra-passe do Windows.

4. Especifique um nome de utilizador.

5. Clique em Definir para gerar uma nova palavra-passe para esta instância do Windows.

6. Tome nota do nome de utilizador e da palavra-passe para poder iniciar sessão na instância.

7. Ligue-se à sua instância através do RDP.

Configurar volumes de disco

Crie e formate os volumes:

1. No menu Iniciar, pesquise "Gestão do computador" e, de seguida, abra-o.
2. Na secção Armazenamento, selecione Gestão de discos.
3. Quando lhe for pedido para inicializar os discos, aceite as seleções predefinidas e clique em OK.

4. Crie uma partição para os discos SSD locais:

   Para localizar um disco SSD local, clique com o botão direito do rato num disco e selecione Propriedades. O nome das propriedades do disco SSD local é Google EphemeralDisk para uma interface SCSI ou nvme_card para uma interface NVMe. Os discos SSD locais e os SSDs persistentes estão marcados como tendo partições Unallocated.

   1. Se a VM contiver apenas 1 unidade SSD local, siga estes passos:

      1. Na lista de unidades de disco, clique com o botão direito do rato no disco SSD local de 374,98 GB e selecione New Simple Volume.
      2. No ecrã de boas-vindas, clique em Seguinte para iniciar o assistente de volume do disco.
      3. No passo Especifique o tamanho do volume, deixe o tamanho do volume com o valor predefinido e clique em Seguinte para continuar.
      4. No passo Atribuir letra do disco ou caminho, escolha P: para a letra do disco e clique em Seguinte para continuar.

      5. No passo Formatar volume, altere o Tamanho da unidade de alocação para 8192 e introduza "pagefile" para a Etiqueta de volume. Clique em Seguinte para continuar.

         

      6. Clique em Concluir para concluir o assistente de volume do disco.

   2. Se a VM contiver várias unidades SSD locais, siga estes passos:

      1. Na lista de unidades de disco, clique com o botão direito do rato no primeiro disco SSD local de 374,98 GB e selecione New Striped Volume (Novo Volume Distribuído).
      2. No ecrã de boas-vindas, clique em Seguinte para iniciar o assistente de volume do disco.

      3. No passo Selecionar discos, adicione todos os discos disponíveis com o tamanho de 383 982 MB à secção Selecionados. Clique em Seguinte para continuar.

         

      4. No passo Atribuir letra do disco ou caminho, escolha P: para a letra do disco e clique em Seguinte para continuar.

      5. No passo Formatar volume, altere o Tamanho da unidade de alocação para 8192 e introduza "pagefile" para a Etiqueta de volume. Clique em Seguinte para continuar.

         

      6. Clique em Concluir para concluir o assistente de volume do disco.

5. Repita os passos anteriores para criar um Novo volume simples para o disco SSD, com as seguintes três alterações:

   • Escolha D: para a letra da unidade.

   • Defina o Tamanho do bloco de anúncios de alocação como 64k.

     Para ver detalhes sobre a seleção de um tamanho da unidade de alocação, consulte o artigo Práticas recomendadas para instâncias do SQL Server.

   • Introduza sqldata para a etiqueta de volume.

Mover o ficheiro de paginação do Windows

Agora que os novos volumes foram criados e montados, mova o ficheiro de paginação do Windows para o disco SSD local, o que liberta IOPS do disco persistente e melhora o tempo de acesso da sua memória virtual.

1. No menu Iniciar, pesquise Ver definições avançadas do sistema e, em seguida, abra a caixa de diálogo.
2. Clique no separador Avançadas e, na secção Desempenho, clique em Definições.
3. Na secção Memória virtual, clique no botão Alterar.
4. Desmarque a caixa de verificação Gerir automaticamente o tamanho do ficheiro de paginação para todas as unidades. O sistema já deve ter configurado o ficheiro de paginação na unidade C:\ e tem de o mover.
5. Clique em C: e, de seguida, clique no botão de opção Sem ficheiro de paginação.
6. Clique no botão Definir.
7. Para criar o novo ficheiro de paginação, clique na unidade P: e, de seguida, clique no botão de opção Tamanho gerido pelo sistema.
8. Clique no botão Definir.

9. Clique em OK três vezes para sair das propriedades avançadas do sistema.

   O apoio técnico da Microsoft publicou sugestões adicionais para as definições de memória virtual.

Definir o perfil de energia

Defina o perfil de energia para High-Performance em vez de Balanced.

1. No menu Iniciar, pesquise "Escolher um plano de energia" e, de seguida, abra as opções de energia.
2. Selecione o botão de opção Alto desempenho.
3. Saia da caixa de diálogo.

Configurar o SQL Server

Use o SQL Server Management Studio para realizar a maioria das tarefas administrativas. As imagens pré-configuradas para o SQL Server são fornecidas com o Management Studio já instalado. Inicie o Management Studio e, de seguida, clique em Associar para se associar à base de dados predefinida.

Mover os ficheiros de dados e registo

A imagem pré-configurada para o SQL Server inclui tudo instalado na unidade C:\, incluindo as bases de dados do sistema. Para otimizar a configuração, mova esses ficheiros para a nova unidade D:\ que criou. Lembre-se também de criar todas as novas bases de dados na unidade D:\. Como está a usar um SSD, não precisa de armazenar os ficheiros de dados e os ficheiros de registo em partições de disco separadas.

Existem duas formas de mover a instalação para o disco secundário: através do instalador ou movendo os ficheiros manualmente.

Usar o instalador

Para usar o instalador, execute c:\setup.exe e selecione um novo caminho de instalação no disco secundário.

Mover os ficheiros manualmente

Mova as bases de dados do sistema e configure o SQL Server para guardar os ficheiros de dados e de registo no mesmo volume:

1. Crie uma nova pasta com o nome D:\SQLData.
2. Abra uma janela de comandos.

3. Introduza o seguinte comando para conceder acesso total a NT Service\MSSQLSERVER:

   icacls D:\SQLData /Grant "NT Service\MSSQLServer:(OI)(CI)F"
   

4. Use o Management Studio e os seguintes guias para mover as bases de dados do sistema e alterar as localizações predefinidas dos ficheiros para novas bases de dados.

5. Se planeia usar funcionalidades do servidor de relatórios, mova também os ficheiros ReportServer e ReportServerTempDB.

Depois de mover os ficheiros da base de dados de configuração principal e reiniciar, tem de configurar o sistema para apontar para a nova localização das bases de dados model e MSDB. Segue-se um script auxiliar para executar no Management Studio:

ALTER DATABASE model MODIFY FILE ( NAME = modeldev , FILENAME = 'D:\SQLData\model.mdf' )
ALTER DATABASE model MODIFY FILE ( NAME = modellog , FILENAME = 'D:\SQLData\modellog.ldf' )
ALTER DATABASE msdb MODIFY FILE ( NAME = MSDBData , FILENAME = 'D:\SQLData\MSDBData.mdf' )
ALTER DATABASE msdb MODIFY FILE ( NAME = MSDBlog , FILENAME = 'D:\SQLData\MSDBLog.ldf' )

Depois de executar estes comandos:

1. Use o snap-in services.msc para parar o serviço de base de dados do SQL Server.
2. Use o Explorador de ficheiros do Windows para mover os ficheiros físicos da unidade C:\ onde a base de dados master estava localizada para o diretório D:\SQLData.
3. Inicie o serviço de base de dados do SQL Server.

Definir autorizações do sistema

Depois de mover as bases de dados do sistema, modifique algumas definições adicionais, começando pelas autorizações da conta de utilizador do Windows criada para executar o processo do SQL Server, que se chama NT Service\MSSQLSERVER.

Conceder a autorização Lock Pages in Memory

A autorização da política de grupo Lock Pages in Memory impede que o Windows mova páginas na memória física para a memória virtual. Para manter a memória física livre e organizada, o Windows tenta trocar páginas antigas e raramente modificadas para o ficheiro de paginação da memória virtual no disco.

O SQL Server armazena informações importantes na memória, como estruturas de tabelas, planos de execução e consultas em cache. Algumas destas informações raramente mudam, pelo que se tornam um alvo para o ficheiro de paginação. Se estas informações forem movidas para o ficheiro de paginação, o desempenho do SQL Server pode degradar-se. A concessão da autorização Lock Pages in Memory da política de grupo à conta de serviço do SQL Server impede esta troca.

Siga estes passos:

1. Clique em Iniciar e, de seguida, procure Editar política de grupo para abrir a consola.
2. Expanda Política de computador local > Configuração do computador > Definições do Windows > Definições de segurança > Políticas locais > Atribuição de direitos do utilizador.
3. Pesquise e, em seguida, clique duas vezes em Bloquear páginas na memória.
4. Clique em Adicionar utilizador ou grupo.
5. Pesquise "NT Service\MSSQLSERVER".
6. Se vir vários nomes, clique duas vezes no nome MSSQLSERVER.
7. Clique em OK duas vezes.
8. Mantenha a consola do Group Policy Editor aberta.

Conceder a autorização Perform volume maintenance tasks

Por predefinição, quando uma aplicação pede uma fatia de espaço em disco ao Windows, o sistema operativo localiza um bloco de espaço em disco de tamanho adequado e, em seguida, preenche com zeros todo o bloco de disco antes de o devolver à aplicação. Uma vez que o SQL Server é bom a aumentar os ficheiros e a preencher o espaço em disco, este comportamento não é o ideal.

Existe uma API separada para atribuir espaço em disco a uma aplicação, frequentemente denominada inicialização instantânea de ficheiros. Infelizmente, esta definição só funciona para ficheiros de dados, mas vai saber mais sobre o crescimento dos ficheiros de registo numa secção futura. A inicialização instantânea de ficheiros requer que a conta de serviço que executa o processo do SQL Server tenha outra autorização de política de grupo, denominada Perform volume maintenance tasks.

1. No Editor de políticas de grupo, pesquise "Executar tarefas de manutenção de volume".
2. Adicione a conta "NT Service\MSSQLSERVER" como fez na secção anterior.
3. Reinicie o processo do SQL Server para ativar ambas as definições.

A configurar o tempdb

Anteriormente, era uma prática recomendada otimizar a utilização da CPU do SQL Server criando um ficheiro tempdb por CPU. No entanto, uma vez que o número de CPUs aumentou ao longo do tempo, seguir esta diretriz pode fazer com que o desempenho diminua. Como ponto de partida, use 4 ficheiros tempdb. À medida que mede o desempenho do sistema, em casos raros, pode ter de aumentar gradualmente o número de ficheiros tempdb até um máximo de 8.

Pode executar um script Transact-SQL (T-SQL) no SQL Server Management Studio para mover os ficheiros tempdb para uma pasta na unidade "p:".

1. Crie o diretório p:\tempdb.

2. Conceda acesso de segurança total à conta de utilizador "NT Service\MSSQLSERVER":

   icacls p:\tempdb /Grant "NT Service\MSSQLServer:(OI)(CI)F"
   

3. Execute o seguinte script no SQL Server Management Studio para mover o ficheiro de dados tempdb e o ficheiro de registo:

   USE master
   GO
   ALTER DATABASE [tempdb] MODIFY FILE (NAME = tempdev, FILENAME = 'p:\tempdb\tempdb.mdf')
   GO
   ALTER DATABASE [tempdb] MODIFY FILE (NAME = templog, FILENAME = 'p:\tempdb\templog.ldf')
   GO
   

4. Reinicie o SQL Server.

5. Execute o seguinte script para modificar os tamanhos dos ficheiros e criar três ficheiros de dados adicionais para o novo tempdb.

   ALTER DATABASE [tempdb] MODIFY FILE (NAME = tempdev, FILENAME = 'p:\tempdb\tempdb.mdf', SIZE=8GB)
   ALTER DATABASE [tempdb] MODIFY FILE (NAME = templog, FILENAME = 'p:\tempdb\templog.ldf' , SIZE = 2GB)
   ALTER DATABASE [tempdb] ADD FILE (NAME = 'tempdev1', FILENAME = 'p:\tempdb\tempdev1.ndf' , SIZE = 8GB, FILEGROWTH = 0);
   ALTER DATABASE [tempdb] ADD FILE (NAME = 'tempdev2', FILENAME = 'p:\tempdb\tempdev2.ndf' , SIZE = 8GB, FILEGROWTH = 0);
   ALTER DATABASE [tempdb] ADD FILE (NAME = 'tempdev3', FILENAME = 'p:\tempdb\tempdev3.ndf' , SIZE = 8GB, FILEGROWTH = 0);
   GO
   

   Se usar o SQL Server 2016, existem 3 ficheiros tempdb adicionais a remover depois de seguir os passos anteriores:

   ALTER DATABASE [tempdb] REMOVE FILE temp2;
   ALTER DATABASE [tempdb] REMOVE FILE temp3;
   ALTER DATABASE [tempdb] REMOVE FILE temp4;
   

6. Reinicie novamente o SQL Server.

7. Eliminar os ficheiros model, MSDB, master e tempdb da localização original na unidade C:\.

Moveu com êxito os seus ficheiros tempdb para a partição do disco SSD local. Esta mudança acarreta alguns riscos, mencionados anteriormente, mas se forem perdidos por qualquer motivo,o SQL Server recompila os ficheiros tempdb. A mudança de tempdb oferece-lhe o desempenho adicional do SSD local e diminui os IOPS usados nos volumes do disco persistente.

A definir max degree of parallelism

A predefinição recomendada para max degree of parallelism é igualá-la ao número de CPUs no servidor. No entanto, chega a um ponto em que a execução de uma consulta em 16 ou 32 partes paralelas e a união dos resultados é muito mais lenta do que a execução num único processo. Se estiver a usar uma instância de 16 ou 32 núcleos, pode definir o valor max degree of parallelism como 8 através do seguinte T-SQL:

USE master
GO
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'max degree of parallelism', 8
GO
RECONFIGURE WITH OVERRIDE
GO

A definir max server memory

Esta definição é predefinida para um número muito elevado, mas deve defini-la para o número de megabytes de RAM física disponível, menos alguns gigabytes para o sistema operativo e a sobrecarga. O seguinte exemplo de T-SQL ajusta max server memory para 100 GB. Modifique-o para ajustar o valor de acordo com a sua instância. Reveja o documento Opções de configuração do servidor de memória do servidor para mais informações.

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO
exec sp_configure 'max server memory', 100000
GO
RECONFIGURE WITH OVERRIDE
GO

A finalizar

Reinicie a instância mais uma vez para garantir que todas as novas definições entram em vigor. O seu sistema SQL Server está configurado e está pronto para criar as suas próprias bases de dados e começar a testar as suas cargas de trabalho específicas. Reveja o guia de práticas recomendadas do SQL Server para mais informações sobre atividades operacionais, outras considerações de desempenho e capacidades da Enterprise Edition.