EXISTS VS COUNT

Este es un pequeño post para hacer una comparación que al inicio pareció para mi poco obvia debido a que estas dos instrucciones bajo cierto contexto hacen lo mismo pero en cuanto a performance hay una diferencia entre ellas. Estamos hablando de las instrucciones “COUNT” y “EXISTS”.

Muchas veces cuando dentro de un proceso se desea averiguar si en una tabla hay o no registros se pueden usar ambas sentencias de manera intercambiable, pero es importante conocer cual es el comportamiento de cada una y cual es más óptima bajo el punto de vista de la performance de la consulta.

La instrucción “COUNT” es una función de agrupación que nos permite agregar resultados. En el caso que se quisiera saber cuantos registros hay en una tabla esta sentencia seria la ideal, pero si sólo se quiere saber si en una consulta devuelve o no registros entonces veremos que no es tan óptima cono se piensa, esto debido a que al usar esta instrucción el motor de base de datos tiene que ejecutar la consulta, luego contar todos los registros devueltos y finalmente devolver el resultado. Veamos un ejemplo simple con el buffer cache limpio y también con datos:

DBCC DROPCLEANBUFFERS

SET STATISTICS IO ON

DECLARE @v_cont INT
SELECT
@v_cont = COUNT(1) FROM Person.Contact
IF @v_cont > 0
PRINT 'Hay registros'

/*
COLD CACHE
==========
Table 'Contact'. Scan count 1, logical reads 59, physical reads 3, read-ahead reads 57, lob logical reads 0,
lob physical reads 0, lob read-ahead reads 0.

HOT CACHE
=========
Table 'Contact'. Scan count 1, logical reads 59, physical reads 0, read-ahead reads 0, lob logical reads 0,
*/

Como se puede observar se han efectuado 59 reads para poder obtener el resultado. Ahora vamos a compararlo con la instrucción “EXISTS” la cual es una expresión que devuelve un valor verdadero o falso. Si se usa esta instrucción para verificar si en una consulta devuelve o no registros, el motor de base de datos ejecutará la consulta y si encuentra un registro al menos, dejara de leer debido a que solo le hace falta un registro para saber que el resultado es verdadero. Es decir que esta instrucción ayuda con la performance de las consultas debido a que usa menos registros. Veamos el mismo ejemplo anterior pero con la instrucción “EXISTS”:

DBCC DROPCLEANBUFFERS

SET STATISTICS IO ON

IF EXISTS (SELECT 1 FROM Person.Contact)
PRINT 'Hay registros'

/*
COLD CACHE
==========
Table 'Contact'. Scan count 1, logical reads 2, physical reads 2, read-ahead reads 0, lob logical reads 0,
lob physical reads 0, lob read-ahead reads 0.

HOT CACHE
=========
Table 'Contact'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0,
lob physical reads 0, lob read-ahead reads 0.
*/

Como se puede observar hay una diferencia bastante marcada, con la instrucción “EXISTS” sólo se hicieron 2 lecturas, mientras que con la instrucción “COUNT” se hicieron 59. Entonces es muchísimo mas óptimo usar un “EXISTS” en comparación a un “COUNT” debido a la cantidad de recursos que usan cada uno.  Espero que lo tengan en cuenta cuando escriban código para SQL Server.

Proceso de Recuperación de la Base de Datos

El proceso de recuperación de la base de datos es muy importante, que ocurre en ocasiones cuando se cierra inesperada o incorrectamente la base de datos. Cuando esto ocurre la base de datos queda en una estado inconsistente debido a que como ya hemos mencionado las bases de datos de SQL Server trabajan la información en memoria, es decir cuando se hacen modificaciones de datos en las tablas, estas antes deben de llevar las páginas de datos involucradas a la memoria, una vez en memoria recién se hacen las modificaciones, pero que es lo que sucede si justo después del cambio, el flujo de energía eléctrica hacia el servidor se corta, como sabemos las paginas se trabajaron en memoria, y este recurso es volátil, entonces cuando el servidor se vuelva a encender las paginas en memoria habrán desaparecido, ahora ¿cómo es que SQL Server asegura la durabilidad de la información cuando se hacen transacciones?, pues bueno lo hace a través del log de transacciones.

Recordemos un poco el proceso, cuando ya se tienen las paginas en memoria y se les hacen los cambios respectivos en cuanto a data se refiere, SQL Server escribe en el log de transacciones los cambios realizados por la transacción, esta escritura si es en disco, es decir las paginas se modifican en memoria, pero el registro de transacciones se modifica en disco, una vez que la escritura en el log de transacciones ha sido realizada, recién la base de datos le informa al usuario que la transacción ha sido confirmada.  Entonces cuando el servidor se enciende luego de que la base de datos ha sido cerrada incorrectamente, SQL Server ejecuta un proceso de recuperación para cada una de las bases de datos, en el cual primero se hace un “Rollforward” y luego un “Rollback”, luego de esto la información queda en un estado consistente.

Un “Rollforward” es un proceso por el cual SQL Server lee el log de transacciones y empieza a aplicar todos y cada uno de los cambios realizados en las páginas de la base de datos luego de ultimo checkpoint. En este punto no interesa si la transacción ha sido confirmada o rechazada, simplemente aplican todos los cambios.

Luego en el proceso de “Rollback”, SQL Server comienza a hacer un rollback de todas las transacciones que no fueron confirmadas además de las que fueron rechazadas, dejando de esta manera la base de datos en un estado consistente.

Este proceso de recuperación en algunos casos puede tardar mucho tiempo debido a la gran cantidad de información que tienen que replicar desde el log de transacciones. Es por eso que la frecuencia con la que se hacen los checkpoints dentro de la base de datos es crucial para el tiempo que tardara el servidor en ejecutar el proceso de recuperación.

Adicionalmente cabe mencionar que en algunas pocas ocasiones el terminar el servicio de SQL Server de manera inesperada puede causar corrupciones de datos, y esto sí es grave debido a que en algunos casos puede ser recuperable la información, pero siempre con un riesgo de perder algo de data, y en otros no es posible arreglar la base de datos, entonces lo único que queda en estas situaciones es la restauración de backups y es ahí donde si se tiene una buena estrategia de backups se puede llegar a recuperar absolutamente toda la información hasta el momento del desastre.

Bueno esta ha sido una breve explicación en lo que refiere al proceso de recuperación que se ejecuta en el servidor de base de datos cada vez que el servicio se apaga de una manera incorrecta.

 

Manejo de Páginas en Memoria

Como ya se ha mencionado anteriormente, SQL Server es un motor de base de datos que trabaja en memoria, es decir que antes de retornar un resultado al usuario o de modificar algun registro en la base de datos, antes debe tener las páginas de datos en memoria, si es que estas aún no se encuentran en memoria, estas deben ser traidas desde los “Datafiles” hasta el “Buffer Cache” donde se almacenaran.

Dentro del “Buffer Cache” se tienen paginas “Clean” y “Dirty”. Las paginas “Clean” son las paginas que han sido traidas desde los “Datafiles” y no han cambiado, es decir la pagina de memoria es una copia exacta de la pagina que se encuentra en los “Datafiles”. Las páginas “Dirty”, como ya lo deben suponer, son las paginas que han sido modificadas y como ya no son iguales a su página origen que se encuentra en disco, se les llama asi.

Las páginas “Clean” son mantenidas en memoria para mejorar la performance de las consultas. Estas paginas son eliminadas de la memoria cuando se necesita espacio en la memoria de servidor, a este fenomeno se le llama “Memory Preasure”. Adicionalmente a esto SQL Server tiene un proceso de sistema que se llama “Lazy Writter” el cual despierta cada cierto tiempo para poder mantener una cierta cantidad de páginas libres en la memoria y va deshechando las páginas que no han sido tocadas en un tiempo determinado, esto lo hace a traves de un algoritmo LRU (Least Recently Used).

Los cambios realizados en las páginas “Dirty” son bajados a los “Datafiles” cuando se da un proceso llamado “Checkpoint”, el cual baja a los “Datafiles” todos los cambios realizados a las páginas den memoria. La frecuencia de ejecución de los check ponints es muy importante debido a que dependiendo de que tan seguido se ejecuten los “Checkpoints”, el proceso de recuperación de la base de datos se data tardara menos en terminar. Esto lo explicaremos en otro post en detalle. Es importante mencionar que cuando se da el checkpoint en la base de datos, las paginas “Dirty” se mantienen en memoria pero son marcadas como páginas “Clean”.

Otro punto importante a tener en cuenta es que las paginas cuando son subidas a memoria, no son subidas individualmente sino que el motor de base de datos sube a memoria el “Extend” completo donde se encuentra la página. Es decir que sube ocho paginas de 8kb, en total 64 kb de información.

Ahora un poco de ejemplos, en el siguiente video mostrare a mas detalle el manejo de estas paginas dentro de la memoria de SQL Server. Espero sea de utilidad.

Los archivos de los ejemplos realizados pueden ser descargados aqui

Arquitectura del Motor de Base de Datos

Para que una persona pueda tomar buenas decisiones al momento de diseñar una base de datos o al momento de elaborar una consulta, es necesario que tenga en mente que todas las consultas enviadas al motor de base de datos deben ser procesadas por el mismo, el cual tiene una arquitectura definida. Esta arquitectura no es muy conocida ni difundida en la comunidad de SQL Server en español, la verdad no entiendo el porqué, casi ninguno de los libros habla de ella. Yo particularmente buscando en una serie de libros de MVPs y MCMs pude por fin encontrar esas definiciones que no las encontraba en otras partes y es por eso que ahora la tratare de difundir en nuestra comunidad.

En mi opinión personal, conocer la arquitectura del motor de base de datos es muy importante debido a que con este conocimiento uno puede saber y entender lo que sucede tras bambalinas en el interior del motor de base de datos, lo cual conlleva a poder tomar mejores decisiones con respecto al diseño y a la construcción de consultas sobre el servidor de base de datos.

Primero comenzaremos indicando que el motor de SQL Server esta básicamente formado por dos motores o “engines” internos. El primero es el motor relacional, o “Relational Engine”, el cual se encarga de procesar las consultas de la base de datos, optimizarlas y ejecutarlas. El segundo es el motor de almacenamiento, o “Storage Engine”, el cual es responsable de realizar todas las operaciones de I/O de la data, administrar las transacciones, y de manejar el “Buffer Pool”. A continuación se presenta un grafico representando a estos dos motores y a sus componentes internos, de los cuales hablaremos más adelante.

Relational Engine

Este es el primer motor interno de SQL Server, el cual está encargado como ya hemos mencionado de poder procesar las sentencias T-SQL enviadas desde los clientes. Este motor tiene a su vez tres componentes internos los cuales le permiten hacer su trabajo. A continuación explicaremos un poco más las funciones que tiene cada uno de estos componentes internos.

Command Parser: Este componente es el primero en recibir las sentencias T-SQL enviadas desde el cliente al servidor de base de datos. Lo primero que hace este componente es revisar que la sintaxis T-SQL sea correcta, si no lo es, enviara directamente un error al cliente. Si la sintaxis es correcta entonces el siguiente paso es generar un plan de ejecución, para poder realizar esto el “Command Parser”, primero genera un “hash” a partir de la sentencia T-SQL a procesar para poder determinar si es que ya existe algún plan que se ajuste a ésta dentro del “Plan Cache”, la cual es un área dentro del “Buffer Pool” utilizada para poder almacenar los planes de ejecución. Si no encuentra un plan adecuado, entonces genera un “Query Tree” basado en la sentencia T-SQL a procesar, un “Query Tree” es una estructura interna donde cada nodo en el árbol representa una operación en la sentencia SQL que necesita ser ejecutada. Una vez que se tiene generado el “Query Tree”, éste es pasado al siguiente componente, el “Query Optimizer”.

Query Optimizer: Este componente es la pieza más preciada y más compleja dentro de SQL Server. A este componente también se le denomina “Cost-BasedOptimizer”, debido a que evalúa múltiples formas de ejecutar una sentencia T-SQL, y escoge el método que le costó menos. La descripción anterior podría llevarnos a pensar que la función del “Query Optimizer” es encontrar el mejor plan para nuestra consulta, pero en realidad su función principal es encontrar un buen plan en un periodo de tiempo razonable, en vez de encontrar el mejor plan, se podría decir que el objetivo principal de este componente es encontrar el plan más eficiente, en relación al costo y al tiempo. Si el “Query Optimizer” intentara encontrar el *mejor* plan, quizás la generación del plan demoraría más que la misma ejecución de la consulta, entonces, es por esto que se intenta encontrar un balance entre costo y tiempo de generación del plan. Una vez que se tiene el plan de ejecución generado, éste es enviado al siguiente componente que es el “QueryExecutor”.

Query Executor: La función principal de este componente es ejecutar las consultas, para ser mas específicos ejecutar los planes de ejecución interactuando directamente con el “Storage Engine” para obtener y modificar la data, y finalmente enviar el resultado al cliente.

Storage Engine

Una vez que el “Query Executor” comienza a ejecutar el plan de ejecución para la consulta, necesita trabajar en conjunto con el “Storage Engine” que es el encargado de manejar las operaciones de I/O de la data, ademas de las transacciones y el “Buffer Pool”. El “Query Executor” interactúa con este motor a través de uno de sus componentes que es el “Access Methods”.

Access Methods: Este componente tiene como objetivo fundamental el de permitir la comunicación entre el “Relational Engine” y el “Storage Engine”. Este componente recibe las instrucciones enviadas en el plan de ejecución y verifica si hay algún bloqueo con el “Transaction Manager”, si no es así procede con el “BufferManager”.

Buffer Manager: Este es el encargado de buscar las páginas de datos en el “BufferCache”, el cual es un área dentro del “Buffer Pool” donde se guardan las páginas de datos en la memoria, en el caso de que la pagina no se encuentre en esa sección de memoria, procederá a extraerla de la base de datos en el disco duro, y traerá a memoria las páginas de datos necesarias, una vez que ya tiene las paginas en el “Buffer Cache” recién envía el resultado devuelta al “AccessMethods”, el cual es el que forma el set de datos resultante y finalmente lo devuelve al componente “Query Executor” del “Relational Engine” para que este a su vez lo envíe al cliente. Es importante recordar que para que una página pueda ser leída y/o modificada, ésta debe encontrarse antes en la memoria del servidor “Buffer Cache”.

Transaction Manager: Este componente esta divido internamente en dos sub-componentes los cuales son el “Lock Manager” y el “Log Manager”.

Lock Manager: Este es el responsable de mantener la concurrencia en la data a través de los diferentes tipos de bloqueos de la base de datos. Permite mantener la data consistente entre las diferentes sesiones a través de la administración de los diferentes bloqueos que se obtienen en cada una de las operaciones de la base de datos.

Log Manager: Este componente es crucial en el caso de que se hagan modificaciones a la data, debido a que antes de realizar algún cambio en las páginas de datos, este necesita estar guardado en el log de la base de datos, en su archivo físico en el disco duro. Esta es la única vez durante el ciclo normal de ejecución de transacciones T-SQL en la que el motor de base de datos ira al disco duro a guardar los cambios. Una vez que este componente confirma que el cambio ha sido guardado en el log de base de datos, el cambio a las paginas lo realizara el “Buffer Manager” dentro del “Buffer Cache”, lo cual permite tener una mejor performance durante las transacciones porque todas las operaciones se hacen en la memoria del servidor y muy poco se baja a disco.

En conclusión como se habrá podido observar el motor de base de datos se vale a través de sus dos motores internos para poder entregar al cliente los resultados de sus transacciones en la base de datos. Es importante tener en claro todo el recorrido que tienen las consultas T-SQL para poder entender mejor el comportamiento  del servidor de base de datos. Finalmente me gustaría dejar la referencia del libro que me ayudo mucho a entender estos conceptos, en realidad es un magnífico libro, y es Professional SQL Server 2008 Internals and Troubleshooting.

Funcionalidad de IDENTITY_INSERT

Las tablas comúnmente tienen un campo codigo el cual es el que identifica al registro, este campo normalmente es la clave primaria de la tabla. Normalmente se usa una columna de tipo identity para poder implementar el campo codigo en una tabla debido a su facilidad de manejo, debido a que no se necesita hacerle mantenimientos y siempre genera un numero correlativo segun la secuencia que se le haya definido.

Pero hay ocaciones en las que se desea cargar información a tablas con campos de tipo identity, y entre los campos a cargar se tienen los codigos que son representados por el campo tipo identity. Para poder cargar informacion a este campo se debe de deshabilitar el identity, lo cual se realiza a través de una instrucción simple “identity_insert”. Seguidamente podremos ingresar los codigo que se desee, obviamente sin repetir el mismo codigo, finalmente volvemos a habilitar el campo identity y automaticamente la tabla reseteara el siguiente numero que generara el identity al maximo valor que actualmente se encuentra en la tabla. A continuación se presenta un ejemplo simple del funcionamiento de esto.

USE tempdb
GO
-- creamos la tabla de prueba
IF (OBJECT_ID('dbo.TestIdentity') IS NOT NULL)
DROP TABLE dbo.TestIdentity
GO
CREATE TABLE dbo.TestIdentity
(id INT IDENTITY(1,1)
,
VALUE CHAR(1));
GO
-- insertamos dos valores cualquiera
INSERT INTO dbo.TestIdentity
VALUES ('a'),('b')
GO
-- verificamos que el campo id fue generado secuencialmente
-- comportamiento propio de los campos identity
SELECT * FROM dbo.TestIdentity
GO
/*
id          value
----------- -----
1           a
2           b
*/
-- activamos la opcion para poder ingresar valores especificos
-- a un campo identity
SET IDENTITY_INSERT dbo.TestIdentity ON
GO
-- ingresamos un registro con el id en 1000
INSERT INTO dbo.TestIdentity (id,VALUE)
VALUES (1000,'c')
GO
-- desactivamos la opcion para ingresar valores especificos
-- a un campo identity
SET IDENTITY_INSERT dbo.TestIdentity OFF
GO
-- verificamos que el nuevo registro fue ingresado con el valor que idicamos
SELECT * FROM dbo.TestIdentity
GO
/*
id          value
----------- -----
1           a
2           b
1000        c
*/
-- ingresamos dos nuevos valores
INSERT INTO dbo.TestIdentity
VALUES ('d'),('e')
GO
-- verificamos que los codigos de los dos ultimos valores
-- inicia a partir del maximo codigo que contiene la tabla
SELECT * FROM dbo.TestIdentity
GO
/*
id          value
----------- -----
1           a
2           b
1000        c
1001        d
1002        e
*/
-- volvemos a activar la opcion para poder ingresar valores
-- especificos a un campo identity
SET IDENTITY_INSERT dbo.TestIdentity ON
GO
-- ingresamos un valor de menor valor al maximo
INSERT INTO dbo.TestIdentity (id,VALUE)
VALUES (900,'f')
GO
-- desactivamos la opcion para ingresar valores especificos
-- a un campo identity
SET IDENTITY_INSERT dbo.TestIdentity OFF
GO
-- comprobamos que el valor fue ingresado
SELECT * FROM dbo.TestIdentity
GO
/*
id          value
----------- -----
1           a
2           b
1000        c
1001        d
1002        e
1003        d
1004        e
900         f
*/
-- volvemos a ingresar dos nuevos registros
INSERT INTO dbo.TestIdentity
VALUES ('g'),('h')
GO
-- Verificamos que el siguiente codigo generado sigue siendo
-- a partir del maximo de la tabla
SELECT * FROM dbo.TestIdentity
GO
/*
id          value
----------- -----
1           a
2           b
1000        c
1001        d
1002        e
1003        d
1004        e
900         f
1005        g
1006        h
*/

Como se puede observar, así se inserte un numero menor al que se tiene ya ingresado en la tabla el campo identity, no colisionara con otros código debido a que siempre esta actualizado con el máximo código que tiene la tabla. Bueno espero que les haya servido esta corta explicación.