Hoy vamos a comentar una opción que aunque existe desde hace varios años, es desconocida por muchos: “Large Value Types Out Of Row”.

¿Qué nos ofrece? Imagina una tabla con varias columnas y algunas de ellas de tipo grande (VARCHAR(MAX), NVARCHAR(MAX), XML, TEXT, VARBINARY, etc). La tabla requerirá muchas más páginas (disco) para almacenar la información, que si no tuviera esas columnas. Bien, con esta opción habilitada (y algo de trabajo adicional si la tabla ya tiene datos) podemos hacer que las consultas y operaciones DML con esta tabla sean significativamente más rápidas, además de otros beneficios.

¿Cómo es posible? Tras habilitar esta opción, las columnas de tipo grande pasan a almacenarse en páginas separadas. En una tabla que ya existe con miles de registros, esto puede implicar una liberación de espacio significativa en las páginas que usaba la tabla inicialmente. Dependerá de cuanta información de tipo grande existe. En una consulta que utiliza scans como la del ejemplo más abajo, vamos a ver cómo se reduce el número de páginas cuando se ejecuta la consulta. Esto significa que la consulta va a ser más rápida y consumirá menos recursos: Memoria y CPU.

La opción se habilita a nivel de tabla y la sintaxis es bastante sencilla:

USE AdventureWorks2014; -- Cambiar por tu nombre de BBDD
GO

EXEC sp_tableoption 'Person.Person', 'large value types out of row', 1;
 

En el siguiente ejemplo donde puedes, ejecutando paso a paso, cómo afecta positivamente esta opción al número de lectura lógicas cuando hacemos una consulta en la tabla modificada.

-- Limpiamos la cache para hacer pruebas sin nada precargado en memoria
DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE;
GO
USE AdventureWorks2014;
GO

-- 1) Comprobamos las estadísticas de acceso a disco para la siguiente consulta, esta tabla tiene la opción large_value_types_out_of_row desactivada(valor por defecto)
SET STATISTICS IO, TIME ON;
GO

SELECT FirstName
		, MiddleName
		, LastName
		, ModifiedDate
	FROM Person.Person;

SET STATISTICS IO, TIME OFF;
GO
-- RESULTADO:
--		(19972 rows affected)
--		Table 'Person'. Scan count 1, logical reads 3847, physical reads 3, read-ahead reads 3976, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--		(1 row affected)
--		SQL Server Execution Times:
--		CPU time = 47 ms,  elapsed time = 1055 ms.

-- 2) Cambiamos la opción large_value_types_out_of_row en la tabla
EXEC sp_tableoption 'Person.Person', 'large value types out of row', 1; -- switch on the feature
GO

-- 3) El cambio de esta opción no toma efecto para los valores existentes hasta el momento en que se actualicen
UPDATE Person.Person
	SET AdditionalContactInfo = AdditionalContactInfo
	WHERE AdditionalContactInfo IS NOT NULL;

UPDATE Person.Person
	SET Demographics = Demographics
	WHERE Demographics IS NOT NULL;

-- Reconstruimos todos los índices para qué las páginas que lo componen se reordenen, ojo esta operación puede ser costosa y generar bloqueos
ALTER INDEX ALL ON Person.Person REBUILD;

-- Limpiamos la cache de nuevo para comparar el efecto de la nueva config.
DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE;

-- 4) Comprobar de nuevo las estadísticas de acceso a disco, como en el paso 1)
SET STATISTICS IO, TIME ON;
GO

SELECT FirstName
		, MiddleName
		, LastName
		, ModifiedDate
		--, AdditionalContactInfo
	FROM Person.Person;

SET STATISTICS IO, TIME OFF;
GO
-- RESULTADO:
--		(19972 rows affected)
--		Table 'Person'. Scan count 1, logical reads 268, physical reads 0, read-ahead reads 220, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--		(1 row affected)
--		SQL Server Execution Times:
--		CPU time = 15 ms,  elapsed time = 241 ms.

Observa que la cantidad de lecturas lógicas pasa de 3800 a menos de 300. Esto es una reducción del 90% para este escenario. En todas las tablas no va a pasar lo mismo, ya que depende principalmente de los datos que haya en la tabla, y de la consulta en sí.

Necesitarás la Base de datos de AdventureWorks2014, puedes descargarla en este enlance. El script anterior también está disponible en GitHub.

Si quieres comprobar cuantas tablas pueden beneficiarse de esta opción en tus bases de datos, puedes usar esta consulta que he subido a la TechNet Gallery. No olvides dar tu valoración! 🙂 Si quieres ver más aquí tienes un link a la documentación oficial.

Si lo piensas, esto es equivalente a hacer un particionado vertical de una tabla, sacando las columnas grandes a una tabla nueva. Lo interesante de la opción “Large Value Types Out Of Row” es que es completamente transparente para la aplicación, y mucho más sencillo de implementar.

Si lo probáis, comentadme que tal os parece y los resultados.

Hasta el próximo artículo!