As melhores práticas do servidor SQL para otimizar o desempenho no Azure Stack HubSQL server best practices to optimize performance in Azure Stack Hub

Este artigo fornece as melhores práticas do servidor SQL para otimizar o SQL Server e melhorar o desempenho nas máquinas virtuais do Microsoft Azure Stack Hub (VMs).This article provides SQL server best practices to optimize SQL Server and improve performance in Microsoft Azure Stack Hub virtual machines (VMs). Ao executar o SQL Server em VMs Azure Stack Hub, utilize as mesmas opções de afinação de desempenho da base de dados aplicáveis ao SQL Server num ambiente de servidor no local.When running SQL Server in Azure Stack Hub VMs, use the same database performance-tuning options applicable to SQL Server in an on-premises server environment. O desempenho de uma base de dados relacional numa nuvem Azure Stack Hub depende de muitos fatores, incluindo o tamanho familiar de um VM e a configuração dos discos de dados.The performance of a relational database in an Azure Stack Hub cloud depends on many factors, including family size of a VM and the configuration of the data disks.

Ao criar imagens do SQL Server, considere a provisionar os seus VMs no portal Azure Stack Hub.When creating SQL Server images, consider provisioning your VMs in the Azure Stack Hub portal. Descarregue a extensão SQL IaaS da Marketplace Management no portal de administrador do Azure Stack Hub e descarregue a sua escolha de imagens VM do SQL Server.Download the SQL IaaS Extension from Marketplace Management in the Azure Stack Hub administrator portal and download your choice of SQL Server VM images. Estes incluem SQL Server 2016 SP1, SQL Server 2016 SP2 e SQL Server 2017.These include SQL Server 2016 SP1, SQL Server 2016 SP2, and SQL Server 2017.

Nota

Embora o artigo descreva como providenciar um SQL Server VM utilizando o portal Azure global, a orientação também se aplica ao Azure Stack Hub com as seguintes diferenças: O SSD não está disponível para o disco do sistema operativo e existem pequenas diferenças na configuração do armazenamento.While the article describes how to provision a SQL Server VM using the global Azure portal, the guidance also applies to Azure Stack Hub with the following differences: SSD isn't available for the operating system disk and there are minor differences in storage configuration.

Nas imagens VM, para SQL Server, só pode utilizar a sua própria licença (BYOL).In the VM images, for SQL Server, you can only use bring-your-own-license (BYOL). Para o Windows Server, o modelo de licença predefinido é pay-as-you-go (PAYG).For Windows Server, the default license model is pay-as-you-go (PAYG). Para obter informações detalhadas sobre o modelo de licença do Windows Server em VM, consulte o artigo Windows Server em Azure Stack Hub Marketplace FAQ.For detailed information of Windows Server license model in VM, refer the article Windows Server in Azure Stack Hub Marketplace FAQ.

Obter o melhor desempenho para SQL Server em Azure Stack Hub VMs é o foco deste artigo.Getting the best performance for SQL Server on Azure Stack Hub VMs is the focus of this article. Se a sua carga de trabalho for menos exigente, poderá não necessitar de todas as otimizações recomendadas.If your workload is less demanding, you might not require every recommended optimization. Considere as suas necessidades de desempenho e padrões de carga de trabalho à medida que avalia estas recomendações.Consider your performance needs and workload patterns as you evaluate these recommendations.

Nota

Para obter orientação de desempenho para o SQL Server em VMs Azure, consulte este artigo.For performance guidance for SQL Server in Azure VMs, refer to this article.

Lista de verificação para as melhores práticas do servidor SQLChecklist for SQL server best practices

A seguinte lista de verificação destina-se a um melhor desempenho do SQL Server em VMs Azure Stack Hub:The following checklist is for optimal performance of SQL Server on Azure Stack Hub VMs:

ÁreaArea OtimizaçõesOptimizations
Tamanho da VMVM size DS3 ou superior para edição SQL Server Enterprise.DS3 or higher for SQL Server Enterprise edition.

DS2 ou superior para edição padrão do SQL Server e edição Web.DS2 or higher for SQL Server Standard edition and Web edition.
ArmazenamentoStorage Utilize uma família VM que suporte o armazenamento Premium.Use a VM family that supports Premium storage.
DiscosDisks Utilize um mínimo de dois discos de dados (um para ficheiros de registo e outro para ficheiros de dados e TempDB), e escolha o tamanho do disco com base nas suas necessidades de capacidade.Use a minimum of two data disks (one for log files and one for data file and TempDB), and choose the disk size based on your capacity needs. Desacrie as localizações predefinidos do ficheiro de dados para estes discos durante a instalação do SQL Server.Set the default data file locations to these disks during the SQL Server install.

Evite utilizar sistema operativo ou discos temporários para armazenamento de bases de dados ou registo de registo.Avoid using operating system or temporary disks for database storage or logging.
Stripe vários discos de dados Azure para obter uma produção de IO aumentada usando espaços de armazenamento.Stripe multiple Azure data disks to get increased IO throughput using Storage Spaces.

Formato com tamanhos de atribuição documentados.Format with documented allocation sizes.
I/OI/O Ativar a inicialização instantânea do ficheiro para ficheiros de dados.Enable instant file initialization for data files.

Limite o autogrow nas bases de dados com incrementos fixos razoavelmente pequenos (64 MB-256 MB).Limit autogrow on the databases with reasonably small fixed increments (64 MB-256 MB).

Desative o auto-shrink na base de dados.Disable autoshrink on the database.

Configurar as localizações de ficheiros de cópias de segurança e de ficheiros de base de dados predefinidos em discos de dados, não no disco do sistema operativo.Set up default backup and database file locations on data disks, not the operating system disk.

Ativar páginas fechadas.Enable locked pages.

Aplicar pacotes de serviços SQL Server e atualizações cumulativas.Apply SQL Server service packs and cumulative updates.
Específico de recursosFeature-specific Volte a fazer o back-up diretamente para o armazenamento de bolhas (se suportado pela versão SQL Server em uso).Back up directly to blob storage (if supported by the SQL Server version in use).

Para obter mais informações sobre como e porquê fazer estas otimizações, reveja os detalhes e orientações fornecidos nas seguintes secções.For more information on how and why to make these optimizations, review the details and guidance provided in the following sections.

Orientação do tamanho VMVM size guidance

Para aplicações sensíveis ao desempenho, recomenda-se os seguintes tamanhos VM:For performance-sensitive applications, the following VM sizes are recommended:

  • Edição da SQL Server Enterprise: DS3 ou superiorSQL Server Enterprise edition: DS3 or higher

  • Edição Padrão do SQL Server e edição Web: DS2 ou superiorSQL Server Standard edition and Web edition: DS2 or higher

Com o Azure Stack Hub, não há diferença de desempenho entre a série dS e DS_v2 vm.With Azure Stack Hub, there's no performance difference between the DS and DS_v2 VM family series.

Orientações de armazenamentoStorage guidance

Os VMs da série DS (juntamente com a série DSv2) em Azure Stack Hub fornecem o máximo de disco do sistema operativo e a produção de disco de dados (IOPS).DS-series (along with DSv2-series) VMs in Azure Stack Hub provide the maximum operating system disk and data disk throughput (IOPS). Um VM da série DS ou DSv2 fornece até 1.000 IOPS para o disco do sistema operativo e até 2.300 IOPS por disco de dados, independentemente do tipo ou tamanho do disco escolhido.A VM from the DS or DSv2 series provides up to 1,000 IOPS for the operating system disk and up to 2,300 IOPS per data disk, no matter the type or size of the chosen disk.

A produção de disco de dados é determinada exclusivamente com base na série familiar VM.Data disk throughput is determined uniquely based on the VM family series. Pode consultar este artigo para identificar o rendimento do disco de dados por séries familiares VM.You can refer to this article to identify the data disk throughput per VM family series.

Nota

Para cargas de trabalho de produção, selecione um VM série DS ou DSv2 para fornecer o máximo de IOPS possível no disco do sistema operativo e discos de dados.For production workloads, select a DS-series or DSv2-series VM to provide the maximum possible IOPS on the operating system disk and data disks.

Ao criar uma conta de armazenamento no Azure Stack Hub, a opção de geo-replicação não tem efeito porque esta capacidade não está disponível no Azure Stack Hub.When creating a storage account in Azure Stack Hub, the geo-replication option has no effect because this capability isn't available in Azure Stack Hub.

Orientação de discosDisks guidance

Existem três tipos principais de discos num Azure Stack Hub VM:There are three main disk types on an Azure Stack Hub VM:

  • Disco do sistema operativo: Quando cria um Azure Stack Hub VM, a plataforma anexa pelo menos um disco (rotulado como unidade C) ao VM para o disco do seu sistema operativo.Operating system disk: When you create an Azure Stack Hub VM, the platform attaches at least one disk (labeled as the C drive) to the VM for your operating system disk. Este disco é um VHD armazenado como uma bolha de página no armazenamento.This disk is a VHD stored as a page blob in storage.

  • Disco temporário: Os VM do Azure Stack Hub contêm outro disco chamado disco temporário (rotulado como unidade D).Temporary disk: Azure Stack Hub VMs contain another disk called the temporary disk (labeled as the D drive). Este é um disco no nó que pode ser usado para o espaço de risco.This is a disk on the node that can be used for scratch space.

  • Discos de dados: Pode anexar discos adicionais ao seu VM como discos de dados, e estes discos são armazenados como bolhas de página.Data disks: You can attach additional disks to your VM as data disks, and these disks are stored in storage as page blobs.

As seguintes secções descrevem recomendações para a utilização destes discos diferentes.The following sections describe recommendations for using these different disks.

Disco do sistema operativoOperating system disk

Um disco de sistema operativo é um VHD que pode iniciar e montar como uma versão em execução de um sistema operativo e é rotulado como unidade C.An operating system disk is a VHD that you can boot and mount as a running version of an operating system and is labeled as C drive.

Disco temporárioTemporary disk

A unidade de armazenamento temporária, rotulada como unidade D, não é persistente.The temporary storage drive, labeled as the D drive, isn't persistent. Não guarde nenhum dado que não esteja disposto a perder na unidade D.Don't store any data you're unwilling to lose on the D drive. Isto inclui os ficheiros de base de dados do utilizador e ficheiros de registo de transações de utilizadores.This includes your user database files and user transaction log files.

Recomendamos o armazenamento de TempDB num disco de dados, uma vez que cada disco de dados fornece um máximo de 2.300 IOPS por disco de dados.We recommend storing TempDB on a data disk as each data disk provides a maximum of up to 2,300 IOPS per data disk.

Discos de dadosData disks

  • Utilize discos de dados para ficheiros de dados e registos.Use data disks for data and log files. Se não estiver a utilizar a desmontagem de discos, utilize dois discos de dados de um VM que suporta o armazenamento Premium, onde um disco contém os ficheiros de registo e o outro contém os dados e ficheiros TempDB.If you're not using disk striping, use two data disks from a VM that supports Premium storage, where one disk contains the log files and the other contains the data and TempDB files. Cada disco de dados fornece uma série de IOPS dependendo da família VM, conforme descrito nos tamanhos VM suportados no Azure Stack Hub.Each data disk provides a number of IOPS depending on the VM family, as described in VM sizes supported in Azure Stack Hub. Se estiver a utilizar uma técnica de striping em disco, como espaços de armazenamento, coloque todos os dados e registar ficheiros na mesma unidade (incluindo o TempDB).If you're using a disk-striping technique, such as Storage Spaces, place all data and log files on the same drive (including TempDB). Esta configuração dá-lhe o número máximo de IOPS disponível para o SQL Server consumir, independentemente do ficheiro que necessite deles em qualquer momento.This configuration gives you the maximum number of IOPS available for SQL Server to consume, no matter which file needs them at any particular time.

Nota

Ao providenciar um SQL Server VM no portal, tem a opção de editar a sua configuração de armazenamento.When you provision a SQL Server VM in the portal, you have the option of editing your storage configuration. Dependendo da sua configuração, o Azure Stack Hub configura um ou mais discos.Depending on your configuration, Azure Stack Hub configures one or more disks. Vários discos são combinados numa única piscina de armazenamento.Multiple disks are combined into a single storage pool. Tanto os ficheiros de dados como os ficheiros de registo residem juntos nesta configuração.Both the data and log files reside together in this configuration.

  • Tiragem de disco: Para obter mais produção, pode adicionar discos de dados adicionais e utilizar a desmontagem do disco.Disk striping: For more throughput, you can add additional data disks and use disk striping. Para determinar o número de discos de dados de que necessita, analise o número de IOPS necessário para os seus ficheiros de registo e para os seus ficheiros de dados e tempDB.To determine the number of data disks you need, analyze the number of IOPS required for your log files and for your data and TempDB files. Note que os limites do IOPS são por disco de dados baseados na família da série VM, e não com base no tamanho VM.Notice that IOPS limits are per data disk based on the VM series family, and not based on the VM size. No entanto, os limites de largura de banda da rede baseiam-se no tamanho do VM.Network bandwidth limits, however, are based on the VM size. Consulte as tabelas dos tamanhos VM no Azure Stack Hub para obter mais detalhes.See the tables on VM sizes in Azure Stack Hub for more detail. Utilize as seguintes diretrizes:Use the following guidelines:

    • Para o Windows Server 2012 ou posterior, utilize espaços de armazenamento com as seguintes diretrizes:For Windows Server 2012 or later, use Storage Spaces with the following guidelines:

      1. Decreta a interleave (tamanho das listras) para 64 KB (65.536 bytes) para o processamento de transações online (OLTP) e 256 KB (262.144 bytes) para trabalhos de armazenamento de dados para evitar o impacto do desempenho devido ao desalinhamento da partição.Set the interleave (stripe size) to 64 KB (65,536 bytes) for online transaction processing (OLTP) workloads and 256 KB (262,144 bytes) for data warehousing workloads to avoid performance impact due to partition misalignment. Isto tem de ser definido com o PowerShell.This must be set with PowerShell.

      2. Definir contagem de colunas = número de discos físicos.Set column count = number of physical disks. Utilize o PowerShell ao configurar mais de oito discos (não o Server Manager UI).Use PowerShell when configuring more than eight disks (not Server Manager UI).

        Por exemplo, o seguinte PowerShell cria uma nova piscina de armazenamento com o tamanho interleave definido para 64 KB e o número de colunas a 2:For example, the following PowerShell creates a new storage pool with the interleave size set to 64 KB and the number of columns to 2:

        $PoolCount = Get-PhysicalDisk -CanPool $True
        $PhysicalDisks = Get-PhysicalDisk | Where-Object {$_.FriendlyName -like "*2" -or $_.FriendlyName -like "*3"}
        
        New-StoragePool -FriendlyName "DataFiles" -StorageSubsystemFriendlyName "Storage Spaces*" -PhysicalDisks $PhysicalDisks | New-VirtualDisk -FriendlyName "DataFiles" -Interleave 65536 -NumberOfColumns 2 -ResiliencySettingName simple -UseMaximumSize |Initialize-Disk -PartitionStyle GPT -PassThru |New-Partition -AssignDriveLetter -UseMaximumSize |Format-Volume -FileSystem NTFS -NewFileSystemLabel "DataDisks" -AllocationUnitSize 65536 -Confirm:$false
        
  • Determine o número de discos associados à sua piscina de armazenamento com base nas suas expectativas de carga.Determine the number of disks associated with your storage pool based on your load expectations. Tenha em mente que diferentes tamanhos de VM permitem diferentes números de discos de dados anexados.Keep in mind that different VM sizes allow different numbers of attached data disks. Para obter mais informações, consulte os tamanhos VM suportados no Azure Stack Hub.For more information, see VM sizes supported in Azure Stack Hub.

  • Para obter o máximo de IOPS possível para discos de dados, a recomendação é adicionar o número máximo de discos de dados suportados pelo seu tamanho VM e utilizar a tiragem de disco.To get the maximum possible IOPS for data disks, the recommendation is to add the maximum number of data disks supported by your VM size and to use disk striping.

  • Tamanho da unidade de atribuição NTFS: Ao formatar o disco de dados, recomendamos que utilize um tamanho de unidade de atribuição de 64 KB para ficheiros de dados e registos, bem como o TempDB.NTFS allocation unit size: When formatting the data disk, we recommend you use a 64-KB allocation unit size for data and log files as well as TempDB.

  • Práticas de gestão de discos: Ao remover um disco de dados, pare o serviço SQL Server durante a alteração.Disk management practices: When removing a data disk, stop the SQL Server service during the change. Além disso, não altere as definições de cache nos discos, uma vez que não fornece melhorias de desempenho.Also, don't change cache settings on the disks as it doesn't provide any performance improvements.

Aviso

A não paragem do Serviço SQL durante estas operações pode causar corrupção na base de dados.Failure to stop the SQL Service during these operations can cause database corruption.

Orientação de I/OI/O guidance

  • Considere permitir a inicialização instantânea do ficheiro para reduzir o tempo necessário para a atribuição inicial de ficheiros.Consider enabling instant file initialization to reduce the time that is required for initial file allocation. Para tirar partido da inicialização instantânea do ficheiro, concede à conta de serviço SQL Server (MSSQLSERVER) SE_MANAGE_VOLUME_NAME e adicioná-la à política de segurança de Tarefas de Manutenção de Volume de Desempenho.To take advantage of instant file initialization, you grant the SQL Server (MSSQLSERVER) service account with SE_MANAGE_VOLUME_NAME and add it to the Perform Volume Maintenance Tasks security policy. Se estiver a utilizar uma imagem da plataforma SQL Server para o Azure, a conta de serviçopadrão (NT Service\MSSQLSERVER) não é adicionada à política de segurança de Tarefas de Manutenção de Volume de Execução.If you're using a SQL Server platform image for Azure, the default service account (NT Service\MSSQLSERVER) isn't added to the Perform Volume Maintenance Tasks security policy. Por outras palavras, a inicialização instantânea do ficheiro não está ativada numa imagem da plataforma SQL Server Azure.In other words, instant file initialization isn't enabled in a SQL Server Azure platform image. Depois de adicionar a conta de serviço SQL Server à política de segurança de Tarefas de Manutenção de Volume de Desempenho, reinicie o serviço SQL Server.After adding the SQL Server service account to the Perform Volume Maintenance Tasks security policy, restart the SQL Server service. Pode haver considerações de segurança para a utilização desta funcionalidade.There could be security considerations for using this feature. Para obter mais informações, consulte a inicialização do ficheiro database.For more information, see Database File Initialization.

  • O autogrow é uma contingência para um crescimento inesperado.Autogrow is a contingency for unexpected growth. Não gere os seus dados e regista o crescimento no dia-a-dia com o autogrow.Don't manage your data and log growth on a day-to-day basis with autogrow. Se for utilizado um auto-sobrancelha, pré-cresça o ficheiro utilizando o interruptor Tamanho.If autogrow is used, pre-grow the file using the Size switch.

  • Certifique-se de que o autoshrink está desativado para evitar sobrecargas desnecessárias que possam afetar negativamente o desempenho.Make sure autoshrink is disabled to avoid unnecessary overhead that can negatively affect performance.

  • Configurar a cópia de segurança predefinidora e as localizações dos ficheiros de base de dados.Setup default backup and database file locations. Utilize as recomendações neste artigo e faça as alterações na janela de propriedades do Servidor.Use the recommendations in this article and make the changes in the Server properties window. Para obter instruções, consulte Ver ou Alterar as Localizações Predefinidos para Ficheiros de Dados e Registo (SQL Server Management Studio).For instructions, see View or Change the Default Locations for Data and Log Files (SQL Server Management Studio). A imagem que se segue mostra onde fazer estas alterações:The following screenshot shows where to make these changes:

    Ver ou Alterar as Localizações Predefinidos

  • Ativar páginas bloqueadas para reduzir io e quaisquer atividades de paging.Enable locked pages to reduce IO and any paging activities. Para obter mais informações, consulte Ativar as páginas de bloqueio na opção Memória (Windows).For more information, see Enable the Lock Pages in Memory Option (Windows).

  • Considere comprimir quaisquer ficheiros de dados ao transferir para dentro/para fora do Azure Stack Hub, incluindo cópias de segurança.Consider compressing any data files when transferring in/out of Azure Stack Hub, including backups.

Orientação específica para recursosFeature-specific guidance

Algumas implementações podem obter benefícios adicionais de desempenho usando técnicas de configuração mais avançadas.Some deployments may achieve additional performance benefits using more advanced configuration techniques. A lista a seguir destaca algumas funcionalidades do SQL Server que podem ajudá-lo a obter um melhor desempenho:The following list highlights some SQL Server features that may help you achieve better performance:

  • De volta ao armazém do Azure. storage.Back up to Azure storage. Ao fazer cópias de segurança para o SQL Server em execução em VMs Azure Stack Hub, pode utilizar backup do SQL Server para URL.When making backups for SQL Server running in Azure Stack Hub VMs, you can use SQL Server Backup to URL. Esta funcionalidade está disponível a partir do SQL Server 2012 SP1 CU2 e recomendada para fazer o backup dos discos de dados anexados.This feature is available starting with SQL Server 2012 SP1 CU2 and recommended for backing up to the attached data disks.

    Quando fizer cópia de segurança ou restaurar o armazenamento Azure, siga as recomendações fornecidas no SQL Server Backup para URL Best Practices e Troubleshooting and Restoring From Backups Armazenados no Microsoft Azure.When you backup or restore using Azure storage, follow the recommendations provided in SQL Server Backup to URL Best Practices and Troubleshooting and Restoring From Backups Stored in Microsoft Azure. Também pode automatizar estas cópias de segurança utilizando cópias de segurança automatizadas para O Servidor SQL em VMs Azure.You can also automate these backups using Automated Backup for SQL Server in Azure VMs.

  • Volte para o armazenamento do Azure Stack Hub.Back up to Azure Stack Hub storage. Você pode voltar para o armazenamento Azure Stack Hub de forma semelhante ao do back up para Azure Storage.You can back up to Azure Stack Hub storage in a similar fashion as with backing up to Azure Storage. Quando criar uma cópia de segurança dentro do SQL Server Management Studio (SSMS), tem de introduzir manualmente a informação de configuração.When you create a backup inside SQL Server Management Studio (SSMS), you need to enter the configuration information manually. Não é possível utilizar o SSMS para criar o recipiente de armazenamento ou a Assinatura de Acesso Partilhado.You can't use SSMS to create the storage container or the Shared Access Signature. O SSMS apenas liga-se a subscrições do Azure, e não a subscrições do Azure Stack Hub.SSMS only connects to Azure subscriptions, not Azure Stack Hub subscriptions. Em vez disso, precisa de criar a conta de armazenamento, o contentor e a assinatura de acesso partilhado no portal Azure Stack Hub ou com o PowerShell.Instead, you need to create the storage account, container, and Shared Access Signature in the Azure Stack Hub portal or with PowerShell.

    Backup do servidor SQL

    Nota

    A Assinatura de Acesso Partilhado é o símbolo SAS do portal Azure Stack Hub, sem o principal '?'The Shared Access Signature is the SAS token from the Azure Stack Hub portal, without the leading ‘?' na corda.in the string. Se utilizar a função de cópia a partir do portal, tem de eliminar o ''?' de te principalIf you use the copy function from the portal, you need to delete the leading ‘?' para o símbolo funcionar dentro do SQL Server.for the token to work within SQL Server.

    Uma vez configurado e configurado o Destino de Cópia de Segurança no SQL Server, pode voltar ao armazenamento de blob do Azure Stack Hub.Once you have the Backup Destination set up and configured in SQL Server, you can then back up to the Azure Stack Hub blob storage.

Passos seguintesNext steps

Utilizando serviços ou aplicativos de construção para Azure Stack HubUsing services or building apps for Azure Stack Hub