Exercício – Otimizar o desempenho do aplicativo

• 10 minutos

Neste exercício, você observará um novo cenário de desempenho e o resolverá otimizando o aplicativo e as consultas.

Otimizar o desempenho do aplicativo com o SQL Azure

Em alguns casos, migrar um aplicativo existente e uma carga de trabalho de consulta SQL para o Azure pode revelar oportunidades de otimizar e ajustar as consultas.

Para dar suporte a uma nova extensão de um site de pedidos do AdventureWorks a fim de fornecer um sistema de classificação dos clientes, você precisa adicionar uma nova tabela para um grande conjunto de atividades INSERT simultâneas. Você testou a carga de trabalho de consulta SQL em um computador de desenvolvimento com o SQL Server 2022 que tem uma unidade SSD local para o banco de dados e o log de transações.

Quando você passa o teste para o Banco de Dados SQL do Azure usando a camada de uso geral (oito vCores), a carga de trabalho INSERT fica mais lenta. Você precisa alterar o objetivo ou a camada de serviço para dar suporte à nova carga de trabalho ou precisa examinar o aplicativo?

Você pode encontrar todos os scripts deste exercício na pasta 04-Performance\tuning_applications no repositório GitHub clonado ou no arquivo zip que você baixou.

Criar uma tabela para o aplicativo

No Pesquisador de Objetos, selecione o banco de dados AdventureWorks. Use Arquivo>Abrir>Arquivo para abrir o script order_rating_ddl.sql para criar uma tabela no banco de dados AdventureWorks. A janela do editor de consultas deverá ser semelhante ao seguinte texto:

DROP TABLE IF EXISTS SalesLT.OrderRating;
GO
CREATE TABLE SalesLT.OrderRating
(OrderRatingID int identity not null,
SalesOrderID int not null,
OrderRatingDT datetime not null,
OrderRating int not null,
OrderRatingComments char(500) not null);
GO

Selecione Executar para executar o script.

Carregar consultas para monitorar a execução da consulta

Agora, vamos carregar algumas consultas T-SQL para as DMVs (exibições de gerenciamento dinâmico) para observar o desempenho da consulta com relação a consultas ativas, esperas e E/S. Carregue todas essas consultas no contexto do banco de dados AdventureWorks.

1. No Pesquisador de Objetos, selecione o banco de dados AdventureWorks. Use Arquivo>Abrir>Arquivo para abrir o script sqlrequests.sql para examinar as consultas SQL ativas. A janela do editor de consultas deverá ser semelhante ao seguinte texto:

   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;
   

2. No Pesquisador de Objetos, selecione o banco de dados AdventureWorks. Use Arquivo>Abrir>Arquivo para abrir o script top_waits.sql para analisar os principais tipos de espera por quantidade. A janela do editor de consultas deverá ser semelhante ao seguinte texto:

   SELECT * FROM sys.dm_os_wait_stats
   ORDER BY waiting_tasks_count DESC;
   

3. No Pesquisador de Objetos, selecione o banco de dados AdventureWorks. Use Arquivo>Abrir>Arquivo para abrir o script tlog_io.sql para observar a latência para gravações do log de transações. A janela do editor de consultas deverá ser semelhante ao seguinte texto:

   SELECT io_stall_write_ms/num_of_writes as avg_tlog_io_write_ms, * 
   FROM sys.dm_io_virtual_file_stats
   (db_id('AdventureWorks'), 2);
   

Preparar o script da carga de trabalho para execução

Abra e edite o script de carga de trabalho order_rating_insert_single.cmd.

• Substitua a unique_id que você recebeu no primeiro exercício para o nome do servidor pelo -S parameter.
• Substitua a senha que você forneceu na implantação do banco de dados do primeiro exercício pelo -P parameter.
• Salve as alterações no arquivo.

Executar a carga de trabalho

1. Em um prompt de comando do PowerShell, passe para o diretório desta atividade do módulo:

   cd c:<base directory>\04-Performance\tuning_applications
   

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

   .\order_rating_insert_single.cmd
   

   Esse script usa o programa ostress.exe para executar 25 usuários simultâneos executando a seguinte instrução T-SQL (no script order_rating_insert_single.sql):

   DECLARE @x int;
   SET @x = 0;
   WHILE (@x < 500)
   BEGIN
   SET @x = @x + 1;
   INSERT INTO SalesLT.OrderRating
   (SalesOrderID, OrderRatingDT, OrderRating, OrderRatingComments)
   VALUES (@x, getdate(), 5, 'This was a great order');
   END
   

   Você pode ver nesse script que não se trata exatamente de uma representação real dos dados provenientes do site. Mas ele simula a ingestão de muitas classificações de pedidos pelo banco de dados.

Observar as DMVs e o desempenho da carga de trabalho

Agora, execute no SSMS (SQL Server Management Studio) as consultas que você carregou anteriormente para observar o desempenho. Execute as consultas para sqlrequests.sql, top_waits.sql e tlog_io.sql.

Com essas consultas, você pode observar os seguintes fatos:

• Muitas solicitações constantemente têm um wait_type de WRITELOG com valor > 0.
• O tipo de espera WRITELOG é uma das contagens mais altas para tipos de espera.
• O tempo médio para gravar no log de transações (a coluna avg_tlog_io_write_ms no conjunto de resultados tlog_io.sql) está em torno de 2 ms.

A duração dessa carga de trabalho em uma instância do SQL Server 2022 com uma unidade SSD é de aproximadamente 10 a 12 segundos. A duração total no Banco de Dados SQL do Azure com um núcleo Gen5 v8 é aproximadamente 25 segundos.

Tipos de espera WRITELOG com tempos de espera mais altos são indicativos de liberação de latência para o log de transações. Um tempo de espera de 2 ms por gravação não parece muito, mas em uma unidade SSD local, essas esperas podem ser de menos de 1 ms.

Decidir por uma resolução

O problema não é um percentual alto de atividade de gravação em log. O portal do Azure e sys.dm_db_resource_stats não mostram números superiores a 20 a 25% (você não precisa consultá-los). O problema também não é um limite de IOPS. O problema é que a carga de trabalho do aplicativo é sensível à baixa latência para gravações do log de transações e a camada de uso geral não foi projetada para esse tipo de requisito de latência. A latência de E/S esperada para o Banco de Dados SQL do Azure é de 5 a 7 ms.

Examine o script T-SQL da carga de trabalho order_rating_insert_single.sql. Cada INSERT é uma confirmação de transação única, que requer uma liberação de log de transações.

Ter uma confirmação para cada inserção não é eficiente, mas o aplicativo não foi afetado no SSD local porque cada confirmação foi muito rápida. O tipo de preço (objetivo de serviço ou SKU) Comercialmente Crítico fornece unidades SSD locais com uma latência mais baixa. É possível que haja uma otimização de aplicativo, portanto, a carga de trabalho não é tão sensível à latência de E/S para o log de transações.

Você pode alterar o lote T-SQL da carga de trabalho para encapsular BEGIN TRAN/COMMIT TRAN em torno das iterações INSERT.

Executar uma carga de trabalho modificada mais eficiente

Faça edições nos scripts e execute-os para ver um desempenho de E/S mais eficiente. Você pode encontrar a carga de trabalho modificada no script order_rating_insert.sql.

1. Prepare o script de carga de trabalho editando order_rating_insert.cmd para usar o nome do servidor e a senha corretos.

2. Execute a carga de trabalho modificada usando o script order_rating_insert.cmd, semelhante à forma como você executou o script de carga de trabalho anterior.

Observar os novos resultados

1. Examine os resultados do script T-SQL para sqlrequests.sql no SSMS. Observe que há bem menos esperas por WRITELOG e que, de modo geral, o período dessas esperas é menor.

   Agora, a carga de trabalho é executada muito mais rapidamente em comparação com a execução anterior. Este é um exemplo de ajuste de aplicativo para consultas SQL que serão executadas dentro ou fora do Azure.

   Observação

   Essa carga de trabalho pode ser executada ainda mais rapidamente em uma instância do Banco de Dados SQL do Azure com um tipo de conexão de redirecionamento. A implantação que você fez neste exercício usa um tipo de conexão padrão, que é um tipo de proxy porque você está conectado fora do Azure. O uso de Redirecionamento pode acelerar significativamente uma carga de trabalho como essa, considerando as viagens de ida e volta necessárias do cliente para o servidor.

2. Observe a duração da carga de trabalho. A carga de trabalho é executada tão rapidamente que pode ser difícil observar dados de diagnóstico das consultas usadas anteriormente nesta atividade.

   O conceito de "envio em lote" pode ajudar a maioria dos aplicativos, incluindo aqueles conectados ao SQL Azure.

Na próxima unidade, você aprenderá sobre o desempenho inteligente no SQL do Azure.