Exercício – Otimizar o desempenho da aplicação

• 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 da aplicação com o SQL do Azure

Em alguns casos, a migração de uma aplicação existente e da carga de trabalho de consulta SQL para o Azure pode revelar oportunidades para otimizar as consultas.

Para suportar uma nova extensão para um site, para que as encomendas da AdventureWorks forneçam um sistema de classificação dos clientes, tem de adicionar uma nova tabela para um conjunto pesado de atividade INSERT em simultâneo. 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 passa o teste para a Base de Dados SQL do Azure com o escalão de fins gerais (8 vCores), a carga de trabalho INSERT é mais lenta. Assim, deve alterar o escalão ou o objetivo do serviço para suportar a nova carga de trabalho ou deve analisar a aplicação?

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

Criar uma nova tabela para a aplicação

No Object Explorer, selecione a base de dados AdventureWorks. Use Arquivo>Aberto> para abrir o script order_rating_ddl.sql para criar uma tabela no AdventureWorks banco de dados. A janela do editor de consultas deverá ter um aspeto semelhante ao texto abaixo:

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 monitorizar a execução de consultas

Agora, vamos carregar algumas consultas T-SQL para vistas de gestão dinâmica (DMVs) de modo a observar o desempenho das consultas ativas, esperas e E/S. Carregue todas estas consultas no contexto da base de dados AdventureWorks.

1. No Object Explorer, selecione a base de dados AdventureWorks. Use Arquivo Aberto>de Arquivo> para abrir o script sqlrequests.sql para examinar consultas SQL ativas. 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;
   

2. No Object Explorer, selecione a base de dados AdventureWorks. Use Arquivo>Aberto> para abrir o script top_waits.sql para examinar os principais tipos de espera por contagem. A janela do editor de consultas deverá ter um aspeto semelhante ao texto abaixo:

   SELECT * FROM sys.dm_os_wait_stats
   ORDER BY waiting_tasks_count DESC;
   

3. No Object Explorer, selecione a base de dados AdventureWorks. Use Arquivo Aberto>de Arquivo> para abrir o script tlog_io.sql para observar a latência das gravações do log de transações. A janela do editor de consultas deverá ter um aspeto semelhante ao texto abaixo:

   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 o que unique_id lhe foi dado no primeiro exercício pelo nome do servidor para o -S parameter.
• Substitua a senha fornecida na implantação do banco de dados do primeiro exercício pelo -P parameter.
• Guarde as alterações ao ficheiro.

Executar a carga de trabalho

1. Numa linha de comandos do PowerShell, altere o diretório para a atividade deste 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
   

   Este script utiliza o programa ostress.exe para executar 25 utilizadores em simultâneo a executar 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
   

   Pode ver nesse script que não é exatamente uma representação real dos dados provenientes do site. Contudo, simula muitas classificações de encomendas que são ingeridas na base de dados.

Observar as DMVs e o desempenho da carga de trabalho

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

Com estas consultas, pode observar os seguintes factos:

• Muitas solicitações têm constantemente um de WRITELOG, com um wait_type valor > 0.
• O WRITELOG tipo de espera é uma das contagens mais altas para tipos de espera.
• O tempo médio para gravar no log de transações (a avg_tlog_io_write_ms coluna no conjunto de resultados tlog_io.sql ) é algo 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 na Base de Dados SQL do Azure com um núcleo Gen5 v8 é de aproximadamente 25 segundos.

WRITELOG Os tipos de espera 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 escrita não parece muito, mas essas esperas podem ser inferiores a 1 ms numa unidade SSD local.

Decidir uma resolução

O problema não é uma percentagem elevada da atividade de escrita de registos. O portal do Azure e sys.dm_db_resource_stats não mostrar números superiores a 20-25 por cento (não é necessário consultá-los). O problema também não é um limite de IOPS. O problema é que esta carga de trabalho da aplicação é sensível à baixa latência para escritas dos registos de transações e o escalão de fins gerais não foi criado para este tipo de requisitos 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 é uma única confirmação de transação, que requer uma liberação de log de transações.

Uma consolidação para cada inserção não é eficiente, mas a aplicação não foi afetada num SSD local porque cada consolidação foi muito rápida. O escalão de preço Crítico para a Empresa (objetivo do serviço ou SKU) fornece às unidades SSD locais 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 envolver as BEGIN TRAN/COMMIT TRAN INSERT iterações.

Executar uma carga de trabalho modificada mais eficiente

Faça edições aos 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 e a senha corretos do servidor.

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

Observar os novos resultados

1. Veja os resultados do script T-SQL sqlrequests.sql no SQL Server Management Studio. Repare que existem muito menos esperas WRITELOG e que os tempos de espera são geralmente inferiores.

   A carga de trabalho funciona agora muito mais rapidamente em comparação com a execução anterior. Trata-se de um exemplo de otimização de uma aplicação de consultas SQL que será executada dentro ou fora do Azure.

   Nota

   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 Redirect . 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. A utilização de Redirecionamento pode acelerar significativamente este tipo de cargas de trabalho, tendo em conta os percursos de ida e volta que são necessários do cliente para o servidor.

2. Observe a duração da carga de trabalho. A carga de trabalho é tão rápida que poderá ser difícil observar os dados de diagnóstico das consultas utilizadas anteriormente nesta atividade.

   O conceito de "criação de batches" pode ser útil para a maior parte das aplicações, incluindo as que estão ligadas ao SQL do Azure.

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