Nueva versión de Open DBDiff

Bueno gente, hay una nueva versión de Open DBDiff en la calle (o mejor dicho, en CodePlex).

Que es Open DBDiff?
Es un pequeño programa de sincronización de schemas de bases de datos que funciona en SQL Server 2005/2008.
Hay casi diria decenas de aplicaciones similares en el mercado. La gran mayoría, exceptuando Red Gate y un par más, son bastante limitadas y funcionan mal. No capturan todos los objetos de la base y son incapaces de generar un script que funcione cuando se presentan casos minimamente complejos.
Por otro lado, no existe ningún producto similar, que sea open source.
La idea de Open DbDiff es precisamente llenar este hueco. Hacer una herramienta de comparacion de schemas potente, facil de usar y 100% libre.
A futuro, planeo hacer lo mismo para MySql y a un futuro muy a largo plazo, Oracle.
Es capaz de generar un correcto script de sincronización en situaciones bastante extrañas. Despues de mas de un año de desarrollo, creo que la aplicacion evolucionó lo suficiente para ser probada seriamente en producción (aunque soy conciente de que todavia quedan algunos bugs por resolver).

Este es link a la home en CodePlex para quien le interese probarlo.

Diferencias entre TRUNCATE TABLE y DELETE FROM

Una duda bastante habitual entre los desarrolladores, es cuál es la diferencia entre un TRUNCATE TABLE y un DELETE FROM TABLE. Este post (y el blog en general) está centrado en SQL Server, sin embargo, la mayoría de las diferencias entre ambas sentencias aplican a cualquier motor de bases de datos (Oracle, MySQL, DB2, etc).
Primero voy a enumerar las diferencias y luego voy a explicar el porqué de dichas diferencias.

TRUNCATE TABLE	DELETE FROM
Es una operación DDL.	Es una operación DML.
No permite el borrado selectivo. TRUNCATE TABLE elimina todo el contenido de la tabla.	Permite el borrado selectivo, mediante la clausula WHERE.
No se puede ejecutar, si la tabla tiene asociadas, aun si no existiesen registros en la tabla que contiene la FK.	Se puede ejecutar si hay FK asociadas a la tabla, pero siempre y cuando no tenga registros asociados o la FK este deshabilitada.
Es la forma más rápida de eliminar el contenido de una tabla.	Es más lenta.
No se activa ningún trigger al ejecutarse (a partir de SQL Server 2005, es posible capturar el evento mediante un DDL trigger, pero a modo de auditoría, no es posible tener acceso a los valores que fueron eliminados).	Puede activarse el trigger de ON DELETE y poder determinar que registros están siendo eliminados. siendo eliminados.
En caso que la tabla tuviese un campo Identity, se resetea el valor a 1 (o al valor base determinado en el campo).	No resetea el valor del campo Identity, en caso que la tabla tuviese uno.
TRUNCATE TABLE desasocia (deallocate) las páginas de datos de la tabla.	DELETE FROM marca cada registro afectado, como eliminado.
El logueo en el transaction log es mínimo. Solo registra el dealloc de las páginas de datos.	Loguea cada operación sobre los registros afectados.
No puede ser ejecutado si la tabla tiene asociadas vistas indexadas.	Se puede ejecutar si la tabla tiene vistas indexadas.

Como podemos ver, TRUNCATE TABLE es bastante más restrictivo que DELETE, al punto que en muchas situaciones, aun si queremos eliminar todo el contenido de la tabla, no podemos hacerlo.

Ahora bien, que es lo que hace TRUNCATE y porque es mas rápido que su “competidor”?
Para eso primero voy a hacer una brevísima introducción a como almacena internamente SQL Server los datos.
En SQL Server, los registros de una tabla, son agrupados en una estructura física de datos, que se llama página. Cada página tiene un tamaño fijo de 8060 bytes, y puede almacenar uno o cientos de registros, dependiendo del tamaño del mismo. Cuando se intenta insertar más registros en una tabla y la página de datos está llena, se crea otra donde se inserta el nuevo registro y así sucesivamente.
El comando TRUNCATE, lo que hace es desasociar (deallocate) las páginas de datos de la tabla, sin alterar los registros en sí mismo, mientras que el DELETE FROM recorre cada uno de los registros y los marca como borrados, por lo tanto, hace muchas más operaciones de I/O, que aumenta exponencialmente en relación con TRUNCATE, a medida que aumenta el tamaño de la tabla.
Otra razón que explica la diferencia de performance, es que TRUNCATE TABLE solo loguea en el transaction log, el deallocate de las paginas con la tabla (por lo tanto, es posible hacer un rollback de un TRUNCATE, cosa que muchos piensan que no), mientras que el DELETE FROM manda al transaction log todos los registros afectados, lo que es obviamente mucho más costoso a nivel recursos de I/O.
Por último, al hacer un TRUNCATE un lockeo sobre la tabla, a diferencia del DELETE FROM que hace un lockeo por pagina o registro, el consumo de memoria para almacenar los objetos lockeados es mucho menor.

Lo nuevo de SQL Server 2008: MERGE

Otra de las características interesantes que Microsoft incorporó en SQL Server 2008 y que al igual que la sentencia GROUPING SETS, ya existía en otros motores de bases de datos, es la clausula MERGE (de la cual habíamos hablado brevemente antes).
Esta cláusula nos va a permitir definir lógica de combinación para operaciones atómicas de inserción, borradas y actualización de datos.
La idea es comparar 2 conjuntos de datos y detectar las diferencias de datos entre las 2 tablas y en función de eso, ejecutar alguna operación de actualización sobre la tabla.
La clausula MERGE nos sirve básicamente para 2 cosas:

1) Sincronizar los datos de 2 tablas. Supongamos que tenemos 2 bases distintas (Producción y Desarrollo por ejemplo) y queremos sincronizar los datos de una tabla para que queden exactamente iguales. Lo que antes hubiese implicado
algunas sentencias mezcladas con INNER JOIN y NOT EXISTS, ahora es posible resumirlo en una operación atómica mucho
más sencilla y eficiente.

2) La otra razón por la cual podríamos usar MERGE, es cuando tenemos nuevos datos que queremos almacenar en una tabla y no sabemos si la primary key de la tabla ya existe o no, por lo tanto, no sabemos si hacer un UPDATE o un INSERT en la tabla. El famoso IF EXISTS… (tema que ya habíamos visto acá y que es un poquito más complicado de lo que parece a simple vista).

Veamos un ejemplo:

MERGE dbo.Tabla1 AS Target
USING (SELECT ID,Campo1,Campo2,Campo3 FROM dbo.Tabla2) AS Source
ON (Target.ID = Source.ID)
WHEN MATCHED THEN

UPDATE
SET Target.Campo1 = Source.Campo1, Target.Campo2 = Source.Campo2

WHEN NOT MATCHED BY TARGET THEN

INSERT (ID,Campo1,Campo2,Campo3)
VALUES (Source.ID,Source.Campo1,Source.Campo2, Source.Campo3)

WHEN NOT MATCHED BY SOURCE THEN

DELETE;

Realmente creo que es una de las cosas más interesantes que incorporó la nueva versión de SQL Server y es increíble que algo tan útil y tan práctico como esto, no hubiese estado antes.
Es importante entender que la ventaja del MERGE no es una simple cuestión sintáctica que nos permite escribir un par de líneas menos de código. La ventaja real y más importante está en que permite hacer sincronizar 1 tabla en función de una consulta, de manera atómica. Esto significa que si una operación de delete/insert/update falla, se hace un rollback de todo el conjunto de datos y más importante aún, se garantiza en escenarios de alta concurrencia, donde puede haber otros procesos escribiendo en la misma tabla en el mismo instante, no existan incoherencias.

Capturar salida:
Algo fantástico que existe a partir de SQL Server 2005, es la clausula OUTPUT que permite capturar todo lo que sucedió dentro de una operación INSERT/DELETE/UPDATE. En SQL Server 2008 el uso conjunto de MERGE + OUTPUT nos sirve saber que registros fueron modificados y que acción se hizo sobre ese registro (INSERT, UPDATE o DELETE).
La nueva función $action indica que operación se realizó, mientras que los atributos deleted e inserted guardan la información sobre el registro afectado (de la misma manera que funcionan con los triggers).

Ejemplo:

MERGE dbo.Tabla1 AS Target
USING (SELECT ID,Campo1,Campo2,Campo3 FROM dbo.Tabla2) AS Source
ON (Target.ID = Source.ID)
WHEN MATCHED THEN

UPDATE SET Target.Campo1 = Source.Campo1, Target.Campo2 = Source.Campo2

WHEN NOT MATCHED BY TARGET THEN

INSERT (ID,Campo1,Campo2,Campo3)
VALUES (Source.ID,Source.Campo1,Source.Campo2, Source.Campo3)

WHEN NOT MATCHED BY SOURCE THEN

DELETE

OUTPUT $action, deleted.*, inserted.*;

Resultado:

Avanzando en las profundidades del MERGE:
Si vemos el plan de ejecución, podemos encontrar cosas muy interesantes. En primer lugar, aparece un nuevo operador lógico en el plan, que se llama “Clustered Index Merge” y es nuevo de SQL Server 2008. Este operador en función de un conjunto de datos, realiza un delete, insert o update a una tabla usando su respectivo índice clustered. Si la tabla no tuviese un índice clustered, entonces se usaría el también nuevo operador “Table Merge”. Esta es una de las razones por la cual MERGE no es una simple encapsulación que simplifica cosas que ya podíamos hacer antes, sino que al ser una operación interna del motor de SQL Server, es más eficiente que cualquier otra alternativa existente en las versiones previas de SQL Server.
Otro punto interesante es que podemos ver es que cuando existen índices y las 2 tablas tienen similares volúmenes de datos, el operador MERGE JOIN resulta el método más eficiente en estas operaciones, ya ambas tablas son escaneadas una sola vez y no hay necesidad de ordenar los datos. Es posible cambiar estableciendo otro join hint, pero en la mayoría de los casos, el MERGE JOIN es lo más óptimo.

Algunas recomendaciones para obtener mejor performance con esta sentencia:

• Crear un índice clustered sobre las columnas relacionadas en el JOIN, en la tabla destino.
• Crear un índice único sobre las columnas relacionadas en el JOIN, en la tabla de origen (en caso que hubiese).

Esto garantiza al motor que no tiene que ejecutar ninguna validación adicional ni efectuar ningún sort extra.

Links:
http://technet.microsoft.com/en-us/library/cc879317.aspx
http://technet.microsoft.com/en-us/library/bb522522.aspx

Identity en SQL Server

Quienes trabajaron mínimamente en alguna versión de SQL Server, conocerán los campos Identitys, que son columnas cuyo valor es autoincremental. Hay muchas discusiones sobre las ventajas y desventajas del uso de este tipo de campos, para las columnas que componen la clave primaria (PK), pero es algo que ya voy a tratar en otro artículo.
Por el momento muestro un listado de tips que cualquier programador que trabaje con SQL Server debería saber al respecto de los campos Identitys:

1) Resetear el valor del campo identity:

Mucha gente piensa que no es posible resetear el contador de un identity de una tabla. FALSO. Existen dos formas de hacerlo, una es mediante TRUNCATE TABLE. Cuando hacemos un TRUNCATE de una tabla, además de borrar todo su contenido, reseteamos el valor del campo Identity. Esto es muy obvio, porque un TRUNCATE TABLE no hace nada más y nada menos que borrar la tabla y volver a crearla.
La segunda opción (y la que nos interesa) es usar el comando DBCC CHECKIDENT. Esta instrucción permite setera el valor que queremos del campo Identity.
Su sintaxis es la siguiente:

DBCC CHECKIDENT <NombreDeTabla>,RESEED,NuevoValor

Hay que tener mucho cuidado con el uso de este comando, ya que podemos setear un valor menor al máximo de la tabla actual y crear en algún momento un valor duplicado. Una opción interesante de este comando es asignar al campo el valor máximo de la tabla + 1, y así garantizar que no tendremos problemas de registros duplicados, para eso solo hay que poner:

DBCC CHECKIDENT <NombreDeTabla>

2) Insertar valores explícitos en un campo Identity:

Cuando se inserta un registro en una tabla con un campo Identity, este se incrementa automáticamente y si queremos modificar o insertar este valor manualmente, el SQL nos tira error y nos indica que no es posible.
Sin embargo, algunas veces puede que necesitemos deshabilitar temporalmente la propiedad Identity, y poder ingresar un valor explícito a la tabla. Generalmente queremos hacer algo así cuando hacemos copias o replicaciones de una tabla de una base a otra tabla.
Para esto, tenemos la opción:

SET IDENTITY_INSERT <NombreDeTabla> ON

Con esta opción, podemos insertar valores en un campo Identity como si fuese un campo más. Luego, cuando terminamos de insertar valores, hay que volver habilitar nuevamente la propiedad Identity del campo. Para eso, corremos el mismo comando anterior, pero con el parámetro OFF.

SET IDENTITY_INSERT <NombreDeTabla> OFF

3) Capturar el valor en un INSERT:

Muchas veces cuando tenemos una tabla con un campo Identity, al hacer un INSERT, queremos saber cuál es el valor que insertó en este campo.
Lo mas común en estos casos es usar @@Identity, que es una variable global que indica el valor del último campo identity insertado en cualquier tabla de la base. Esto último es un detalle muy importante. Si la tabla contiene un trigger que inserta registros en otra tabla, o antes de leer la variable @@Identity, se inserta otro registro en cualquier tabla de la base, el valor @@Identity, tendrá un valor diferente al que estábamos esperando.
Para resolver esta situación, tenemos dos funciones muy útiles: SCOPE_IDENTITY e IDENT_CURRENT. La función SCOPE_IDENTIY nos devuelve el último valor generado dentro de un scope, o sea dentro de un entorno, ya sea un Store Procedure, Function o Trigger. En nuestro caso anterior, como el trigger esta fuera del scope, la función SCOPE_IDENTIY nos devolvería el valor que queremos.
Pero por otro lado, tenemos la función, IDENT_CURRENT, donde se le pasa por parámetro el nombre de la tabla y nos devuelve el ultimo valor identity generado para esta tabla, sin importar el scope. Esta opción en mi opinión es la más clara. Su sintaxis es la siguiente:

SELECT IDENT_CURRENT(‘NombreTabla’)

4) Capturar los valores Identity en un INSERT con multiples registros:

Supongamos que insertamos en una sola sentencia INSERT, mas de un registro en una tabla. Como hacemos para capturar el valor de la columna identity de todos los nuevos registros insertados?. Ni la funcion IDENT_CURRENT ni SCOPE_IDENTITY nos sirven, ya que estas solo devuelven el ultimo valor insertado, pero a nosotros nos interesan todos los valores insertados. Veamos este ejemplo:

CREATE TABLE Cliente (ClienteID INT IDENTITY, Nombre VARCHAR(50))

INSERT INTO Cliente
SELECT ‘Esteban’
UNION
SELECT ‘Juan’
UNION
SELECT ‘Ricardo’

SELECT IDENT_CURRENT(‘Cliente’)

Como ya dijimos antes, IDENT_CURRENT solo nos va a devolver el valor del identity del registro ‘Ricardo’, que fue el último registro que se insertó, por lo tanto, el ejemplo anterior no nos sirve.
Pero a partir de SQL Server 2005, existe la interesante clausula OUTPUT, que permite capturar el valor de las operaciones INSERT y DELETE.
Veamos entonces este ejemplo, que usa OUTPUT y almacena los valores de los registros insertados en una variable de tabla.

DECLARE @NuevosIdentitys TABLE (ID INT)

INSERT INTO Cliente
OUTPUT INSERTED.ClienteID INTO @NuevosIdentitys(ID)

SELECT ‘Esteban’
UNION
SELECT ‘Juan’
UNION
SELECT ‘Ricardo’

SELECT * FROM @NuevosIdentitys

5) Detectar si una columna es Identity:

Usando la función COLUMNPROPERTY, podemos obtener esta información.

SELECT COLUMNPROPERTY(OBJECT_ID(‘<NombreDeTabla>’),’<NombreColumna>‘,‘IsIdentity’)

Otra opción, más potente, consiste en usar la vista sys.identity_columns
(solo a partir de SQL Server 2005), que además de determinar si una columna es Identity, nos permite obtener mayor información sobre la columna relacionada con esta propiedad, como por ejemplo el valor inicial, el valor de incremento, si se aplica o no también para casos de replicación (IsNotForReplication), etc.

SELECT
Name,
is_identity,
seed_value,
increment_value,
is_not_for_replication,
last_value
FROM sys.identity_columns

6) Obtener el valor de la columna Identity aun si saber el nombre del campo:

Supongamos que queremos obtener el valor de una columna que sabemos que es identity, pero solo sabemos el nombre de la tabla (en situaciones donde ejecutamos SQL dinámico podría llegar a darse un caso así).
Existe en SQL Server una función que se llama IDENTITYCOL, que precisamente nos devuelve este valor que queremos averiguar.

SELECT IDENTITYCOL FROM <NombreDeTabla>

Parameter Sniffing

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.

Lo nuevo de SQL Server 2008: Grouping Sets

Una característica piola que tiene la nueva versión de SQL Server, es el nuevo operador GROUPING SETS, que ya existía en otros motores como Oracle hace tiempo (es un estándar ANSI 2006) y permite combinar consultas de agrupación distintas en una sola consulta. El operador GROUPING SETS es una extensión de la cláusula estándar GROUP BY.
Cuando no se requieren todas las agrupaciones posibles que se generan utilizando un operador ROLLUP o CUBE (que ya existían en SQL Server 2005), se debe utilizar GROUPING SETS para especificar sólo las agrupaciones que se deseen. O sea, gracias a GROUPING SETS obtenemos los niveles de agrupación deseados y además nos devuelve el subtotal para cada subconjunto de agrupación.
Podríamos decir que GROUPING SET es mas abarcativo y genérico que ROLLUP o CUBE. Generalmente consultas que usen este tipo de operadores están relacionadas con el análisis de datos, reporting y todo lo relacionado con el mundo BI.
Este nuevo operador no permite hacer nada nuevo que antes no se pudiese, solo simplifica y optimiza determinadas consultas, lo ya de por si, es un factor importante.
Veamos un ejemplo de donde utilizarlo:

Supongamos que tenemos la siguiente tabla de Clientes con datos ya cargados:

CREATE TABLE [dbo].[Client]
(

[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NULL,
[Country] [varchar](50) NULL,
[Zone] [varchar](50) NULL,
[Year] [int] NULL,
[Precio] [numeric](12, 4) NULL,

)

Y ahora supongamos que queremos traer la suma del campo Precio, agrupada por los Campos Country y Zone, luego solo por Zone y luego por el total y todo en una sola misma sentencia de tal manera que el resultado de la consulta fuese el siguiente:

La primera opción que uno piensa, es hacer una consulta usando UNION ALL con tres consultas diferentes, una por cada agrupación:

SELECT SUM(Value) AS Total, Zone, Country FROM dbo.Client
GROUP BY Zone,Country

UNION ALL
SELECT SUM(Value), NULL, Country FROM dbo.Client
GROUP BY Country

UNION ALL
SELECT SUM(Value), NULL, NULL FROM dbo.Client
ORDER BY Zone, Country

Sin embargo, podemos escribir una consulta equivalente y acá entra GROUPING SETS de SQL Server 2008 para simplificarnos la vida:

SELECT SUM(value) AS Total, Zone, Country FROM dbo.Client
GROUP BY
GROUPING SETS
(
(Zone, Country),
(Country),
()
)
ORDER BY GROUPING(Zone), GROUPING(Country)

Ahora como podrán ver en este último ejemplo, además de usar el operador GROUPING SETS, hago uso de la función GROUPING en el ORDER BY, para ordenar el resultado por Zona y Country.
Esta función (que ya existía en SQL Server 2005), nos indica si una expresión de columna especificada en una lista GROUP BY es agregada o no. GROUPING devuelve 1 para agregado y 0 para no agregado, en el conjunto de resultados.

Performance:

La ventaja del uso GROUPING SETS no está solo dada por simplificar sintácticamente las consultas, sino también en cuestiones de performance.
En las pruebas que hice para monitorear el uso de recursos (SET STATISTICS IO ON) con 90000 registros en la tabla Client estos fueron los resultados:

Usando UNION ALL:
Table ‘Client’. Scan count 3, logical reads 1728, physical reads 6, read-ahead reads 590, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Usando GROUPING SETS:
Table ‘Client’. Scan count 1, logical reads 576, physical reads 6, read-ahead reads 590, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Como podemos ver, GROUPING SETS hace menor uso de recursos de I/O. La razón de la mejor performance, se debe a que usando la nueva característica de SQL Server, el motor necesita leer menos paginas de datos, ya que hace el cálculo de agregación de más alto nivel sobre las agregaciones de menor nivel (usando como ejemplo nuestra consulta, el nivel de menor de agregación seria Zone y Country, luego solo Country y por último a partir del resultado de la prime).
Dejo la tarea al lector para que compare los planes de ejecución y pueda apreciar las diferencias en las operaciones que realiza el motor entre una consulta y otra.

Links:
Recomiendo leer el articulo de MSDN de equivalencias de GROUPING SETS.

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

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

sql_expression_dependencies y Filtered Index en SQL Server 2008

SQL Server incorpora montón de nuevas novedades, muy interesantes.
Una de ellas, son los Índices Filtrados, (Filtered Index), que permite indexar pero solo para determinado rango de valores dada una expresión. No es intención del post entrar en detalles de esto. Si quieren información al respecto, recomiendo ver este link.

Ahora, como hago para saber los objetos que están siendo referenciados en la expresión del filtro del índice? SQL Server 2008 incorpora una nueva vista de sistema, que se llama sys.sql_expression_dependencies y que contiene una fila para cada dependencia por nombre en una entidad definida por el usuario en la base de datos actual.
Dada esta vista, saber por ejemplo todas las columnas usadas dentro de expresiones en un índice filtrado, resulta sumamente fácil:

SELECT I.name AS IndexName, OBJECT_NAME(I.object_id) AS TableName, C.name
FROM sys.sql_expression_dependencies SED
INNER JOIN sys.indexes I ON I.object_id = SED.referencing_id AND I.index_id = SED.referencing_minor_id
INNER JOIN sys.columns C ON C.object_id = SED.referenced_id AND C.column_id = SED.referenced_minor_id

Para más información sobre esta Vista, ver este link.

Como hacer un Attach dinámicamente de un conjunto de MDF a una instancia de SQL Server

Recientemente tuve la necesidad de attach a una instancia de SQL Server, más de 200 bases de datos. Tenía los archivos .mdf (Datos) y .ldf (Transaction log) de cada una de las 200 bases.
Por supuesto que hacer esta operación manualmente es algo bastante engorroso. Por lo cual, investigué un poco como hacer esta operación dinámicamente. O sea, hacer un script que lea todos los archivos .mdf de un directorio y haga el attach de cada uno de los mdf a la instancia del SQL Server, obviamente con su respectivo archivo de transaction log.

Paso 1:
Lo primero que necesitamos es tener un listado de todos los archivos .mdf que se encuentran dentro de un directorio y que deseamos hacer el attach.
Existe en SQL Server 2005, un store procedure extendido no documentado, que se llama xp_dirtree, que devuelve todos los archivos existentes dentro de una carpeta.
Para capturar la info de este store, almacenamos dentro de una tabla temporal el resultado de la consulta:

declare @Path nvarchar(255)
select @Path = N‘C:\Data\’
create table #filetmp (Name nvarchar(255) NOT NULL, depth int NOT NULL, IsFile bit NULL)
insert #filetmp EXECUTE master.dbo.xp_dirtree @Path, 1, 1

Paso 2:
Necesitamos saber a partir del .mdf, cual es el nombre de la base de datos que tenemos que crear. Por ejemplo, un archivo puede llamarse Test1.mdf, sin embargo, lógicamente, el nombre de la base que contiene es distinto. Para averiguar este dato, debemos usar el comando DBCC no documentado de Microsoft, CheckPrimaryFile.

DBCC CHECKPRIMARYFILE (‘C:\Data\ISMM2_Data.MDF’ , 2)

(El primer parámetro es el path completo y el nombre del archivo mdf que se desea tener información, el segundo parámetro, indica que tipo de información queremos extraer)

El resultado de esta consulta es el siguiente:

Como podemos observar, este comando nos devuelve un listado de propiedades del .mdf, donde la primer propiedad, Database name, es el nombre lógico de la base de datos. Debemos capturar este valor para usarlo más adelante.

Paso 3:
Luego, debemos obtener todos los archivos dependientes de la base. Por ejemplo, una base de datos puede tener además obviamente del .mdf, otros archivos secundarios, como por ejemplo el archivo .ldf (transaction log) y en caso de que tenga más de un filegroup, sus respectivos archivos .ndf.
Para averiguar esta información, nuevamente debemos usar el comando CHECKPRIMARYFILE, pero el segundo parámetro ahora debe ser un 3.

DBCC CHECKPRIMARYFILE (‘C:\Data\ISMM2_Data.MDF’ , 3)

El resultado de esta consulta es el siguiente:

En este caso, la instrucción devuelve el nombre físico y lógico de todos los archivos dependientes de la base de datos. Debemos almacenar el valor del campo filename dentro de una tabla temporal (ver el script de ejemplo para ver como se hace).

Paso 4:
Ok, ya tenemos toda la info que necesitamos para hacer el attach dinámico de todas la base de datos a la instancia del SQL Server, pero como se hace vía script un attach a partir de un mdf en SQL Server? Bueno, existen 2 alternativas. Una es usando la instrucción CREATE DATABASE … FOR ATTACH y la otra es usando el Store Procedure sp_attach.
Personalmente encuentro mucho más claro el primer método.

Ejemplo de uso:

CREATE DATABASE ISMM ON
(FILENAME = N‘C:\Data\ISMM_Data.mdf’),
(FILENAME = N‘C:\Data\ISMM_log.ldf’) FOR ATTACH

Paso 5:
Ahora viene lo más difícil. Tenemos toda la info sobre las bases de datos y sabemos cómo hacer un attach individual de un mdf. Pero ahora tenemos que juntar toda la información y generar un script que haga el attach de cada una de las bases con todos sus archivos dependientes.
Copiar este script en la web, sería poco claro de entender. Por lo tanto, dejo a disposición de quien ande necesitando algo similar, un script genérico, para efectuar el attach dinámico, usando los 4 pasos anteriores.
Lo pueden bajar desde acá: http://opendbdiff.com/AttachMasivo.txt

Cualquier comentario, duda, mejora u error, no duden en informar. Espero que les sea útil. A mí me ahorro bastante tiempo.

Nueva version de Open DBDiff

Bueno gente, disculpen que haya “desaparecido” casi 2 meses. Pero ando con mucho laburo, otro blog que mantener, proyectos particulares, facultad, ustedes me entienden.

En fin, entre tantas cosas que vengo haciendo, una es una herramienta de comparacion y sincronizacion de schemas de bases de datos para SQL Server 2005. A principios de año, subi una version a codeplex. Ahora, recien salida del horno, esta disponible la Beta 3 de dicha aplicacion.

Que mejoras trae? Un varias:

1) Montón de bugs arreglados, especialmente en la comparacion de UserTypes. De todas las herramientas que probé de este tipo, no encontre ninguna sola, que sincronize siempre bien en todas las situaciones, incluyendo dependencias, los UserTypes. Open DBDiff en este aspecto, funciona mejor incluso que la herramienta de Red Gate y de Visual Studio.

2) Tambien arreglé bugs y lios con las depedencias para los objetos XML Schema, Assemblys y Foreing Keys.

3) Mejoras en la sincronizacion de Store Procedures, Funciones y Vistas. Agregue soporte para Funciones y Vistas con la opcion SCHEMABINDING.

4) Agregue soporte para CLR-UDT, Users y Roles.

5) Una mejor vista grafica, incluyendo un arbol de objetos, con las diferencias entre los 2 schemas.

En fin, esta nueva version, realmente esta mucho mas acabada. El que esta en el tema, le recomiendo bajarla. Como siempre, se aceptan comentarios, criticas y sugerencias.

El link para bajarla es este: http://www.codeplex.com/OpenDBiff/Release/ProjectReleases.aspx?ReleaseId=15533