Ejercicio: Supervisión y solución de problemas de rendimiento

  • 10 minutos

En este ejercicio, veremos sobre cómo supervisar y solucionar problemas de rendimiento con Azure SQL usando herramientas y funcionalidades tanto conocidas como nuevas.

Configuración: Uso de scripts para implementar Azure SQL Database

La sesión de terminal de la derecha de Azure Cloud Shell permite interactuar con Azure mediante un explorador. En este ejercicio va a ejecutar un script para crear el entorno, una instancia de Azure SQL Database con la base de datos AdventureWorks. (Se usa la base de datos de muestra AdventureWorksLT, más pequeña y sencilla, pero la llamaremos AdventureWorks para evitar confusiones). En el script se le pide una contraseña y la dirección IP local para permitir que el dispositivo se conecte a la base de datos.

Este script tarda unos 3-5 minutos en completarse. Asegúrese de anotar la contraseña, el identificador único y la región, ya que no volverán a mostrarse.

  1. Para empezar, obtenga la dirección IP local. Asegúrese de que está desconectado de cualquier servicio VPN y abra un terminal de PowerShell local en el dispositivo. Ejecute el comando siguiente y anote la dirección IP resultante:

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

  2. En Azure Cloud Shell, a la derecha, escriba el código siguiente y, cuando se le pida, proporcione una contraseña compleja y la dirección IP pública local que ha recuperado en el paso anterior. Presione Entrar para ejecutar la última línea del 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. Ejecute el script siguiente en Azure Cloud Shell. Guarde la salida; va a necesitar esta información a lo largo del módulo. Presione Entrar después de pegar el código para que la última línea de código imprima la salida que necesita.

    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

    Sugerencia

    Guarde la salida y anote la contraseña, el identificador único y el servidor. Necesitará estos datos a lo largo del módulo.

  4. Ejecute el siguiente script para implementar una instancia de Azure SQL Database y un servidor lógico con el ejemplo AdventureWorks. Este script agrega la dirección IP como regla de firewall, habilita Advanced Data Security y crea una cuenta de almacenamiento para su uso en los ejercicios restantes de este módulo. El script puede tardar varios minutos en completarse, y se va a poner en pausa varias veces. Espere hasta que aparezca un símbolo del sistema.

    # 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. En el dispositivo local, abra SQL Server Management Studio (SSMS) para crear una nueva conexión con el servidor lógico.

  6. En el cuadro de diálogo de inicio de sesión Conectarse al servidor, proporcione la siguiente información:

    Campo Valor
    Tipo de servidor Motor de base de datos (valor predeterminado).
    Nombre de servidor Valor $serverName devuelto en Cloud Shell, más el resto del URI. Por ejemplo: aw-server<unique ID>.base_de_datos.windows.net.
    Authentication Autenticación de SQL Server (valor predeterminado).
    Iniciar sesión cloudadmin Valor adminSqlLogin asignado en el paso 1 de este ejercicio.
    Contraseña Contraseña proporcionada en el paso 1 de este ejercicio.
    Recordar contraseña checked

  7. Seleccione Conectar.

    Screenshot of connection dialog for SQL Database in SSMS.

    Nota:

    En función de la configuración local (por ejemplo, VPN), es posible que la dirección IP del cliente sea diferente a la que ha usado Azure Portal durante la implementación. Si así es, aparecerá el siguiente mensaje: "La dirección IP del cliente no tiene acceso al servidor. Inicie sesión con una cuenta de Azure y cree una regla de firewall para habilitar el acceso". Si recibe este mensaje, inicie sesión con la cuenta que usa en el espacio aislado y agregue una regla de firewall relativa a la dirección IP del cliente. Puede completar todos estos pasos con el asistente de SSMS.

Preparación del ejercicio mediante la carga y edición de scripts

Puede encontrar todos los scripts de este ejercicio en la carpeta 04-Performance\monitor_and_scale en el repositorio de GitHub que ha clonado o el archivo ZIP que ha descargado. Ahora se preparará el ejercicio mediante la carga y edición de scripts.

  1. En SSMS, en Explorador de objetos, expanda la carpeta Bases de datos y seleccione la base de datos AdventureWorks.

  2. Seleccione Archivo>Abrir>Archivo y abra el script dmexecrequests.sql. 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;

  3. Use el mismo método en SSMS para cargar el script dmdbresourcestats.sql. Una ventana nueva del editor de consultas debería tener un aspecto similar al texto siguiente:

    SELECT * FROM sys.dm_db_resource_stats;

    Esta vista de administración dinámica (DMV) realizará el seguimiento del uso de recursos general de la carga de trabajo en Azure SQL Database, por ejemplo, de la CPU, E/S y la memoria.

  4. Abra y edite el script sqlworkload.cmd (que usará el programa ostress.exe).

    • Sustituya el valor unique_id que ha guardado del script de implementación por el nombre del servidor.
    • Sustituya la contraseña que ha usado para el inicio de sesión del servidor de Azure SQL Database por -P parameter.
    • Guarde los cambios en el script.

Ejecución de la carga de trabajo

En esta tarea va a ejecutar una carga de trabajo en una consulta T-SQL para observar su rendimiento mediante la simulación de usuarios simultáneos.

  1. Use SSMS para abrir el archivo de script topcustomersales.sql y observar la consulta. La consulta no se ejecutará desde SSMS. La ventana del editor de consultas debería tener un aspecto similar al siguiente:

    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 datos es pequeña. La consulta para recuperar una lista de clientes y su información de ventas asociada, ordenada por clientes con más ventas, no debería generar un gran conjunto de resultados. Esta consulta se puede optimizar si se reduce el número de columnas del conjunto de resultados, pero aquí son necesarias para los fines de demostración de este ejercicio.

  2. En un símbolo del sistema de PowerShell, escriba el siguiente comando para ir al directorio correcto de este ejercicio. Reemplace <base directory> por el identificador de usuario y la ruta de acceso de este módulo:

    cd <base directory>\04-Performance\monitor_and_scale

  3. Ejecute la carga de trabajo con el siguiente comando:

    .\sqlworkload.cmd

    Este script usará 10 usuarios simultáneos que ejecutan la consulta de carga de trabajo dos veces. Observe que el propio script se ejecuta en un único lote, pero se repite 10 000 veces. Además, el resultado se asigna a una variable, de forma que se elimina casi todo el tráfico del conjunto de resultados al cliente. Esto no es necesario, pero ayuda a mostrar la ejecución de una carga de trabajo de CPU "pura" en el servidor.

    Sugerencia

    Si no ve el comportamiento del uso de CPU con esta carga de trabajo para este entorno, puede ajustar -n parameter al número de usuarios y -r parameter a las iteraciones.

    La salida del símbolo del sistema debe ser similar a la siguiente:

    [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...

Observación del rendimiento de la carga de trabajo

Ahora se van a usar las consultas DMV que se han cargado antes para observar el rendimiento.

  1. En SSMS, ejecute la consulta que ha cargado anteriormente para supervisar dm_exec_requests (dmexecrequests.sql) y observar las solicitudes activas. Ejecute esta consulta cinco o seis veces, y observe algunos de los 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;

    Debería ver que muchas de las solicitudes tienen un estado de RUNNABLE y last_wait_type es SOS_SCHEDULER_YIELD. La presencia de muchas solicitudes RUNNABLE y muchas esperas SOS_SCHEDULER_YIELD es un indicador de una posible falta de recursos de CPU para las consultas activas.

    Nota:

    Puede ver una o varias solicitudes activas si usa un comando SELECT y un valor wait_type de XE_LIVE_TARGET_TVF. Estas consultas las ejecutan servicios administrados por Microsoft. Ayudan con funcionalidades como la información del rendimiento por medio de eventos extendidos. Microsoft no publica detalles de estas sesiones.

    Deje esta ventana del editor de consultas abierta, ya que volverá a ejecutarla en el siguiente ejercicio.

  2. En SSMS, ejecute la consulta que ha cargado antes para supervisar sys.dm_db_resource_stats (dmdbresourcestats.sql). Ejecute la consulta para ver los resultados de esta DMV tres o cuatro veces.

    SELECT * FROM sys.dm_db_resource_stats;

    En esta DMV se registra una instantánea del uso de recursos de la base de datos cada 15 segundos (se mantiene durante 1 hora). La columna avg_cpu_percent debería aparecer cercana al 100 por cien en varias de las instantáneas. Esto es un síntoma de una carga de trabajo que fuerza los límites de los recursos de CPU para la base de datos.

    En el caso de un entorno local de SQL Server, normalmente se usaría una herramienta específica del sistema operativo para realizar el seguimiento del uso general de recursos como la CPU. Por ejemplo, se puede usar el Monitor de rendimiento de Windows para este fin. Si este ejemplo se ejecutara en una instancia local de SQL Server o en SQL Server en una máquina virtual con dos CPU, se vería un uso de la CPU cercano al 100 por cien en el servidor.

    Nota:

    Puede ejecutar otra DMV, sys.resource_stats, en el contexto de la base de datos master del servidor Azure SQL Database para ver el uso de recursos de todas las bases de datos de Azure SQL Database asociadas al servidor. Esta vista es menos detallada y muestra el uso de los recursos cada cinco minutos (se mantiene durante 14 días).

    Deje esta ventana del editor de consultas abierta, ya que volverá a ejecutarla en el siguiente ejercicio.

  3. Deje que se complete la carga de trabajo y anote su duración total. Una vez que se haya completado la carga de trabajo, deberían aparecer resultados como los de la salida siguiente y volver al símbolo del sistema:

    [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

    El tiempo de duración puede variar, pero normalmente tarda al menos 1-3 minutos. Asegúrese de dejar que se ejecute hasta el final. Cuando la carga de trabajo termine, volverá a al símbolo del sistema.

Uso del Almacén de consultas para un análisis más exhaustivo

El Almacén de consultas es una funcionalidad de SQL Server para realizar el seguimiento de la ejecución de consultas. Los datos de rendimiento se almacenan en la base de datos de usuario. Almacén de consultas no está habilitado de forma predeterminada en las bases de datos creadas en SQL Server, pero sí en Azure SQL Database (y Azure SQL Managed Instance).

Incluye una serie de vistas de catálogo del sistema para ver los datos de rendimiento. SSMS proporciona informes por medio de estas vistas.

  1. En el Explorador de objetos de SSMS, abra la carpeta Almacén de consultas para buscar el informe Consultas que más recursos consumen.

    Screenshot of the Query Store.

  2. Seleccione el informe para averiguar qué consultas han consumido la mayoría de los recursos y ver sus detalles de ejecución. En función de la carga de trabajo ejecutada hasta este punto, el informe debería ser similar al de la imagen siguiente:

    Screenshot of the top query report.

    La consulta que se muestra es la consulta SQL de la carga de trabajo para las ventas de clientes. Este informe tiene tres componentes: consultas con una duración total elevada (puede cambiar la métrica), el plan de consulta asociado y estadísticas en tiempo de ejecución, y el plan de consulta asociado en un mapa visual.

  3. Seleccione el gráfico de barras de la consulta (el valor de query_id podría ser diferente en su sistema). Los resultados deberían ser similares a los de la imagen siguiente:

    Screenshot of the query ID.

    Puede ver la duración total y el texto de la consulta.

  4. A la derecha de este gráfico de barras hay un gráfico de estadísticas del plan de consulta asociado a la consulta. Mantenga el mouse sobre el punto asociado al plan. Los resultados deberían ser similares a los de la imagen siguiente:

    Screenshot of slow query statistics.

    Observe la duración media de la consulta. Los tiempos pueden variar, pero compare esta duración media con el tiempo de espera promedio de esta consulta. Más adelante, daremos a conocer una mejora del rendimiento y volverá a realizar esta comparación para apreciar la diferencia.

  5. El componente final es el plan de consulta visual. El plan de esta consulta es similar al de la imagen siguiente:

    Screenshot of the workload query plan.

    Esta tabla de base de datos tiene tan pocas filas que no necesita un plan: puede resultar ineficaz. La optimización de la consulta no va a mejorar mucho el rendimiento. Puede que aparezca una advertencia en el plan sobre la falta de estadísticas de una de las columnas en la búsqueda en el índice agrupado. Esto no tiene impacto en el rendimiento general.

  6. En SSMS, debajo del informe Consultas que más recursos consumen, verá otro llamado Estadísticas de espera de consulta. Ya sabemos, a raíz de un diagnóstico anterior, que había un gran número de solicitudes que se encontraban constantemente en un estado RUNNABLE junto con uso de la CPU cercano al 100 por cien. Almacén de consultas incluye informes para ver los posibles cuellos de botella de rendimiento debidos a las esperas de los recursos. Seleccione este informe y mantenga el ratón sobre el gráfico de barras. Los resultados deberían ser similares a los de la imagen siguiente:

    Screenshot of the top wait statistics.

    Puede ver que la categoría de espera superior es CPU (equivale al valor wait_type SOS_SCHEDULER_YIELD, que se puede ver en sys.dm_os_wait_stats) y el tiempo de espera medio.

  7. Seleccione el gráfico de barras de CPU del informe. La consulta principal, a la espera de la CPU, es la consulta de la carga de trabajo que se está usando.

    Screenshot of the top wait statistics query.

    Observe que el tiempo medio de espera de la CPU en esta consulta es un porcentaje elevado de la duración media total de la consulta.

    Si se tiene en cuenta la evidencia, sin ninguna optimización de la consulta, la carga de trabajo necesita más capacidad de CPU de la que se ha implementado para la instancia de Azure SQL Database.

  8. Cierre ambos informes del Almacén de consultas. Usaremos los mismos informes en el siguiente ejercicio.

Examen del rendimiento con Azure Monitor

Ahora se va a usar otro método para ver el uso de recursos de la carga de trabajo. Azure Monitor proporciona métricas de rendimiento que se pueden ver de diversas maneras, incluso mediante Azure Portal.

  1. Abra Azure Portal y busque la instancia de la base de datos SQL AdventureWorks. En el panel Información general de la base de datos, seleccione la pestaña Supervisión. La vista predeterminada en el panel Supervisión es Uso del proceso:

    Screenshot of the Azure portal with a slow query.

    En este ejemplo el porcentaje de la CPU se aproxima al 100 por cien en un intervalo de tiempo reciente. Este gráfico muestra el uso de recursos (los valores predeterminados son CPU y E/S) durante la última hora y se actualiza de forma continua. Seleccione el gráfico para poder personalizarlo a fin de examinar otro uso de recursos.

  2. En el menú de la base de datos SQL, seleccione Añadir métricas. Otra forma de ver las métricas de Uso de procesos y otras recopiladas automáticamente por Azure Monitor para Azure SQL Database consiste en usar el Explorador de métricas.

    Nota:

    Uso de procesos es una vista predefinida del Explorador de métricas. Si selecciona la lista desplegable Métrica en la ventana Añadir métricas, verá los siguientes resultados:

    Screenshot of Azure Monitor metrics.

    Como se muestra en la captura de pantalla, hay varias métricas que se pueden ver con el Explorador de métricas. La vista predeterminada del Explorador de métricas corresponde a un período de 24 horas, con una granularidad de cinco minutos. La vista Uso de procesos se corresponde a la última hora con una granularidad de un minuto (que puede cambiar). Para ver la misma vista, seleccione Porcentaje de CPU y cambie la captura a una  hora. La granularidad cambiará a un minuto y debe ser similar a la imagen siguiente:

    Screenshot of Azure Monitor metrics, including CPU after 1 minute.

    El tipo de gráfico predeterminado es un gráfico de líneas, pero la vista Explorador permite cambiarlo. El Explorador de métricas tiene varias opciones, incluida la posibilidad de mostrar varias métricas en el mismo gráfico.

Registros de Azure Monitor

En este ejercicio no hemos configurado un registro de Azure Monitor, pero merece la pena ver cómo sería un registro en un escenario de uso de recursos de CPU. Los registros de Azure Monitor pueden proporcionar un registro histórico mucho más extenso que las métricas de Azure.

Si se hubieran configurado registros de Azure Monitor con un área de trabajo de Log Analytics, se podría usar la siguiente consulta de Kusto para ver los mismos resultados de uso de CPU de la base de datos:

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

Los resultados serían similares a los de la imagen siguiente:

Screenshot of a query measuring CPU.

Los registros de Azure Monitor tienen un retraso al configurar por primera vez los diagnósticos de registro de una base de datos, por lo que estos resultados pueden tardar algún tiempo en aparecer.

En este ejercicio, hemos aprendido a observar un escenario de rendimiento de SQL Server común y a profundizar en los detalles para decidir una posible solución de mejora del rendimiento. En la unidad siguiente, conoceremos algunos métodos para acelerar y ajustar el rendimiento.