Melhores práticas para performance em Sql Server no Azure

Artigo
17/01/2024

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:

http://www.azurekb.com.br/wp-content/uploads/2015/04/Capture.png

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

http://www.azurekb.com.br/wp-content/uploads/2015/04/Capture2.png

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.

http://www.azurekb.com.br/wp-content/uploads/2015/02/Img7-1024x576.png

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

http://www.azurekb.com.br/wp-content/uploads/2015/04/Capture3.png

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:

Coloque o banco em modo Offline

ALTER DATABASE database_name SET OFFLINE;

Mova os arquivos do banco para a nova localidade

Para cada um dos arquivos movidos, execute o seguinte:

ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name, FILENAME = 'new_path\os_file_name' );

Coloque o banco de volta Online

 ALTER DATABASE database_name SET ONLINE;

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.

http://www.azurekb.com.br/wp-content/uploads/2015/04/dpi-sep25-2.png-550x0.png

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.

http://www.azurekb.com.br/wp-content/uploads/2015/04/dpi-sep25-4.png-550x0.png

É 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

Compartilhar via