Mejora de desempeno de SQL 2014 con Delayed Transaction Durability

Imagen de kenneth

Votar: 

Average: 5 (6 votes)

En el boom de SQL server 2014, hay una fiebre por hablar de In-Memory, pero no solo in-memory fue agregada en esta versión de SQL Server, hay una funcionalidad que a mi humilde opinión tiene mucho valor para ciertas características de carga y durabilidad de la información. Esta funcionalidad se llama Delayed Transaction Durability.

En este artículo, vamos a mostrar los siguientes aspectos:

  1. Descripción del funcionamiento
  2. Sintaxis para activarlo
  3. Comparaciones de desempeño
  4. Conclusiones

Descripción del funcionamiento

Recordemos como funciona una transacción de escritura, por ejemplo un update, dentro de SQL server, en forma general, y dejando de lado la parte de locking/blocking.

  1. SQL Server recibe el comando de update.
  2. Si la página que necesita actualizar no está en memoria, entonces SQL Server la carga.
  3. Se realiza la actualización sobre la página en memoria y escribe en el VLF(Virtual Log File) actual de memoria.
  4. Al realizar el commit se baja el VLF(Virtual Log File) al Log File
  5. Se devuelve el control a la aplicación

Tal y como se muestra en la siguiente figura: 

Ahora, con la nueva funcionalidad de Delayed Transaction Durability, lo que se logra es devolverle el control a la aplicacion, antes de bajar el VLF de memoria a disco, siguiendo los siguientes pasos:

  1. SQL Server recibe el comando de update.
  2. Si la página que necesita actualizar no está en memoria, entonces SQL Server la carga.
  3. Se realiza la actualización sobre la página en memoria y escribe en el VLF(Virtual Log File) actual de memoria.
  4. Se devuelve el control a la aplicación
  5. Posteriormente se baja a el VLF a Disco.

Tal y como se muestra en la siguiente figura

Sintaxis para Activarlo

En General se necesitan dos cosas:

  1. Que la base de datos acepte Delayed Durability
  2. Cuando se realiza commit de una transacción, indicar que es Delayed_durability 

Tal y como se muestra en el siguiente ejemplo.

  1. CREATE DATABASE DelayedDurability_test
  2.  
  3. /*Activar Delayed_Durability en la base de datos*/
  4. ALTER DATABASE [DelayedDurability_test]
  5.    SET DELAYED_DURABILITY = ALLOWED WITH NO_WAIT
  6.  
  7. USE DelayedDurability_test
  8. GO
  9. CREATE TABLE t1 (
  10.   id INT IDENTITY (1, 1) PRIMARY KEY CLUSTERED,
  11.   Name VARCHAR(100) NOT NULL
  12. )
  13. GO
  14.  
  15. BEGIN TRANSACTION
  16.   INSERT INTO t1 (Name)
  17.     VALUES ('Kenneth prueba de 31 caracteres')
  18. COMMIT TRANSACTION WITH(DELAYED_DURABILITY = ON )
  19. /*Usarlo en la transacción*/

Comparaciones del desempeño

Realice dos pruebas de performance

  1. El log file de la base de datos guardado en una unidad lenta (memory card)
  2. El Log File de la base de datos guardo en una unidad rápida (SSD)
     

El log file de la base de datos guardado en una unidad lenta (memory card)

Mediante la herramienta CristalDiskMark, obtuve las siguientes métricas de la unidad:

Al ejecutar SQLQuerystress en las mismas condiciones, la mejora que se obtuvo fue de 6.5x aproximadamente tal y como se puede ver en la siguiente imagen:

El Log File de la base de datos guardo en una unidad rápida (SSD)

Mediante la herramienta CristalDiskMark, obtuve las siguientes métricas de la unidad:

Al ejecutar SQLQuerystress en las mismas condiciones, la mejora que se obtuvo fue de 1.6x tal y como se puede ver en la siguiente imagen:

Conclusiones

Al activar la funcionalidad de "Control Transaction Durability (delayed)" estamos entrando en un mundo de aceptar perdida de datos, ya que si por A o B la escritura de disco falla, la información no va a ser persistida, y puede llegar a perderse.

En el fondo, lo que estamos sacrificando en consistencia de la información por desempeño. Y aquí es donde se  nos dan las herramientas para proponer soluciones.

Casos de uso o consideraciones:

  • Si por la naturaleza del tráfico de la información es aceptable la posible inconsistencia de datos, y mis unidades de log files están saturadas, y no se puede mejorar el desempeño de estas unidades, sería buena opción activar esta funcionalidad.
  • Si mis unidades de log están saturadas, y no se puede (a nivel de presupuesto) actualizar el desempeño de la unidad, le mostraría los pros y contras a los stakeholders de la base de datos, y decidiríamos en conjunto si encendemos la funcionalidad.

En el fondo es, si no puedo obtener unidades para log que me den el desempeño de SSD's, y el desempeño de la aplicación se está viendo afectado creo que esa sería la ocasión para sacarle proVecho a esta funcionalidad.