Exercício – Monitorizar e resolver problemas de desempenho

  • 10 minutos

Neste exercício, você aprenderá a monitorar e solucionar um problema de desempenho com o SQL do Azure usando ferramentas e recursos novos e conhecidos.

Configurar: usar scripts para implantar o Banco de Dados SQL do Azure

A sessão de terminal à direita, Azure Cloud Shell, permite que você interaja com o Azure usando um navegador. Para este exercício, você executará um script para criar seu ambiente, uma instância do Banco de Dados SQL do Azure com o AdventureWorks banco de dados. (O banco de dados de exemplo AdventureWorksLT menor e mais simples é usado, mas vamos chamá-lo AdventureWorks para evitar confusão.) No script, você será solicitado a fornecer uma senha e seu endereço IP local para permitir que seu dispositivo se conecte ao banco de dados.

Este script demora entre 3 e 5 minutos a ser concluído. Certifique-se de que anota a palavra-passe, o ID exclusivo e a região. Estes não serão apresentados novamente.

  1. Comece por obter o seu endereço IP local. Certifique-se de que está desligado de todos os serviços de VPN e abra um terminal do PowerShell local no seu dispositivo. Execute o seguinte comando e anote o endereço IP resultante:

    (Invoke-WebRequest -Uri "https://ipinfo.io/ip").Content

  2. No Azure Cloud Shell à direita, insira o código a seguir e, quando solicitado, forneça uma senha complexa e seu endereço IP público local recuperado na etapa anterior. Pressione Enter para executar a última linha do script.

    $adminSqlLogin = "cloudadmin"
$password = Read-Host "Your username is 'cloudadmin'. Please enter a password for your Azure SQL Database server that meets the password requirements"
# Prompt for local ip address
$ipAddress = Read-Host "Disconnect your VPN, open PowerShell on your machine and run '(Invoke-WebRequest -Uri "https://ipinfo.io/ip").Content'. Please enter the value (include periods) next to 'Address':"
# Get resource group and location and random string
$resourceGroup = Get-AzResourceGroup | Where ResourceGroupName -like "<rgn>[sandbox resource group name]</rgn>"
$resourceGroupName = "<rgn>[sandbox resource group name]</rgn>"
$uniqueID = Get-Random -Minimum 100000 -Maximum 1000000
$storageAccountName = "mslearnsa"+$uniqueID
$location = $resourceGroup.Location
$serverName = "aw-server$($uniqueID)"

  3. Execute o seguinte script no Azure Cloud Shell. Salve a saída; Você precisará dessas informações ao longo do módulo. Pressione Enter depois de colar o código, para que a última linha de código imprima a saída que você precisa.

    Write-Host "Please note your unique ID for future exercises in this module:"  
Write-Host $uniqueID
Write-Host "Your resource group name is:"
Write-Host $resourceGroupName
Write-Host "Your resources were deployed in the following region:"
Write-Host $location
Write-Host "Your server name is:"
Write-Host $serverName

    Gorjeta

    Salve a saída e anote sua senha, ID exclusivo e servidor. Você precisará desses itens ao longo do módulo.

  4. Execute o seguinte script para implementar uma Base de Dados SQL do Azure e um servidor lógico com o exemplo AdventureWorks. Este script adiciona o seu endereço IP como uma regra de firewall, permite a Segurança Avançada de Dados e cria uma conta de armazenamento para utilização nos restantes exercícios deste módulo. O script pode levar vários minutos para ser concluído e será pausado várias vezes. Aguarde um prompt de comando.

    # The logical server name has to be unique in the system
$serverName = "aw-server$($uniqueID)"
# The sample database name
$databaseName = "AdventureWorks"
# The storage account name has to be unique in the system
$storageAccountName = $("sql$($uniqueID)")
# Create a new server with a system wide unique server name
$server = New-AzSqlServer -ResourceGroupName $resourceGroupName `
    -ServerName $serverName `
    -Location $location `
    -SqlAdministratorCredentials $(New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $adminSqlLogin, $(ConvertTo-SecureString -String $password -AsPlainText -Force))
# Create a server firewall rule that allows access from the specified IP range and all Azure services
$serverFirewallRule = New-AzSqlServerFirewallRule `
    -ResourceGroupName $resourceGroupName `
    -ServerName $serverName `
    -FirewallRuleName "AllowedIPs" `
    -StartIpAddress $ipAddress -EndIpAddress $ipAddress 
$allowAzureIpsRule = New-AzSqlServerFirewallRule `
    -ResourceGroupName $resourceGroupName `
    -ServerName $serverName `
    -AllowAllAzureIPs
# Create a database
$database = New-AzSqlDatabase  -ResourceGroupName $resourceGroupName `
    -ServerName $serverName `
    -DatabaseName $databaseName `
    -SampleName "AdventureWorksLT" `
    -Edition "GeneralPurpose" -Vcore 2 -ComputeGeneration "Gen5"
# Enable Advanced Data Security
$advancedDataSecurity = Enable-AzSqlServerAdvancedDataSecurity `
    -ResourceGroupName $resourceGroupName `
    -ServerName $serverName
# Create a Storage Account
$storageAccount = New-AzStorageAccount -ResourceGroupName $resourceGroupName `
    -AccountName $storageAccountName `
    -Location $location `
    -Type "Standard_LRS"

  5. No dispositivo local, abra o SQL Server Management Studio (SSMS) para criar uma nova conexão com o servidor lógico.

  6. Na caixa de diálogo Conectar ao login do servidor, forneça as seguintes informações:

    Campo Value
    Tipo de servidor Mecanismo de Banco de Dados (padrão).
    Nome do servidor O $serverName que foi retornado no Cloud Shell, além do restante do URI. Por exemplo: aw-server<unique ID>.database.windows.net.
    Autenticação Autenticação do SQL Server (padrão).
    Iniciar sessão cloudadmin O adminSqlLogin atribuído na etapa 1 deste exercício.
    Palavra-passe A palavra-passe que forneceu no passo 1 deste exercício.
    Memorizar palavra-passe verificado

  7. Selecione Ligar.

    Captura de ecrã da caixa de diálogo de ligação da Base de Dados SQL no SSMS.

    Nota

    Consoante a configuração local (por exemplo, VPN), o endereço IP de cliente poderá ser diferente do endereço IP que o portal do Azure utilizou durante a implementação. Se isso acontecer, você receberá a seguinte mensagem: "O endereço IP do cliente não tem acesso ao servidor. Entre em uma conta do Azure e crie uma nova regra de firewall para habilitar o acesso." Se receber esta mensagem, inicie sessão utilizando a conta que está a utilizar para a sandbox e adicione uma regra de firewall para o endereço IP do cliente. Pode concluir todos estes passos com o assistente no SSMS.

Preparar o exercício ao carregar e editar scripts

Você pode encontrar todos os scripts para este exercício na pasta 04-Performance\monitor_and_scale no repositório GitHub que você clonou ou no arquivo zip que você baixou. Vamos preparar o exercício ao carregar e editar scripts.

  1. No SSMS, no Pesquisador de Objetos, expanda a pasta Bancos de Dados e selecione o banco de dados AdventureWorks .

  2. Selecione Arquivo>Abrir>arquivo e abra o script dmexecrequests.sql. A janela do editor de consultas deverá ter um aspeto semelhante ao texto abaixo:

    SELECT er.session_id, er.status, er.command, er.wait_type, er.last_wait_type, er.wait_resource, er.wait_time
FROM sys.dm_exec_requests er
INNER JOIN sys.dm_exec_sessions es
ON er.session_id = es.session_id
AND es.is_user_process = 1;

  3. Utilize o mesmo método no SQL Server Management Studio para carregar o script dmdbresourcestats.sql. A nova janela do editor de consultas deverá ter um aspeto semelhante ao texto abaixo:

    SELECT * FROM sys.dm_db_resource_stats;

    Esta vista de gestão dinâmica (DMV) irá monitorizar a utilização global de recursos da sua carga de trabalho na Base de Dados SQL do Azure. Por exemplo, monitoriza a CPU, E/S e memória.

  4. Abra e edite o script sqlworkload.cmd (que usará o programa ostress.exe).

    • Substitua o unique_id que você salvou do script de implantação no nome do servidor.
    • Substitua a senha usada para a entrada do servidor do Banco de Dados SQL do Azure pelo -P parameter.
    • Salve as alterações no script.

Executar a carga de trabalho

Nesta tarefa, você executará uma carga de trabalho em uma consulta T-SQL para observar seu desempenho simulando usuários simultâneos.

  1. Use o SSMS para abrir o arquivo de script topcustomersales.sql para observar a consulta. Não irá executar a consulta a partir do SSMS. A janela do editor de consultas deverá ter um aspeto semelhante ao texto abaixo:

    DECLARE @x int
DECLARE @y float
SET @x = 0;
WHILE (@x < 10000)
BEGIN
SELECT @y = sum(cast((soh.SubTotal*soh.TaxAmt*soh.TotalDue) as float))
FROM SalesLT.Customer c
INNER JOIN SalesLT.SalesOrderHeader soh
ON c.CustomerID = soh.CustomerID
INNER JOIN SalesLT.SalesOrderDetail sod
ON soh.SalesOrderID = sod.SalesOrderID
INNER JOIN SalesLT.Product p
ON p.ProductID = sod.ProductID
GROUP BY c.CompanyName
ORDER BY c.CompanyName;
SET @x = @x + 1;
END
GO

    Esta base de dados é pequena. A consulta para recuperar uma lista de clientes e suas informações de vendas associadas, ordenadas pelos clientes com mais vendas, não deve gerar um grande conjunto de resultados. Você pode ajustar essa consulta reduzindo o número de colunas no conjunto de resultados, mas elas são necessárias para fins de demonstração deste exercício.

  2. Em um prompt de comando do PowerShell, digite o seguinte comando para mover para o diretório correto para este exercício. Substitua <base directory> por seu ID de usuário e caminho para este módulo :

    cd <base directory>\04-Performance\monitor_and_scale

  3. Execute a carga de trabalho com o seguinte comando:

    .\sqlworkload.cmd

    Este script utilizará 10 utilizadores em simultâneo que executam duas vezes a consulta da carga de trabalho. Repare que o próprio script executa um lote, mas percorre um ciclo 10 000 vezes. Além disso, atribuiu o resultado a uma variável, eliminando assim quase todo o tráfego de conjunto de resultados para o cliente. Isso não é necessário, mas ajuda a mostrar uma carga de trabalho de CPU "pura" executada inteiramente no servidor.

    Gorjeta

    Se não estiver a ver o comportamento de utilização da CPU com esta carga de trabalho no seu ambiente, pode ajustar o -n parameter para o número de utilizadores e o -r parameter para iterações.

    A saída no prompt de comando deve ser semelhante à seguinte saída:

    [datetime] [ostress PID] Max threads setting: 10000
[datetime] [ostress PID] Arguments:
[datetime] [ostress PID] -S[server].database.windows.net
[datetime] [ostress PID] -isqlquery.sql
[datetime] [ostress PID] -U[user]
[datetime] [ostress PID] -dAdventureWorks
[datetime] [ostress PID] -P********
[datetime] [ostress PID] -n10
[datetime] [ostress PID] -r2
[datetime] [ostress PID] -q
[datetime] [ostress PID] Using language id (LCID): 1024 [English_United States.1252] for character formatting with NLS: 0x0006020F and Defined: 0x0006020F
[datetime] [ostress PID] Default driver: SQL Server Native Client 11.0
[datetime] [ostress PID] Attempting DOD5015 removal of [directory]\sqlquery.out]
[datetime] [ostress PID] Attempting DOD5015 removal of [directory]\sqlquery_1.out]
[datetime] [ostress PID] Attempting DOD5015 removal of [directory]\sqlquery_2.out]
[datetime] [ostress PID] Attempting DOD5015 removal of [directory]\sqlquery_3.out]
[datetime] [ostress PID] Attempting DOD5015 removal of [directory]\sqlquery_4.out]
[datetime] [ostress PID] Attempting DOD5015 removal of [directory]\sqlquery_5.out]
[datetime] [ostress PID] Attempting DOD5015 removal of [directory]\sqlquery_6.out]
[datetime] [ostress PID] Attempting DOD5015 removal of [directory]\sqlquery_7.out]
[datetime] [ostress PID] Attempting DOD5015 removal of [directory]\sqlquery_8.out]
[datetime] [ostress PID] Attempting DOD5015 removal of [directory]\sqlquery_9.out]
[datetime] [ostress PID] Starting query execution...
[datetime] [ostress PID]  BETA: Custom CLR Expression support enabled.
[datetime] [ostress PID] Creating 10 thread(s) to process queries
[datetime] [ostress PID] Worker threads created, beginning execution...

Observar o desempenho da carga de trabalho

Vamos usar as consultas do Detran que você carregou anteriormente para observar o desempenho.

  1. No SSMS, execute a consulta que carregou anteriormente para monitorizar dm_exec_requests (dmexecrequests.sql) e, assim, observar os pedidos ativos. Execute esta consulta cinco ou seis vezes e observe alguns dos resultados:

    SELECT er.session_id, er.status, er.command, er.wait_type, er.last_wait_type, er.wait_resource, er.wait_time
FROM sys.dm_exec_requests er
INNER JOIN sys.dm_exec_sessions es
ON er.session_id = es.session_id
AND es.is_user_process = 1;

    Você deve ver que muitas das solicitações têm um status de RUNNABLE, e last_wait_type é SOS_SCHEDULER_YIELD. Um indicador de muitas RUNNABLE solicitações e muitas SOS_SCHEDULER_YIELD esperas é uma possível falta de recursos da CPU para consultas ativas.

    Nota

    Você pode ver uma ou mais solicitações ativas com um comando de SELECT e um wait_type de XE_LIVE_TARGET_TVF. Trata-se de consultas executadas por serviços geridos pela Microsoft. Ajudam em capacidades, como informações de desempenho, ao utilizar eventos alargados. A Microsoft não publica os detalhes destas sessões.

    Deixe esta janela do editor de consultas aberta. Irá executá-la novamente no próximo exercício.

  2. Execute a consulta no SSMS que carregou anteriormente para monitorizar sys.dm_db_resource_stats (dmdbresourcestats.sql). Execute a consulta para ver os resultados desta DMV três ou quatro vezes.

    SELECT * FROM sys.dm_db_resource_stats;

    Esta DMV regista um instantâneo da utilização dos recursos da base de dados a cada 15 segundos (mantida durante 1 hora). Deverá ver a coluna avg_cpu_percent perto dos 100% para vários dos instantâneos. Este é um sintoma de uma carga de trabalho que ultrapassa os limites dos recursos da CPU da base de dados.

    Para um ambiente local do SQL Server, você normalmente usaria uma ferramenta específica para o sistema operacional para controlar o uso geral de recursos, como uma CPU. Por exemplo, pode utilizar o Monitor de Desempenho do Windows para este efeito. Se você executasse este exemplo em um SQL Server local ou SQL Server em uma máquina virtual com duas CPUs, veria quase 100% de utilização da CPU no servidor.

    Nota

    Você pode executar outro DMV, sys.resource_stats, no contexto do banco de dados do servidor do Banco de Dados SQL do master Azure para ver o uso de recursos para todos os bancos de dados do Banco de Dados SQL do Azure associados ao servidor. Esta vista é menos granular e mostra a utilização de recursos a cada cinco minutos (mantida durante 14 dias).

    Deixe esta janela do editor de consultas aberta. Irá executá-la novamente no próximo exercício.

  3. Permita que a carga de trabalho seja concluída e anote a duração global. Quando a carga de trabalho for concluída, deverá ver resultados como o seguinte e o regresso à linha de comandos:

    [datetime] [ostress PID] Total IO waits: 0, Total IO wait time: 0 (ms)
[datetime] [ostress PID] OSTRESS exiting normally, elapsed time: 00:01:22.637

    O período de duração pode variar, mas normalmente demora, pelo menos, 1 a 3 minutos. Certifique-se de que permite a execução até à conclusão. Quando a carga de trabalho estiver concluída, você retornará ao prompt de comando.

Utilizar o Arquivo de Consultas para mais análises

O Arquivo de Consultas é uma capacidade no SQL Server para controlar a execução do desempenho das consultas. Os dados de desempenho são armazenados na base de dados de utilizador. O Arquivo de Consultas não está ativado por predefinição para bases de dados criadas no SQL Server, mas está ativado por predefinição para a Base de Dados SQL do Azure (e o Azure SQL Managed Instance).

O Arquivo de Consultas inclui uma série de vistas de catálogo do sistema para ver os dados de desempenho. O SSMS fornece relatórios com estas vistas.

  1. Com o Object Explorer no SSMS, abra a pasta do Arquivo de Consultas para localizar o relatório Top Resource Consuming Queries (Consultas Principais de Consumo de Recursos).

    Captura de ecrã do Arquivo de Consultas.

  2. Selecione o relatório para saber que consultas consumiram a maior média de recursos e detalhes de execução dessas consultas. Com base na carga de trabalho até agora, o seu relatório deve ter o seguinte aspeto:

    Captura de ecrã do relatório da consulta principal.

    A consulta apresentada é a consulta SQL da carga de trabalho para vendas de clientes. Este relatório tem três componentes: consultas com a duração total elevada (pode alterar a métrica), o plano de consulta associado e as estatísticas de tempo de execução, bem como o plano de consulta associado num mapa visual.

  3. Selecione o gráfico de barras da consulta (o valor query_id pode ser diferente para o seu sistema). Os resultados devem ter o seguinte aspeto:

    Captura de ecrã do ID da consulta.

    Pode ver a duração total da consulta e do texto da consulta.

  4. À direita deste gráfico de barras encontra-se um gráfico de estatísticas do plano de consulta associado à consulta. Paire o rato sobre o ponto associado ao plano. Os resultados devem ter o seguinte aspeto:

    Captura de ecrã de estatísticas de consultas lentas.

    Tenha em atenção a duração média da consulta. Os períodos podem variar, mas compare esta duração média com a média do tempo de espera desta consulta. Mais adiante, iremos introduzir uma melhoria de desempenho e fará esta comparação novamente para ver a diferença.

  5. O componente final é o plano de consulta visual. O plano de consulta para esta consulta tem o seguinte aspeto:

    Captura de ecrã do plano de consulta da carga de trabalho.

    Esta tabela de banco de dados tem tão poucas linhas que não precisa de um plano; pode ser ineficiente. Ajustar a consulta não melhorará o desempenho em uma quantidade mensurável. Você pode ver um aviso no plano sobre a falta de estatísticas para uma das colunas para a busca de índice clusterizado. Este não é um fator que afete o desempenho global.

  6. A seguir ao relatório Principais Consultas que Consomem Recursos no SSMS está um relatório chamado Estatísticas de Espera de Consulta. Sabe, de diagnósticos anteriores, que um elevado número de pedidos estavam constantemente no estado EXECUTÁVEL, juntamente com quase 100% de utilização da CPU. O Arquivo de Consultas inclui relatórios para analisar possíveis estrangulamentos de desempenho devido a esperas de recursos. Selecione este relatório e paire o rato sobre o gráfico de barras. Os resultados devem ter o seguinte aspeto:

    Captura de ecrã da estatística de espera principal.

    Pode ver que a categoria de espera principal é a CPU (isto é equivalente ao wait_type SOS_SCHEDULER_YIELD, que pode ver visto em sys.dm_os_wait_stats) e a média do tempo de espera.

  7. Selecione o gráfico de barras da CPU no relatório. A consulta principal, aguardando CPU, é a consulta da carga de trabalho que você está usando.

    Captura de ecrã da consulta da estatística de espera principal.

    Observe que o tempo médio de espera para a CPU nesta consulta é uma alta porcentagem da duração média geral da consulta.

    Considerando as evidências, sem qualquer ajuste de consulta, nossa carga de trabalho requer mais capacidade de CPU do que implantamos para nossa instância do Banco de Dados SQL do Azure.

  8. Feche ambos os relatórios do Repositório de Consultas. Irá usar os mesmos relatórios no próximo exercício.

Observe o desempenho com o Azure Monitor

Vamos usar outro método para exibir o uso de recursos de nossa carga de trabalho. O Azure Monitor fornece métricas de desempenho que você pode exibir de várias maneiras, inclusive por meio do portal do Azure.

  1. Abra o portal do Azure e localize sua instância do banco de dados SQL AdventureWorks. No painel Visão geral do banco de dados, selecione a guia Monitoramento. A visualização padrão no painel Monitoramento é Utilização da computação:

    Captura de ecrã do portal do Azure com uma consulta lenta.

    Neste exemplo, a porcentagem da CPU está próxima de 100% para um intervalo de tempo recente. Este gráfico mostra o uso de recursos (CPU e E/S são padrões) na última hora e é atualizado continuamente. Selecione o gráfico para que você possa personalizá-lo para examinar outros usos de recursos.

  2. No menu Banco de dados SQL, selecione Adicionar métricas. Outra maneira de exibir as métricas de Utilização de Computação e outras métricas coletadas automaticamente pelo Azure Monitor para o Banco de Dados SQL do Azure é usar o Gerenciador de Métricas.

    Nota

    A Utilização de Computação é uma exibição predefinida do Metrics Explorer. Se você selecionar a lista suspensa Métrica na janela Adicionar métricas , verá os seguintes resultados:

    Captura de ecrã das métricas do Azure Monitor.

    Como mostrado na captura de tela, há várias métricas que você pode usar para visualizar com o Metrics Explorer. A visualização padrão do Metrics Explorer é para um período de 24 horas, com uma granularidade de cinco minutos. A visualização Utilização de Computação é a última hora com uma granularidade de um minuto (que pode ser alterada). Para ver a mesma vista, selecione Percentagem de CPU e altere a captura durante uma hora. A granularidade mudará para um minuto e deve ter o seguinte aspeto:

    Captura de ecrã a mostrar as métricas do Azure Monitor, incluindo a CPU após um minuto.

    A predefinição é um gráfico de linhas, mas a vista Explorador permite-lhe alterar o tipo de gráfico. O Metrics Explorer tem muitas opções, incluindo a capacidade de mostrar várias métricas no mesmo gráfico.

Registos do Azure Monitor

Neste exercício, não chegou a configurar um registo do Azure Monitor, mas poderá ser importante observar o respetivo aspeto num cenário de utilização de recursos da CPU. Os registos do Azure Monitor podem fornecer um registo muito mais extenso do histórico do que as Métricas do Azure.

Se você configurou os logs do Azure Monitor com um espaço de trabalho do Log Analytics, poderá usar a seguinte consulta Kusto para exibir os mesmos resultados de utilização da CPU para o banco de dados:

AzureMetrics
| where MetricName == 'cpu_percent'
| where Resource == "ADVENTUREWORKS"
| project TimeGenerated, Average
| render columnchart

Os resultados teriam o seguinte aspeto:

Captura de ecrã de uma consulta a medir a CPU.

Os registos do Azure Monitor têm um atraso durante a primeira configuração dos registos de diagnóstico de uma base de dados, portanto poderá demorar algum tempo até ver estes resultados.

Neste exercício, aprendeu a observar um cenário comum de desempenho do SQL Server e analisar os detalhes para tomar uma decisão relativa a uma possível solução para melhorar o desempenho. Na próxima unidade, você aprenderá métodos para acelerar e ajustar o desempenho.