Una de las novedades técnicas de bajo nivel que se introdujeron en SQL Server 2008 R2 y versiones posteriores fue una mejora en el algoritmo de hashing utilizado para la gestión de bloqueos. Aunque a algunos puede sorprenderles, cuando el gestor de bloqueos de SQL Server registra un bloqueo de fila lo que se almacena realmente no es la clave de la fila. Lo que realmente se almacena es un hash de 6 bytes que podremos ver en la columna «resource description» de la DMV sys.dm_tran_locks.

La mejora en el algoritmo desde SQL Server 2008 R2 lo que consigue es una mejor distribución de los hash generados lo cual reduce (según MS) en hasta 15000 veces la probabilidad de colisión comparada con el algoritmo anterior. Sin embargo debemos tener en cuenta que el hash tiene únicamente 6 bytes (esto no se ha modificado) lo cual deja la puerta abierta a colisiones cuando la cantidad de valores distintos sea elevada. En este post vamos a intentar forzar este tipo de colisión en SQL Server 2014 para poder constatar por nosotros mismos la mejora en el algoritmo utilizado.

El escenario necesario para buscar encontrar un conflicto requiere de una tabla suficientemente grande por lo que utilizamos una tabla de enteros de 8 bytes (bigint). A efectos prácticos no necesitamos llegar a 2^48 valores distintos (281.474.976.710.656) ya que no necesitamos el 100% de probabilidades de conflicto. El primer reto es cómo llenar dicha tabla con una cantidad de valores elevados en un tiempo razonable. Al tratarse de inserciones en una misma tabla rápidamente nos podemos encontrar con problemas de concurrencia, de throughput en el log de transacciones, etc. Como factor adicional indicar que la prueba la realizamos sobre una máquina virtual de Azure por lo que el reto era aún mayor.

Lo primero que hicimos fue descartar la posibilidad de que el log de transacciones estuviera almacenado en un disco de datos tradicional de Azure ya que, simple y llanamente, es demasiado lento para poder llegar a los niveles de inserción que nos planteamos como objetivo. Utilizar Premium Storage tampoco hubiera servido de nada ya que son operaciones de escritura (no aceleradas en Premium Storage, solo las de lectura). Probamos a almacenar el log de transacciones en el disco temporal de una máquina D13 pero finalmente optamos por almacenar el log de transacciones en un disco RAM para reducir al máximo las latencias. El rendimiento del disco RAM en operaciones pequeñas de escritura con baja profundidad de cola (típicas del log de transacciones) era el más elevado entre las opciones que teníamos disponibles:

lockres_config_ramdisk_qd1

Para almacenar el fichero de datos sí utilizaremos el disco SSD local (unidad D:) el cual nos proporcionará un rendimiento suficiente de escritura aleatoria. Desde el punto de vista de configuración de la base de datos y de la instancia también hicimos algunos ajustes. Para comenzar configuramos la base de datos en modo simple y forzamos el uso de delayed transactions:

lockres_config_bbdd

 

También se activó el trace flag 610, así como otros flags orientados a optimizar el uso de tempdb y la asignación de extents:

lockres_config

 

Aunque intentamos hacer algunas pruebas con compresión de datos a nivel de página (ya que parecía que podríamos mejorar) finalmente se optó por una tabla de números sin compresión al obtener un mejor rendimiento con esta opción. Tras las pruebas, se optó también por una carga de datos concurrente sobre un índice cluster sin tablock. Concretamente se utilizó la combinación marcada en amarillo que sí permite que la operación sea mínimamente registrada en el log de transacciones:

Table IndexesRows in tableHintsWithout TF 610With TF 610Concurrent possible
HeapAnyTABLOCKMinimalMinimalYes
HeapAnyNoneFullFullYes
Heap + IndexAnyTABLOCKFullDepends (3)No
ClusterEmptyTABLOCK, ORDER (1)MinimalMinimalNo
ClusterEmptyNoneFullMinimalYes (2)
ClusterAnyNoneFullMinimalYes (2)
ClusterAnyTABLOCKFullMinimalNo
Cluster + IndexAnyNoneFullDepends (3)Yes (2)
Cluster + IndexAnyTABLOCKFullDepends (3)No

Quien tenga especial interés sobre este tipo de cargas masivas tiene una muy interesante lectura en el whitepaper de MS al respecto (https://technet.microsoft.com/en-us/library/dd425070%28v=sql.100%29.aspx)

El siguiente paso era generar los inserts para nuestra tabla de enteros. Probamos varias alternativas, pero la opción que mejor throughput nos dio fue utilizar varios paquetes de SSIS insertando concurrentemente. El paquete tenía únicamente un dataflow muy sencillo:

lockres_ssis2

El script generador de enteros es muy sencillo también y básicamente tiene un bucle que genera enteros entre dos valores (minInt y maxInt) que nos llegan como variables:

lockres_ssis3

Estas variables están a nivel de paquete y se pueden configurar en las ejecuciones del paquete de forma que cada uno de los paquetes se encarga de «rellenar» un rango de enteros sin que exista solapamiento entre ellos:

lockres_ssis1

Otra opción para minimizar el coste de la ordenación en el índice cluster es indicar al generador que la salida de enteros va a estar en orden ascendente:

lockres_ssis5

Esto se combina con la propiedad bulkinsertorder del destino y un commit size de tamaño moderado.

lockres_ssis4

 

En las pruebas comprobamos que con un único thread, sin concurrencia, la opción con tablock y ordenación de datos es la más rápida. Sin embargo no nos compensa si podemos lanzar varias cargas concurrentes. Es decir, si lanzábamos con tablock y concurrencia se serializaban las ejecuciones con lo que el throughput total no escalaba, suficiente razón para descartar su uso. Finalmente en la configuración del paquete indicamos también que se ejecutara en modo optimizado y con el runtime de 64 bits:

lockres_optimizedmode_64 bits

Una vez almacenado el paquete en el sistema de ficheros, para lanzar los paquetes simplemente utilizaremos el comando START de MSDOS combinado con el comando dtexec:

start dtexec /FILE ""C:UserssolidqDesktopCarga BigIntCarga BigIntPackage.dtsx"" /CHECKPOINTING OFF /REPORTING P /SET ""Package.Variables[User::minInt]"";0 
start dtexec /FILE ""C:UserssolidqDesktopCarga BigIntCarga BigIntPackage.dtsx"" /CHECKPOINTING OFF /REPORTING P /SET ""Package.Variables[User::minInt]"";1000000000 
start dtexec /FILE ""C:UserssolidqDesktopCarga BigIntCarga BigIntPackage.dtsx"" /CHECKPOINTING OFF /REPORTING P /SET ""Package.Variables[User::minInt]"";2000000000
start dtexec /FILE ""C:UserssolidqDesktopCarga BigIntCarga BigIntPackage.dtsx"" /CHECKPOINTING OFF /REPORTING P /SET ""Package.Variables[User::minInt]"";3000000000
start dtexec /FILE ""C:UserssolidqDesktopCarga BigIntCarga BigIntPackage.dtsx"" /CHECKPOINTING OFF /REPORTING P /SET ""Package.Variables[User::minInt]"";4000000000
start dtexec /FILE ""C:UserssolidqDesktopCarga BigIntCarga BigIntPackage.dtsx"" /CHECKPOINTING OFF /REPORTING P /SET ""Package.Variables[User::minInt]"";5000000000
start dtexec /FILE ""C:UserssolidqDesktopCarga BigIntCarga BigIntPackage.dtsx"" /CHECKPOINTING OFF /REPORTING P /SET ""Package.Variables[User::minInt]"";6000000000
start dtexec /FILE ""C:UserssolidqDesktopCarga BigIntCarga BigIntPackage.dtsx"" /CHECKPOINTING OFF /REPORTING P /SET ""Package.Variables[User::minInt]"";7000000000
start dtexec /FILE ""C:UserssolidqDesktopCarga BigIntCarga BigIntPackage.dtsx"" /CHECKPOINTING OFF /REPORTING P /SET ""Package.Variables[User::minInt]"";8000000000
start dtexec /FILE ""C:UserssolidqDesktopCarga BigIntCarga BigIntPackage.dtsx"" /CHECKPOINTING OFF /REPORTING P /SET ""Package.Variables[User::minInt]"";9000000000
start dtexec /FILE ""C:UserssolidqDesktopCarga BigIntCarga BigIntPackage.dtsx"" /CHECKPOINTING OFF /REPORTING P /SET ""Package.Variables[User::minInt]"";10000000000
start dtexec /FILE ""C:UserssolidqDesktopCarga BigIntCarga BigIntPackage.dtsx"" /CHECKPOINTING OFF /REPORTING P /SET ""Package.Variables[User::minInt]"";11000000000
start dtexec /FILE ""C:UserssolidqDesktopCarga BigIntCarga BigIntPackage.dtsx"" /CHECKPOINTING OFF /REPORTING P /SET ""Package.Variables[User::minInt]"";12000000000
start dtexec /FILE ""C:UserssolidqDesktopCarga BigIntCarga BigIntPackage.dtsx"" /CHECKPOINTING OFF /REPORTING P /SET ""Package.Variables[User::minInt]"";13000000000
start dtexec /FILE ""C:UserssolidqDesktopCarga BigIntCarga BigIntPackage.dtsx"" /CHECKPOINTING OFF /REPORTING P /SET ""Package.Variables[User::minInt]"";14000000000
start dtexec /FILE ""C:UserssolidqDesktopCarga BigIntCarga BigIntPackage.dtsx"" /CHECKPOINTING OFF /REPORTING P /SET ""Package.Variables[User::minInt]"";15000000000

Comenzamos testeando el paquete con 8 ejecuciones concurrentes (1 por core) y nos encontramos que saturábamos la CPU al 100% de forma constante al  generar aproximadamente 1.3 millones de inserciones de enteros por segundo:

lockres_8threads

Analizando las esperas a nivel de SQL Server y operativo observamos valores excesivos en el acceso a la CPU, lo cual sugería que estábamos exprimiendo demasiado el uso de dicho recurso. Si bajábamos a 4 ejecuciones concurrentes vimos que prácticamente usábamos toda la CPU y gracias a reducirse estas esperas el throughput subía a algo más de 2 millones de inserciones por segundo:lockres_4threadsEste es otro caso más donde la virtualización y el Hyperthreading pueden llevarnos a engaño (pensar que tenemos 8 cores físicos dedicados) y hacernos añadir más carga que la que soportan los recursos físicos reales que tenemos disponibles (con la consecuente penalización en rendimiento).

Una vez tuvimos ese rendimiento por encima de 2 millones de filas por segundo, 7.200 millones por hora, 172.800 millones por día, dejamos a la máquina trabajar unas horas. Cuando consideramos que teníamos suficientes registros para hacer alguna prueba, detuvimos la carga. La base de datos con una única tabla tenía en ese momento unos 350 GB de datos y algo más de 16 mil millones de filas. Para que nos pongamos un poco en contexto, este número de filas equivale a aproximadamente a 4 veces el rango completo de un entero de 32 bits (incluyendo los valores negativos).

lockres_rowcount

Es importante comentar también que las filas finalmente optamos por cargarlas tanto en el rango negativo del bigint como en el positivo, un escenario quizás menos habitual y que pensamos que podría complicarle un poco evitar las colisiones al algoritmo de hashing.  Una vez tuvimos la tabla cargada lanzamos la siguiente consulta en búsqueda de conflictos:

ALTER TABLE [dbo].[bigint] SET (LOCK_ESCALATION = DISABLE)
GO

SELECT res
FROM
(
	SELECT bigint,%%lockres%% res FROM bigint (NOLOCK)
)
a
GROUP BY res
HAVING count(*)>1

Indicar que la cantidad de espacio temporal necesario para ejecutar la consulta anterior fue elevado (~1 TB), así como el tiempo de ejecución (~10 horas):

lockres_tempdb

El resultado final de la consulta nos indicó que en los 16 mil millones de registros solo hay un único conflicto con el mismo valor de hash (b9e8b80ea96e). Por tanto podemos concluir que la mejora del algoritmo es cierta ya que en versiones anteriores podíamos encontrar múltiples colisiones utilizando únicamente un entero de 32 bits mientras que con el nuevo algoritmo solo hemos encontrado una colisión con cuatro veces más volumen de datos.

 

Rubén Garrigós

Rubén Garrigós is an expert in high-availability enterprise solutions based on SQL Server design, tuning, and troubleshooting. Over the past fifteen years, he has worked with Microsoft data access technologies in leading companies around the world. He currently is a Microsoft SQL Server and .NET applications architect with SolidQ. Ruben is certified by Microsoft as a Solution Expert on the Microsoft Data Platform (MSCE: Data Platform) and as a Solution Expert on the Microsoft Private Cloud (MSCE: Private Cloud). As a Microsoft Certified Trainer (MCT), Ruben has taught multiple official Microsoft courses as well as other courses specializing in SQL Server. He has also presented sessions at official events for various Microsoft technologies user groups.

Latest posts by Rubén Garrigós (see all)