Criação de uma instância de alto desempenho do SQL Server

Neste tutorial, você verá como criar uma instância de VM do Compute Engine ao executar o SQL Server otimizado para desempenho. Neste tutorial, orientamos você na criação da instância e na configuração do SQL Server para conseguir o melhor desempenho no Google Cloud. Você aprenderá sobre as diversas opções de configuração disponíveis para ajudar a ajustar o desempenho do sistema.

Neste tutorial, é usado o SQL Server Standard Edition 2022. Portanto, nem todas as opções de configuração apresentadas neste guia funcionam para todo mundo, nem oferecem benefícios de desempenho perceptíveis para todas as cargas de trabalho.

Objetivos

  • Configurar a instância e os discos do Compute Engine.
  • Configurar o sistema operacional Windows.
  • Configurar o SQL Server.

Custos

Neste tutorial, há componentes faturáveis do Google Cloud, entre eles:

  • instância com grande quantidade de memória do Compute Engine;
  • armazenamento em disco permanente SSD do Compute Engine;
  • armazenamento em disco SSD local do Compute Engine;
  • imagem pré-configurada do SQL Server Standard

A calculadora de preços pode gerar uma estimativa de custo com base no uso projetado. O link fornecido mostra a estimativa de custo dos produtos usados neste tutorial, que podem custar mais de US$ 4 e mais de US$ 3.000 por mês. Novos usuários do Google Cloud podem estar qualificados para uma avaliação gratuita.

Antes de começar

  1. Sign in to your Google Cloud account. If you're new to Google Cloud, create an account to evaluate how our products perform in real-world scenarios. New customers also get $300 in free credits to run, test, and deploy workloads.

  2. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

    Go to project selector

  3. Verifique se a cobrança está ativada para o seu projeto do Google Cloud.

  4. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

    Go to project selector

  5. Verifique se a cobrança está ativada para o seu projeto do Google Cloud.

Como criar a VM do Compute Engine com discos

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

Considerações sobre discos permanentes

Para selecionar o tipo de disco permanente para a VM, revise as seguintes considerações:

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

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

  • 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 máximo de 40.000 operações de leitura por segundo (ops) e 30.000 operações de gravação. A capacidade total de leituras e gravações sustentadas é de 800 MB por segundo e 400 MB por segundo, respectivamente. Essas medidas representam a soma de todos os discos permanentes anexados à máquina virtual, incluindo o drive C:\. Por essa razão, você precisa criar um SSD local para descarregar todas as IOPS necessárias ao arquivo de paginação, ao tempdb, aos dados de preparo e aos backups.

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

Como criar a 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.

    Acesse "Criar uma instância"

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

  3. Na seção Configuração da máquina, selecione Uso 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 como 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 seja compatível com o tamanho previsto de arquivos tempdb.
      3. Para concluir a criação do disco, clique em Salvar.

    3. Para criar mais discos, clique em Adicionar novos discos.

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

  6. Para criar a VM, clique em Criar.

Como configurar o Windows

Agora que você tem uma instância de trabalho executando o SQL Server, conecte-se à instância e configure o sistema operacional Windows. Depois disso, você aprenderá a configurar o SQL Server em uma seção mais à frente.

Conecte-se à instância

  1. No Console do Google Cloud, acesse a página Instâncias de VMs.

    Acessar instâncias de VM

  2. Na coluna Nome, clique no nome da 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 essa instância do Windows.

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

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

Como configurar volumes de disco

Crie e formate os volumes:

  1. No menu Iniciar, procure "Gerenciamento do computador" e abra essa opção.
  2. Na seção Armazenamento, selecione Gerenciamento de disco.
  3. Quando for solicitado que você inicialize discos, aceite as seleções padrão 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 mouse 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. Os SSDs locais e permanentes 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 do mouse no disco SSD local de 374,98 GB e selecione Novo volume simples.
      2. Na tela de boas-vindas, clique em Próxima para iniciar o assistente de volume de disco.
      3. Na etapa Especificar tamanho do volume, mantenha o valor padrão e clique em Próximo para continuar.
      4. Na etapa Atribuir letra ou caminho da unidade, escolha P: como a letra da unidade e clique em Próxima para continuar.

      5. Na etapa Formatar volume, mude o Tamanho da unidade de alocação para 8.192 e digite "pagefile" em Rótulo do volume. Clique em Próximo para continuar.

        Assistente de novo volume

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

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

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

      3. Na etapa Selecionar discos, adicione todos os discos disponíveis com o tamanho 383.982 MB à seção "Selecionado". Clique em Próximo para continuar.

        Adicionar discos listrados

      4. Na etapa Atribuir letra ou caminho da unidade, escolha P: como a letra da unidade e clique em Próxima para continuar.

      5. Na etapa Formatar volume, mude o Tamanho da unidade de alocação para 8.192 e digite "pagefile" em Rótulo do volume. Clique em Próximo para continuar.

        Assistente de novo volume

      6. Clique em Concluir para finalizar 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:

Como transferir o arquivo de paginação do Windows

Agora que os novos volumes foram criados e montados, transfira 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 memória virtual.

  1. No menu Iniciar, procure 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 de todas as unidades. O sistema já deve ter configurado o arquivo de paginação no drive C:\, e você precisa movê-lo.
  5. Clique em C: e, em seguida, no botão de opção Nenhum 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 mais dicas sobre configurações de memória virtual.

Como definir o perfil de energia

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

  1. No menu Iniciar, procure "Escolher 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.

Como configurar o SQL Server

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

Como transferir os arquivos de dados e de registro

A imagem pré-configurada do SQL Server já vem com tudo instalado no drive C:\, inclusive os bancos de dados do sistema. Para otimizar a configuração, transfira esses arquivos para o novo drive D:\ que você criou. Lembre-se também de criar todos os novos bancos de dados no drive D:\. Como você está usando um disco permanente SSD, não precisa armazenar os arquivos de dados e de registro em partições de disco separadas.

Há duas maneiras de transferir a instalação para o disco secundário: pelo instalador ou pela transferência manual de arquivos.

Instalador

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

Transferência manual dos arquivos

Transfira os bancos de dados do sistema e configure o SQL Server para salvar os arquivos de dados e de registro 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 a NT Service\MSSQLSERVER:

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

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

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

Depois de transferir os arquivos de banco de dados de configuração principal e reiniciar, você precisará configurar o sistema para que ele aponte para o novo local do modelo e dos bancos de dados MSDB. Aqui está um script de ajuda para ser executado 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' )

Após a execução desses comandos, siga estas etapas:

  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 transferir os arquivos físicos do drive C:\ em que o banco de dados master estava localizado para o diretório D:\SQLData.
  3. Inicie o serviço de banco de dados SQL Server.

Como definir as permissões do sistema

Depois de transferir os bancos de dados do sistema, modifique algumas outras configurações, a começar pelas permissões para a conta de usuário do Windows criada para executar seu processo do SQL Server, denominado NT Service\MSSQLSERVER.

Como conceder a permissão Lock Pages in Memory

A permissão Lock Pages in Memory da política de grupo impede que o Windows transfira 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 pouco modificadas para o arquivo de paginação de memória virtual no disco.

O SQL Server armazena informações importantes na memória, como estruturas de tabela, planos de execução e consultas em cache. Algumas dessas informações raramente são alteradas, portanto elas se tornam um destino para o arquivo de paginação. Se essas informações forem transferidas para o arquivo de paginação, o desempenho do SQL Server pode ser reduzido. A concessão da permissão Lock Pages in Memory da política de grupo para a conta de serviço do SQL Server impede essa transferência.

Siga estas etapas:

  1. Clique em Iniciar e procure Editar política de grupo para abrir o console.
  2. Expanda Política do computador local > Configuração do computador > Configurações do Windows > Configurações de segurança > Políticas locais > Atribuição de direitos do 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 "NT Service\MSSQLSERVER".
  6. Se aparecerem vários nomes, clique duas vezes em MSSQLSERVER.
  7. Clique em OK duas vezes.
  8. Mantenha o console Editor de Política de Grupo aberto.

Bloquear páginas

Como conceder a permissão Perform volume maintenance tasks

Por padrão, quando um aplicativo solicita um pouco de espaço em disco do Windows, o sistema operacional localiza uma parte do espaço com o tamanho apropriado e, em seguida, zera toda a parte antes de concedê-la ao aplicativo. Como o SQL Server usa muitos arquivos e preenche muito espaço em disco, esse comportamento não é o ideal.

Há uma API separada para alocação de espaço em disco para um aplicativo, muitas vezes conhecida como inicialização instantânea de arquivo. Essa configuração apenas funciona para arquivos de dados, mas você aprenderá em uma seção futura sobre crescimento de arquivos de registro. A inicialização instantânea do arquivo 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, procure “Executar tarefas de manutenção de volume”.
  2. Adicione a conta “NT Service\MSSQLSERVER” como você fez na seção anterior.
  3. Reinicie o processo do SQL Server para ativar as duas configurações.

Como configurar tempdb

A criação de um arquivo tempdb por CPU costumava ser uma prática recomendada para otimizar o uso da CPU do SQL Server. No entanto, como o número de CPUs cresceu ao longo do tempo, seguir essa orientação pode diminuir o desempenho. Como um bom ponto de partida, use quatro arquivos tempdb. Conforme você avalia o desempenho do seu sistema, em casos raros, pode ser necessário aumentar gradualmente o número de arquivos tempdb até um máximo de oito.

É possível executar um script Transact-SQL (T-SQL) no SQL Server Management Studio a fim de transferir 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 script a seguir no SQL Server Management Studio para transferir o arquivo de dados tempdb e o arquivo de registro:

    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 crie 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 a serem removidos após 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 no drive C:\.

Você transferiu os arquivos tempdb para a partição SSD local. Essa transferência tem alguns riscos, mencionados anteriormente. Porém, se os arquivos tempdb forem perdidos por qualquer motivo, o SQL Server os recriará. A transferência de tempdb oferece mais desempenho do SSD local e diminui a IOPS usada nos discos permanentes.

Como definir max degree of parallelism

A configuração padrão recomendada para max degree of parallelism é correspondê-lo 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, defina o valor max degree of parallelism como oito por meio 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

Como definir max server memory

Essa configuração assume um número muito alto como padrão, mas é preciso configurá-lo como o número de megabytes de memória RAM física disponível, menos alguns gigabytes para o sistema operacional e uma sobrecarga. No exemplo de T-SQL a seguir, max server memory é ajustado para 100 GB. Modifique-o para ajustar o valor de acordo com sua instância. Consulte o documento Opções de configuração do Server Memory 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

Finalizando

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

Limpar

Depois de concluir o tutorial, você pode limpar os recursos que criou para que eles parem de usar a cota e gerar cobranças. Nas seções a seguir, você aprenderá a excluir e desativar esses recursos.

Excluir o projeto

O jeito mais fácil de evitar cobranças é excluindo o projeto que você criou para o tutorial.

Para excluir o projeto:

  1. In the Google Cloud console, go to the Manage resources page.

    Go to Manage resources

  2. In the project list, select the project that you want to delete, and then click Delete.
  3. In the dialog, type the project ID, and then click Shut down to delete the project.

Como excluir instâncias

Para excluir uma instância do Compute Engine:

  1. In the Google Cloud console, go to the VM instances page.

    Go to VM instances

  2. Select the checkbox for the instance that you want to delete.
  3. To delete the instance, click More actions, click Delete, and then follow the instructions.

Como excluir discos permanentes

Para excluir o disco permanente:

  1. No console do Google Cloud, acesse a página Discos.

    Acessar "Discos"

  2. Marque a caixa de seleção ao lado do nome do disco que quer excluir.

  3. Clique no botão Excluir na parte superior da página.

Próximas etapas