Breaking

Post Top Ad

Your Ad Spot

domingo, 15 de diciembre de 2019

Problema de rendimiento de registros reenviados en SQL Server

Este artículo analiza los registros reenviados y sus problemas de rendimiento para las tablas de montón en SQL Server.

Introducción al montón y problemas de rendimiento

Una página de 8 KB es la unidad de almacenamiento más pequeña en SQL Server. En cada página de SQL Server, tenemos un encabezado de 96 bytes para almacenar la información del sistema. SQL Server almacena los datos de dos maneras:
  • Índice agrupado
    Almacena datos en una estructura de árbol B + de acuerdo con la clave de índice agrupada definida. SQL Server almacena la nueva o una actualización de una fila existente en la posición lógica correcta
  • Montón
    Una tabla sin ningún índice agrupado es un montón. Una tabla de almacenamiento dinámico almacena datos sin ningún orden lógico. No vincula páginas porque no tenemos ninguna clave definida en las tablas de montón. Podemos crear un índice no agrupado en la tabla de montón, pero que contiene una dirección física para los registros de datos subyacentes. Contiene el número de archivo, el número de página y el número de ranura dentro de esa página para el registro
  • Página Espacio libre (PFS)
    SQL Server escanea la página Espacio libre de página (PFS) antes de insertar un nuevo registro en la tabla de montón. Si la página de datos contiene suficiente espacio libre, almacena la nueva fila en la página existente. Alternativamente, si no tiene suficiente espacio libre, SQL Server asigna una extensión (ocho nuevas páginas de datos - 64 KB). PFS monitorea cada página de datos utilizando dos bits como se especifica a continuación:
    0x00
    Página de datos vacía
    0x01
    50% página de datos completa
    0x02
    51 a 80% página de datos completa
    0x03
    81 a 95% página de datos completa
    0x04
    96 a 100% página de datos completa

Simulación de problema de registro reenviado

Digamos que actualizamos los datos existentes en una tabla de montón. SQL Server no puede acomodar los nuevos datos en la página existente debido a su mayor tamaño de datos. En este caso, estos datos se almacenan en una ubicación de almacenamiento diferente y SQL Server inserta un registro reenviado en la ubicación de datos original. SQL Server también mantiene un puntero en la nueva ubicación de datos que apunta al puntero reenviado para que pueda realizar un seguimiento de la cadena de puntero de reenvío en caso de cualquier movimiento de datos.
Creemos una tabla de montón y reproduzcamos el escenario de registro reenviado:

Crear una base de datos y una tabla de montón Empleados

Las tablas de montón tienen un índice de identificación cero, y puede identificar las tablas de montón utilizando la vista del sistema sys.indexes. El siguiente comando muestra la salida requerida de la vista del sistema:
Verificar tipo de tabla
Utilizamos la función de administración dinámica (DMF) sys.dm_db_index_physical_stats para verificar el porcentaje de fragmentación del índice, el tipo de índice de la tabla, los recuentos de páginas y los recuentos de registros reenviados. Estos detalles están disponibles en el modo DETALLADO de este DMV:
Index fragmenantion
En la captura de pantalla anterior, podemos ver 17 páginas para la tabla del montón Empleados y cero recuentos de registros reenviados.
Inserte algunos registros más utilizando la siguiente consulta. Debería aumentar las páginas y la fragmentación del índice:
Vuelva a ejecutar la consulta DMF especificada anteriormente y valide los recuentos de páginas y recuentos de registros de reenvío:
  • El recuento de páginas aumenta de 17 a 27
  • La fragmentación aumenta del 33 al 50%.
  • El registro reenviado cuenta aún en cero
Recuento de registros reenviados
Ahora, actualice los registros en la tabla Empleado para la columna de longitud variable [Nombre]:
Ahora, nuevamente use el DMF y vea los recuentos de registros hacia adelante:
  • El recuento de páginas aumenta de 27 a 35
  • Los recuentos de registros enviados aumentan de cero a 747
Use el DMF para el recuento de páginas y los registros previstos
Realicemos algunas actualizaciones más utilizando la siguiente consulta:
En la siguiente captura de pantalla, tenga en cuenta las cosas:
  • El recuento de páginas aumenta de 35 a 46
  • Los recuentos de registros enviados aumentan de 747 a 1752
revalidar los datos
SQL Server no pudo acomodar las nuevas actualizaciones en las páginas existentes y creó los registros de reenvío que aumentan los recuentos de páginas y reenvío de registros de cuentas.

Pregunta: ¿Los registros reenviados causan problemas de rendimiento?

Sí, podemos ver un impacto en el rendimiento de la tabla del montón debido a estos registros reenviados. Ejecute la instrucción de selección que realiza la búsqueda en la columna [Nombre]. Permitimos que las estadísticas IO capturen las estadísticas requeridas:
En el plan de ejecución, podemos ver un operador de exploración de tabla para el montón:
Plan de ejecución real
En la siguiente imagen, podemos ver que SQL Server usa páginas IAM para encontrar las páginas y extensiones que requieren un escaneo. Analiza la extensión que pertenece a la tabla de montón y los procesa en su orden de asignación:
Estructura del montón
Si la asignación de páginas cambia, también cambia el orden en que las páginas deben escanearse. Aquí, escanea una página antes de la página 2 debido a su asignación:
Orden de escaneo de tabla de montón
En la pestaña de mensajes de la ejecución de consultas anteriores, muestra información sobre exploraciones lógicas y físicas. Podemos ver 1798 operaciones de lectura lógica para recuperar los datos solicitados:
Lecturas lógicas
En el caso de una gran tabla de almacenamiento dinámico, podemos ver el valor considerable de estas lecturas lógicas que pueden estar causando problemas de rendimiento para la recuperación de datos, DML.

Corrección de problemas de registros reenviados

Usar tipo de datos de longitud fija

A veces, usamos tablas de montón para las tablas de ensayo y no requerimos índice agrupado en una tabla de montón. La mejor manera de solucionar estos problemas de registros reenviados y evitar tantas lecturas lógicas es mediante el uso de tipos de datos de longitud fija. No debemos usar tipos de datos de longitud variable a menos que sea necesario.

Use un índice agrupado

Deberíamos agregar un índice agrupado a una tabla porque clasifica y almacena datos según la clave de índice agrupado. Funciona tanto para datos existentes como nuevos y actualizados. Idealmente, deberíamos definir una clave primaria en la tabla, ya que crea una clave de índice agrupada de forma predeterminada.

Supervisar y reconstruir la tabla de montón

Si debido a algún requisito específico, no podemos usar el tipo de datos de longitud fija o el índice agrupado en una tabla de montón, la mejor manera es monitorear las tablas de montón para los registros reenviados utilizando los scripts proporcionados anteriormente. Podemos usar el comando Alter Table..REBUILD para reconstruir una tabla de montón. Está disponible en SQL Server 2008. También actualiza el índice no agrupado en la tabla de montón.

Lecturas lógicas antes de la reconstrucción de la tabla

Lecturas lógicas antes de la reconstrucción de la tabla

Reconstruir tabla de montón

Ejecute el siguiente comando para reconstruir la tabla de montón de empleados:

Verifique las lecturas lógicas después de la reconstrucción

Lecturas lógicas después de la reconstrucción de la tabla
Podemos notar el siguiente cambio:
  • Lecturas lógicas antes de la reconstrucción del montón: 1798
  • Lecturas lógicas después de la reconstrucción del montón: 40
El comando Modificar tabla reconstruye completamente el montón y elimina todos los registros reenviados. No hacemos ningún registro reenviado después de la reconstrucción. Vamos a verificarlo:
  • Los recuentos de páginas se restablecen a 40 desde 46
  • Los recuentos de registros enviados se vuelven cero desde 1752 a cero
  • La fragmentación promedio para el montón también se reduce a cero desde el 33 por ciento anterior
La fragmentación se reduce a cero

Conclusión


El diseño de la base de datos es un criterio esencial para los desarrolladores y administradores de bases de datos para evitar problemas de rendimiento debido al diseño. En este artículo, exploramos los problemas de los registros reenviados con la tabla de montón y las lecturas lógicas debido a estos grandes recuentos de registros reenviados. También desperdicia las páginas de la base de datos debido a los registros reenviados y pone problemas de rendimiento de E / S debido a las tablas grandes. Deberíamos tratar de evitar las tablas de montón y, si es necesario, seguir los pasos necesarios descritos anteriormente.

No hay comentarios.:

Publicar un comentario

Dejanos tu comentario para seguir mejorando!

Post Top Ad

Your Ad Spot

Páginas