-- 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