Tablas Temporales vs Variables Tipo Tabla

Desde que era programador pensé que las variables de tipo tabla eran mejores que las tablas temporales porque alguna vez escuché que las primeras eran tablas que vivían en la memoria, mientras que las tablas temporales almacenaban sus datos en disco. Sin embargo, este es un mito que debe ser desterrado ya que la verdad es completamente opuesta y a continuación lo vamos a comprobar.
Sigue leyendo “Tablas Temporales vs Variables Tipo Tabla”

Foreign Keys Duplicadas

Hola, hace ya algún tiempo que no posteo nada, y bueno la verdad es que he estado en una transición bastante amplia en mi vida profesional y me aleje un poco de mi blog, pero ahora pienso retomarlo con fuerza.

Bueno el tema que me trae a este nuevo post son los constraints duplicados especialmente los foreign keys. Hace un tiempo recibí un servidor productivo de base de datos del cual los usuarios tenían quejas constantes sobre lentitud. Una de las tareas que realice sobre el servidor fue la revisión de las claves foráneas de las diferentes bases de datos, y encontré una gran cantidad de estas, la gran mayoría estaban duplicadas, triplicadas y hasta cuadriplicadas.

Haciendo consultas a los desarrolladores, me comentaron que ellos usaban el ERwin para poder hacer la creación de objetos de base de datos, y simplemente ejecutaban los scripts que este generaba, entonces comencé a entender el problema, ahora ya puedia plantear una solución al mismo. Pero antes quisiera mencionar que por cada constraint de clave foránea que tiene la tabla, cuando se ejecute alguna operación de modificación de datos, el motor validara los datos contra todos los constraints de la tabla generando lecturas en las tablas referenciadas, entonces si tenemos muchos contraints duplicados, el motor de base de datos hara muchas lecturas innecesarias para validar dichos constraints. A continuación se muestra un ejemplo de esto:

CREATE DATABASE DUPFKS
GO

USE DUPFKS
GO

IF (OBJECT_ID(‘dbo.Hijo’)>0)
DROP TABLE dbo.Hijo
GO
IF (OBJECT_ID(‘dbo.Padre’)>0)
DROP TABLE dbo.Padre
GO
— Creamos la tabla padre
CREATE TABLE dbo.Padre
(col1 INT CONSTRAINT PK_Padre PRIMARY KEY)
GO
— Creamos la tabla hijo sin NINGUN constraint duplicado
CREATE TABLE dbo.Hijo
(col1 INT NOT NULL
,
col2 INT NOT NULL)
GO
ALTER TABLE dbo.Hijo
ADD CONSTRAINT PK_Hijo
PRIMARY KEY (col1,col2)
GO
ALTER TABLE dbo.Hijo
ADD CONSTRAINT FK_Hijo_col1_1
FOREIGN KEY (col1)
REFERENCES Padre(col1)
GO
— Llenamos la tabla padre con algunos registros
INSERT INTO Padre
SELECT OBJECT_ID
FROM sys.objects

— Indicamos que se midan las estadisticas de IO
SET STATISTICS IO ON

INSERT INTO Hijo VALUES (4,1)
GO
/*
Table ‘Padre’. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Hijo’. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
*/

INSERT INTO Hijo VALUES (4,2)
GO
/*
Table ‘Padre’. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Hijo’. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
*/
— Ingresamos el primer constraint Duplicado
ALTER TABLE dbo.Hijo
ADD CONSTRAINT FK_Hijo_col1_2
FOREIGN KEY (col1)
REFERENCES Padre(col1)
GO

— Volvemos ha hacer una insercion
INSERT INTO Hijo VALUES (4,3)
GO
/*
Table ‘Padre’. Scan count 0, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Hijo’. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
*/
— Si comparamos las estadisticas contra las anteriores
— vemos que las lecturas en la tabla padre se duplicaron

— Ingresamos el segundo constraint Duplicado
ALTER TABLE dbo.Hijo
ADD CONSTRAINT FK_Hijo_col1_3
FOREIGN KEY (col1)
REFERENCES Padre(col1)
GO

— Volvemos ha hacer una insercion
INSERT INTO Hijo VALUES (4,4)
/*
Table ‘Padre’. Scan count 0, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Hijo’. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
*/
— Si comparamos las estadisticas contra las anteriores
— vemos que las lecturas en la tabla padre se triplicaron

— Para comprobar la teoria deshabilitaremos el ultimo constraint duplicado creado
ALTER TABLE dbo.Hijo
NOCHECK CONSTRAINT FK_Hijo_col1_3
GO

— Realizamos la inserción
INSERT INTO Hijo VALUES (4,5)
/*
Table ‘Padre’. Scan count 0, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Hijo’. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
*/
— Podemos ver que las lecturas contra la tabla padre ya no validaron
— el ultimo constraint duplicado ya que este fue eliminado.

Para solucionar este problema se ha realizado el siguiente script el cual permite identificar los constraints duplicados además de proveer la sentencia necesaria para eliminarlos.

;WITH ForeignKeys1 AS
(
SELECT fk.OBJECT_ID
,OBJECT_NAME(fk.parent_object_id) AS table_name
,fk.name AS foreign_key_name
,fk.create_date
,(SELECT CAST(parent_object_id AS VARCHAR(50))
+
SPACE(1) + CAST(parent_column_id AS VARCHAR(50))
+
SPACE(1) + CAST(referenced_object_id AS VARCHAR(50))
+
SPACE(1) + CAST(referenced_column_id AS VARCHAR(50)) AS [data()]
FROM sys.foreign_key_columns fkc
WHERE fk.OBJECT_ID = fkc.constraint_object_id
ORDER BY constraint_column_id
FOR XML PATH('')) foreign_key
FROM sys.foreign_keys fk
WHERE is_disabled = 0
)
,
ForeignKeys2 AS
(
SELECT OBJECT_ID, table_name, foreign_key_name, foreign_key
,COUNT(1) OVER (PARTITION BY foreign_key) AS NbrDuplicated
,ROW_NUMBER() OVER (PARTITION BY foreign_key ORDER BY foreign_key_name) AS Rownum
FROM ForeignKeys1
)

— Sentencia para listar los constraints duplicados
SELECT table_name AS [Table Name]
,foreign_key_name AS [Foreign Key]
,‘ALTER TABLE ‘ + QUOTENAME(table_name,‘]’) + ‘ DROP CONSTRAINT ‘ +
QUOTENAME(foreign_key_name,‘]’) AS [Drop Sentence]
FROM ForeignKeys2
WHERE NbrDuplicated > 1
ORDER BY table_name, foreign_key_name

Este script ha sido modificado del original encontrado en la siguiente URL: http://www.jasonstrate.com/2009/01/find-duplicate-foreign-keys/#

Espero haya sido de utilidad este post y sirva para mantener más sanas sus bases de datos. Hasta la próxima.

Limpieza del Historial de Backups

Hola, queria compartir con todos una experiencia que tuve el día de hoy y es que mientras estaba sacando un backup a una base de datos relativamente pequeña, y el proceso estaba demorando mucho tiempo y el causante de esa demora es algo que normalmente uno no toma en cuenta, y es el historial de backups de las bases de datos de nuestros servidores SQL Server. Muchas veces tenemos servidores que se encuentran corriendo ya varios años e incluso algunos configurados con log shipping lo cual aumente considerablemente nuestro historial de backups, y con el pasar del tiempo los procesos de backup y restore van haciendose mas lentos y es debido a que cada vez que se saca una backup a una base de datos, algunos registros son ingresados dentro de la base de datos de sistema MSDB, y con el pasar del tiempo esta base de datos puede llegar a ser bastante grande si es que no se hace un trabajo de depuracion regularmente.

Entonces ya habia encontrado el problema, ahora tenia que buscar una solucion al mismo y buscando en internet encontre este post de Brent Ozar, donde explica que el problema se puede solucionar haciendo uso del procedimiento almacenado sp_delete_backuphistory, el cual justamente se encarga de limpiar el historial de los backups.

Bueno para no tener este problema mas decidí crear un job que hiciera este trabajo regularmente para mantener activos solo hasta tres meses de historial de los backups. El código que use en el job es el siguiente:


USE msdb;
GO
DECLARE @v_MaxDateHistory DATETIME
SET
@v_MaxDateHistory = CONVERT(DATETIME,CONVERT(VARCHAR,DATEADD(MONTH,-3,GETDATE()),112))
EXEC sp_delete_backuphistory @v_MaxDateHistory;
GO

Bueno, espero que les haya sido de utilidad.

Foreign Keys, Buenas o Malas?

Hola, ha pasado un buen tiempo desde mi último post, y la verdad es que he estado bastante atareado con el trabajo estas últimas semanas, pero ya volví, y decidí darle un poco de tiempo a este pequeño rincón que he creado para poder compartir el conocimiento en base de datos. Ahora quisiera hablar sobre algo que vi en estos últimos días y la verdad es algo polémico, porque la gente en la comunidad de SQL Server no está 100% de acuerdo con el tema, y a lo que me refiero es a las relaciones entre las tablas de las bases de datos.

Las relaciones entre las tablas de la base de datos son implementadas a través de constraints, los cuales aseguran la integridad de la información contenida en las tablas. Desde mi punto de vista los constraints de base de datos son el último cordón de seguridad para nuestra información. Un sistema puede tener muchísimas reglas de negocio y de integridad de los datos en el lado de la aplicación, y eso está bien, pero no hay que olvidar que las bases de datos siempre están expuestas a bugs dentro de la aplicación o ataques externos o internos dentro de la organización los cuales pueden introducir información maliciosa o incorrecta en las tablas sin necesidad de pasar por las capas aplicación, en este caso la base de datos queda completamente expuesta a inconsistencia de datos.

Ahora los detractores de las relaciones entre las tablas indican que al colocar este constraint en las tablas tiene un precio en la performance de las operaciones de insert, delete y update. Esto es cierto, pero si los constraints de Foreign Key han sido bien definidos, esto no tiene porque ser un problema debido a que una de las mejores prácticas al momento de crear un constraint de Foreign Key es Indexarlo. Esto no lo hace el motor de base de datos automáticamente, esto debe ser hecho por el arquitecto de base de datos o el administrador de base de datos. Si una relación esta indexada, se reduce bastante el impacto en las operaciones de insert, delete y update e incluso se logra reducir la posibilidad de obtener un deadlock en la base de datos; además de esto es muy beneficioso para las operaciones de lectura contra las tablas ya que si las relaciones están indexadas, el optimizador de consultas de la base de datos podrá obtener mejores planes de ejecución dando como resultado respuestas mas rápidas.

A continuación veremos un ejemplo simple que demostrara el efecto que puede tener el hecho de tener o no relaciones en las bases de datos. Para este ejemplo usaremos la base de datos AdventureWorksR2 la cual puede ser descargada desde aquí. Primero revisaremos el plan de ejecución que se genera con la siguiente consulta:

SELECT pro.Name
FROM Production.ProductCategory cat
INNER JOIN Production.ProductSubcategory sub
ON sub.ProductCategoryID = cat.ProductCategoryID
INNER JOIN Production.Product pro
ON sub.ProductSubcategoryID = pro.ProductSubcategoryID

Se puede observar que de las tres tablas que intervienen en la consulta solo dos aparecen en el plan de ejecución. Esto se debe a que sql server confia en la información que se encuentra en las tablas debido a los foreign keys que hay entre ellas. Para comprobarlo vamos a crear las mismas tres tablas pero sin relaciones para ver el comportamiento del motor de base de datos.

SELECT *
INTO dbo.ProductCategory
FROM Production.ProductCategory

SELECT *
INTO dbo.ProductSubcategory
FROM Production.ProductSubcategory

SELECT *
INTO dbo.Product
FROM Production.Product

Ahora ejecutaremos la misma consulta pero con las nuevas tablas sin relaciones.
SELECT pro.Name
FROM dbo.ProductCategory cat
INNER JOIN dbo.ProductSubcategory sub
ON sub.ProductCategoryID = cat.ProductCategoryID
INNER JOIN dbo.Product pro
ON sub.ProductSubcategoryID = pro.ProductSubcategoryID

Ahora vemos que el plan de ejecución es diferente, esto se debe a que como no hay relaciones entre las tablas SQL debe acceder a todas las tablas para poder obtener la información requerida. Pero algunos dirán que esas tablas no tienen indices y que los índices ayudarían. Entonces crearemos los índices sugeridos.

ALTER TABLE dbo.ProductCategory
ADD CONSTRAINT PK_ProductCategory_tmp
PRIMARY KEY (ProductCategoryID)
GO

ALTER TABLE dbo.ProductSubcategory
ADD CONSTRAINT PK_ProductSubcategory_tmp
PRIMARY KEY (ProductSubcategoryID)
GO

ALTER TABLE dbo.Product
ADD CONSTRAINT PK_Product_tmp
PRIMARY KEY (ProductID)
GO

CREATE NONCLUSTERED INDEX IDX_tmp_ProductSubcategory_01
ON dbo.ProductCategory (ProductCategoryID)
GO

CREATE NONCLUSTERED INDEX IDX_tmp_Product_01
ON dbo.Product (ProductSubcategoryID)
GO

Ahora volvemos a ejecutar la misma consulta, pero ademas ahora mediremos las operaciones de IO.

SET STATISTICS IO ON
GO
SELECT pro.Name
FROM dbo.ProductCategory cat
INNER JOIN dbo.ProductSubcategory sub
ON sub.ProductCategoryID = cat.ProductCategoryID
INNER JOIN dbo.Product pro
ON sub.ProductSubcategoryID = pro.ProductSubcategoryID


Vemos que el plan de ejecución ha variado un poco. Ahora veamos las estadísticas de IO

Table 'Product'. Scan count 37, logical reads 664, physical reads 0
Table
'ProductCategory'. Scan count 0, logical reads 74, physical reads 0
Table
'ProductSubcategory'. Scan count 1, logical reads 2, physical reads 0

Ahora mediremos las estadisticas de IO que resultan de la consulta a las tablas con relaciones.

SELECT pro.Name
FROM Production.ProductCategory cat
INNER JOIN Production.ProductSubcategory sub
ON sub.ProductCategoryID = cat.ProductCategoryID
INNER JOIN Production.Product pro
ON sub.ProductSubcategoryID = pro.ProductSubcategoryID

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0
Table
'Product'. Scan count 1, logical reads 15, physical reads 0
Table
'ProductSubcategory'. Scan count 1, logical reads 2, physical reads

Como se puede observar claramente la consulta con las tablas que tienen relaciones es mucho más eficiente que la consulta a las tablas sin relaciones, debido a que el optimizador de consultas de SQL Server tiene más recursos de los cuales valerse para poder obtener planes de ejecución mas óptimos. El numero de lecturas baja considerablemente casi un 98%. Entonces mi opinión personal las relaciones entre las tablas es bastante beneficioso para la base de datos tanto desde el punto de vista de seguridad como de performance.

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.