Diagnósticos de SQL Server ayuda a detectar operaciones de E/S detenidas y atascadas

IMPORTANTE: Este artículo ha sido traducido por un software de traducción automática de Microsoft (http://support.microsoft.com/gp/mtdetails) en lugar de un traductor humano. Microsoft le ofrece artículos traducidos por un traductor humano y artículos traducidos automáticamente para que tenga acceso en su propio idioma a todos los artículos de nuestra base de conocimientos (Knowledge Base). Sin embargo, los artículos traducidos automáticamente pueden contener errores en el vocabulario, la sintaxis o la gramática, como los que un extranjero podría cometer al hablar el idioma. Microsoft no se hace responsable de cualquier imprecisión, error o daño ocasionado por una mala traducción del contenido o como consecuencia de su utilización por nuestros clientes. Microsoft suele actualizar el software de traducción frecuentemente.

Haga clic aquí para ver el artículo original (en inglés): 897284
Resumen
Un sistema de administración de base de datos (DBMS), como SQL Server, basa en la adecuación de los archivos de entrada y salida (E/S) de las operaciones. Uno de los siguientes elementos puede producir operaciones de E/S atascadas o detenidas y afectar negativamente al rendimiento y la capacidad de respuesta de SQL Server:

  • Hardware defectuoso
  • Hardware configurado incorrectamente
  • Configuración del firmware
  • Controladores de filtro
  • Compresión
  • Errores
  • Otras condiciones en el path de I/O
Estos problemas de E/S pueden hacer que se produzca el comportamiento siguiente:

  • Bloqueo
  • Contención de registros y tiempos de espera
  • Tiempo de respuesta lento
  • Ampliación de los límites de recursos
A partir del Service Pack 4 (SP4) de Microsoft SQL Server 2000, SQL Server incluye lógica que ayuda a detecta colgado y pegado condiciones de lecturas de E/S de base de datos y escrituras y escrituras y lecturas de E/S de archivo de registro. Cuando una operación de E/S ha estado pendiente durante 15 segundos o más, SQL Server realiza los siguientes pasos:

  1. Detecta que la operación ha estado pendiente
  2. Escribe un mensaje informativo en el registro de errores de SQL Server

    El texto del mensaje de registro similar al siguiente:

    2004-11: 11 00:21:25.26 spid1 SQL Server ha encontrado 192 apariciones de solicitudes IO tardando más de 15 segundos para que finalice en el archivo [E:\SEDATA\stressdb5.ndf] [stressdb] (7) de la base de datos. El identificador de archivo del sistema operativo es 0x00000000000074D4. El desplazamiento de la IO mucho más reciente es: 0x00000000022000 ".

Explicación del mensaje informativo

Texto del mensajeDescripción
Número > apariciones de laEl número de solicitudes de E/S que no se ha completado la operación de lectura o la escritura en menos de 15 segundos.
Información de archivoEl nombre completo del archivo, el nombre de la base de datos y el número de identificación (DBID) de la base de datos.
IdentificadorEl identificador del sistema operativo del archivo. Puede utilizar el identificador del sistema operativo con los depuradores o con otras utilidades para ayudar a realizar el seguimiento de solicitudes de paquete (IRP) de solicitud de E/S.
DesplazamientoEl desplazamiento de la última operación de E/S de pegado o había detenido de la última operación de E/S. Puede utilizar el desplazamiento con los depuradores o con otras utilidades para ayudar a realizar un seguimiento de las solicitudes IRP.

Nota: Cuando el mensaje informativo se escribe en el registro de errores de SQL Server, puede que la operación de E/S ya no ser pegada o detenida.
Este mensaje informativo indica que la carga pueda experimentar una de las siguientes condiciones:

  • La carga de trabajo supera las capacidades de ruta de E/S.
  • La carga de trabajo supera las capacidades del sistema actual.
  • El path de I/O tiene el software que funciona; tal vez un firmware o un problema del controlador.
  • El path de I/O tiene componentes de hardware que no funciona correctamente.
Para obtener más información acerca de los patrones de E/S de SQL Server 2000, visite el siguiente sitio Web de Microsoft:Nota: Este artículo de TechNet también se aplica a Microsoft SQL Server 2005 y versiones posteriores.
Más información

E/s pegado y E/S detenida

E/S atascado

Pegado de i/OS se define como una solicitud de E/S que no finaliza. Con frecuencia, entrada-salida atascado indica un IRP atascado. Para resolver una condición de entrada-salida atascada, debe reiniciar el equipo normalmente o realizar una acción similar. Una condición de entrada-salida atascada normalmente indica uno de los siguientes:

  • Hardware defectuoso
  • Un error en un componente de la ruta de acceso de E/S

E/S detenida

E/S detenida se define como una solicitud de E/S que termine o que tarda excesivo tiempo en Finalizar. Comportamiento de entrada-salida detenida normalmente se produce debido a una de las siguientes razones:

  • La configuración de hardware
  • La configuración del firmware
  • Un problema de controlador de filtro que necesita asistencia de hardware o con el proveedor de software para seguimiento y resolver

SQL Server detenida la i/OS y pegado de i/OS, registrar y realizar informes

Soporte técnico de Microsoft SQL Server controla muchos casos cada año que implican problemas de E/S atascados o detenidas. Estos problemas de i/OS aparecen en distintas formas. Problemas de E/S son algunos de los problemas más difíciles de diagnosticar y depurar, y que requieren mucho tiempo y recursos para la depuración de Microsoft y del cliente. Las características de informes que se han agregado a SQL Server 2000 SP4 y versiones posteriores significativamente reducen el tiempo que se requiere para identificar un problema de E/S.

El reporting y la grabación de las solicitudes de E/S están diseñados sobre una base por archivo. La detección y la presentación de las solicitudes de E/S detenidas y atrapadas son dos acciones distintas.

Grabación

Hay dos momentos cuando se produce una acción de registro de SQL Server. La primera es cuando termina realmente de la operación de E/S. Si una solicitud de E/S tarda más de 15 segundos en Finalizar, se produce una operación de registro. El segundo momento es cuando se ejecuta la escritura diferida. Cuando se ejecuta la escritura diferida, el escritor diferido comprueba todos los datos pendientes y el registro de pendiente las solicitudes de E/S de archivo. Si se ha superado el umbral de 15 segundos, se produce una operación de registro.

Informes

Informes se producen en intervalos que están separados de 5 minutos o más. Esto se produce cuando se realiza la siguiente solicitud de E/S en el archivo. Si se ha producido una acción de registro y han pasado 5 minutos o más desde que se produjo el último informe, el mensaje informativo que se menciona en la sección "Resumen" se escribe en el registro de errores de SQL Server.

El umbral de 15 segundos no es ajustable. Sin embargo, puede deshabilitar la detección de entrada-salida detenida o atascada utilizando el indicador de traza 830, aunque no es recomendable hacerlo.

Para deshabilitar la detección cuando se inicia SQL Server, utilice la -T830 parámetro de inicio para deshabilitar la detección cada vez que SQL Server se inicie. Para deshabilitar la detección de una instancia de SQL Server que se está ejecutando, utilice la siguiente instrucción:

DBCC traceoff (830, -1)
Esta configuración es efectiva sólo durante la vida del proceso de SQL Server.

Nota: Se informa de una solicitud de E/S que se convierte en colgado o pegada sólo una vez. Por ejemplo, si el mensaje informa de que se detengan 10 solicitudes de E/S, los 10 informes no se producen otra vez. Si el mensaje siguiente informa de que se detengan los 15 de las solicitudes de E/S, que significa que han paralizado 15 nuevas solicitudes de E/S.

El paquete de solicitud de E/S (IRP) de seguimiento

SQL Server utiliza las llamadas a API de Windows de Microsoft estándares para leer y escribir datos. Por ejemplo, SQL Server utiliza las siguientes funciones:

  • WriteFile
  • ReadFile
  • WriteFileScatter
  • ReadFileGather
La solicitud de lectura o escritura se controla por Windows como un paquete de solicitud de E/S (IRP). Para determinar el estado de IRP, utilice las dos opciones siguientes:

  • Asistencia de soporte de la plataforma de Microsoft
  • El depurador del núcleo
Para obtener más información acerca de IRP y el IRP seguimiento, vaya al siguiente sitio Web de Microsoft y buscar en la palabra clave "IRP":Nota: Depuración del núcleo puede ser un proceso invasor porque la depuración del núcleo puede requerir detener el sistema para completar las acciones de depuración. Le recomendamos que busque las actualizaciones disponibles para los siguientes elementos:

  • El BIOS
  • El firmware
  • Otros componentes de ruta de acceso de E/S
Antes de realizar acciones de depuración adicionales, póngase en contacto con su proveedor de hardware. La sesión de depuración seguramente incluirá un controlador de otro fabricante, el firmware o el componente de controlador de filtro.

El rendimiento del sistema y las acciones del plan de consulta

En general el rendimiento del sistema puede jugar un papel clave en el procesamiento de E/S. El estado general del sistema se debe tener en cuenta cuando se están investigando informes de operaciones de E/S detenidas o atascados. Cargas excesivas pueden provocar que el sistema general es lenta. Esto incluye procesamiento de E/S. El comportamiento del sistema en el momento que se produce el problema puede ser un factor clave para determinar la causa del problema. Por ejemplo, si el uso de CPU es alto o si el uso de la CPU se mantiene alto cuando se produce el problema, este comportamiento puede indicar que un proceso en el sistema está utilizando tanta CPU que otros procesos están siendo perjudicados.

Contadores de rendimiento

Para supervisar el rendimiento de E/S, examine los siguientes contadores de rendimiento para obtener información de ruta de acceso específica de i/OS:

  • Promedio segundos/transferencia
  • Longitud de la cola de disco promedio
  • Longitud de cola de disco actual
Por ejemplo, el tiempo en segundos/transferencia de disco promedio en un equipo que está ejecutando SQL Server suele ser inferior a 15 milisegundos. Si aumenta el valor de segundos de disco promedio/transferencia, esto indica que el subsistema de E/S es no óptima mantenerse al día con la demanda de i/OS.

Tenga cuidado al utilizar los contadores de rendimiento porque SQL Server aprovecha al máximo de capacidades de i/OS asincrónicas que push mucho las longitudes de cola de disco. Por lo tanto, mayores longitudes de cola de disco solos no indican un problema.

En el Monitor de sistema de Windows, puede revisar los contadores de "disco físico: Bytes de disco/seg." para cada uno de los afectados, disco y comparar la tasa de actividad de los contadores de "proceso: datos Bytes es/s" y "proceso: IO otros Bytes/s" para que cada proceso identificar si un conjunto específico de procesos es generar E/S excesiva solicita. Hay varios otros entrada-salida relacionados con objeto de contadores disponibles en el proceso que revela información más detallada. Si determina que una instancia de SQL Server es responsable de la excesiva carga de IO en el servidor, revise la siguiente sección sobre "Índices y paralelismo". Para obtener información detallada sobre la detección y resolución de los cuellos de botella de E/S, revise la sección "Cuellos de botella de E/S" en el artículo técnico MSDN Solucionar problemas de rendimiento en SQL Server 2008 o Solucionar problemas de rendimiento en SQL Server 2005.

Índices y paralelismo

Con frecuencia, las ráfagas de i/OS se producen porque falta un índice. Este comportamiento puede ingresar gravemente el path de I/O. Un paso que utiliza el Asistente de activación de índice (ITW) puede ayudar a resolver la presión de E/S en el sistema. Si una consulta se beneficia de un índice en lugar de un recorrido de tabla, o tal vez si utiliza una ordenación o hash, el sistema puede obtener las siguientes ventajas:

  • Se realiza una reducción en la E/S física que se necesita para completar la acción que crea directamente las ventajas de rendimiento de la consulta.
  • Menos páginas en la caché de datos tienen a la vuelta. Por lo tanto, las páginas que están en la caché de datos continúa siendo relevantes para consultas activas.
  • Ordena y hash se utilizan porque falte un índice o porque las estadísticas están actualizadas. Puede reducir tempdb uso y contención agregando uno o más índices.
  • Se realiza una reducción en los recursos, operaciones en paralelo o ambos. Porque SQL Server no garantiza la ejecución de consultas en paralelo, y porque se considera la carga en el sistema, es mejor optimizar todas las consultas de ejecución en serie. Para optimizar una consulta, abra el analizador de consultas y establezca el valor de sp_configure de la grado máximo de paralelismo opción a 1. Si todas las consultas se ajustan para ejecutar rápidamente como una operación en serie, paralelos de ejecución suele ser simplemente un mejor resultado. Sin embargo, muchas veces la ejecución en paralelo se selecciona porque la cantidad de datos es simplemente grande. Para un índice perdido, una ordenación grandes que tenga que producirse. Varios trabajadores que están realizando la operación de ordenación creará una respuesta más rápida. Sin embargo, esta acción puede aumentar drásticamente la presión en el sistema. Solicitudes de lectura grande muchos trabajadores pueden provocar una ráfaga de entrada-salida junto con un mayor uso de CPU de varios trabajadores. Puede ajustarse muchas veces en una consulta se ejecute más rápidamente y utilizar menos recursos si se agrega un índice, o si se produce otra acción de optimización.

Ejemplos prácticos de soporte técnico de Microsoft SQL Server

En los ejemplos siguientes han sido controlados por el soporte técnico de Microsoft SQL Server y soporte de plataformas de elevación. En estos ejemplos se pretenden proporcionar un marco de referencia y un conjunto de ayuda sus expectativas acerca de colgado y había pegado situaciones de i/OS y acerca de cómo un sistema puede verse afectado o puede responder. No hay ningún hardware específico o un conjunto de controladores que se plantean cualquier riesgo específico o mayor riesgo respecto a otro. Todos los sistemas son los mismos en este sentido.

Ejemplo 1: Una escritura en el registro que se ha quedado atascado durante 45 segundos

Intentar escribir un archivo de registro de SQL Server periódicamente queda detenido durante aproximadamente 45 segundos. La escritura en el registro no finaliza de manera oportuna. Este comportamiento crea una condición de bloqueo que hace que los tiempos de espera de cliente de 30 segundos.

La aplicación envía una confirmación a SQL Server y la confirmación se convirtió pegada como una escritura de registro pendientes. Este comportamiento hace que la consulta para seguir reteniendo bloqueos y bloquear las solicitudes entrantes de otros clientes. A continuación, otros clientes inician tiempo de espera. Esto agrava el problema porque la aplicación no deshace las transacciones abiertas cuando se produce un tiempo de espera de consulta. Esto crea cientos de transacciones abiertas que se bloquean. Por lo tanto, se produce una situación de bloqueo grave.

Para obtener más información acerca de control y bloqueo de transacciones, consulte el siguiente artículo de Microsoft Knowledge Base:La aplicación de servicios de un sitio Web utilizando la agrupación de conexiones. A medida que más conexiones se bloquean, el sitio Web crea más conexiones. Estas conexiones se bloquean y el ciclo se repite.

Después de aproximadamente 45 segundos, finalice la escritura en el registro. Sin embargo, en este momento, cientos de conexiones son una copia. Los problemas de bloqueo provocar varios minutos de tiempo de recuperación de SQL Server y de la aplicación. Combinado con los problemas de la aplicación, la condición de entrada-salida detenida tiene un efecto muy negativo en el sistema.
Solución:
Este problema se realiza un seguimiento a una solicitud de E/S atascada en un controlador de adaptador de Bus Host (HBA). El equipo tuvo varias tarjetas HBA con soporte de conmutación por error. Cuando un HBA detrás o no se comunica con la red de área de almacenamiento (SAN), el valor de tiempo de espera "reintento antes de la conmutación por error" se configuró en 45 segundos. Cuando se ha superado el tiempo de espera, la solicitud de E/S se enrutó al segundo HBA. El segundo HBA controló la solicitud y terminado rápidamente. Para ayudar a evitar tales condiciones de detención, el fabricante de hardware recomienda una configuración "reintento antes de la conmutación por error" de 5 segundos.

Ejemplo 2: Filtrar intervención del conductor

Muchos programas de software antivirus y productos de copia de seguridad utilizan controladores de filtro de i/OS. Estos controladores de filtro de i/OS a formar parte de la pila de solicitudes de E/S, y tienen acceso a la solicitud IRP. Servicios de soporte técnico de Microsoft ha visto varios problemas de los errores que crean atascado condiciones de i/OS o detenido condiciones de i/OS en una implementación de controlador de filtro.

Una condición era un controlador de filtro para el procesamiento de copia de seguridad permite una copia de seguridad de los archivos que estaban abiertos cuando se produjo la copia de seguridad. El administrador del sistema había incluido el directorio de archivos de datos de SQL Server en las selecciones de copia de seguridad del archivo. Cuando se produjo la copia de seguridad, la copia de seguridad intentó recopilar la imagen correcta del archivo en el momento de inicia la copia de seguridad. Esto retrasa las solicitudes de E/S. Las solicitudes de I/O se les permitió finalizar sólo uno a la vez como que se han controlado por el software.

Cuando se inicia la copia de seguridad, el rendimiento de SQL Server caído considerablemente debido a las operaciones de E/s de SQL Server se veían obligados a fin de uno en uno. Para complicar el problema, la lógica de "uno a uno" era tal que la operación de E/S podría no realizarse de forma asincrónica. Por lo tanto, cuando SQL Server espera para registrar una solicitud de E/S y continuar, el trabajador se bloqueó en la lectura o la llamada de escritura hasta que finaliza la solicitud de E/S. Las tareas de procesamiento, como SQL Server lectura anticipada se deshabilitaron eficazmente por las acciones del controlador de filtro. Además, otro error en el controlador de filtro deja las acciones "uno por uno" en proceso, incluso cuando se ha terminado la copia de seguridad. La única forma de restaurar el rendimiento de SQL Server fue para cerrar y volver a abrir la base de datos, reinicie SQL Server para que el identificador de archivo fue liberado y volvió a adquirir sin la interacción del controlador de filtro.
Solución:
Para resolver este problema, se han quitado los archivos de datos de SQL Server desde el proceso de copia de seguridad de archivos. La fabricación de software también corrige el problema que deja el archivo en modo "uno por uno".

Ejemplo 3: Oculta los errores

Muchos sistemas de high-end tienen multicanales paths de I/O para controlar el equilibrio de la carga o actividades similares. Soporte técnico de Microsoft ha encontrado problemas con el software donde se produce un error en una solicitud de E/S, pero el software no controla correctamente la condición de error de equilibrio de carga. El software puede intentar reintentos infinitos. La operación de E/S se atasca y SQL Server no puede finalizar la acción especificada. Igual el registro escriba condición anteriormente descrito, muchos de los comportamientos de un sistema deficiente pueden producirse después de dicha condición el sistema las cuñas.
Solución:
Para resolver este problema, a menudo es necesario reiniciar SQL Server. Sin embargo, a veces, debe reiniciar el sistema operativo para restaurar el procesamiento. También recomendamos que obtenga una actualización de software del proveedor de i/OS.

Ejemplo 4: Almacenamiento remoto, el reflejo y unidades raid

Muchos sistemas utilizan reflejo o seguir pasos similares para evitar la pérdida de datos. Algunos sistemas que utilizan reflejo están basados en software y algunos están basados en hardware. La situación que se descubre normalmente por Microsoft Support para estos sistemas es mayor latencia.

Un aumento en el tiempo total de i/OS se produce cuando las i/OS debe finalizar al reflejo antes de las i/OS se considera completa. Para las instalaciones de espejado remoto, pueden intervenir reintentos de la red. Cuando se producen errores de disco y el sistema raid se está reconstruyendo, también se puede interrumpir el patrón de E/S.
Solución:
Estricto de configuración es necesarios para reducir la latencia para espejos o para las operaciones de reconstrucción de raid.

Para obtener más información, consulte los requisitos de SQL Server admitir un espejado remoto de bases de datos de usuario.

Ejemplo 5: compresión

Microsoft no admite Microsoft SQL Server 7.0 o archivos de datos de Microsoft SQL Server 2000 y archivos de registro en unidades comprimidas. Compresión NTFS no es segura para SQL Server porque el protocolo de escribir registro anticipada (WAL) interrumpe la compresión NTFS. Compresión NTFS también requiere un mayor procesamiento para cada operación de E/S. Compresión crea "uno por uno" como el comportamiento que provoca otros problemas graves de rendimiento.
Solución:
Para resolver este problema, descomprima los archivos de registro y de los datos.

Para obtener más información, consulte la descripción de la compatibilidad con bases de datos de SQL Server en volúmenes comprimidos.

Puntos de datos adicionales

PAGEIOLATCH_ * y writelog esperas en las vistas de administración dinámica (DMV) de sys.dm_os_wait_stats son indicadores clave para investigar el rendimiento de E/S de ruta de acceso. Si ve significativo PAGEIOLATCH de espera, esto significa que SQL Server está esperando en el subsistema de E/S. Es habitual una cierta cantidad de esperas PAGEIOLATCH y comportamiento esperado. Sin embargo, si el promedio de tiempos de espera de PAGEIOLATCH es constantemente superior a 10 milisegundos (ms), debe investigar por qué el subsistema de E/S está bajo presión. Para obtener más información, consulte los siguientes documentos:



SQL Server requiere que los sistemas admiten "entrega garantizada a medios estables" tal como se describe en los requisitos del programa de i/OS de confiabilidad de SQL Server. Para obtener más información acerca de los requisitos de entrada y salidos para el motor de base de datos de SQL Server, consulte el siguiente artículo de Microsoft Knowledge Base:

EventID IO 833

Advertencia: este artículo se tradujo automáticamente

Propiedades

Id. de artículo: 897284 - Última revisión: 09/30/2015 15:59:00 - Revisión: 6.0

Microsoft SQL Server 2014 Business Intelligence, Microsoft SQL Server 2014 Developer, Microsoft SQL Server 2014 Enterprise, Microsoft SQL Server 2014 Enterprise Core, Microsoft SQL Server 2014 Express, Microsoft SQL Server 2014 Standard, Microsoft SQL Server 2014 Web, Microsoft SQL Server 2012 Developer, Microsoft SQL Server 2012 Enterprise, Microsoft SQL Server 2012 Enterprise Core, Microsoft SQL Server 2012 Express, Microsoft SQL Server 2012 Standard, Microsoft SQL Server 2012 Web, Microsoft SQL Server 2012 Business Intelligence, Microsoft SQL Server 2008 Developer, Microsoft SQL Server 2008 Enterprise, Microsoft SQL Server 2008 Express, Microsoft SQL Server 2008 Standard, Microsoft SQL Server 2008 R2 Datacenter, Microsoft SQL Server 2008 R2 Developer, Microsoft SQL Server 2008 R2 Enterprise, Microsoft SQL Server 2008 R2 Express, Microsoft SQL Server 2008 R2 Standard, Microsoft SQL Server 2008 R2 Web, Microsoft SQL Server 2008 R2 Workgroup, Microsoft SQL Server 2005 Workgroup Edition, Microsoft SQL Server 2005 Standard Edition, Microsoft SQL Server 2005 Developer Edition, Microsoft SQL Server 2005 Enterprise Edition

  • kbinfo kbtshoot kbsqlserv2000sp4fea kbmt KB897284 KbMtes
Comentarios