Como criar 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. Descrevemos a criação da instância e a configuração do SQL Server para conseguir o melhor desempenho no Google Cloud Platform. Você aprenderá sobre as diversas opções de configuração disponíveis para ajudá-lo a ajustar o desempenho do sistema.

Neste tutorial, o SQL Server Standard Edition 2014 é usado. 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 do Google Cloud Platform que são cobrados, entre eles:

  • instância de alta 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 dos produtos usados neste tutorial, que podem custar mais de 4 dólares por hora e mais de 3.000 dólares por mês. Novos usuários do Google Cloud Platform podem estar qualificados para uma avaliação gratuita.

Antes de começar

  1. Faça login na sua Conta do Google.

    Se você ainda não tiver uma, inscreva-se.

  2. Selecione ou crie um projeto do Google Cloud Platform.

    Acessar a página Gerenciar recursos

  3. Verifique se o faturamento foi ativado no projeto do Google Cloud Platform.

    Saiba como ativar o faturamento

  4. Se você não estiver usando o Windows na máquina local, instale um cliente RDP de terceiros, como o Chrome RDP, da FusionLabs.

Como criar a instância e os discos do Compute Engine

Crie a instância do Compute Engine com o SQL Server e dois discos permanentes.

  • Um SSD local oferece um local de alto desempenho para o 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 nem desligue a instância, a menos que esteja preparado para recriá-los.

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

    O desempenho do disco permanente SSD é 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 contínuas é de 800 MB/s e 400 MB/s, 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 o desempenho do disco, consulte Desempenho do armazenamento em blocos.

Como criar a instância do Compute Engine

Crie uma VM que tenha o SQL Server 2014 Standard pré-instalado no Windows Server 2012.

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

    Acessar a página Instâncias de VMs

  2. Clique no botão Criar instância.

  3. Nomeie a instância como "ms-sql-server".

  4. Defina a Configuração da máquina como 16 vCPUs 104 GB, n1-highmem-16.

  5. Na seção Disco de inicialização, clique em Alterar para configurar o disco de inicialização.

  6. Na guia Imagens do aplicativo, selecione SQL Server 2014 Standard no Windows Server 2012 R2.

  7. Na seção Tipo de disco de inicialização, selecione Disco permanente padrão.

  8. Na seção Tamanho (GB), defina o tamanho do disco de inicialização como 50 GB.

  9. Clique em Selecionar.

  10. Expanda Gerenciamento, segurança, discos, rede, locatário único.

  11. Clique em Discos.

  12. Em Discos adicionais, clique em Adicionar novo disco para criar outro disco.

  13. Não altere o campo Nome.

  14. Em Tipo, selecione Disco de trabalho SSD local (no máximo 8).

  15. Clique em Concluído para concluir a criação do disco.

  16. Em Discos adicionais, clique em Adicionar novo disco novamente para criar outro.

  17. Não altere o campo Nome.

  18. Em Tipo, selecione Disco permanente SSD.

  19. Em Tipo de origem, selecione Disco em branco.

  20. Clique em Concluído para concluir a criação do segundo disco.

  21. Clique em Criar para criar a instância.

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.

Conectar-se à instância

  1. Acesse a página Instâncias de VMs no Console do GCP:

    Acessar a página Instâncias de VMs

  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 o RDP:

    • Se você instalou o Chrome RDP da FusionLabs, clique no botão RDP na parte superior da página de detalhes da instância.
    • Se você estiver usando um cliente RDP diferente, como a Conexão de Área de Trabalho Remota do Windows, clique no menu flutuante do botão RDP e faça o download do arquivo RDP. Abra o arquivo RDP usando o cliente.

Como configurar volumes de disco

Crie e formate os volumes:

  1. No menu Iniciar, procure por "Gerenciador de Servidores" e abra-o.
  2. Selecione Serviços de Arquivo e Armazenamento e selecione Discos.

    O SSD local é denominado Google EphemeralDisk. O SSD tanto local quanto permanente é marcado como tendo partições Unknown:

    Localizar a entrada Google EphemeralDisk

  3. Clique com o botão direito do mouse no disco SSD local de 375 GB chamado "Google Ephemeral Disk" e selecione Novo volume.

  4. Prossiga com os padrões e escolha P: para o rótulo de disco, porque esse será o disco de arquivo de paginação.

  5. Quando você chegar à etapa de configurações do sistema de arquivos, altere o Tamanho da unidade de alocação para 8.192 e digite "pagefile" em Rótulo do volume.

    Assistente de novo volume

  6. Repita as mesmas etapas acima para o segundo disco permanente SSD, exceto pelas seguintes três alterações:

  • Escolha D: como a letra do drive.
  • Defina o Tamanho da unidade de alocação como "32k".

    A Microsoft recomenda que os dados e os discos de registro do SQL Server sejam formatados como 64k, mas a tecnologia de disco permanente do GCP se alinha melhor com 32k. Essa alteração também diminui o número de operações de disco consideradas no limite de E/S do seu disco permanente.

  • Digite "sqldata" em Rótulo de volume.

Como corrigir o erro Failed to mount path - Invalid Parameter

Se você encontrar esse erro, siga estas etapas:

  1. Clique em Fechar.
  2. Clique no ícone de atualização de discos no canto superior direito.
  3. Clique no disco permanente de 500 GB da lista.
  4. No painel Volumes, clique com o botão direito do mouse no volume e escolha Gerenciar letra de unidade e caminhos de acesso.

    Gerenciar letra de unidade e caminhos de acesso

  5. Selecione D: como a letra do drive.

  6. Clique em OK.

Como transferir o arquivo de paginação do Windows

Agora que os novos volumes foram criados e ativados, 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 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 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 2014 já vêm com o Management Studio instalado. No entanto, se você estiver usando a imagem do SQL Server 2016, será preciso fazer o download e instalá-lo manualmente. Após a instalação, 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 mestre e fazer a reinicialização, você precisará configurar o sistema para que ele aponte para o novo local dos bancos de dados modelo e 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 poderá 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 troca.

Siga estas etapas:

  1. Clique em Iniciar e procure Editar Política de Grupo para abrir o console.
  2. Expanda Política Computador Local > Configuração do Computador > Configurações do Windows > 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 "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 T-SQL no SQL Server Management Studio a fim de transferir os arquivos TempDB para uma pasta no drive 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 defini-la 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

Conclusão

Reinicie a instância mais uma vez para garantir que todas as novas configurações entrem em vigor. 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.

Limpeza

Depois de concluir o tutorial do SQL Server, limpe os recursos criados no GCP para evitar que eles gastem sua cota e sejam cobrados no futuro. Nas seções a seguir, veja como excluir ou desativar esses recursos.

Como 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, faça o seguinte:

  1. No Console do GCP, acesse a página Projetos.

    Acessar a página Projetos

  2. Na lista de projetos, selecione o projeto que você quer excluir e clique em Excluir delete.
  3. Na caixa de diálogo, digite o código do projeto e clique em Encerrar para excluí-lo.

Como excluir instâncias

Para excluir uma instância do Compute Engine:

  1. No Console do GCP, acesse a página Instâncias de VM.

    Acessar a página "Instâncias de VMs"

  2. Clique na caixa de seleção ao lado da a instância que você quer excluir.
  3. Para excluir a instância, clique em Excluir excluir no topo da página.

Como excluir discos permanentes

Para excluir um disco permanente:

  1. No Console do GCP, acesse a página "Discos".

    Acessar a página "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.

A seguir

Esta página foi útil? Conte sua opinião sobre:

Enviar comentários sobre…