Criando uma instância do SQL Server de alto desempenho

Mantenha tudo organizado com as coleções Salve e categorize o conteúdo com base nas suas preferências.

Como criar a VM do Compute Engine com discos

Para criar uma instância do SQL Server de alto desempenho, primeiro você deve criar uma instância de VM com o SQL Server e dois discos permanentes.

Considerações sobre discos permanentes

Para selecionar o tipo de discos permanentes para sua VM, revise as seguintes considerações:

• Um SSD local fornece um local de alto desempenho para tempdb e arquivo de paginação do Windows.

  Há algumas considerações importantes a serem observadas ao usar um SSD local. Quando você desliga sua instância do Windows ou a reinicia usando a API, o SSD local é removido. Esta ação torna a instância não inicializável. Para que a máquina volte a funcionar, você precisará desanexar os discos permanentes, criar uma nova instância com eles e, em seguida, definir um novo SSD local. Após a inicialização, você também precisará formatar o novo disco e reinicializar. Portanto, você não deve armazenar permanentemente dados críticos em um SSD local ou desligar a instância, a menos que esteja preparado para reconstruí-la.

• Um disco permanente SSD fornece armazenamento de alto desempenho para os arquivos de banco de dados.

  O desempenho do disco permanente é baseado em um 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 o pico de 40.000 operações de leitura por segundo (ops) e 30.000 operações de gravação. A taxa de transferência sustentada total para leituras e gravações é de 800 MB por segundo e 400 MB por segundo, respectivamente. Essas medidas representam uma soma de todos os discos permanentes conectados à máquina virtual, incluindo a unidade C:\ . É por isso que você deve criar um SSD local para descarregar todos os IOPS necessários para o arquivo de paginação, tempdb , dados de teste e backups.

Para ler mais sobre o desempenho do disco, consulte Configurar discos para atender aos requisitos de desempenho .

Criação de VM do Compute Engine com discos

Para criar uma VM que tenha o SQL Server 2022 Standard pré-instalado no Windows Server 2022, siga estas etapas:

1. No console do Google Cloud, acesse a página Criar uma instância .

   Vá para Criar uma instância

2. Para Nome , insira ms-sql-server .

3. Na seção Configuração da máquina , selecione Finalidade geral e faça o seguinte:

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

4. Na seção Disco de inicialização , clique em Alterar e faça o seguinte:

   1. Na guia Imagens públicas , clique na lista Sistema operacional e 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 inicialização , clique em Disco permanente padrão .
   4. No campo Tamanho (GB) , defina o tamanho do disco de inicialização para 50 GB.
   5. Para salvar a configuração do disco de inicialização, clique em Selecionar .

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

   1. Expanda a seção Discos .

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

      1. Na lista Interface , selecione o protocolo que atende aos requisitos de desempenho do seu sistema.
      2. Na lista Capacidade do disco , selecione uma capacidade de disco que suporte o tamanho previsto dos arquivos tempdb .
      3. Para finalizar a criação deste disco, clique em Salvar .

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

      1. Mantenha o campo Nome inalterado.
      2. Na lista Tipo de origem de disco , selecione Disco em branco .
      3. Na lista Tipo de disco , selecione Disco permanente SSD .
      4. No campo Tamanho , insira o tamanho do disco que pode acomodar o tamanho do banco de dados.
      5. Para finalizar a criação do segundo disco, clique em Salvar .

6. Para criar a VM, clique em Criar .

Configurando o Windows

Agora que você tem uma instância funcionando executando o SQL Server, conecte-se à sua instância e configure o sistema operacional Windows. Depois disso, você aprenderá a configurar o SQL Server em uma próxima seção.

Conecte-se à sua instância

1. No console do Google Cloud, acesse a página de instâncias de VM .

   Acesse as 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 senha do Windows .

4. Especifique um nome de usuário.

5. Clique em Definir para gerar uma nova senha para esta instância do Windows.

6. Anote o nome de usuário e a senha para poder fazer login na instância.

7. Conecte-se à sua instância usando RDP .

Configurando volumes de disco

Crie e formate os volumes:

1. No menu Iniciar , pesquise “Gerenciamento do Computador” e abra-o.
2. Na seção Armazenamento , selecione Gerenciamento de disco .
3. Quando solicitado a inicializar os discos, aceite as seleções padrão e clique em OK .

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

   Para localizar um disco SSD local, clique com o botão direito em um disco e selecione Propriedades . O nome das propriedades do disco SSD local será Google EphemeralDisk para uma interface SCSI ou nvme_card para uma interface NVMe. Tanto os SSDs locais quanto os SSDs persistentes são marcados como tendo partições Unallocated .

   1. Se a VM contiver apenas uma unidade SSD local, siga estas etapas:

      1. Na lista de unidades de disco, clique com o botão direito no disco SSD local de 374,98 GB e selecione Novo volume simples .
      2. Na tela de boas-vindas, clique em Avançar para iniciar o assistente de volume de disco.
      3. Na etapa Especificar tamanho do volume , deixe o tamanho do volume no valor padrão e clique em Avançar para continuar.
      4. Na etapa Atribuir letra de unidade ou caminho , escolha P: para a letra da unidade e clique em Avançar para continuar.

      5. Na etapa Formatar volume , altere o tamanho da unidade de alocação para 8192 e insira "pagefile" para o rótulo do volume . Clique em Avançar para prosseguir.

         

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

   2. Se a VM contiver várias unidades SSD locais, siga estas etapas:

      1. Na lista de unidades de disco, clique com o botão direito no primeiro disco SSD local de 374,98 GB e selecione Novo volume distribuído .
      2. Na tela de boas-vindas, clique em Avançar para iniciar o assistente de volume de disco.

      3. Na etapa Selecionar discos , adicione todos os discos disponíveis com tamanho de 383.982 MB à seção Selecionados. Clique em Avançar para prosseguir.

         

      4. Na etapa Atribuir letra de unidade ou caminho , escolha P: para a letra da unidade e clique em Avançar para continuar.

      5. Na etapa Formatar volume , altere o tamanho da unidade de alocação para 8192 e insira "pagefile" para o rótulo do volume . Clique em Avançar para prosseguir.

         

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

5. Repita as etapas anteriores para criar um novo volume simples para o disco SSD, com as três alterações a seguir:

   • Escolha D: para a letra da unidade.

   • Defina o tamanho da unidade de alocação como 64k .

     Para obter detalhes sobre como selecionar um tamanho de unidade de alocação, consulte Práticas recomendadas para instâncias do SQL Server .

   • Insira sqldata para o rótulo do volume .

Movendo o arquivo de paginação do Windows

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

1. No menu Iniciar , pesquise Exibir configurações avançadas do sistema e abra a caixa de diálogo.
2. Clique na guia Avançado e na seção Desempenho , clique em Configurações .
3. Na seção Memória virtual , clique no botão Alterar .
4. Desmarque a caixa de seleção Gerenciar automaticamente o tamanho do arquivo de paginação para todas as unidades . O sistema já deve ter configurado seu arquivo de paginação na C:\ e você precisa movê-lo.
5. Clique em C: e, em seguida, clique no botão de opção Sem arquivo de paginação .
6. Clique no botão Definir .
7. Para criar o novo arquivo de paginação, clique na unidade P: e, em seguida, clique no botão de opção Tamanho gerenciado pelo sistema .
8. Clique no botão Definir .

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

   O Suporte da Microsoft publicou dicas adicionais para configurações de memória virtual .

Configurando o perfil de energia

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

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

Configurando o SQL Server

Use o SQL Server Management Studio para executar a maioria das tarefas administrativas. As imagens pré-configuradas para SQL Server já vêm com o Management Studio instalado. Inicie o Management Studio e clique em Conectar para conectar-se ao banco de dados padrão.

Movendo os dados e arquivos de log

A imagem pré-configurada para SQL Server vem com tudo instalado na unidade C:\ , inclusive os bancos de dados do sistema. Para otimizar sua configuração, mova esses arquivos para a nova D:\ que você criou. Lembre-se também de criar todos os novos bancos de dados na D:\ . Como você está usando um SSD, não é necessário armazenar os arquivos de dados e de log em partições de disco separadas.

Existem duas maneiras de mover a instalação para o disco secundário: usando o instalador ou movendo os arquivos manualmente.

Usando o instalador

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

Movendo os arquivos manualmente

Mova os bancos de dados do sistema e configure o SQL Server para salvar os dados e arquivos de log no mesmo volume:

1. Crie uma nova pasta chamada D:\SQLData .
2. Abra uma janela de comando.

3. Digite o seguinte comando para conceder acesso total ao NT Service\MSSQLSERVER :

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

4. Use o Management Studio e os guias a seguir para mover os bancos de dados do sistema e alterar os locais de arquivo padrão para novos bancos de dados.

5. Se você planeja usar os recursos do Report Server , mova também os arquivos ReportServer e ReportServerTempDB .

Depois de mover os arquivos do banco de dados de configuração primário e reiniciar, será necessário configurar o sistema para apontar para o novo local dos bancos de dados modelo e MSDB. Aqui está 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 interromper o serviço de banco de dados SQL Server.
2. Use o explorador de arquivos do Windows para mover os arquivos físicos da C:\ onde o banco de dados master estava localizado para o diretório D:\SQLData .
3. Inicie o serviço de banco de dados SQL Server.

Configurando permissões do sistema

Depois de mover os bancos de dados do sistema, modifique algumas configurações adicionais, começando com as permissões para a conta de usuário do Windows criada para executar o processo do SQL Server, chamada NT Service\MSSQLSERVER .

Concedendo a permissão Lock Pages in Memory

A permissão de política de grupo Lock Pages in Memory impede que o Windows mova páginas da 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 arquivo 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 dessas informações raramente mudam, por isso se tornam um alvo para o arquivo de paginação. Se essas informações forem movidas para o arquivo de paginação, o desempenho do SQL Server poderá ser prejudicado. Conceder à política de grupo a permissão Lock Pages in Memory para a conta de serviço do SQL Server evita essa troca.

Siga estas etapas:

1. Clique em Iniciar e pesquise Editar Política de Grupo para abrir o console.
2. Expanda Política de computador local > Configuração do computador > Configurações do Windows > Configurações de segurança > Políticas locais > Atribuição de direitos de usuário .
3. Procure e clique duas vezes em Bloquear páginas na memória .
4. Clique em Adicionar usuário ou grupo .
5. Procure por "Serviço NT\MSSQLSERVER".
6. Se você vir vários nomes, clique duas vezes no nome MSSQLSERVER .
7. Clique em OK duas vezes.
8. Mantenha o console do Editor de Política de Grupo aberto.

Concedendo a permissão Perform volume maintenance tasks

Por padrão, quando um aplicativo solicita uma fatia de espaço em disco do Windows, o sistema operacional localiza uma parte de espaço em disco de tamanho apropriado e zera toda a parte do disco antes de devolvê-la ao aplicativo. Como o SQL Server é bom para aumentar arquivos e preencher espaço em disco, esse comportamento não é ideal.

Existe uma API separada para alocar espaço em disco para um aplicativo, geralmente chamada de inicialização instantânea de arquivo . Infelizmente, essa configuração só funciona para arquivos de dados, mas você aprenderá em uma próxima seção sobre o crescimento de arquivos de log. A inicialização instantânea de arquivos exige que a conta de serviço que executa o processo do SQL Server tenha outra permissão de política de grupo, chamada Perform volume maintenance tasks .

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

Configurando tempdb

Costumava ser uma prática recomendada otimizar o uso da CPU do SQL Server criando um arquivo tempdb por CPU. No entanto, como as contagens de CPU aumentaram com o tempo, seguir esta diretriz pode diminuir o desempenho. Como um bom ponto de partida, use 4 arquivos tempdb . À medida que você mede o desempenho do seu sistema, em casos raros, pode ser necessário aumentar gradativamente o número de arquivos tempdb até um máximo de 8.

Você pode executar um script Transact-SQL (T-SQL) dentro do SQL Server Management Studio para mover os arquivos tempdb para uma pasta na unidade `p:`.

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

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

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

3. Execute o seguinte script dentro do SQL Server Management Studio para mover o arquivo de dados tempdb e o arquivo de log:

   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 script a seguir para modificar os tamanhos dos arquivos e criar três arquivos 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 você usa o SQL Server 2016, há três arquivos tempdb adicionais para remover depois de executar as etapas anteriores:

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

6. Reinicie o SQL Server novamente.

7. Exclua os arquivos model , MSDB , master e tempdb do local original na unidade C:\ .

Você moveu com êxito seus arquivos tempdb para a partição SSD local. Essa movimentação acarreta alguns riscos, mencionados anteriormente, mas se eles forem perdidos por qualquer motivo, o SQL Server recriará os arquivos tempdb . A migração tempdb oferece desempenho adicional do SSD local e diminui o IOPS usado nos discos permanentes.

Configurando max degree of parallelism

A configuração padrão recomendada para max degree of parallelism é combiná-la com o número de CPUs no servidor. No entanto, há um ponto em que executar uma consulta em 16 ou 32 partes paralelas e mesclar os resultados é muito mais lento do que executá-la em um único processo. Se você estiver usando uma instância de 16 ou 32 núcleos, poderá definir o valor max degree of parallelism como 8 usando o 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

Configurando max server memory

O padrão dessa configuração é um número muito alto, mas você deve configurá-la para o número de megabytes de RAM física disponível, menos alguns gigabytes para sistema operacional e sobrecarga. O exemplo T-SQL a seguir ajusta max server memory para 100 GB. Modifique-o para ajustar o valor para corresponder à sua instância. Revise o documento de opções de configuração do servidor de memória do servidor para obter 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

Terminando

Reinicie a instância mais uma vez para garantir que todas as novas configurações tenham efeito. Seu sistema SQL Server está configurado e você está pronto para criar seus próprios bancos de dados e começar a testar suas cargas de trabalho específicas. Revise o guia de práticas recomendadas do SQL Server para obter mais informações sobre atividades operacionais, outras considerações de desempenho e recursos da Enterprise Edition.