Compartir a través de

SQL q &R: Cuellos de botella y registros de transacciones

  • Artículo

A veces es fácil determinar la naturaleza de un cuello de botella de rendimiento, otras veces, no tantos. Lo mismo ocurre con la configuración de los registros de transacciones.

Paul S. Randal

Cuello de botella de almacenamiento de información

P: ¿ Estoy tratando de determinar la naturaleza del cuello de botella de rendimiento de mi sistema. Estoy convencido de que es la capa de almacenamiento de información como estoy viendo la cola de disco longitudes ir más alto que los dos. He leído que esto es una buena manera de probar que SQL Server es sobrecargar el almacenamiento de información. ¿Es esto cierto? Si es así, ¿qué puedo hacer acerca de él?

R: Lamentablemente, lo que has leído es una leyenda urbana. SQL Server está diseñado para usar E/S asincrónica y felizmente impulsará la longitud de la cola de disco por encima de los dos. Cada subproceso que emite una IO a continuación, continúa haciendo otra cosa (potencialmente) hasta la IO está completa. SQL Server trata de maximizar el rendimiento del subsistema de E/S mediante la emisión de IOs asincrónicas concurrentes. También llevará a cabo operaciones como lectura anticipada al analizar grandes volúmenes de datos.

De hecho, algunas operaciones como DBCC CHECKDB saturarán subsistema de E/S. No es raro ver longitudes de cola de disco de varios cientos. Puede leer más acerca de esta leyenda urbana de longitudes de cola de disco en el este de blog post de .

Se hace la pregunta, "qué hacer se mire para determinar si hay un cuello de botella del subsistema de E/S?" Hay dos contadores de rendimiento en el objeto de rendimiento de disco físico. Debe prestar atención a estos:

  • Prom. Segundos/lectura
  • Prom. Segundos de disco/escritura

Ofrecen el tiempo en milisegundos que se tarda en completar una IO. Si estos números son sistemáticamente más altos (o tiene los picos más altos regulares) que la norma (que debería estar entre 5 ms y 12 ms), entonces el disco físico es el cuello de botella de E/S. Por supuesto, que el disco físico puede ser un LUN de SAN, pero no puede ir más allá de Windows.

Si tiene varios archivos de datos y de registro de SQL Server en ese disco físico, puede que necesite determinar qué archivos están causando la carga de IO. Utilice la administración dinámica de vista (DMV) sys.dm_io_virtual_file_stats y realizar algunos simples análisis de series temporales en los resultados.

Si los resultados de la DMV no indican una pesada carga en ese disco físico, un administrador de almacenamiento de información puede ha colocado archivos de otras aplicaciones en esa parte del subsistema de E/S. Ese volumen de trabajo podría ser lo que está monopolizando el ancho de banda de E/S. En ese caso, debe pedirle a ese particular administrador para mover los archivos de SQL Server a una dedicado parte de subsistema de E/S.

Si es puramente archivos de SQL Server en el subsistema y puede identificar cuáles son causa de la excesiva IO, tenga en cuenta estas estrategias:

  • Mire la carga de trabajo de consulta de base de datos y determinar si está realizando exploraciones de tablas excesiva debido a una estrategia de indexación incorrecta, o planes de consulta mal causados por las estadísticas no actualizadas.
  • Mover algunos de los archivos a una parte diferente del subsistema de E/S.
  • Agregar más memoria en el servidor para permitir un mayor grupo de búferes de SQL Server (en memoria caché de las páginas del archivo de datos) y evitar tanto Lee IO.

Si ninguno de estos trabajos y es realmente sólo un caso donde la carga de trabajo ha superado el subsistema de E/S, mover a un subsistema de E/S más capaz. Usted también podría considerar el almacenamiento de memoria flash de clase empresarial como fusión-io.

Cuestiones de tamaño

P: ¿ Yo estoy diseñar los requerimientos de almacenamiento de algunos nuevos servidores y estoy teniendo problemas para determinar cuán grande para que los registros de transacciones. En el pasado, he tratado de que se basa en el tamaño de la transacción. A veces, sin embargo, parece que doble. ¿Puede explicar ¿cómo puedo llegar a una estimación decente?

R: No hay ninguna fórmula fácil para calcular el tamaño de registro de transacción óptimo. Por desgracia, también es difícil para evitar que la transacción crecimiento de registro a menos que has desactivado crecimiento automático del archivo de registro. Sin embargo, si desactiva crecimiento automático nunca es aconsejable.

El primero de ellos es el nivel RAID subyacente. Los diferentes niveles RAID tienen diferentes compensaciones en cuanto se refiere a rendimiento y redundancia. Por ejemplo, la configuración de RAID más barata que aún ofrece algunos redundancia es RAID-5, pero esta configuración sólo puede hacer frente a un fallo de disco único (a menos que mediante RAID-6, o configurados unidades de repuesto) y a veces puede perjudicar el rendimiento para cargas de trabajo de escritura-pesado, dependiendo de cuántas unidades de disco son de la matriz.

El registro de transacciones, siempre debe ser capaz de crecimiento automático. Esto es particularmente cierto para situaciones de emergencia cuando falla control de tamaño de su archivo de registro. Por ejemplo, dispone de una alerta de agente SQL Server en el contador de rendimiento por ciento registro utiliza va superior al 90 por ciento, pero el contacto de emergencia una notificación por correo electrónico/buscapersonas final está fuera por enfermedad. Si el registro no puede crecer, todo está ejecutando transacciones de modificación de la base de datos se detener y hacer retroceder. Esto se traduce en el tiempo de inactividad para su carga de trabajo.

Sin embargo, diciendo que no hay ninguna fórmula fácil es un poco engañosa. Hay numerosas operaciones que ocupan un espacio de registro de transacciones. Se puede utilizar el tamaño de estas operaciones para estimar sus requerimientos de registro de transacciones. Éstos pueden ser las operaciones que se producen como parte de su carga de trabajo día a día, o acciones más infrecuentes como mantenimiento de base de datos. Usted tiene que tener en cuenta a todos ellos. La lista de operaciones incluye:

  • La mayor operación de insert/update/delete sola su carga de trabajo realiza (ya sea una transacción implícita de single-declaración que afecta a millones de filas de la tabla, o una transacción explícita que realiza muchas operaciones).
  • La operación masiva más grande realiza de su carga de trabajo, como por ejemplo un BULK INSERT. Si está utilizando el modo de recuperación completa, puede reducir la cantidad de registro de transacciones generado mediante el modelo de recuperación BULK_LOGGED. Si utiliza el modelo de recuperación BULK_LOGGED para el registro de un mínimo-en algunas operaciones, puede afectar a su capacidad para recuperar después de un desastre. Ver mi blog post, " un servidor SQL Server DBA mito al día: BULK_LOGGED recuperación modelo . "
  • Una reconstrucción de índices de su mayor índice agrupado. Usted puede ser capaz de utilizar BULK_LOGGED aquí también.

Con todas estas operaciones, no es sólo la cantidad de registros de transacciones que es necesario considerar, también debe tener en cuenta el espacio del sistema de gestión de registro de transacciones se "reserva" para permitir la reversión de la transacción adecuada. Si una transacción genera 100 MB de registros del registro de transacciones, el sistema reservará aproximadamente 100 MB de espacio vacío en el registro de transacciones para garantizar que puede anular la transacción y hacer retroceder el correctamente. Es un mecanismo de seguridad para evitar que una base de datos cada vez incoherentes. Por eso, es posible que han visto el registro de transacciones crecen, a pesar de que cree que le ha dado suficiente espacio para la transacción más grande.

Otro aspecto a considerar es si hay cualquier razones por qué registros del registro de transacciones deben permanecer en el registro. Esto podría conducir a un registro de transacciones cada vez mayor necesidad de crecer aún más. Algunas de las razones posibles incluyen:

  • La base de datos es utilizando los modelos de recuperación completa o BULK_LOGGED y no realizar copias de seguridad de registro de transacciones (o ejecución con poca frecuencia). Debe guardar registros del registro antes de descartarlos les.
  • Hay una transacción inusualmente larga. Esto evitará que descartar todos los registros de registro de transacciones generados desde que se inició la transacción de larga duración.
  • Reflejo de la base de datos está en uso y algunos registros del registro de transacciones no han sido enviados desde el servidor principal al servidor reflejado. No se puede descartar estos hasta que han sido enviados.
  • Duplicación transaccional (o replicación peer-to-peer) está en uso y hay algunos registros de registro de la transacción que no ha procesado el trabajo de agente de lector del registro.

Si usted está viendo la creciente de registro de la transacción y no está seguro de por qué está sucediendo, solicitar SQL Server. Ejecutar la consulta:

SELECT [log_reuse_wait_desc] FROM sys.databases
WHERE [name] = 'dbmaint2008';
GO

El resultado será la razón no puede descartar algunos registros de registro y volver a utilizar el espacio de registro (llamado "Borrar" o "truncar" el registro).

Como puede ver, hay bastantes cosas que pueden afectar el tamaño del registro de transacciones, aún más si está considerando la base de tempdb datos también. Puede leer un poco más sobre este tema en mi blog post, " de la importancia de la gestión de tamaño de registro de transacción adecuada," y en el technet Magazine el artículo " comprensión registro del servidor SQL y recuperación ."

Registro obligatorio

P: ¿ ¿Puede explicar por qué no se puede realizar operaciones de SQL Server no ha iniciado la sesión? He leído que truncamiento de tabla es no ha iniciado sesión: ¿por qué no puede haber un ajuste para hacer todas las operaciones que no-identificadas con lo que SQL Server se ejecuta más rápido? ¿Qué sucede si no me preocupa ser capaz de recuperarse después de un desastre? Sobre todo, me gustaría ser capaz de pasar por alto el registro de transacciones de tempdb.

R: No es cierto lo que has leído acerca de una operación de TRUNCATE TABLE. Todas las operaciones en las bases de datos se registran en cierto grado. Algunos "mínimamente iniciados," tales como truncamiento de tabla. En pocas palabras, una operación mínimamente registrada es uno que sólo la asignación y la eliminación de la asignación de las páginas del archivo de datos que haya iniciado. No se registra ninguna operación en registros de índice de la tabla en las páginas. Esto aumenta la velocidad de las operaciones y significa menos de las transacciones se generaron, pero todavía hay algunos registro.

Una truncamiento de tabla siempre mínimamente se registra en todos los modelos de recuperación. Las demás operaciones mínimamente registradas (por ejemplo, cargas de compilación/reconstrucción y a granel de índice) se registran sólo mínimamente al utilizar los modelos de recuperación SIMPLE o BULK_LOGGED.

Las operaciones sólo verdaderamente no registrados en SQL Server son los que afectan a la tienda de versión en tempdb, que admite funciones tales como el aislamiento de instantáneas y las operaciones de índice en línea. Estos pueden ser no-registrados porque nunca hay necesidad de hacer retroceder a una operación de almacén de versión o la ejecución de recuperación de fallos sobre la base de datos tempdb.

Este modo se obtiene al quid de la cuestión. ¿Por qué no pueden ser operaciones en SQL Server no ha iniciado la sesión? SQL Server siempre tiene que ser capaz de hacer retroceder una operación si algo va mal. ¿Si hay que ninguna descripción (como los registros de transacción de registros) de la operación de lo que había hecho, cómo SQL Server sabría qué hacer durante la restauración? Sólo se puede lograr a través del registro.

Incluso si no se preocupan por la recuperación de fallos, SQL Server aún tiene que ser capaz de hacer retroceder las operaciones si la base de datos se queda sin espacio o encuentra con un sector dañado, o si la consulta se queda sin memoria. Si SQL Server no puede revertir una operación, la base de datos se vuelve inutilizable y se detiene la carga de trabajo.

Esto también se aplica a la base de datos tempdb. Aunque el registro es simplificado y reducido en tempdb, nunca se puede eliminar totalmente por las mismas razones. Además, SQL Server tiene que ser capaz de ejecutar la recuperación de fallos después cada accidente para cada base de datos sea coherente. De lo contrario, la base de datos es inutilizable. Balance: No hay ninguna manera de que las operaciones no registradas en SQL Server y no espero para cambiar.

Paul Randal

Paul S. Randal es el director general de SQLskills.com, un director regional de Microsoft y un MVP de SQL Server. Trabajó en el equipo de motor de almacenamiento de SQL Server en Microsoft desde 1999 a 2007. Escribió DBCC CHECKDB o reparación para SQL Server 2005 y fue responsable por el motor de almacenamiento de información principal durante el desarrollo de SQL Server 2008. Randal es un experto en recuperación ante desastres, alta disponibilidad y mantenimiento de la base de datos y es una presentadora de regular en conferencias en todo el mundo. Él blogs en SQLskills.com/blogs/paul y usted lo puede encontrar en Twitter en Twitter.com/PaulRandal.

Contenido relacionado