Ejercicio: Optimización del rendimiento de la aplicación

10 minutos

En este ejercicio, observará un nuevo escenario de rendimiento y lo resolveremos optimizando la aplicación y las consultas.

Optimización del rendimiento de la aplicación con Azure SQL

En algunos casos, la migración a Azure de una aplicación existente y de la carga de trabajo de consultas SQL puede abrir nuevas oportunidades para optimizar y ajustar las consultas.

Para dar cabida a una nueva extensión de un sitio web para pedidos de AdventureWorks que permita proporcionar un sistema de clasificación a los clientes, debemos agregar una tabla nueva para un gran conjunto de actividades de inserción simultáneas. Ha probado la carga de trabajo de consultas SQL en un equipo de desarrollo con SQL Server 2022 que tiene una unidad SSD local para la base de datos y el registro de transacciones.

Al pasar la prueba a Azure SQL Database usando el nivel De uso general (8 núcleos virtuales), la carga de trabajo de inserción es más lenta. ¿Deberemos cambiar el nivel u objetivo de servicio para admitir la nueva carga de trabajo, o bien examinar la aplicación?

Puede encontrar todos los scripts de este ejercicio en la carpeta 04-Performance\tuning_applications en el repositorio de GitHub que ha clonado o el archivo ZIP que ha descargado.

Creación de una tabla para la aplicación

En el Explorador de objetos, seleccione la base de datos AdventureWorks. Use Archivo>Abrir>Archivo para abrir el script order_rating_ddl.sql para crear una tabla en la base de datos AdventureWorks. La ventana del editor de consultas debería tener un aspecto similar al siguiente:

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

Seleccione Ejecutar para ejecutar el script.

Carga de consultas para supervisar la ejecución de las consultas

Ahora, vamos a cargar varias consultas de T-SQL relativas a vistas de administración dinámica (DMV) con el fin de observar el rendimiento de las consultas activas, las esperas y la E/S. Cargue todas estas consultas en el contexto de la base de datos AdventureWorks.

En el Explorador de objetos, seleccione la base de datos AdventureWorks. Use Archivo>Abrir>Archivo para abrir el script sqlrequests.sql para ver las consultas SQL activas. La ventana del editor de consultas debería tener un aspecto similar al siguiente:
```
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;
```
En el Explorador de objetos, seleccione la base de datos AdventureWorks. Use Archivo>Abrir>Archivo para abrir el script top_waits.sql para ver los tipos de espera principales por recuento. La ventana del editor de consultas debería tener un aspecto similar al siguiente:
```
SELECT * FROM sys.dm_os_wait_stats
ORDER BY waiting_tasks_count DESC;
```
En el Explorador de objetos, seleccione la base de datos AdventureWorks. Use Archivo>Abrir>Archivo para abrir el script tlog_io.sql para observar la latencia de las escrituras de registros de transacciones. La ventana del editor de consultas debería tener un aspecto similar al siguiente:
```
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);
```

Preparación del script de carga de trabajo para la ejecución

Abra y edite el script de carga de trabajo order_rating_insert_single.cmd.

Sustituya el valor unique_id que se le ha proporcionado en el primer ejercicio por el nombre del servidor para el -S parameter.
Sustituya por el -P parameter la contraseña que ha proporcionado en la implementación de base de datos del primer ejercicio.
Guarde los cambios en el archivo.

Ejecución de la carga de trabajo

Desde un símbolo del sistema de PowerShell, cambie al directorio de la actividad de este módulo:
```
cd c:<base directory>\04-Performance\tuning_applications
```
Ejecute la carga de trabajo con el siguiente comando:
```
.\order_rating_insert_single.cmd
```
Este script usa el programa ostress.exe para ejecutar 25 usuarios simultáneos que ejecutan la siguiente instrucción de T-SQL (en el 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
```
Si se fija en este script, verá que no es exactamente una representación real de los datos procedentes del sitio web, pero sí simula muchas valoraciones de pedidos ingeridas en la base de datos.

Examen de las DMV y el rendimiento de la carga de trabajo

Ahora, ejecute en SQL Server Management Studio (SSMS) las consultas que ha cargado antes para observar el rendimiento. Ejecute las consultas correspondientes a sqlrequests.sql, top_waits.sql y tlog_io.sql.

Con estas consultas se puede concluir lo siguiente:

Muchas solicitudes tienen constantemente un wait_type WRITELOG con un valor > 0.
El tipo de espera WRITELOG es uno de los recuentos más altos de los tipos de espera.
El promedio de tiempo para escribir en el registro de transacciones (la columna avg_tlog_io_write_ms del conjunto de resultados tlog_io.sql) está en algún lugar alrededor de 2 ms.

La duración de esta carga de trabajo en una instancia de SQL Server 2022 con una unidad SSD es de unos 10-12 segundos. La duración total en Azure SQL Database con un núcleo v8 de Gen5 es de unos 25 segundos.

Los tipos de espera WRITELOG con tiempos de espera más altos son indicativos del vaciado de latencia en el registro de transacciones. Un tiempo de espera de 2 ms por operación de escritura no parece mucho, pero en una unidad SSD local, estas esperas pueden ser de menos de 1 ms.

Elección de una solución

El problema no es un porcentaje elevado de la actividad de escritura en el registro. Azure Portal y sys.dm_db_resource_stats no arrojan ninguna cifra superior al 20-25 por ciento (esto no es necesario consultarlo). El problema tampoco es un límite de IOPS; el problema reside en que esta carga de trabajo de la aplicación es sensible a latencias bajas en operaciones de escritura en el registro de transacciones, y el nivel De uso general no está diseñado para este tipo de requisitos de latencia. La latencia de E/S esperada para Azure SQL Database es de 5 a 7 ms.

Nota:

Las instancias de Azure SQL Database De uso general documentan promedios de latencia de E/S aproximados de entre 5-7 (escritura) y 5-10 (lectura), por lo que es posible que experimente latencias más similares a estas cifras. Las latencias de Azure SQL Managed Instance de uso general son parecidas. Si la aplicación es muy sensible a las latencias de E/S, sopese la posibilidad de usar niveles Crítico para la empresa.

Examine el script de carga de trabajo order_rating_insert_single.sql. Cada INSERT es una confirmación de transacción única, lo que requiere un vaciado del registro de transacciones.

Una confirmación de cada operación de inserción no es eficaz, pero la aplicación no se ha visto afectada en una SSD local porque cada confirmación ha sido muy rápida. El plan de tarifa Crítico para la empresa (objetivo de servicio o SKU) proporciona unidades SSD locales con una latencia menor. Es posible que haya una optimización de la aplicación, por lo que la carga de trabajo no es tan sensible a la latencia de E/S del registro de transacciones.

Puede cambiar el lote de T-SQL para la carga de trabajo para encapsular BEGIN TRAN/COMMIT TRAN alrededor de las iteraciones INSERT.

Ejecución una carga de trabajo modificada más eficaz

Modifique los scripts y ejecútelos para ver un rendimiento de E/S más eficaz. Puede encontrar la carga de trabajo modificada en el script order_rating_insert.sql.

Para preparar el script de carga de trabajo, edite order_rating_insert.cmd e incluya el nombre de servidor y la contraseña correctos.
Ejecute la carga de trabajo modificada usando el script order_rating_insert.cmd, de la misma forma que ha ejecutado el script de carga de trabajo anterior.

Análisis de los nuevos resultados

Examine los resultados del script T-SQL para sqlrequests.sql en SSMS. Fíjese en que hay un número mucho menor de esperas de WRITELOG y un tiempo de espera más breve en general en estas esperas.

Ahora la carga de trabajo se ejecuta mucho más rápido en comparación con la ejecución anterior. Este es un ejemplo de cómo ajustar una aplicación para consultas SQL que después se ejecutarán dentro o fuera de Azure.

Nota:

Esta carga de trabajo se puede ejecutar incluso más rápido en una instancia de Azure SQL Database con un tipo de conexión de redirección. En la implementación que hemos realizado en este ejercicio, se usa un tipo de conexión predeterminado que será de tipo proxy, porque la conexión se ha establecido fuera de Azure. El uso de la redirección puede acelerar considerablemente una carga de trabajo como esta, teniendo en cuenta los recorridos de ida y vuelta necesarios desde el cliente al servidor.
Observe la duración de la carga de trabajo. La carga de trabajo se ejecuta tan rápido que puede ser difícil obtener datos de diagnóstico de las consultas usadas anteriormente en esta actividad.

El concepto de "procesamiento por lotes" puede ayudar a la mayoría de las aplicaciones, incluidas las que se conectan a Azure SQL.

Sugerencia

La gobernanza de recursos en Azure puede afectar a transacciones muy grandes y los síntomas serán LOG_RATE_GOVERNOR. En este ejemplo, la columna char(500) not null rellena los espacios y genera entradas de gran tamaño en el registro de transacciones. El rendimiento se puede optimizar todavía más si esa columna se convierte en una columna de longitud variable.

En la siguiente unidad, nos detendremos en el rendimiento inteligente de Azure SQL.