Melhores práticas para performance em Sql Server no Azure

Melhores práticas para performance em Sql Server no Azure

As dúvidas mais comuns em provisionamento de máquinas virtuais com Sql Server no Azure estão geralmente relacionados a performance. Que tipo de máquina utilizar? Como garantir melhor performance de disco? Como aproveitar o cache? Qual a vantagem de máquinas com SSD (tipo D)? Neste artigo, vou explorar algumas das principais técnicas e melhores práticas de provisionamento de servidores Sql Server para responder a estas e outras perguntas.

Tamanho de máquina virtual

A recomendação da Microsoft é que se você vai utilizar a versão Enterprise, a máquina deve ser pelo menos A3 e se a versão é Standard, a maquina deve ser pelo menos A2. Na prática, já vi cenários onde uma máquina do tipo A1 conseguia executar o trabalho com maestria, mas realmente estamos falando de situações com pouca demanda do servidor do banco. Na maioria das vezes, vamos acabar trabalhando com máquinas A6-A8 ou mesmo as do tipo D, que possuem volume temporário SSD.

No final das contas, não fique muito preocupado com que tipo de máquina utilizar, lembre-se que a qualquer momento podemos escalar este ambiente para cima ou para baixo de acordo com a demanda. Para minimizar erros, comece sempre de cima e vá descendo até encontrar a melhor combinação Cpu x Memória x Discos.

Mais informações, acesse Virtual Machine and Cloud Service Sizes for Azure [1]

Configurações de storage, discos e volumes

Antes de colocar o servidor do banco em produção, existem alguns cuidados que temos que tomar com relação ao armazenamento dos dados. Primeiro, não é recomendado utilizar o drive C: para guardar os dados do banco por dois motivos: Limitação de espaço e performance. VMs Windows possuem um limite de 127GB para armazenamento no C:, isso sem contar o espaço utilizado pelos arquivos do sistema operacional. No final, você tem pouco mais de 100GB para utilizar, sem possibilidade de estender futuramente. Além disso, o disco da VM tem uma performance máxima de 500 IOPS (Standard) o que pode vir a ser um gargalo para sua aplicação. Outro ponto negativo é  que não conseguimos desligar o cache do disco do sistema operacional.

Perceba que os principais pontos estão sempre relacionados ao I/O, sendo assim, irei comentar sobre configurações otimizadas de storage, volume e dados no Azure e na VM para garantir a melhor performance no serviço.

Cache

Conforme prévia, o disco do sistema operacional vem com cache ativo por padrão e não é possível desliga-lo, no máximo, podemos alternar entre read/write e read-only. É recomendado que o cache esteja desativado sempre que trabalhamos com banco de dados de aplicações. Isso evita que os dados possam ser corrompidos durante operações de escrita. No caso do cache de leitura, a Microsoft recomenda que seja ativado apenas se você estiver usando o Storage Premium. Portanto, garanta que ao anexar um novo disco à maquina virtual, o cache esteja setado para “None”, conforme imagem abaixo:

Cache desligado Anexar Disco


Geo-Replicação

Apesar de parecer uma boa opção para aumentar a resiliência do sistema, bancos de dados de uma forma geral possuem uma quantidade de operações de I/O muito grande. Quando você ativa a geo-replicação do storage no Azure, este cenário pode gerar um delay de replicação que afeta negativamente a performance tanto para leitura quanto escrita. Sendo assim, utilize outros mecanismos para garantir que seus dados estejam mais seguros e opte por storages localmente redundantes, conforme imagem abaixo. Para mais informações sobre métodos de alta disponibilidade e recuperação de desastres, acesse  High Availability and Disaster Recovery for SQL Server in Azure Virtual Machines [2].

Localmente Redundante Storage


Storage Spaces

Uma excelente opção para aumentar a performance de leitura/escrita do banco é através do storage spaces. No artigo Trabalhando com Storage Spaces em Máquinas Virtuais [3] explico em detalhes como anexar diversos discos e configurá-los para trabalhar como um único volume no Windows Server 2012 R2.  Em uma máquina A3, por exemplo, é possível anexar até 8 discos, isso quer dizer que teremos 8x mais IOPS (Input/Output operations per second) do que se tivéssemos apenas 1 disco. Lembre-se que cada storage do Azure possui um limite máximo de 20,000 IOPS, sendo assim, não é recomendado que você coloque mais do que 40 discos (20,000/500) de grande utilização no mesmo storage, pois a performance poderá ser afetada. Acesse Azure Subscription and Service Limits, Quotas, and Constraints [4] para mais informações sobre limites. Garante que sempre esteja utilizando o máximo de discos que sua VM suportar para criar o Storage Pool, mas fique atento caso tenha a intenção de diminuir o tamanho da máquina futuramente, pois você estará limitado a máquinas que suportem o número de discos em utilização neste momento.

Storage Pool


Unidade de alocação NTFS

Ao formatar o disco, opte pelo tamanho da unidade de alocação em 64-kb para os discos de dados, logs e tempdb. Este é o tamanho recomendado para a maioria dos cenários pela Microsoft. Maiores detalhes em Disk Partition Alignment Best Practices for SQL Server [5].

Format 64-kb


Logs x Dados

Aqui você deve analisar de acordo com o tipo de aplicação que possui. No geral, tanto os arquivos de log quanto de dados podem ser configurados no mesmo volume. Caso seu workload de logs seja muito alto, é recomendável que seja criado um disco só para guarda-los. Para migrar os arquivos do banco de dados já existente para um novo lugar, utilize os seguintes passos:

  1. Coloque o banco em modo Offline
    ALTER DATABASE database_name SET OFFLINE;
  2. Mova os arquivos do banco para a nova localidade
  3. Para cada um dos arquivos movidos, execute o seguinte:
    ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name, FILENAME = 'new_path\os_file_name' );
    
  4. Coloque o banco de volta Online
     ALTER DATABASE database_name SET ONLINE;
  5. Verifique as mudanças através da query abaixo:
    SELECT name, physical_name AS CurrentLocation, state_desc
    FROM sys.master_files
    WHERE database_id = DB_ID(N'<database_name>');


TempDb, Buffer Pool Extensions

É extremamente recomendado que o tempDb do Sql Server seja movido para o volume temporário D: se você estiver utilizando máquinas do tipo D (SSD), caso esteja utilizando máquinas do tipo A (Standard), utilize um disco exclusivo só para ele. Máquinas do tipo D são instâncias voltadas para aplicações que exigem um maior poder de processamento e I/O. Além de utilizar discos SSD para o volume de dados temporário D:, cada vCPU é cerca de 60% mais rápido que os modelos A. Vou demonstrar como utilizar a unidade D:, mas os procedimentos de 1 à 3 serão iguais para quem estiver usando um disco exclusivo.

Para alterar o caminho padrão do TempDb e ativar o Buffer Pool Extensions (Apenas Sql Server 2014) no drive D: siga os seguintes passos:

1) Crie uma nova pasta, por exemplo, D:\SQLTEMP

2) Mova o TempDb para o novo caminho através da query a seguir:

USE MASTER
GO
ALTER DATABASE tempdb MODIFY FILE (NAME= tempdev, FILENAME= 'D:\SQLTEMP\tempdb.mdf') 
GO
ALTER DATABASE tempdb MODIFY FILE (name = templog, filename = 'D:\SQLTEMP\templog.ldf')
GO

3) Configure o Buffer Pool Extensions através da query a seguir:

ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION ON
( FILENAME = 'D:\SQLTEMP\ExtensionFile.BPE' , SIZE = <size> [ KB | MB | GB ] )

Substitua o <size> [KB | MB | GB] pelo tamanho desejado. A recomendação é que esteja dentro de 4-6x o tamanho da memória da VM, sendo assim, em uma máquina com 7gb de memória, vamos utilizar “size = 42GB”.

4) Jump of the cat (Alterar padrão de inicialização):

Atenção: A partir deste passo, o procedimento é necessário apenas para quem estiver utilizando máquinas do tipo D. Se você está utilizando máquinas Standard, não utilize o drive temporário, ao invés disso, a recomendação é anexar um novo disco, neste caso você não precisará alterar o modo de inicialização, pois os dados desde disco não são perdidos.

Note que criamos um novo diretório no drive D:, mas uma vez que reiniciarmos essa máquina, todo o seu conteúdo será apagado. Logo, ao executar o Sql Server durante o processo de inicialização do Windows deverá ocorrer um erro, pois a pasta SqlTemp não mais existirá.

Para contornar este problema, vamos desativar o startup automático do Sql Server e criar um script de inicialização.

I) Primeiro, entre no gerenciador de serviços (services.msc) e altere as configurações do serviço SQL Server (MSSQLSERVER) para modo manual.

Sql Server Manual Start

II) Agora crie o script de inicialização em powershell:

$SQLService="SQL Server (MSSQLSERVER)"
$SQLAgentService="SQL Server Agent (MSSQLSERVER)"
$tempfolder="D:\SQLTEMP"
if (!(test-path -path $tempfolder)) {
    New-Item -ItemType directory -Path $tempfolder
}
Start-Service $SQLService
Start-Service $SQLAgentService

Salve este script em algum lugar no drive C: com a extensão .ps1.

III) No console do powershell, altere as políticas de execução de scripts:

PS C:\> Set-ExecutionPolicy RemoteSigned

IV) Crie uma nova tarefa agendada para executar durante a inicialização do Windows:

Abra o agendador de tarefas -> Criar tarefa básica -> Ao iniciar o Computador -> Iniciar um Programa -> Digite em caminho powershell –file ‘C:\SQL-startup.ps1′, altere o nome e caminho do script conforme o que tiver feito no passo anterior.

Sql Startup Task


É isso pessoal! Há outras recomendações que você pode seguir para melhorar ainda mais a performance do seu banco e recomendo fortemente que leia o artigo Performance Best Practices for SQL Server in Azure Virtual Machines [7] que vai bastante além das dicas que apresentei aqui. É valido ressaltar que as configurações de storage e discos podem ser aproveitados também para outros banco de dados como MySQL, por exemplo. 

Dicas, críticas e sugestões não deixe de comentar.

Referências

[1] Virtual Machine and Cloud Service Sizes for Azure. https://msdn.microsoft.com/en-us/library/dn197896.aspx

[2] High Availability and Disaster Recovery for SQL Server in Azure Virtual Machines.https://msdn.microsoft.com/en-us/library/azure/jj870962.aspx

[3] Trabalhando com Storage Spaces em Máquinas Virtuais.http://www.azurekb.com.br/trabalhando-com-storage-spaces-em-maquinas-virtuais/

[4] Azure Subscription and Service Limits, Quotas, and Constraints. http://azure.microsoft.com/en-us/documentation/articles/azure-subscription-service-limits/

[5] Disk Partition Alignment Best Practices for SQL Server. https://technet.microsoft.com/en-us/library/dd758814%28v=sql.100%29.aspx

[6] Performance Best Practices for SQL Server in Azure Virtual Machines.https://msdn.microsoft.com/en-us/library/azure/dn133149.aspx

Classificar por: Data da Publicação | Mais Recente | Mais Úteis
Comentários
  • Muito bom artigo! O conteúdo detalhado e muito útil.

    Obrigado por compartilhar

Página 1 de 1 (1 itens)