Grimpi IT Blog

febrero 8, 2011

Control de concurrencia multiversión MVCC

Filed under: Engine, MVCC, PostgreSQL, SQL Server — grimpi @ 8:08 pm

Antes de explicar que es MVCC, hay que aclarar dos conceptos muy importantes: bloqueo pesimista y bloqueo optimista. El bloqueo optimista supone que no se va a hacer nada en el código de la aplicación que imponga explícitamente bloqueos de los recursos cuando se esté trabajando con ellos. Mientras que por otro lado, el bloqueo pesimista supone una intervención por parte de la aplicación como gestor del bloqueo.
Para ser más simples: El enfoque optimista delega en la base de datos el bloqueo y manejo de datos, mientras que el bloqueo pesimista, la aplicación es la encargada de gestionar la concurrencia.
MVCC (Multi version concurrency control) es una técnica de concurrencia optimista en donde ninguna tarea o hilo es bloqueado mientras se realiza una operación en la tabla, porque el otro hilo usa su propia copia (versión) del objeto dentro de una transacción.
Si bien obviamente la implementación interna de este algoritmo es distinta en cada motor de bases de datos, a grandes rasgos se podría decir que el MVCC internamente lo que hace es identificar cada transacción con un numero univoco y a cada registro de la tabla, con un numero de versión. Entonces, cada transacción trabaja con su copia y si se modifica un registro de una tabla, el contador de versión del registro se incrementa. Cuando se comitea, la copia del objeto reemplaza a la que existía en la base de datos. Si 2 transacciones modificaron la misma tabla, se hace un mergue de ambas tablas combinando las últimas versiones de cada registro. Si las 2 transacciones, modificaron exactamente el mismo registro, entonces en ese caso, cuando se commitee, el registro que finalmente queda, corresponde a la ultima transacción en realizar una modificación sobre ese registro.
De esta manera se logra que una lectura no bloquee una transacción de escritura y que una transacción de escritura tampoco bloquee una transacción de lectura. Para ser más claros, cuando hago un SELECT, puedo simultáneamente hacer un UPDATE y cuando hago un UPDATE puedo hacer simultáneamente un SELECT, algo que en el isolation habitual de las bases de datos, no es posible.
Al no existir ningún lockeo ni espera por parte del proceso, en determinadas situaciones concurrentes de fuerte escritura donde la información es modificada frecuentemente y de manera concurrente por muchos usuarios, se logran un gran mejoramiento de la performance del sistema. Es muy habitual usar este tipo modelo de concurrencia para evitar los famosos “deadlocks” que suelen ocurrir en bases de datos con mucha demanda.

Sin embargo, MVCC no debe ser usado en cualquier ocasión, ya que tiene un overhead muy importante. Todo el manejo de versionado tiene un alto costo, ya que las versiones de registros se copian y almacenan en tablas o estructuras físicas temporales que luego se descartan (por ejemplo, para guardar las copias, SQL Server usa la tempdb, mientras que Oracle usa el rollback segment). En SQL Server este overhead es un poco alto, por lo tanto solo en escenarios específicos conviene usar este modelo de concurrencia. A medida que las operaciones de escritura sobre la base de datos cobran relevancia sobre las operaciones de lectura, y por otro lado, nuestras lecturas son largas, los beneficios de usar MVCC aumentan, ya que reducimos los bloqueos.
Es fundamental la existencia de un alto grado de paralelismo en nuestra aplicación. MVCC es por definición, un modelo muy escalable y donde mejor se ven sus ventajas, es en escenarios de alta demanda.

Escenarios típicos:
• Aplicaciones que procesan transacciones en línea en gran escala.
• Aplicaciones mixtas que usan reportes contra datos sobre tablas en línea.
• Aplicaciones donde se ejecutan consultas largas mientras que simultaneamente se realizan operaciones sobre ella.

Escenarios donde no generalmente no debería usarse MVCC:
• Aplicaciones de datawarehousing que extraen información sobre tablas históricas donde no hay escritura.
• Aplicaciones que procesan datos en línea con poca concurrencia.
• Aplicaciones realtime, donde la velocidad es mas importante aun que la consistencia de información.

Todas las bases de datos modernas implementan actualmente este algoritmo de concurrencia optimista. En SQL Server se llama SNAPSHOT ISOLATION, en MySql se llama InnoDB Multi Version, en Firebird MGA y en Oracle y PostgreSQL, simplemente MVCC.

Links:
http://msdn.microsoft.com/en-us/library/ms345124(v=sql.90).aspx
http://www.codinghorror.com/blog/2008/08/deadlocked.html
http://www.rtcmagazine.com/articles/view/101612

Anuncios

enero 25, 2011

Lo nuevo de SQL Server 2011: COLUMN STORAGE INDEX

Filed under: Engine, SQL Server, SQL Server 2011 — grimpi @ 4:28 pm

Una de las características más importantes que promete SQL Server 2011, son los índices por columna.

Introducción
A partir del 2007, salieron a la luz distintas bases de datos llamadas “Column-oriented DBMS”, que implementaron el concepto de índice por columna, el cual prometía revolucionar el mercado, especialmente el de datawharehousing. Uno de los motores mas conocidos es Vertica (y su versión gratis, C-Store), también MonetDB, que es open source. Incluso Sybase lanzó un producto llamado Sybase IQ. Por supuesto, hubo otras empresas que lanzaron productos similares.
El problema de estos productos es que son “Column-oriented DBMS” exclusivamente. O sea, no almacenan la información de la misma manera que la almacena una típica base de datos relacional tradicional como Oracle, MySql o SQL Server. Esto significa que el mercado de estos productos está muy limitado a escenarios específicos.

Por el contrario, SQL Server 2011 promete ser la primera de bases de datos relacional “tradicional” en incorporar como una feature mas a su motor, el índice por columna, pudiendo ser combinado su uso con la tradicional estructura “row-store”.
 

¿Ahora bien, que es un índice por columna?
En todos los motores relacionales, los índices se almacenan por filas (rows). Esto significa que la estructura del índice en su última instancia, va a tener un puntero a un registro de una tabla. Existen distintas maneras de construir un índice (bitmap, b-tree, etc), pero todos estos métodos en definitiva hacen lo mismo: acelerar el acceso a un registro.
El índice por columna por el contrario, invierte la filosofía. Ya no busca registros, busca columnas. En una tabla “normal”, los registros son almacenados en páginas físicas de disco. Cada página contiene una determinada cantidad de registros (que varia según el motor y los registros. En SQL Server cada página de datos tiene un tamaño fijo de 8K). Un índice por columna, es al revés. En cada página de datos, se guardan columnas, no filas.

¿Bárbaro, ahora que ventajas tiene esto?
La respuesta es simple. Performance. Y por 2 razones.

1) Al estar las páginas de disco ordenadas por columnas, cuando hago una consulta, el motor puede leer solo las columnas que necesitamos para nuestra query, lo cual evita lecturas de disco innecesarias.
2) Es muchísimo mas probable que exista redundancia de información entre los valores de una misma columna en distintos registros, que entre los valores de las distintas columnas en un mismo registro, por lo tanto, es mucho mas fácil y eficiente la compresión de datos, lo que significaría mas información en memoria y menos acceso al disco. Ejemplo:
Si yo tengo 4 registros de 4 columnas cada uno:

  Col1 Col2 Col3 Col4
Row 1 01 Jose 01/01/2000 4
Row 2 02 Eduardo 02/02/2004 5
Row 3 03 Ramon 03/02/2004 5
Row 4 04 Susana 02/02/2004 5

Si yo comprimo esta información por columna, mi grado de compresión seria mucho mayor a que si yo comprimo por registro. Porque el grado de homogenización de valores dentro de una columna es mucho mayor. En cambio, en un registro, como tengo distintos tipos de datos y valores posibles entre ellos muy disímiles, la compresión no es tan efectiva. No es lo mismo comprimir 100 valores de un mismo tipo de datos, que comprimir 100 valores de distintos tipos de datos.
Por otro lado, existen diferentes técnicas de compresión que funcionan mejor con un tipo de datos que con otro (ejemplo: hay métodos que son más eficientes para números o binarios, y otros métodos que son más eficientes para texto). Por lo tanto, el motor elige una forma de compresión diferente dependiendo del tipo de dato de la columna, algo que es imposible de hacer en una compresión por row, lo que incrementa aun más el nivel de compresión.

Un gran ratio de compresión me permite tener más información en el cache de datos, incrementar fuertemente los aciertos del buffer y evitar el acceso a I/O, que es la operación más costosa en una base de datos.
En las pruebas hechas por los laboratorios de Microsoft sobre datos reales y haciendo la comparativa con SQL Server 2008 R2, en algunos casos se obtuvo un incremento de hasta 60 veces la performance de una consulta. Lo cual resulta un número bastante impresionante.

¿Como se usa un índice por columna?
Si bien todavía la documentación oficial en SQL Server 2011 no está disponible porque el producto no fue liberado, si podemos ver la sintaxis si entramos a la web del sitio.
Para crear un índice por columna simplemente deberemos anteponer la palabra clave COLUMNSTORE.
Ejemplo:

 
CREATE COLUMNSTORE INDEX NuevoIndice ON Personas
(
FechaDeVenta,
FechaDeEntrega,
Producto,
Precio,
Cantidad
)
 

¿Siempre va a convenir entonces usar este tipo de índices?
La respuesta es un rotundo no. Los índices por columna es un índice más que incrementa notablemente la performance en bases de datos muy grandes y en determinado tipo de consultas. Pero son índices pensados para escenarios de datawharehousing, especialmente para consultas del tipo star join. No en todos los casos es mas óptimo este tipo de índice ni tampoco están pensados para las típicas bases de datos transaccionales OLTP. Para una query normal, sigue siendo mas útil usar los índices b-tree clásicos. El motor de SQL Server en función de sus estadísticas, sabrá elegir cuando es más conveniente usar un índice por columna o un índice común.
Por otra parte, este tipo de índices en SQL Server tienen por el momento varias restricciones. No todos los tipos de datos son soportados y más importante aún, no se pueden hacer operaciones de INSERT, UPDATE, DELETE o MERGE sobre tablas con este tipo de índices. La manera de llenar una tabla entonces es deshabilitar el índice temporalmente, llenar la tabla y volver a habilitar el índice. Otra opción es crear una vista indexada con columnstore index sobre la tabla y dejar a esta tabla sin este tipo de índice.
Por ultimo, otro dato a tener en cuenta, que la reconstrucción de un column index lleva entre 2 y 3 veces mas tiempo, que un índice b-tree clásico.

Conclusión:
Lamentablemente la primera CTP de SQL Server 2011 que lanzó Microsoft todavía no incluye esta feature, por lo tanto, no la podemos probar y verificar si el aumento de su rendimiento es tan significativo como ellos dicen. Sin embargo, el índice por columna es una característica muy distintiva y que hasta el momento, ni Oracle ni DB2 pudieron incorporar nativamente en su producto relacional.
Seguramente con el correr de los tiempos y a medida que tengamos mas información, van a aparecer en blogs y en artículos, distintas comparaciones y escenarios en donde conviene utilizar índices por columnas o el clásico índice por filas.

Links interesantes:
ColumnStores vs. RowStores: How Different Are They Really?
Columnstore Indexes for Fast Data Warehouse Query Processing in SQL Server 11.0

enero 23, 2009

Parameter Sniffing

Filed under: Engine, SQL Server — Etiquetas: , , — grimpi @ 1:26 am

Parameter Sniffing es una técnica usada por los motores de base de datos (en este caso SQL Server) para optimizar el plan de ejecución de un store procedure dependiendo de los valores de los parámetros del mismo. En general, es una buena técnica que ayuda a mejorar la performance de la ejecución del store procedure, cuando en la mayoría de los escenarios donde se ejecuta, se trabaja con el mismo conjunto de datos.

Sin embargo, existen al menos 2 situaciones, donde se pueden presentar más inconvenientes que beneficios.

  • Parameter Sniffing es óptimo cuando en general, la mayoría de los valores de los parámetros tienen las mismas características (en realidad, el resultado de los valores de filtro en la tabla tienen la densidades similares). Si los valores que se le envían al Store Procedure, varían mucho en tamaño (cantidad de registros), el motor podría estar ejecutando un plan de ejecución que en la mayoría de los casos, no es el adecuado para el valor.
  • Otra razón en la cual puede haber problemas de performance, es cuando la distribución de datos en la tabla no es uniforme, por lo tanto, el plan de ejecución óptimo es muy sensible a ligeras variaciones en el filtrado de datos.


Muchas veces cuando pasa que una query se ejecuta rápido, pero la misma dentro de un store procedure no, es por problemas relacionados con Parameter Sniffing.


Veamos el siguiente ejemplo, para observar el problema que sucede en algunos casos relacionado con Parameters Sniffing.

1) Creo primero el siguiente Store Procedure:

CREATE PROCEDURE dbo.GetReservaCliente @Param2int
AS
BEGIN

SELECT * FROM Reserva R WHERE R.ClienteId = @Param2

END

Hay que recordar que el plan de ejecución de un Store Procedure no se genera cuando es creado el Store, sino cuando se ejecuta por primera vez. Esto significa que la segunda vez que se llama el procedimiento, va a usar el plan de ejecución que tiene cacheado.

2) Vemos la distribución de datos en la tabla Reserva por cada ClienteId:

SELECT ClienteId, Count(*) AS Cantidad FROM Reserva
GROUP BY ClienteId
ORDER BY Count(*)

El mínimo es el ClienteId 63, que tiene 1 solo registro.
El máximo es el ClienteId 1065, que tiene 28656 registros.

3) Buscamos ahora todos los registros de la tabla Reserva cuyo ClienteId sea 1065 usando el Store Procedure previamente creado.

EXEC GetReservaCliente 1065

Veamos el plan de ejecución que el SQL Server armó para la consulta:

2 cosas nos interesan acá: El plan de ejecución que armó y el “Estimated Number of Rows” (que está marcado en rojo). Como podemos ver, este valor es de 28656, exactamente la cantidad de registros existentes en la tabla para el cliente consultado. De donde sacó esta información el motor? En función de las estadísticas internas que almacena.
Ahora ejecutemos el mismo Store Procedure, pero buscando el ClienteId 63, que como vimos antes, tiene un solo registro.

EXEC GetReservaCliente 63

Veamos el plan de ejecución que el SQL Server armó para la consulta:

El valor de “Estimated Number of Rows” es de 28656, exactamente el mismo valor que la consulta anterior, a pesar de que la cantidad de registros devueltos es 1. Esto significa que el motor esta reutilizando el plan de ejecución anterior, que esta optimizado para devolver 28656 registros y por lo tanto no hay certeza que este plan sea igual de eficiente para el ClienteId 63.

4) Borramos y volvemos a crear el Store Procedure para limpiar el plan de ejecución y ver cuál es el eficiente para el ClienteId 63:

DROP PROCEDURE dbo.GetReservaCliente
GO
CREATE PROCEDURE dbo.GetReservaCliente

@Param2    int
AS

BEGIN

SELECT * FROM Reserva R WHERE R.ClienteId = @Param2
END

GO

Ahora que la cache del Store Procedure fue limpiada, ejecutamos nuevamente el procedimiento y vemos que plan de ejecución genera:

EXEC GetReservaCliente 63


Como podemos observar, el plan más eficiente generado para el ClienteId 63 (1 solo registro) es muy diferente al plan más eficiente para el ClienteId 1065 (28656 registros).

Soluciones cuando hay problemas con Parameter Sniffing:

  • Hacer que el motor regenere el plan de ejecución cada vez que se ejecuta el Store Procedure. Para eso debemos crearlo con la opción WITH RECOMPILE. El costo de hacer esto, es que el SQL Server pierde tiempo en recompilar en vez de usar la cache, pero si este costo es menor al de ejecutar un plan ineficiente, se debe hacer.

CREATE PROCEDURE dbo.GetReservaCliente
@Param2 int
WITH RECOMPILE
AS
BEGIN
SELECT * FROM Reserva R WHERE R.ClienteId = @Param2
END

  • Utilizar el hint RECOMPILE dentro de la query (Solo a partir de SQL Server 2005). Esta solución es similar a la anterior, con la ventaja de que en vez de recompilar todo el store, el SQL Server solo recompila una query especifica. Esto es útil cuando tenemos muchas querys dentro de un mismo Store Procedure y el problema de uso de un plan de ejecución ineficiente refiere a una sola query.

SELECT * FROM Reserva R WHERE R.ClienteId = @Param2 OPTION(RECOMPILE)

  • Deshabilitar Parameter Sniffing usando variables locales. Esto se debe a que SQL Server no tiene en cuenta los valores de las variables locales para hace sniffing, sin embargo, algunos suponen que un futuro esto podría llegar a cambiar, aunque por el momento funciona y es uno de los métodos mas utilizados.

    Ejemplo:

CREATE PROCEDURE dbo.GetReservaCliente
@Param2    int
AS
BEGIN

DECLARE @Param3 int

SET @Param3 = @Param2
SELECT * FROM Reserva R WHERE R.ClienteId = @Param3

END

De este modo, al no utilizar en las consultas del Store Procedure los parámetros propios, al generar el Plan de Ejecución no se utilizan los valores de los parámetros, por lo que en vez de generar el Plan de Ejecución para un caso particular, se generará el Plan de Ejecución genérico para el caso medio.

  • Utilizar el hint OPTIMIZE FOR (Solo a partir de SQL Server 2005). Este hint permite forzar a una consulta a que se ejecute con un plan de ejecución optimizado para un determinado valor.

    SELECT * FROM Reserva R WHERE R.ClienteId = @Param2
    OPTION (OPTIMIZE FOR (@Param2 = 1065))

Este método nos garantiza que para el valor que estamos teniendo problemas va a funcionar bien, aunque podría presentar problemas con otro valores.
Es cuestión de ir probando y ver si vale la pena usarlo. Si el valor típico del parámetro del Store Procedure es compatible con un óptimo plan de ejecución, es una opción interesante.

  • Existen opciones más avanzadas, para casos complejos como por ejemplo crear un propio plan de ejecución usando Plan Guides.


octubre 28, 2008

Entender el Plan de Ejecución en SQL Server 2005/2008

Filed under: Engine, Plan de ejecución, SQL Server — Etiquetas: , , — grimpi @ 11:51 pm

Cada vez que se ejecuta una consulta en un motor de bases de datos, internamente se ejecutan una serie de operaciones, que varían según la consulta, los datos y obviamente, el motor de base de datos. El conjunto de pasos que tiene que realizar el motor para ejecutar la consulta, se llama Plan de Ejecución. Hoy vamos a explicar cómo entender el plan de Ejecución de SQL Server 2005/2008. Que operaciones podemos encontrar en el plan de ejecución?

Table Scan:
Significa que el motor tiene que leer toda la tabla. Esto solo puede suceder cuando la tabla es Heap (o sea, no tiene un índice clustered). En algunos casos, cuando es una tabla chica, un Table Scan es la mejor opción, ya que produce poco overhead. De hecho la tabla puede tener índices y sin embargo el SQL elige usar un table scan porque sería más rápido. Pero cuando la tabla es más grande, no debería haber Table Scan, ya que es muy costoso. Para solucionar este problema, hay ver si la tabla tiene índices y si se están usando correctamente. Lo importante es prestarle atención cuando vemos un table Scan. Muchas veces, nuestro problemas de performance pasan por ahí.

Ejemplo:
(Creamos una tabla sin ningún tipo de índice y se le hace una consulta)

CREATE TABLE [TablaPrueba1]
(
Campo1 int IDENTITY (1, 1) NOT NULL ,
Campo2 int,
Campo3 int,
Campo4 int,
Campo5 char (30)
)

SELECT * FROM TablaPrueba1

Clustered Index Scan:
Esta operación es muy similar a un table scan. El motor recorre toda la tabla. La diferencia entre uno y otro, es que el Clustered Index Scan se realiza en una tabla que tiene un índice Clustered y el Table Scan en una tabla que no tiene este tipo de indice.

Otra vez tenemos que evaluar si esta opción es la que realmente queremos. Muchas veces, por un mal uso de los índices, se ejecuta esta operación, cuando en realidad queríamos otra más eficiente.
Ejemplo de un Clustered Index Scan:

CREATE TABLE [TablaPrueba3]
(
Campo1 int IDENTITY (1, 1) NOT NULL,
Campo2 int,
Campo3 int,
Campo4 int,
Campo5 char (30)
CONSTRAINT [PK_Campo3] PRIMARY KEY CLUSTERED
(
[Campo1]
))

SELECT * FROM TablaPrueba3

Clustered Index Seek:
Si vemos esta operación, en general, podemos estar contentos. Significa que el motor está usando efectivamente el índice Clustered de la tabla.
Ejemplo de esta operación:

(Usamos la tabla creada en el ejemplo anterior con un índice Clustered, le insertamos 10000 registros para que el motor prefiriera usar el índice antes que un scan y filtramos por el índice).

SET NOCOUNT ON
DECLARE @Top int
SET @Top = 0
WHILE @Top <> 10000
BEGIN
INSERT INTO
TablaPrueba3 VALUES (convert(int,rand()*20000),convert(int,rand()*20000),convert(int,rand()*20000), ‘P’)
SET @Top = @Top+1
END

SELECT * FROM TablaPrueba3 WHERE Campo1 = 2

Index Seek:
Aquí también si vemos esta operación, podemos estar contentos. Es similar que el Clustered Index Seek, pero con la diferencia de que se usa un indice Non Clustered.

(Creamos un índice Non Clustered sobre la tabla del ejemplo anterior)

CREATE INDEX [IDX_Campo3] ON [dbo].[TablaPrueba3](Campo2,Campo3) ON [PRIMARY]

SELECT Campo2 FROM TablaPrueba3 WHERE Campo2 = 2 and Campo3 = 2

Index Scan:
Esta operación se ejecuta cuando se lee el índice completo de una tabla. Es preferible a un Table Scan, ya que obviamente leer un indice es mas chico que una tabla. Esta operación puede ser síntoma de un mal uso del índice, aunque también puede ser que el motor haya seleccionado que esta es la mejor operación. Es muy común un Index Scan en un join o en un ORDER BY o GROUP BY.

Usemos la tabla TablaPrueba3, creada en el ejemplo anterior:

(Como no hay ningún filtro, el motor debe leer toda la tabla. Sin embargo, al traer solo el Campo2, que pertenece a un índice Non Clustered, en vez de hacer un Table Scan, es mas optimo hacer un Index Scan).


SELECT Campo2 FROM TablaPrueba3

Bookmark Lookup:
Esta es una operación muy importante, donde hay algunas diferencias entre 2000 y 2005 que vale la pena saber. El Bookmark Lookup indica que SQL Server necesita ejecutar un salto del puntero desde la página de índice a la página de datos de la tabla para recuperar los datos. Esto sucede siempre que tenemos un índice Non Clustered. Para evitar esta operación, hay que limitar los campos que queremos traer en la consulta.
Si el campo que vamos a extraer, esta fuera del índice, entonces se va a ejecutar esta operación y no queda otra opción (para SQL Server 2000). Acá reside la importancia de evitar los SELECT * FROM …

Veamos el siguiente ejemplo usando nuevamente la tabla TablaPrueba3, pero con la siguiente consulta, tanto para SQL Server 2000 y SQL Server 2005:

Ejemplo: (Se trae todos los campos de la tabla, filtrando por un campo perteneciente a un índice non clustered).

SELECT Campo2, Campo3, Campo4 FROM TablaPrueba3 WHERE Campo2 = 2

SQL Server 2000:

SQL Server 2005:

Si vemos el plan de ejecución (de SQL Server 2000), se realizó la operación Index Seek, pero además, aparece la operación Bookmark Lookup. Esto pasa porque en este ejemplo además de traer el campo2 y campo3 que son parte del índice, debe leer el campo5, que solo está en la página de datos y no en el índice. Ademas, como podemos ver, la misma consulta, para exactamente la misma tabla con los mismos datos e índices, pareciera generar un plan de ejecución diferente en SQL Server 2000 y SQL Server 2005.
Pero sin embargo, no es así. Dado que dentro de la estructura interna de un índice non clustered, se almacena un puntero al índice clustered, el boorkmark lookup internamente se traduce como un salto a la lectura del índice clustered de la tabla. Para entender mejor este punto, recomiendo leer este post que escribí hace un tiempo. En SQL Server 2000 toda esta operación es encapsulada en un solo icono al mostrar graficamente el plan, mientras que en SQL Server 2005, el plan de ejecución está más detallado.
Pero la real diferencia entre ambas versiones (2000 y 2005) no es una simple cuestión estética. Una de las más interesantes nuevas características de SQL Server 2005, es la posibilidad de incorporar en la pagina final del índice (donde residen los valores), campos de la tabla que son externos al índice.
Que significa que “externo al índice”?
Significa que el campo no es parte de la estructura del índice, que no va a ser utilizado por el motor a la hora de filtrar y buscar, pero sin embargo, su contenido está copiado a la estructura de la última página del índice. La ventaja de esto, es que le ahorra al motor del SQL, hacer el boorkmark lookup, operación bastante costosa. La desventaja, es que al hacer más grande el índice, entran menos registros por página, lo cual podría llevar a que se tengan que hacer mas operaciones de I/O. Por lo tanto, es necesario hacer una evaluación de costo/beneficio, antes de incluir campos adicionales al índice.


Joins:
Un join es la relación entre 2 tablas. SQL tiene tres tipos de joins. Neested Loop Join, Merge Join y Hash Join. Dependiendo de las características de la consulta y de la cantidad de registros, el motor puede decidir uno u otro.
Ninguno es peor o mejor “per se”. Todo depende de las características de la consulta y del volumen de datos.

  • Neested Loop Join: Suele ser generalmente el más frecuente. Es también el algoritmo más simple de todo. Este operador fisico es usado por el motor cuando tenemos un join entre 2 tablas y la cantidad de registros es relativamente baja. Tambien aplica con cierto tipo de joins (cross joins por ejemplo).
  • Merge Join: Otro de los tipos de join que existen. Generalmente se usa cuando las cantidades de registros a comparar son relativamente grandes y están ordenadas. Aun si no están ordenadas, el motor puede predecir que es más rápido ordenar la tabla y hacer el merge join que hacer un Neested Loop Join. En muchas situaciones es frecuente ver que una consulta anteriormente usaba Neested Loop Join y en algún momento paso a usar un Merge Join. La razón de esto, es porque el volumen de datos aumento y por lo tanto, es mas optimo usar un Merge join.
  • Hash Join: Otro tipo más de join que existe. Mientras que los Loop Joins trabajan bien para conjuntos chicos de datos y los merge join para conjuntos moderados de datos, el hash join es especialmente útil en grandes conjuntos de datos, generalmente en datawarehouses. Este operador es mucho mas paralelizable y escalable. También se usa generalmente cuando las tablas relacionadas no tienen índice en ninguna de los campos a comparar. Hay que prestar atención si vemos este tipo de operaciones, ya que puede significar un mal uso de los índices. Sin embargo, los hash joins consumen mucha memoria y SQL Server tiene un límite en la cantidad de operaciones de este tipo que puede efectuar simultáneamente. Existen varios subtipos de hash joins. El que quiere ver en detalle esto, en este link hay una excelente explicación (http://blogs.msdn.com/craigfr/archive/2006/08/10/687630.aspx).

Ejemplo:
(Se va a ejecutar exactamente la misma consulta con una tabla con 50 registros y con 2000 registros, para ver cómo cambia en función del volumen de datos, el tipo de operación)

SELECT T1.* FROM tablaprueba3 T1 INNER JOIN TablaPrueba3 T2 ON T2.Campo4 = T1.Campo1

Consulta con 50 registros en la tabla

Consulta con 20000 registros en la tabla

Agregaciones:
Las agregaciones refieren a agrupar un conjunto grande de datos en un conjunto de datos más chico.

  • Stream Aggregate: Este tipo de operaciones ocurre cuando hay se llama a un función de agregación, como MIN, COUNT, MAX, SUM, etc. El operador Stream Aggregate requiere que la información esté ordenada por las columnas dentro de sus grupos. Primero, el optimizador ordenará si los datos no están ordenados por un operador Sort anterior. En cierta manera, el Stream Aggregate es similar al Merge Join, en cuanto a en que situaciones se produce.
  • Hash Match (Aggregate): Hay que tener cuidado cuando vemos este operador. Esta operación también ocurre cuando se llama a funciones de agregación del tipo MIN, COUNT, AVG, etc. Así como el Stream Aggregate es comparable al Merge Join, el Hash Match Aggregate es similar al Hash Join. Lo que hace internamente es armar una tabla de hash. En situaciones donde la cantidad de registros es elevada o no se están indexadas las columnas por las cuales agrupa la consulta, el motor del SQL va a elegir esta operación.

Ejemplo:

SELECT MAX(Campo2) FROM TablaPrueba3 GROUP BY Campo2

SELECT MAX(Campo4) FROM TablaPrueba3 GROUP BY Campo4

Como podemos observar en este ejemplo, las 2 consultas son prácticamente similares en estructura, solo que el primer caso agrupa el campo2 que esta indexado y en el segundo caso, agrupa el campo4, que no está indexado y por eso usa el operador Hash Match.

Sort:
Otro punto para observar, es cuando vemos un sort. Como el nombre lo indica, esta operación ordena. Ahora, el Sort solo se hace cuando el campo o los campos que se desean ordenar, no están indexados. A veces esta operación se ejecuta sola, sin que nosotros hayamos puesto en la consulta el ORDER BY, porque el motor necesita ordenar los datos por alguna razón, por ejemplo, para ejecutar un Merge Join. Pero recordemos que si ordenamos por un campo indexado y este indice esta siendo utilizado, no se ejecuta esta operación.

Ejemplo de esta operación:

SELECT * FROM TablaPrueba3 ORDER BY Campo3

mayo 9, 2008

Vardecimal

Filed under: Engine, SQL Server — grimpi @ 7:27 pm

A partir de SQL Server Service Pack 2, se incorpora un nuevo tipo de almacenamiento para los campos decimales, llamado vardecimal. Que es el vardecimal?
Bueno, actualmente los tipos de dato numeric o decimal son de longitud fija. Si declaramos un NUMERIC(38,0), el espacio físico en disco que va a ocupar dicho campo es de 17 bytes, sin importar el valor que tengamos almacenado.
Este es el tamaño que ocupan los tipos de datos numeric/decimal según su precisión:
Precision   Storage (Bytes)
1 to 9                5
10 to 19             9
20 to 28           13
29 to 38           17

El vardecimal lo que haría entonces es transformar los tipos de datos numeric/decimal de longitud fija a longitud variable en función del valor que tenemos almacenado en dicho campo, interesante no?.
Se podría decir que a muy alto nivel, el vardecimal es al decimal y numeric, lo que el varchar es el char.
La ventaja de este nuevo tipo de almacenamiento es obviamente el ahorro de espacio en disco y tener registros más chicos, lo que podría impactar en forma beneficiosa en una reducción de operaciones de I/O, haciendo más rápidas las lecturas.
Sin embargo es importante aclarar, que a diferencia de char/varchar, el vardecimal no es un nuevo tipo de dato, sino un atributo de la tabla, por lo tanto y esto es una limitación, en una tabla con varios campos numeric/decimal, o todas las columnas son de longitud fija o todas son de longitud variable.
Esta característica también se mantiene en SQL Server 2008 CTP6.

Bueno, como hacemos para usar el vardecimal?
En primer lugar, debemos habilitar a la base para que permita este tipo de operaciones, para eso ejecutamos esta instrucción:

exec sp_db_vardecimal_storage_format ‘DATABASE_NAME’, ‘ON’

Esto lo único que hace es habilitar el uso del vardecimal y cambiar la versión de la base de datos, para evitar que se atache la base a un servidor cuya versión sea inferior a SQL Server 2005 Service Pack 2.
Luego, habilitamos a las tablas que queremos, para que usen vardecimal, para eso, hay que ejecutar la siguiente instrucción:

exec sp_tableoption ‘NOMBRE DE LA TABLA’, ‘vardecimal storage format’,‘on’

Al ejecutar esta instrucción, todas las columnas numeric se transformaran en longitud variable. Por lo tanto, si nuestra tabla tiene millones de registros, esta operación puede llegar a durar un buen tiempo y un aspecto importante es que bloquea toda la tabla y los índices con este tipo de campos, para cualquier tipo de operación, por lo cual, por lo cual, hay que ejecutar esta operación en un horario donde no tenga impacto.
También podemos ejecutar la operación inversa, o sea, transformar todas las columnas numeric de longitud variable, en longitud fija. Para eso, hay que ejecutar el mismo SP, pero con el valor off.

exec sp_tableoption ‘NOMBRE DE LA TABLA’, ‘vardecimal storage format’,‘off’

Ahora bien, antes de ir corriendo a nuestro servidor para habilitar esta fantástica nueva feature del SQL 2005 SP2, tenemos que pensar si realmente nos trae un beneficio el uso del vardecimal. Por ejemplo, si tenemos una tabla con una columna cuyo tipo de dato sea NUMERIC(15,0) y la mayoría de los valores de esta columna tengan entre 12 y 15 dígitos, el ahorro de espacio que vamos a tener seria mínimo. Ahora, si la dispersión en el tamaño de los valores es importante, entonces conveniente evaluar el uso del vardecimal.
Para tener una aproximación del ahorro que ganaríamos habilitando este nuevo tipo de formato, SQL nos provee el siguiente SP, que calcula la potencial reducción de espacio en una tabla:

exec sys.sp_estimated_rowsize_reduction_for_vardecimal ‘NOMBRE DE LA TABLA’

Consideraciones y restricciones a tener en cuenta:
1) Como dijimos antes, el uso del vardecimal solo esta disponible a partir del Service Pack 2 Enterprise Edition y Developer Edition.
2) Si se habilita el vardecimal, el backup de la base no podrá ser restoreado en un servidor SQL Server 2005 que no sea SP2.
3) Como ya deberían saber, en SQL Server el tamaño de un registro no puede superar los 8060 bytes (aunque en determinadas situaciones hay una opción para superar este limite).
Por lo tanto, si tenemos un vardecimal cuyo valor máximo podría potencialmente exceder los 8060, SQL Server no permitirá la habilitación del vardecimal en dicha tabla.

Links:
http://msdn.microsoft.com/en-us/library/bb508963.aspx

abril 23, 2008

Tablas temporales versus Variables de Tabla

Filed under: Engine, SQL Server — Etiquetas: — grimpi @ 5:40 pm

Una duda bastante comun entre los desarrolladores que trabajan con SQL Server, es saber
distinguir entre una tabla temporal y una variable de tabla.
Ambas estructuras presentan varias diferencias importantes, y fueron hechas para resolver distintos problemas, aunque en muchas situaciones, su uso puede superponerse.
Veamos algunas características de las variables de tabla y sus diferencias con las tablas temporales:

1) Contra lo que muchos suponen, el contenido de una variable de tabla no siempre esta completamente en memoria. En caso de que se inserte una alta cantidad de registros a la variable, se almacenará en tempdb. En cambio una tabla temporal siempre se guarda en tempdb.
Además, cuando se crea una variable tabla, es como si se efectuara cualquier otra operación DDL, por lo tanto la información de la metada se guarda en las tablas de catalogo.
Veamos este código:

DECLARE @TablaTemporal TABLE (ID int)
SELECT * FROM tempdb.sys.tables

Si ejecutamos esto, vemos que en la vista de catalogo sys.tables, aparece el objeto #XXXX, que es la variable de tabla que creamos.

2) No hay rollback en variables de tabla. Las operaciones contra un objeto de este estilo generan menos bloqueos y hacen menor uso del log de transacciones.
En caso de que se haga un INSERT/UPDATE/DELETE contra la variable, dentro de una transacción, al hacer ROLLBACK, el contenido seguirá siendo el mismo.
La ventaja de esto, es que obviamente es más rápido y consume menos recursos. La desventaja es que si necesitamos que sea transaccional la operación, no lo podemos hacer con este tipo de estructuras.
Ejemplo:

DECLARE @TablaTemporal TABLE (ID int)
BEGIN TRANSACTION
INSERT INTO @TablaTemporal (ID) VALUES (1)
INSERT INTO @TablaTemporal (ID) VALUES (2)
INSERT INTO @TablaTemporal (ID) VALUES (3)

SELECT * FROM @TablaTemporal
ROLLBACK TRANSACTION

SELECT * FROM @TablaTemporal

SELECT * FROM tempdb.sys.indexes I INNER JOIN tempdb.sys.tables T
ON I.object_id = t.object_id

Como podemos ver, el resultado devuelto en el ultimo SELECT es el mismo que el devuelto dentro de la transacción, por lo tanto, no efectuó el ROLLBACK.

3) No se pueden agregar índices en variables de tabla. Esto es una gran desventaja a la hora de trabajar con un conjunto gran de datos. Sin embargo, existe una manera de tener índices. Para eso, hay que definir una PRIMARY o UNIQUE KEY cuando se crea la tabla, aunque claro, no siempre este workaround nos sirve. No es posible declarar un NON-CLUSTERED index en una variable de tabla.
Con una tabla temporal en cambio, se puede efectuar las mismas operaciones que una tabla común, incluyendo el poder agregar índices.
Ejemplo de como crear una variable de tabla con índices:

DECLARE @TablaTemporal TABLE (ID int, PRIMARY KEY ([ID]))

Si ejecutamos la consulta que esta mas arriba, vamos a ver el índice de la variable de tabla en las vista de catalogo de tempdb.

4) Otro ítem importantes es que un store procedure que haga uso de tablas temporales, puede que se recompile seguido (dependiendo de como varían las estadísticas), mientras que un store que haga uso de variables de tabla no va sufrir recompilaciones. Si el store es ejecutado muy seguido o es lo suficientemente largo para que se note el tiempo de recompilación, puede llegar a ver una diferencia de performance.
De todos modos, en SQL Server 2005 se mejoro la forma en que se precompilan los store procedures que usan tablas temporales, mejorando la performance…

5) Las variables de tabla no usan paralelismo (multiple threads) en su plan de ejecución. En tablas grandes o en alta concurrencia, esto impacta en la performance, haciéndolas mas lentas en comparación con las tablas temporales.

6) Las variables de tabla tampoco recolectan estadísticas. Esto puede ser bueno y malo a la vez. En caso de tener tablas chicas, las estadísticas no son tan necesarias, por lo cual, el no tener que recolectarlas hace las operaciones de escritura mas rápidas.
Pero por otro lado, al no tener estadísticas, el plan de ejecución que genera el SQL puede no ser el más óptimo.

7) El scope de una tabla temporal es la conexión a la base. Todos los objetos de la base, puede ver el contenido de esta tabla, mientras persista la misma conexión.
Mientras que el scope de una variable de tabla es local solo el store procedure en que se la llama, al igual que una variable común.

CONCLUSION:
No se puede decir que las tablas temporales son peores o mejores que las variables de tabla. Siempre hay que evaluar las 2 alternativas.
Sin embargo, podríamos afirmar que, en operaciones con muchos datos, las tablas temporales tienen más herramientas para ofrecer mejor performance, mientras que para procesos chicos, con pocos registros, las variable de tabla son mejores, al tener menos overhead.

marzo 8, 2008

Diferencias entre Clustered y Non Clustered Index en SQL Server

Filed under: Engine, SQL Server — grimpi @ 9:13 pm

Una pregunta muy común para los iniciados en el mundo de SQL Server, es cuál es la diferencia entre un índice clustered y un índice non-clustered y en qué caso conviene usar un índice u otro. Bueno, empecemos a describir las características generales de un índice y luego de estos tipos de índices y las conclusiones van a ser evidentes.

Los índices son objetos de la bases de datos, cuya función es optimizar el acceso a datos. A medida que las tablas se van haciendo más grandes y se desea hacer consultar sobre estas tablas, los índices son indispensables.

Internamente un índice normal es una estructura de árbol, que cuenta con una página principal y luego esta con paginas hijas, que a su vez tiene más paginas hijas hasta llegar a la pagina final del índice (leaf level).

La clave del índice está repartida en las páginas del índice, de modo tal que la búsqueda se haga leyendo la menor cantidad posible de datos.

Estructura interna de un índice:

Después de esta brevísima introducción, donde está la diferencia entre un índice clustered y uno non-clustered? En la leaf level (la ultima pagina) del índice. En un índice non-clustered, la clave por la que buscamos tiene un puntero a la página de datos donde se encuentra el registro. Mientras que en índice clustered, la leaf level es la pagina de datos!. Con lo cual, el SQL Server, se ahorra hacer un salto para leer los datos del registro (Bookmark lookup). La diferencia es importante, ya que el uso de este tipo de índices al evitar tener que hacer lecturas adicionales para traer el registro, son más performantes.

Búsqueda por clustered index:

Búsqueda por non-clustered index:

SQL Server 2005 incorpora una nueva feature interesante en los índices non-clustered. Ahora es posible incluir dentro de la leaf page del índice, campos que en sí, no son parte de la clave. Esto nos permitirá en algunos casos, evitar el salto a la página de datos (Bookmark Lookup) que habíamos hablado anteriormente. Aunque hay que tener cuidado de seleccionar bien que campos se desean incluir al índice, porque de poner demasiados se expandería mucho el índice, haciendo ineficiente. Por ejemplo, si tenemos una tabla Personas cuyo campo DNI es un índice non-clustered y queremos hacer una consulta que solo traiga el Apellido y DNI, entonces si incluimos el campo Apellido , nos ahorraríamos tener que ir a la página de datos para buscar el valor. Es importante recalcar que el campo Apellido no sería parte de la tabla, sino un campo mas en pagina final del índice.

Ahora bien, entonces porque no siempre usar índices clustered? Bueno, en primer lugar, lamentablemente solo puede haber 1 solo índice clustered por tabla. La razón es muy sencilla y lógica: Los registros de la tabla físicamente son las paginas leaf-level del índice clustered. Los datos de la tabla esta ordenados según el índice. Y obviamente una tabla no puede simultáneamente estar físicamente ordenada de 2 maneras diferentes.
Por lo tanto, en tablas grandes y muy consultadas, tenemos que ser cuidadosos y analizar a que campos vamos a seleccionar para ser llaves del índice clustered. Tenemos 1 solo índice de este tipo por tabla, no hay que desperdiciarlo!!!
Este último punto es importante para saber en qué situaciones y para que campos se debe utilizar un clustered index o un non-clustered.

Guía general de uso de índices:

  • Campos autoincrementales (Identitys, newsequentialid, etc), deben convenientemente ser del tipo clustered index. La razón es reducir el page split (fragmentación) de la tabla.
  • Los clustered index son convenientes si se va seleccionar un rango de valores, ordenar (ORDER BY) o agrupar (GROUP BY).
  • La PK es un buen candidato para un clustered index. Pero no siempre. Por ejemplo, si tenemos una tabla de ventas, cuya PK es un identity en donde se efectúan muchas consultar por rangos de fecha, el campo Fecha seria un mejor candidato para el clustered que la PK.
  • Para búsquedas de valores específicos, conviene utilizar un non-clustered index.
  • Para índices compuestos, mejor utilizar non-clustered index (generalmente).

Blog de WordPress.com.