Solución de problemas de rendimiento lento SQL Server causados por problemas de E/S

  • Artículo

Se aplica a: SQL Server

En este artículo se proporcionan instrucciones sobre qué problemas de E/S provocan un rendimiento lento SQL Server y cómo solucionar los problemas.

Definir un rendimiento de E/S lento

Los contadores del monitor de rendimiento se usan para determinar el rendimiento lento de E/S. Estos contadores miden la rapidez con la que los servicios del subsistema de E/S cada solicitud de E/S en promedio en términos de tiempo de reloj. Los contadores específicos del monitor de rendimiento que miden la latencia de E/S en Windows son Avg Disk sec/ Read, Avg. Disk sec/Writey Avg. Disk sec/Transfer (acumulativos de lecturas y escrituras).

En SQL Server, las cosas funcionan de la misma manera. Normalmente, se examina si SQL Server notifica cuellos de botella de E/S medidos en tiempo de reloj (milisegundos). SQL Server realiza solicitudes de E/S al sistema operativo llamando a las funciones win32 como WriteFile(), ReadFile(), WriteFileGather()y ReadFileScatter(). Cuando publica una solicitud de E/S, SQL Server veces la solicitud e informa de la duración de la solicitud mediante tipos de espera. SQL Server usa tipos de espera para indicar esperas de E/S en diferentes lugares del producto. Las esperas relacionadas con E/S son:

Si estas esperas superan los 10-15 milisegundos de forma coherente, la E/S se considera un cuello de botella.

Nota:

Para proporcionar contexto y perspectiva, en el mundo de la solución de problemas de SQL Server, Microsoft CSS ha observado casos en los que una solicitud de E/S tardó más de un segundo y hasta 15 segundos por transferencia, por lo que los sistemas de E/S necesitan optimización. Por el contrario, Css de Microsoft ha visto sistemas en los que el rendimiento está por debajo de un milisegundo/transferencia. Con la tecnología SSD/NVMe de hoy en día, las velocidades de rendimiento anunciadas oscilan en decenas de microsegundos por transferencia. Por lo tanto, la cifra de 10-15 milisegundos/transferencia es un umbral muy aproximado que hemos seleccionado en función de la experiencia colectiva entre Windows y SQL Server ingenieros a lo largo de los años. Normalmente, cuando los números superan este umbral aproximado, SQL Server los usuarios comienzan a ver la latencia en sus cargas de trabajo y los notifican. En última instancia, el rendimiento esperado de un subsistema de E/S se define mediante el fabricante, el modelo, la configuración, la carga de trabajo y, potencialmente, varios otros factores.

Metodología

Al final de este artículo, un diagrama de flujo describe la metodología que usa Microsoft CSS para abordar problemas de E/S lentos con SQL Server. No es un enfoque exhaustivo o exclusivo, pero ha demostrado ser útil para aislar el problema y resolverlo.

Puede elegir una de las dos opciones siguientes para resolver el problema:

Opción 1: Ejecutar los pasos directamente en un cuaderno a través de Azure Data Studio

Nota:

Antes de intentar abrir este cuaderno, asegúrese de que Azure Data Studio está instalado en el equipo local. Para instalarlo, vaya a Información sobre cómo instalar Azure Data Studio.

Apertura de Notebook en Azure Data Studio

Opción 2: Siga los pasos manualmente

La metodología se describe en estos pasos:

Paso 1: ¿SQL Server informes de E/S lenta?

SQL Server puede notificar latencia de E/S de varias maneras:

  • Tipos de espera de E/S
  • DMV sys.dm_io_virtual_file_stats
  • Registro de errores o registro de eventos de aplicación
Tipos de espera de E/S

Determine si hay latencia de E/S notificada por SQL Server tipos de espera. Los valores PAGEIOLATCH_*, WRITELOGy ASYNC_IO_COMPLETION los valores de otros tipos de espera menos comunes suelen permanecer por debajo de 10-15 milisegundos por solicitud de E/S. Si estos valores son más coherentes, existe un problema de rendimiento de E/S y requiere una investigación adicional. La siguiente consulta puede ayudarle a recopilar esta información de diagnóstico en el sistema:

#replace with server\instance or server for default instance
$sqlserver_instance = "server\instance" 

for ([int]$i = 0; $i -lt 100; $i++)
{
   
  sqlcmd -E -S $sqlserver_instance -Q "SELECT r.session_id, r.wait_type, r.wait_time as wait_time_ms`
                                       FROM sys.dm_exec_requests r JOIN sys.dm_exec_sessions s `
                                        ON r.session_id = s.session_id `
                                       WHERE wait_type in ('PAGEIOLATCH_SH', 'PAGEIOLATCH_EX', 'WRITELOG', `
                                        'IO_COMPLETION', 'ASYNC_IO_COMPLETION', 'BACKUPIO')`
                                       AND is_user_process = 1"

  Start-Sleep -s 2
}
Estadísticas de archivos en sys.dm_io_virtual_file_stats

Para ver la latencia de nivel de archivo de base de datos como se indica en SQL Server, ejecute la consulta siguiente:

#replace with server\instance or server for default instance
$sqlserver_instance = "server\instance" 

sqlcmd -E -S $sqlserver_instance -Q "SELECT   LEFT(mf.physical_name,100),   `
         ReadLatency = CASE WHEN num_of_reads = 0 THEN 0 ELSE (io_stall_read_ms / num_of_reads) END, `
         WriteLatency = CASE WHEN num_of_writes = 0 THEN 0 ELSE (io_stall_write_ms / num_of_writes) END, `
         AvgLatency =  CASE WHEN (num_of_reads = 0 AND num_of_writes = 0) THEN 0 `
                        ELSE (io_stall / (num_of_reads + num_of_writes)) END,`
         LatencyAssessment = CASE WHEN (num_of_reads = 0 AND num_of_writes = 0) THEN 'No data' ELSE `
               CASE WHEN (io_stall / (num_of_reads + num_of_writes)) < 2 THEN 'Excellent' `
                    WHEN (io_stall / (num_of_reads + num_of_writes)) BETWEEN 2 AND 5 THEN 'Very good' `
                    WHEN (io_stall / (num_of_reads + num_of_writes)) BETWEEN 6 AND 15 THEN 'Good' `
                    WHEN (io_stall / (num_of_reads + num_of_writes)) BETWEEN 16 AND 100 THEN 'Poor' `
                    WHEN (io_stall / (num_of_reads + num_of_writes)) BETWEEN 100 AND 500 THEN  'Bad' `
                    ELSE 'Deplorable' END  END, `
         [Avg KBs/Transfer] =  CASE WHEN (num_of_reads = 0 AND num_of_writes = 0) THEN 0 `
                    ELSE ((([num_of_bytes_read] + [num_of_bytes_written]) / (num_of_reads + num_of_writes)) / 1024) END, `
         LEFT (mf.physical_name, 2) AS Volume, `
         LEFT(DB_NAME (vfs.database_id),32) AS [Database Name]`
       FROM sys.dm_io_virtual_file_stats (NULL,NULL) AS vfs  `
       JOIN sys.master_files AS mf ON vfs.database_id = mf.database_id `
         AND vfs.file_id = mf.file_id `
       ORDER BY AvgLatency DESC"

Examine las AvgLatency columnas y LatencyAssessment para comprender los detalles de latencia.

Error 833 notificado en registro de errores o registro de eventos de aplicación

En algunos casos, puede observar el error 833 SQL Server has encountered %d occurrence(s) of I/O requests taking longer than %d seconds to complete on file [%ls] in database [%ls] (%d) en el registro de errores. Puede comprobar SQL Server registros de errores en el sistema ejecutando el siguiente comando de PowerShell:

Get-ChildItem -Path "c:\program files\microsoft sql server\mssql*" -Recurse -Include Errorlog |
   Select-String "occurrence(s) of I/O requests taking longer than Longer than 15 secs"

Además, para obtener más información sobre este error, consulte la sección MSSQLSERVER_833 .

Paso 2: ¿Los contadores de perfmon indican la latencia de E/S?

Si SQL Server notifica la latencia de E/S, consulte contadores del sistema operativo. Puede determinar si hay un problema de E/S examinando el contador Avg Disk Sec/Transferde latencia . El siguiente fragmento de código indica una manera de recopilar esta información a través de PowerShell. Recopila contadores en todos los volúmenes de disco: "_total". Cambie a un volumen de unidad específico (por ejemplo, "D:"). Para buscar qué volúmenes hospedan los archivos de base de datos, ejecute la siguiente consulta en el SQL Server:

#replace with server\instance or server for default instance
$sqlserver_instance = "server\instance" 
sqlcmd -E -S $sqlserver_instance -Q "SELECT DISTINCT LEFT(volume_mount_point, 32) AS volume_mount_point `
                                     FROM sys.master_files f `
                                     CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id) vs"

Recopile Avg Disk Sec/Transfer métricas en el volumen que prefiera:

clear
$cntr = 0 

# replace with your server name, unless local computer
$serverName = $env:COMPUTERNAME

# replace with your volume name - C: , D:, etc
$volumeName = "_total"

$Counters = @(("\\$serverName" +"\LogicalDisk($volumeName)\Avg. disk sec/transfer"))

$disksectransfer = Get-Counter -Counter $Counters -MaxSamples 1 
$avg = $($disksectransfer.CounterSamples | Select-Object CookedValue).CookedValue

Get-Counter -Counter $Counters -SampleInterval 2 -MaxSamples 30 | ForEach-Object {
$_.CounterSamples | ForEach-Object {
   [pscustomobject]@{
      TimeStamp = $_.TimeStamp
      Path = $_.Path
      Value = ([Math]::Round($_.CookedValue, 5))
         turn = $cntr = $cntr +1
         running_avg = [Math]::Round(($avg = (($_.CookedValue + $avg) / 2)), 5)  
         
   } | Format-Table
     }
   }

   write-host "Final_Running_Average: $([Math]::Round( $avg, 5)) sec/transfer`n"
  
   if ($avg -gt 0.01)
   {
     Write-Host "There ARE indications of slow I/O performance on your system"
   }
   else
   {
     Write-Host "There is NO indication of slow I/O performance on your system"
   }

Si los valores de este contador están constantemente por encima de 10-15 milisegundos, debe examinar más el problema. Los picos ocasionales no cuentan en la mayoría de los casos, pero asegúrese de comprobar dos veces la duración de un pico. Si el pico duró un minuto o más, es más una meseta que un pico.

Si los contadores del monitor de rendimiento no notifican la latencia, pero SQL Server lo hace, el problema se produce entre SQL Server y el Administrador de particiones, es decir, los controladores de filtro. El Administrador de particiones es una capa de E/S donde el sistema operativo recopila contadores perfmon . Para solucionar la latencia, asegúrese de exclusiones adecuadas de los controladores de filtro y resuelva los problemas de los controladores de filtro. Los controladores de filtro los usan programas como software antivirus, soluciones de copia de seguridad, cifrado, compresión, etc. Puede usar este comando para enumerar los controladores de filtro en los sistemas y los volúmenes a los que se asocian. A continuación, puede buscar los nombres de los controladores y los proveedores de software en el artículo Altitudes de filtro asignado .

fltmc instances

Para obtener más información, consulte Elección del software antivirus para que se ejecute en equipos que ejecutan SQL Server.

Evite usar el sistema de cifrado de archivos (EFS) y la compresión del sistema de archivos porque hacen que la E/S asincrónica se convierta en sincrónica y, por tanto, más lenta. Para obtener más información, vea el artículo E/S de disco asincrónico aparece como sincrónica en Windows .

Paso 3: ¿El subsistema de E/S está sobrecargado más allá de la capacidad?

Si SQL Server y el sistema operativo indican que el subsistema de E/S es lento, compruebe si la causa es que el sistema esté sobrecargado más allá de la capacidad. Puede comprobar la capacidad examinando los contadores Disk Bytes/Secde E/S , Disk Read Bytes/Seco Disk Write Bytes/Sec. Asegúrese de consultar con el administrador del sistema o el proveedor de hardware las especificaciones de rendimiento esperadas para su SAN (u otro subsistema de E/S). Por ejemplo, puede insertar no más de 200 MB/s de E/S a través de una tarjeta HBA de 2 GB/s o un puerto dedicado de 2 GB/s en un conmutador SAN. La capacidad de rendimiento esperada definida por un fabricante de hardware define cómo proceder desde aquí.

clear

$serverName = $env:COMPUTERNAME
$Counters = @(
   ("\\$serverName" +"\PhysicalDisk(*)\Disk Bytes/sec"),
   ("\\$serverName" +"\PhysicalDisk(*)\Disk Read Bytes/sec"),
   ("\\$serverName" +"\PhysicalDisk(*)\Disk Write Bytes/sec")
   )
Get-Counter -Counter $Counters -SampleInterval 2 -MaxSamples 20 | ForEach-Object  {
$_.CounterSamples | ForEach-Object       {
   [pscustomobject]@{
      TimeStamp = $_.TimeStamp
      Path = $_.Path
      Value = ([Math]::Round($_.CookedValue, 3)) }
    }
 }

Paso 4: ¿SQL Server está impulsando la actividad de E/S pesada?

Si el subsistema de E/S está sobrecargado más allá de la capacidad, averigüe si SQL Server es el culpable examinando Buffer Manager: Page Reads/Sec (el culpable más común) y Page Writes/Sec (mucho menos común) para la instancia específica. Si SQL Server es el controlador de E/S principal y el volumen de E/S es más allá de lo que el sistema puede controlar, trabaje con los equipos de desarrollo de aplicaciones o el proveedor de aplicaciones para:

  • Ajuste de consultas, por ejemplo: mejores índices, actualización de estadísticas, reescritura de consultas y rediseño de la base de datos.
  • Aumente la memoria máxima del servidor o agregue más RAM en el sistema. Más RAM almacenará en caché más páginas de datos o índices sin volver a leer con frecuencia desde el disco, lo que reducirá la actividad de E/S.

Causas

En general, los siguientes problemas son los motivos de alto nivel por los que SQL Server consultas sufren latencia de E/S:

  • Problemas de hardware:

    • Una configuración incorrecta de SAN (conmutador, cables, HBA, almacenamiento)

    • Capacidad de E/S superada (desequilibrada en toda la red SAN, no solo almacenamiento back-end)

    • Problemas de controladores o firmware

    Los proveedores de hardware o los administradores del sistema deben participar en esta fase.

  • Problemas de consulta: SQL Server satura volúmenes de disco con solicitudes de E/S y está insertando el subsistema de E/S más allá de la capacidad, lo que hace que las tasas de transferencia de E/S sean altas. En este caso, la solución consiste en buscar las consultas que provocan un gran número de lecturas lógicas (o escrituras) y optimizar esas consultas para minimizar la E/S de disco mediante índices adecuados. Además, mantenga actualizadas las estadísticas, ya que proporcionan al optimizador de consultas información suficiente para elegir el mejor plan. Además, el diseño incorrecto de la base de datos y el diseño de consultas pueden provocar un aumento de los problemas de E/S. Por lo tanto, el rediseño de consultas y a veces tablas puede ayudar a mejorar la E/S.

  • Controladores de filtro: La respuesta SQL Server E/S puede verse gravemente afectada si los controladores de filtro del sistema de archivos procesan tráfico de E/S pesado. Se recomiendan exclusiones de archivos adecuadas del análisis antivirus y el diseño correcto del controlador de filtro por parte de los proveedores de software para evitar el impacto en el rendimiento de E/S.

  • Otras aplicaciones: Otra aplicación de la misma máquina con SQL Server puede saturar la ruta de acceso de E/S con solicitudes de lectura o escritura excesivas. Esta situación puede empujar el subsistema de E/S más allá de los límites de capacidad y provocar lentitud de E/S para SQL Server. Identifique la aplicación y ajúsela o muévalo a otro lugar para eliminar su impacto en la pila de E/S.

Representación gráfica de la metodología

Representación visual de la metodología para corregir problemas de E/S lentos con SQL Server.

A continuación se muestran descripciones de los tipos de espera comunes observados en SQL Server cuando se notifican problemas de E/S de disco.

PAGEIOLATCH_EX

Se produce cuando una tarea está esperando un bloqueo temporal para una página de datos o índice (búfer) en una solicitud de E/S. La solicitud de bloqueo temporal está en modo exclusivo. Se usa un modo exclusivo cuando el búfer se escribe en el disco. Las esperas largas pueden indicar problemas con el subsistema de disco.

PAGEIOLATCH_SH

Se produce cuando una tarea está esperando un bloqueo temporal para una página de datos o índice (búfer) en una solicitud de E/S. La solicitud de bloqueo temporal está en modo compartido. El modo compartido se usa cuando se lee el búfer desde el disco. Las esperas largas pueden indicar problemas con el subsistema de disco.

PAGEIOLATCH_UP

Se produce cuando una tarea está esperando un bloqueo temporal para un búfer en una solicitud de E/S. La solicitud de bloqueo temporal está en el modo de actualización. Las esperas largas pueden indicar problemas con el subsistema de disco.

WRITELOG

Se produce cuando una tarea está esperando a que se complete un vaciado del registro de transacciones. Un vaciado se produce cuando el Administrador de registros escribe su contenido temporal en el disco. Las operaciones comunes que provocan vaciados de registros son confirmaciones de transacciones y puntos de control.

Las razones comunes de las esperas prolongadas WRITELOG son:

  • Latencia del disco del registro de transacciones: esta es la causa más común de WRITELOG esperas. Por lo general, la recomendación es mantener los archivos de datos y de registro en volúmenes independientes. Las escrituras de registros de transacciones son escrituras secuenciales mientras que leer o escribir datos de un archivo de datos es aleatorio. La combinación de datos y archivos de registro en un volumen de unidad (especialmente las unidades de disco giratorias convencionales) provocará un movimiento excesivo de la cabeza del disco.

  • Demasiados VLF: demasiados archivos de registro virtuales (VLF) pueden provocar WRITELOG esperas. Demasiadas VLF pueden causar otros tipos de problemas, como la recuperación prolongada.

  • Demasiadas transacciones pequeñas: aunque las transacciones grandes pueden provocar bloqueos, demasiadas transacciones pequeñas pueden dar lugar a otro conjunto de problemas. Si no inicia explícitamente una transacción, cualquier inserción, eliminación o actualización dará lugar a una transacción (llamamos a esta transacción automática). Si realiza 1000 inserciones en un bucle, se generarán 1000 transacciones. Cada transacción de este ejemplo debe confirmarse, lo que da como resultado un vaciado del registro de transacciones y 1000 vaciados de transacciones. Cuando sea posible, agrupe la actualización, eliminación o inserción individuales en una transacción más grande para reducir los vaciados del registro de transacciones y aumentar el rendimiento. Esta operación puede provocar menos WRITELOG esperas.

  • Los problemas de programación hacen que los subprocesos de Log Writer no se programe lo suficientemente rápido: antes de SQL Server 2016, un único subproceso de Log Writer realizaba todas las escrituras de registros. Si hubiera problemas con la programación de subprocesos (por ejemplo, una CPU elevada), tanto el subproceso de Log Writer como los vaciados de registros podrían retrasarse. En SQL Server 2016, se agregaron hasta cuatro subprocesos de Log Writer para aumentar el rendimiento de escritura de registros. Consulte SQL 2016: solo se ejecuta más rápido: varios trabajadores de Log Writer. En SQL Server 2019, se agregaron hasta ocho subprocesos de Log Writer, lo que mejora aún más el rendimiento. Además, en SQL Server 2019, cada subproceso de trabajo normal puede realizar escrituras de registros directamente en lugar de publicarlas en el subproceso de log writer. Con estas mejoras, WRITELOG las esperas rara vez se desencadenarían por problemas de programación.

ASYNC_IO_COMPLETION

Se produce cuando se producen algunas de las siguientes actividades de E/S:

  • El proveedor de inserción masiva ("Insertar de forma masiva") usa este tipo de espera al realizar E/S.
  • Lectura del archivo Deshacer en LogShipping y dirección de E/S asincrónica para el trasvase de registros.
  • Leer los datos reales de los archivos de datos durante una copia de seguridad de datos.

IO_COMPLETION

Se produce mientras se espera a que se completen las operaciones de E/S. Este tipo de espera suele implicar E/S no relacionadas con páginas de datos (búferes). Algunos ejemplos son:

  • Lectura y escritura de los resultados de ordenación o hash desde o hacia el disco durante un derrame (compruebe el rendimiento del almacenamiento tempdb ).
  • Lectura y escritura de colas diligentes en el disco (compruebe el almacenamiento de tempdb ).
  • Leer bloques de registro del registro de transacciones (durante cualquier operación que haga que el registro se lea desde el disco, por ejemplo, la recuperación).
  • Leer una página del disco cuando la base de datos aún no está configurada.
  • Copia de páginas en una instantánea de base de datos (copia en escritura).
  • Cerrar el archivo de base de datos y la descompresión de archivos.

BACKUPIO

Se produce cuando una tarea de copia de seguridad está esperando datos o está esperando a que un búfer almacene los datos. Este tipo no es típico, excepto cuando una tarea está esperando un montaje en cinta.