SQL Server está equipado con varias características para optimizar el rendimiento de las consultas, y una de esas características es la lectura anticipada o precarga. En este artículo, exploraremos cómo las lecturas anticipadas afectan el rendimiento de las consultas y discutiremos el concepto de precarga aleatoria.

SQL Server se esfuerza por proporcionar respuestas rápidas a las consultas de los usuarios. Para lograr esto, utiliza múltiples niveles de optimización para crear planes de consulta eficientes. Sin embargo, incluso con el mejor método de ejecución, las consultas aún pueden sufrir brechas de rendimiento de CPU o E/S. Para mitigar esto, SQL Server utiliza el mecanismo de lectura anticipada.

El mecanismo de lectura anticipada permite a SQL Server traer páginas de datos a la memoria caché del búfer antes de que sean solicitadas por el motor relacional. Cuando el motor relacional solicita filas al motor de almacenamiento, primero verifica si las páginas que contienen las filas ya están en la memoria caché del búfer. Si no se encuentran, las páginas se copian desde el almacenamiento físico a la memoria caché del búfer. El motor de almacenamiento puede anticipar la necesidad de más páginas e inicia el mecanismo de lectura anticipada.

Existen dos tipos de lecturas anticipadas: lecturas anticipadas secuenciales y precarga aleatoria. Las lecturas anticipadas secuenciales recuperan páginas en un orden específico, ya sea en orden de asignación o en orden de índice. Las tablas sin orden siempre se escanean en orden de asignación, mientras que los índices se escanean en el orden de clave en el que están ordenados. Por otro lado, la precarga aleatoria es necesaria para obtener un mejor rendimiento de rendimiento al tratar con E/S aleatorias, que son más lentas que las E/S secuenciales en discos giratorios convencionales.

La precarga aleatoria se observa a menudo en uniones de bucle anidado, donde la tabla externa genera E/S aleatorias a la tabla interna para el conjunto de filas coincidentes. Sin embargo, la precarga aleatoria solo se habilita si el número estimado de filas de la tabla externa es superior a 25. Se puede categorizar aún más como precarga ordenada o no ordenada.

Ilustremos el concepto de lecturas anticipadas con un ejemplo. Crearemos una tabla de prueba con datos aleatorios y habilitaremos las estadísticas IO para monitorear el fenómeno de lectura anticipada:

CREATE TABLE DBO.TestTable (
   ID int IDENTITY (1, 1) NOT NULL,
   IDvarchar AS CAST(ID AS varchar(50)) PERSISTED NOT NULL,
   intcolumn int,
   NAME varchar(50) NOT NULL,
   Age int NOT NULL,
   Randomvalue bigint
);

INSERT INTO TestTable (intcolumn, NAME, AGE)
SELECT s1.number, 'Some Random Data..', s1.number % 10 + 25
FROM master.dbo.spt_values s1
CROSS JOIN master.dbo.spt_values s2;

UPDATE TestTable
SET Randomvalue = CAST(RAND(CHECKSUM(NEWID())) * ID AS int);

ALTER TABLE TestTable ALTER COLUMN Randomvalue int NOT NULL;

CREATE UNIQUE CLUSTERED INDEX CI_TestTable_ID ON DBO.TestTable (ID)

Ahora, ejecutemos algunas consultas para observar el mecanismo de lectura anticipada:

DBCC DROPCLEANBUFFERS;
SET STATISTICS IO ON;

SELECT * FROM TestTable WHERE ID < 300;

-- Salida: Tabla 'TestTable'. Recuento de exploración 1, lecturas lógicas 5, lecturas físicas 1, lecturas anticipadas 2, lecturas lógicas LOB 0, lecturas físicas LOB 0, lecturas anticipadas LOB 0.

En el ejemplo anterior, borramos la memoria caché del búfer utilizando DBCC DROPCLEANBUFFERS y luego ejecutamos una consulta. La salida de estadísticas IO muestra que se trajeron 2 páginas de lectura anticipada a la memoria caché del búfer.

De manera similar, podemos observar el impacto de la precarga aleatoria con uniones de bucle anidado:

SELECT * INTO Testtable2 FROM TestTable t1;
CREATE UNIQUE CLUSTERED INDEX ix_id_testtable2 ON testtable2 (IDvarchar);
CREATE UNIQUE INDEX ix_IDVARCHAR ON testtable2 (IDvarchar);

DBCC DROPCLEANBUFFERS;
SET STATISTICS IO ON;

SELECT *
FROM TestTable t1
WHERE IDvarchar IN 
   (SELECT IDvarchar
    FROM testtable2 t2)
AND ID < 27 
OPTION (LOOP JOIN);

-- Salida: Tabla 'Testtable2'. Recuento de exploración 0, lecturas lógicas 143, lecturas físicas 1, lecturas anticipadas 240, lecturas lógicas LOB 0, lecturas físicas LOB 0, lecturas anticipadas LOB 0.
-- Tabla 'TestTable'. Recuento de exploración 1, lecturas lógicas 3, lecturas físicas 2, lecturas anticipadas 0, lecturas lógicas LOB 0, lecturas físicas LOB 0, lecturas anticipadas LOB 0.

En este ejemplo, creamos otra tabla con datos aleatorios y forzamos al optimizador a elegir una unión de bucle anidado. La salida de estadísticas IO muestra lecturas anticipadas en Testtable2, lo que indica la presencia de precarga aleatoria.

Es importante tener en cuenta que las lecturas anticipadas están habilitadas de forma predeterminada en SQL Server. Sin embargo, puede haber casos en los que la estimación de cardinalidad de exploración o búsqueda sea demasiado baja en comparación con la cardinalidad real, lo que resulta en que el mecanismo de lectura anticipada no se active. Esto puede afectar negativamente el rendimiento de la ejecución de consultas.

Cuando el sistema está bajo una carga pesada o cuando están involucradas uniones de múltiples tablas, la diferencia de rendimiento con y sin lecturas anticipadas se vuelve más pronunciada. Para demostrar esto, podemos utilizar la herramienta de prueba del subsistema de almacenamiento de Microsoft, diskspd.exe, para presionar el subsistema de disco. Al deshabilitar las lecturas anticipadas utilizando indicadores de seguimiento documentados, podemos observar el impacto en el tiempo de ejecución de la consulta:

DBCC DROPCLEANBUFFERS; 
DBCC TRACEON (652);

SELECT *
FROM TestTable
WHERE ID < 300000;

-- Salida: RAR deshabilitado (tiempo transcurrido = 13978 ms.)

DBCC TRACEOFF (652);

DBCC DROPCLEANBUFFERS;

SELECT *
FROM TestTable
WHERE ID < 300000;

-- Salida: RAR habilitado (tiempo transcurrido = 2587 ms.)

En el ejemplo anterior, deshabilitamos las lecturas anticipadas secuenciales utilizando el indicador de seguimiento 652. El tiempo de ejecución de la consulta aumentó significativamente cuando se deshabilitaron las lecturas anticipadas.

De manera similar, podemos deshabilitar la precarga aleatoria utilizando el indicador de seguimiento 8744 y observar el impacto:

DBCC DROPCLEANBUFFERS;
DBCC TRACEON (8744);

SELECT *
FROM TestTable t1
WHERE IDvarchar IN 
   (SELECT IDvarchar
    FROM testtable2 t2)
AND ID < 1000
OPTION (RECOMPILE, LOOP JOIN, MAXDOP 1);

-- Salida: precarga deshabilitada (tiempo transcurrido = 37398 ms.)

DBCC TRACEOFF (8744);

DBCC DROPCLEANBUFFERS;

SELECT *
FROM TestTable t1
WHERE IDvarchar IN 
   (SELECT IDvarchar
    FROM testtable2 t2)
AND ID < 1000
OPTION (RECOMPILE, LOOP JOIN, MAXDOP 1);

-- Salida: precarga habilitada (tiempo transcurrido = 2565 ms.)

En este ejemplo, deshabilitamos la precarga aleatoria utilizando el indicador de seguimiento 8744. El tiempo de ejecución de la consulta aumentó significativamente cuando se deshabilitó la precarga.

Es evidente que las lecturas anticipadas y la precarga aleatoria desempeñan un papel crucial en la optimización del rendimiento de las consultas, especialmente bajo una carga pesada del sistema o al tratar con uniones de múltiples tablas. Comprender estos conceptos puede ayudar a los administradores de bases de datos y desarrolladores a ajustar sus consultas para obtener un rendimiento óptimo.

En conclusión, las lecturas anticipadas y la precarga aleatoria de SQL Server contribuyen significativamente a la optimización del rendimiento de las consultas. Al traer páginas de datos a la memoria caché del búfer antes de que sean solicitadas, SQL Server minimiza las brechas de rendimiento de CPU y E/S. Sin embargo, es importante tener en cuenta los escenarios en los que estos mecanismos pueden no activarse, ya que puede afectar la ejecución de las consultas. Al comprender y aprovechar estas características, los profesionales de bases de datos pueden garantizar un procesamiento de consultas eficiente y rápido en SQL Server.