Contención en tempdb?

O porque necesito 10.000.000 archivos de datos para tempdb.

Uso de tempdb.

A lo largo de las versiones de SQL Server el uso de tempdb se ha hecho cada vez mayor para diferentes partes del sistema, el mayor cambio se vivió de 2000 a 2005 y en 2008 este uso se incrementó un poquito más. Por tanto es crítico tener una configuración correcta para tempdb.

tempdb es usado principalmente en:

• Tablas temporales (#tabla y ##tabla)
• Varibles de tipo tabla (@tabla)
• Operaciones intermedias de ordenación (Sort warnings)
• Operaciones intermedias de “HASH" (Hash warnings)
• …

 

Cual es el número de archivos recomendados para tempdb.

Fácil, tantos como cores físicos tenga nuestra máquina https://msdn.microsoft.com/en-us/library/ms175527.aspx hasta un máximo de 8, a partir de 8 archivos deberemos estudiar si el coste de tener muchos archivos para tempdb es menor que las mejoras alcanzadas por tener tantos archivos, esto depende del tipo de carga que tengamos en tempdb y debe ser estudiado para cada situación.

 

Cual es el indicador de que tenemos algun tipo de contención en tempdb

La mejor forma es monitorizar las esperas con la siguiente query:

 

SELECT  wt.session_id AS [Waiting Session ID], waiting_text.text AS [Waiting SQL], wt.wait_type, bc.session_id AS [Blocking Session ID], blocking_text.text AS [Blocking SQL], resource_description FROM sys.dm_os_waiting_tasks wt LEFT OUTER JOIN sys.dm_exec_connections wc on wt.session_id=wc.session_id LEFT OUTER JOIN sys.dm_exec_connections bc on wt.blocking_session_id=bc.session_id OUTER APPLY sys.dm_exec_sql_text(wc.most_recent_sql_handle) AS waiting_text OUTER APPLY sys.dm_exec_sql_text(bc.most_recent_sql_handle) AS blocking_text

 

Dependiendo del tipo de espera y recurso el tipo de contención es diferente y la solución también.

Primero vamos a explicar como leer los recursos de página, un recurso de página se compone de tres partes X:Y:Z donde:

X – ID de la base de datos (SELECT * FROM sys.databases WHERE database_id = X para obtener la base de datos)

Y – ID del archivo de la base de datos (SELECT * FROM sys.master_files where database_id = X and file_id = Y para obtener el archivo en cuestión)

Caso 1 -  Tipo de espera PAGEIOLATCH_XX – Recurso 2:Y:Z

PAGEIOLATCH_XX (XX cualquier valor) Indica que SQL a iniciado una lectura de disco y está esperando a que la pagina esté en memoria para continuar la ejecución de la query.

 

En este caso deberemos

1 – Estudiar si el rendimiento de los discos es adecuado

2 – Separar diferentes archivos de tempdb en diferentes discos para mejorar el rendimiento de entrada salida

 

Caso 2 – Tipo de espera PAGALATCH_UP – Recurso 2:Y:1 o "2:Y:P (P múltiplo de 8088)

La pagina 1 y las páginas múltiplo de 8088 son un tipo de página especial llamadas PFS (Page Free Space) estas paginas sirven para llevar la cuenta de que páginas están usadas y cuales no y cuando se crea una nueva tabla debemos consultarlas y actualizarlas convenientemente, para evitar corrupción SQL debe serializar las actualizaciones a estas paginas.

Para solucionar este tipo de contención deberemos:

1 – Crear tantos archivos de base de datos como cores físicos tenga la máquina

SQL Server intenta asignar el trabajo nuevo a diferentes archivos de base de datos al tener mas archivos de base de datos reducimos la probabilidad de que dos threads ataquen el mismo archivo mismo PFS.

2 – En casos extremos deberemos habilitar el traceflag 1118 al inicio de SQL Server. https://blogs.msdn.com/b/psssql/archive/2008/12/17/sql-server-2005-and-2008-trace-flag-1118-t1118-usage.aspx

Por defecto SQL Server cuando crea una nueva tabla reserva las primeras paginas de 1 en 1, a la novena pagina las paginas son reservadas de 8 en 8, esto es para optimizar el espacio. Con esta opción activada SQL Server reserva las páginas de 8 en 8 desde el principio reduciendo los viajes al PFS.

 

Un saludo,

Pablo Gavela López – Microsoft Customer Support Services