Como criar uma instância de alto desempenho do SQL Server

Neste tutorial, mostramos como criar uma instância de VM do Google Compute Engine otimizada para o desempenho executando o SQL Server. Descrevemos a criação da instância e depois a configuração do SQL Server para conseguir o melhor desempenho no Google Cloud Platform. Você aprenderá inúmeras opções de configuração disponíveis para ajudar no ajuste do desempenho do sistema.

Este tutorial usa o SQL Server Standard Edition 2014. Portanto, nem todas as opções de configuração apresentadas neste guia funcionam para qualquer pessoa, nem todas elas 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

Este tutorial usa componentes faturáveis do Cloud Platform, incluindo os seguintes:

  • instância de alta memória do Google 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 custos com base no uso projetado. O link fornecido mostra a estimativa de custo 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 Cloud Platform podem se qualificar 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 GCP.

    Acessar a página Gerenciar recursos

  3. Verifique se o faturamento foi ativado para o projeto.

    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 fornece 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ê desliga a instância do Windows ou a redefine usando a API, o SSD local é removido. Essa ação faz com que a instância fique não inicializável. Para que a máquina volte a ser executada, você precisará 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. 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 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 medições representam uma soma de todos os discos permanentes anexados à máquina virtual, incluindo a C:\` drive.. This is why you should create a local SSD to offload all the IOPS needed for the paging file,tempdb`, dados de teste e backups

Para ler mais sobre desempenho de disco, consulte Como otimizar o desempenho de discos permanentes e SSDs locais.

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 o Tipo de 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.

Estabelecer conexão com a 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 RDP:

    • Se o Chrome RDP da FusionLabs estiver instalado, 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 seu 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, em seguida, Discos.

    O SSD local é chamado de Google EphemeralDisk. O SSD local e o SSD permanente são marcados 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 da unidade.
  • Defina o Tamanho da unidade de alocação como "32k".

    A Microsoft recomenda que os dados do SQL Server e os discos de registro 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 que contam para o limite de E/S do disco permanente.

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

Correção do 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 da unidade e caminhos de acesso

  5. Selecione D: como a letra da unidade.

  6. Clique em OK.

Como transferir o arquivo de paginação do Windows

Agora que os novos volumes estão 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 possivelmente já configurou o arquivo de paginação na unidade "C:", e você precisa transferi-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 2014 vêm com o Management Studio já instalado, mas, 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 acompanha tudo instalado na C:\` drive, including the system databases. In order to optimize your setup, move those files to the newunidade D: criada. Lembre-se também de criar todos os novos bancos de dados na unidade "D:". Como você está usando um disco permanente SSD, não é preciso 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: com o instalador ou transferindo os arquivos manualmente.

Com o 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 que salve 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 abaixo para transferir os bancos de dados do sistema e alterar os locais de arquivos padrão para 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 principais 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' )

Depois de você executar esses comandos, siga estas etapas:

  1. Use o snap-in services.msc para interromper o serviço de banco de dados do SQL Server.
  2. Use o explorador de arquivos do Windows para mover os arquivos físicos de C:\` drive where theMasterdatabase was located to the para o diretório D:\SQLData.
  3. Inicie o serviço de banco de dados do SQL Server.

Como definir as permissões do sistema

Depois de transferir os bancos de dados do sistema, modifique algumas configurações adicionais, começando com 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 raramente 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. Conceder a 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 de 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 você vir vários nomes, clique duas vezes no nome 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 um pedaço de espaço do tamanho apropriado e, em seguida, zera todo o pedaço antes de devolvê-lo 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. Infelizmente, essa configuração só funciona para arquivos de dados, mas você aprenderá em uma próxima seção sobre crescimento de arquivos de registro. A inicialização instantânea de arquivo exige que a conta de serviço executando 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 o 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ê mede o desempenho do sistema, em casos raros talvez seja necessário aumentar gradualmente o número de arquivos TempDB até, no máximo, oito.

É possível executar um script T-SQL dentro do SQL Server Management Studio para 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 de arquivo 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ê usar o SQL Server 2016, precisará remover três arquivos TempDB adicionais depois de concluir 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ê transferiu com êxito os arquivos TempDB para a partição SSD local. Essa transferência tem seus riscos, mencionados anteriormente, mas, se os arquivos TempDB se perderem por qualquer motivo, o SQL Server os recriará. Transferir TempDB oferece mais desempenho do SSD local e diminui a IOPS usada nos discos permanentes.

Como configurar max degree of parallelism

A configuração padrão recomendada para max degree of parallelism é combiná-lo com o número de CPUs no servidor. No entanto, há um ponto em que executar uma consulta em 16 ou 32 pedaços paralelos 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 máximo de 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

Como configurar 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. O seguinte exemplo de T-SQL ajustamax server memory para 100 GB. Modifique-o para ajustar o valor para que ele corresponda a sua instância.

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 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.

Como fazer a limpeza

Depois de concluir o tutorial do SQL Server, é possível limpar os recursos criados no Google Cloud Platform para que eles não ocupem cotas e você não seja cobrado por eles no futuro. Nas seções a seguir, aprenda a excluir e desativar esses recursos.

Como excluir o projeto

A maneira mais fácil de evitar o faturamento é excluir o projeto criado para o tutorial.

Para excluir o projeto:

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

    Acessar a página Projetos

  2. Na lista de projetos, selecione o que você quer excluir e clique em Excluir projeto. Após marcar a caixa de seleção ao lado do nome do projeto, clique em Excluir projeto
  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 da VM".

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

  2. Clique na caixa de seleção ao lado da da instância que deseja excluir.
  3. Clique no botão Excluir na parte superior da página para excluir a instância.

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.

Próximas etapas

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

Enviar comentários sobre…