Verne academy

VERNE ACADEMY > BLOG > ARTíCULOS SOBRE DATA > OTRA RAZóN MáS PARA MIGRAR A SQL SERVER 2022

Otra razón más para migrar a SQL Server 2022

Contenidos del Post

Entre las nuevas características que se introducen en SQL Server 2022, destacan las mejoras que se incluyen relacionadas con el rendimiento, es una de las razones por la que apostamos por migrar a esta versión.

En el post que se desarrolla a continuación, te mostraremos mejoras en escenarios de contención de acceso de páginas del sistema, concretamente sobre estructuras GAM y SGAM. En es desarrollo del artículo, veremos un ejemplo del comportamiento de SQL Server 2022 en estos escenarios, frente a versiones anteriores.

Antes de ver el ejemplo, aclararemos algunos conceptos que son necesarios para entender el objetivo de este post que es mostrar la mejora en escenarios de contención de las estructuras GAM y SGAM. Los conceptos que se revisarán son las estructuras de páginas, extensiones y mapas de asignación en SQL Server.

Concepto de extensión en SQL Server

La unidad fundamental del almacenamiento de datos en SQL Server es la página. El espacio en disco asignado a un archivo de datos (.mdf o .ndf) en una base de datos se divide lógicamente en páginas numeradas de forma contigua de 0 a n. Estas páginas son estructuras de datos de 8KB y es la unidad que se utiliza en las operaciones de E/S de disco, por tanto, SQL Server lee o escribe páginas de datos enteras.

Aunque no entremos en profundidad en estructuras de páginas y extensiones, veremos, principalmente, la estructura básica de páginas de datos y páginas de índices. Por otro lado, existen páginas con metadatos que ayudan al sistema a organizar la estructura de las páginas de datos, como es el caso de las páginas asignadas a las estructuras GAM y SGAM, que veremos más adelante.

La estructura de una página consta de un encabezado con metadatos de 96 bytes y, a continuación, las filas de datos y, al final de dicha página, tenemos una estructura de datos en la que se registra la ubicación de cada fila, permitiendo al motor buscar filas en una página de forma ágil.

Estructura interna de una página en SQL Server
Estructura interna de una página

Por otro lado, las extensiones son una colección de ocho páginas físicamente contiguas y se utilizan para administrar las páginas de forma eficaz. Todas las páginas se organizan en extensiones.

Como podemos ver en la siguiente ilustración, existen dos tipos de extensiones:

  • Uniform Extents: Un uniform extent está asociado a un solo objeto, es decir, todas las páginas de datos de esa extensión pertenecen al mismo objeto.
  • Mixed Extents: Un mixed extent contiene páginas de datos compartidas por múltiples objetos. Estas páginas se asignan a objetos diferentes dentro de la misma extensión. Un mixed extent puede alojar página de hasta ocho objetos.

El sistema de administración de almacenamiento del SQL Server organiza y gestiona los datos utilizando las extensiones para optimizar el almacenamiento y el rendimiento de la base de datos.

Estructura de extensiones SQL Server 2022
Estructura de extensiones

Para ver de forma práctica los conceptos de página y su organización en extensiones, vemos un ejemplo básico en el que se crea una tabla en el que se insertan datos. En la demo, podremos identificar las páginas que se ubican en la misma extensión.

				
					USE tempdb;

IF OBJECT_ID(N'dbo.T1', N'U') IS NOT NULL DROP TABLE dbo.T1;
CREATE TABLE dbo.T1(
   Name CHAR(8000)
)

INSERT INTO dbo.T1 VALUES('John')

GO 26 -- Insertamos 26 registros

DBCC IND('TempDB','dbo.T1',1)

				
			
ejemplo extensiones sql server - Verne Academy

PageType (1 – Datos, 2 – Índice, 3 y 4 – Texto, 8 – GAM, 9 – SGAM, 10 – IAM, 11 – PFS).

En la tabla anterior, podemos identificar las páginas que se organizan en 4 extensiones.

Este ejemplo es un caso simplificado para ilustrar la organización interna de los datos. Para la asignación de páginas a una extensión se utilizan los mapas de asignación que se apoyan en estructuras de datos que permitirán una administración eficiente del espacio en disco.

Cursos de SQL Server

Tengas el nivel que tengas, ¡tenemos un curso de SQL para ti! Desde un nivel básico, pasando por el intermedio y llegando al avanzado, hasta aprender planes de ejecución y a auditar tus propias consultas.

Concepto de mapa de asignación (GAM, SGAM y PFS)

Un mapa de asignación es una estructura de datos cuyo objetivo es administrar la distribución de la información en un archivo de datos.

Los tres tipos de mapas de asignación son:

  • Global Allocation Map (GAM): Este mapa de asignación registra las extensiones que se han asignado para cualquier uso. GAM tiene un bit para cada extensión del archivo de datos. Si el bit es 1, la extensión correspondiente está libre, si el bit es 0, la extensión correspondiente está en uso como extensión uniforme o mixta.
  • Page Free Space (PFS): El PFS es otro tipo de mapa de asignación que registra el espacio libre dentro de una página de datos. Ayuda a determinar si hay espacio disponible en una página de datos para insertar nuevos registros.
  • Shared Global Allocation Map (SGAM): SGAM es similar al GAM. En este caso, se registran las extensiones mixtas con, al menos, una página sin uso facilitando la administración del espacio de almacenamiento en un archivo de datos.

Gestión de concurrencia a estructuras GAM/SGAM en SQL Server 2022

Veamos con un ejemplo la mejora introducida por SQL Server 2022 en referencia al acceso concurrente a estructuras GAM/SGAM. Te invito a que reproduzcas la demo que se muestra a continuación y que te ayudará a entender, de forma comparativa, el comportamiento entre versiones de SQL Server.

Una vez aclarados los conceptos vistos anteriormente, vamos a desarrollar una demo en la que nos marcamos tres objetivos:

  • Poner en práctica los conceptos relacionadas con mapas de asignación que hemos visto en apartados anteriores.
  • Entender la contención sobre las estructuras GAM/SGAM en operaciones de creación de objetos en un periodo corto de tiempo.
  • Entender como se gestiona la contención de las estructuras GAM/SGAM en SQL Server 2022 que, por cierto, es el objetivo principal de este post.

La demo, que se desarrolla a continuación, muestra la contención a estructuras GAM/SGAM de la base de datos tempdb en un proceso masivo de creación de variables tipo tabla.

Para forzar este escenario, vamos a limitar el número de archivos en la TempDB para que el efecto que perseguimos sea más visible.

Para visualizar el efecto que vamos a provocar, utilizamos dos contadores de Performance Monitor SQL Statistics: SQL Statistics/Batch requests/sec y SQL Server:SQL Statistics/Page latch waits/Waits.

Las esperas Page Latch muestra la tasa de contención sobre páginas en memoria. En nuestro caso, esa contención se localizará en las páginas de las estructuras GAM y SGAM.

Por otro lado, el contador Batch requests/sec nos mostrará el número de operaciones que se ejecutan:

				
					-- Configuración de la demo

-- Configurar Performance Monitor con SQL Server SQL Statistics:SQL Statistics/Batch requests/sec (set Scale to 0.1) y SQL Server:SQL Statistics/Page latch waits/Waits started per second.

-- Reducimos el número de archivos en la tembdb para forzar mayor contención.
-- Revisamos la configuración actual de archivos en tempdb
USE master;
GO
SELECT name, physical_name, size*8192/1024 as size_kb, growth*8192/1024 as growth_kb
FROM sys.master_files
WHERE database_id = 2;
GO

/*
name	physical_name						size_kb	growth_kb
tempdev	C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\tempdb.mdf		8192	65536
templog	C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\templog.ldf		8192	65536
temp2	C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_2.ndf	8192	65536
temp3	C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_3.ndf	8192	65536
temp4	C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_4.ndf	8192	65536
*/

-- Antes de cambiar la configuración hemos de hacer un inicio mínimo de la instancia para que el motor permita aplicar dicho cambio.
-- Para el inicio mínimo (configuración mínima), hemos de parar los servicios de SQL Server y reiniciarlos con el parámetro -f
-- net stop mssqlserver
-- net start mssqlserver /f

-- Modificamos configuración de tempdb, para limitar el número de archivos.
-- USE master;
-- GO
-- ALTER DATABASE tempdb MODIFY FILE (NAME=templog , SIZE = 200Mb, FILEGROWTH = 65536Kb);
-- GO
-- ALTER DATABASE tempdb REMOVE FILE temp2;
-- GO
-- ALTER DATABASE tempdb REMOVE FILE temp3;
-- GO
-- ALTER DATABASE tempdb REMOVE FILE temp4;
-- GO

-- Una vez modificada la configuración de la tempdb, volvemos a arrancar la instancia de forma normal.
-- net stop mssqlserver
-- net start mssqlserver

-- Comprobamos que la configuración de la tempdb se ha aplicado correctamente. En este caso solo debe aparecer un solo archivo de datos.
USE master;
GO
SELECT name, physical_name, size*8192/1024 as size_kb, growth*8192/1024 as growth_kb
FROM sys.master_files
WHERE database_id = 2;
GO

-- Pasamos a ejecutar el proceso de creación de 5.000.000 de variables tipo tabla. Dicha operación generará tiempos de espera Page Latch
-- Compara los resultados obtenidos en un entorno SQL Server 2019 y SQL Server 2022


-- Ejecutamos operación que hará uso de la tempdb como es la creación y uso de variables tipo tabla
-- Ejecutar en otra sesión
GO
declare @t table (c1 varchar(100)); insert into @t values ('x');
GO 5000000

-- Ejecutamos la siguiente consulta en otra sesión mientras se ejecuta el proceso anterios.
-- Cuando ejecutamos la demo en entorno SQL Server 2019, observaremos que todos los latch waits se hacen sobre páginas de estructuras GAM/SGAM
-- Cuando ejecutamos la demo en entorno SQL Server 2022, el resultado de la siguiente consulta, apenas devuelve datos por la optimización de la contención en estas estructuras
USE tempdb;
GO
SELECT object_name(page_info.object_id), page_info.* 
FROM sys.dm_exec_requests AS d 
  CROSS APPLY sys.fn_PageResCracker(d.page_resource) AS r
  CROSS APPLY sys.dm_db_page_info(r.db_id, r.file_id, r.page_id,'DETAILED')
    AS page_info;
GO

-- NO TE OLVIDES DE REVERTIR LA CONFIGURACION DE LA TEMPDB. Modifica el script 
-- USE master;
-- GO
-- ALTER DATABASE tempdb MODIFY FILE (NAME=templog, SIZE = 8192Kb, FILEGROWTH = 65536kb);
-- GO
-- ALTER DATABASE tempdb ADD FILE (NAME=temp2, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_2.ndf', SIZE = 8192Kb, FILEGROWTH = 65536Kb);
-- GO
-- ALTER DATABASE tempdb ADD FILE (NAME=temp3, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_3.ndf', SIZE = 8192Kb, FILEGROWTH = 65536Kb);
-- GO
-- ALTER DATABASE tempdb ADD FILE (NAME=temp4, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_4.ndf', SIZE = 8192Kb, FILEGROWTH = 65536Kb);
-- GO

				
			

La importancia de ejecutar esta demo es comparar los resultados obtenidos de Performance Monitor que se muestra a continuación. En ellas se observa que la tasa de Pages Latch Waits es mucho más abrupta en un entorno SQL Server 2019 frente a SQL Server 2022.

Comportamiento de la demo en SQL Server en 2019

Demo en SQL Server en 2019

Comportamiento de la demo en SQL Server en 2022

Demo en SQL Server en 2022

Conclusión

Como hemos visto, dentro del marco de nuestra apuesta por la migración a SQL Server 2022, el objetivo principal de este post es mostrar la mejora de rendimiento en escenarios de contención de las estructuras GAM y SGAM.

Si te animas a ejecutar la demo, podrás comprobar, de forma objetiva, la mejora que supone en la gestión de contención de estructuras claves de sistema, minimizando tiempos de esperas asociada a dicha contención (Page Latch Waits).

No es esta la única mejora que incorpora la nueva versión, en artículos anteriores analizamos las mejoras incluidas en el ámbito de Intelligent Processing Query apoyados en Query Store.

Te animo a que analices estas mejoras y que evalúes si encajan en tus sistemas de información. Las mejoras analizadas en este post y en los post anteriores, no requieren refactorización de código en SQL, por tanto, no requiere un esfuerzo adicional para poder acceder a dichas mejoras.

Facebook
Twitter
LinkedIn
Antonio Llompart
Antonio Llompart
Data & Cloud Mentor
Deja una respuesta

Artículos Recomendados

¿Buscas formación para

ti o para tu empresa?

Desarrolla tu talento o el de tu equipo con formaciones prácticas impartidas por expertos de la industria. Tú eliges la modalidad: formación privada, clases públicas online en directo o cursos on-demand (formación en video). Durante los cursos trabajamos sobre casos reales y soluciones aplicadas en proyectos, basadas en nuestros años de experiencia. ¿Hablamos? 😉

Carrito0
Aún no agregaste productos.
Seguir viendo
0