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. 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, 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 faturáveis do Google Cloud, 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 de custo dos produtos usados neste tutorial, que podem custar mais de US$ 4 e mais de US$ 3.000 por mês. Os novos usuários do Google Cloud podem estar qualificados para um teste gratuito.

Antes de começar

  1. Faça login na sua conta do Google Cloud. Se você começou a usar o Google Cloud agora, crie uma conta para avaliar o desempenho de nossos produtos em situações reais. Clientes novos também recebem US$ 300 em créditos para executar, testar e implantar cargas de trabalho.
  2. No Console do Google Cloud, na página do seletor de projetos, selecione ou crie um projeto do Google Cloud.

    Acessar o seletor de projetos

  3. Verifique se o faturamento está ativado para seu projeto na nuvem. Saiba como confirmar se o faturamento está ativado para o projeto.

  4. No Console do Google Cloud, na página do seletor de projetos, selecione ou crie um projeto do Google Cloud.

    Acessar o seletor de projetos

  5. Verifique se o faturamento está ativado para seu projeto na nuvem. Saiba como confirmar se o faturamento está ativado para o projeto.

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 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 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 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 VM do Compute Engine com discos

Para criar uma VM que tenha o SQL Server 2014 Standard pré-instalado no Windows Server 2012, 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 N1.
    2. Na lista Tipo de máquina, clique em n1-highmem-16 (16vCPU, 104 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 2014 Standard no Windows Server 2012 R2 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 Rede, discos, segurança, gerenciamento, locatário único 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 Número de SSDs locais, selecione o número de SSDs locais. É possível adicionar até 24 SSDs locais.
      2. 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 lista Tipo, selecione Disco permanente SSD.
      4. 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 VM.

    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 por "Gerenciador do Servidor" e abra-o.
  2. Selecione Serviços de Arquivo e Armazenamento e, depois, 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:

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

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.

Limpeza

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. No Console do Cloud, acesse a página Gerenciar recursos:

    Acessar "Gerenciar recursos"

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

Excluir instâncias

Para excluir uma instância do Compute Engine:

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

    Acessar instâncias de VM

  2. Marque a caixa de seleção de a instância que você quer excluir.
  3. Para excluir a instância, clique em Mais ações, clique em Excluir e siga as instruções.

Como excluir discos permanentes

Para excluir um disco permanente:

  1. No Console do 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