¿Alguna vez te has encontrado con restricciones de verificación o claves foráneas no confiables en tu base de datos de SQL Server? Si es así, es posible que te hayas preguntado por qué estas restricciones no son confiables y cómo solucionarlas. En este artículo, exploraremos el concepto de restricciones no confiables en SQL Server y discutiremos cómo abordar este problema.

El problema

Las restricciones no confiables pueden causar problemas en tu base de datos porque el optimizador de SQL Server no las utilizará al generar planes de consulta. Esto puede llevar a un rendimiento subóptimo y posibles problemas de integridad de datos. Para identificar restricciones no confiables, puedes utilizar consultas como las siguientes:

SELECT
    cc.object_id AS ccId,
    OBJECT_NAME(cc.parent_object_id) AS tableName,
    cc.name AS ccName
FROM sys.check_constraints AS cc
WHERE
    cc.is_not_trusted = 1
    AND cc.is_disabled = 0;

SELECT
    fk.object_id AS fkId,
    OBJECT_NAME(fk.parent_object_id) AS tableName,
    fk.name AS fkName
FROM sys.foreign_keys AS fk
WHERE
    fk.is_not_trusted = 1
    AND fk.is_disabled = 0;

Si encuentras restricciones no confiables, debes solucionarlas para asegurarte de que sean confiables para el optimizador.

El infractor recurrente

Después de solucionar las restricciones no confiables, es posible que descubras que vuelven a ser no confiables al día siguiente. El culpable más común de este comportamiento es una operación de inserción masiva o copia masiva utilizando herramientas como BULK INSERT o bcp. Estas herramientas tienen un parámetro llamado -h (indicaciones), y una de las indicaciones es CHECK_CONSTRAINTS. Si se omite este parámetro, las restricciones se marcan como no confiables.

La operación masiva se puede realizar utilizando varios métodos, incluyendo T-SQL (BULK INSERT), la línea de comandos de bcp, aplicaciones .NET y paquetes SSIS. Esto dificulta identificar la fuente de las restricciones no confiables.

Usando Eventos Extendidos para rastrear al culpable

Para rastrear la fuente de las restricciones no confiables, puedes configurar una sesión de Eventos Extendidos (XE) con eventos específicos relacionados con los casos de uso que deseas monitorear. En este caso, podemos utilizar los siguientes eventos:

• object_altered: Rastrea las declaraciones ALTER en las bases de datos de restricciones
• databases_bulk_copy_rows: Rastrea las operaciones de copia masiva
• databases_bulk_insert_rows: Rastrea las operaciones de inserción masiva

Al capturar campos de auditoría globales adicionales, como el nombre de la aplicación cliente y el nombre del host cliente, puedes localizar rápidamente el proceso o la persona responsable.

Ejemplo

Veamos un ejemplo para demostrar cómo abordar las restricciones no confiables. Primero, necesitamos crear una base de datos y dos tablas con una clave foránea y una restricción de verificación:

CREATE DATABASE InConstraintWeTrust;

USE InConstraintWeTrust;

CREATE TABLE dbo.[Order]
(
    Id int IDENTITY (1,1) NOT NULL,
    DateCreated datetime2(3) NOT NULL CONSTRAINT DF_Order_DateCreated DEFAULT SYSDATETIME(),
    CONSTRAINT PK_Order PRIMARY KEY CLUSTERED (Id)
);

CREATE TABLE dbo.OrderItem
(
    Id int IDENTITY (1,1) NOT NULL,
    OrderId int NOT NULL,
    ProductName varchar(50) NOT NULL,
    Qty int NOT NULL,
    CONSTRAINT PK_OrderItem PRIMARY KEY CLUSTERED (Id),
    CONSTRAINT FK_OrderItem_Order_Parent FOREIGN KEY (OrderId) REFERENCES dbo.[Order],
    CONSTRAINT CK_OrderItem_PositiveQty CHECK (Qty > 0)
);

INSERT INTO dbo.[Order] DEFAULT VALUES;
INSERT INTO dbo.OrderItem (OrderId, ProductName, Qty)
VALUES
    (1, 'abc', 2),
    (1, 'bcd', 4),
    (2, 'abc', 1),
    (3, 'bcd', 2);

Podemos verificar el estado de confiabilidad de las restricciones utilizando las consultas mencionadas anteriormente. Inicialmente, deberían ser confiables.

A continuación, simularemos un escenario donde deshabilitamos y habilitamos las restricciones:

ALTER TABLE dbo.OrderItem NOCHECK CONSTRAINT FK_OrderItem_Order_Parent;
ALTER TABLE dbo.OrderItem CHECK CONSTRAINT FK_OrderItem_Order_Parent;

ALTER TABLE dbo.OrderItem NOCHECK CONSTRAINT CK_OrderItem_PositiveQty;
ALTER TABLE dbo.OrderItem CHECK CONSTRAINT CK_OrderItem_PositiveQty;

Después de volver a habilitar las restricciones, se vuelven no confiables. Para hacer que sean confiables nuevamente, podemos utilizar el siguiente código:

ALTER TABLE dbo.OrderItem WITH CHECK CHECK CONSTRAINT FK_OrderItem_Order_Parent;
ALTER TABLE dbo.OrderItem WITH CHECK CHECK CONSTRAINT CK_OrderItem_PositiveQty;

Para los escenarios de inserción masiva y bcp, podemos crear un archivo CSV simple y utilizar la instrucción BULK INSERT o la herramienta de línea de comandos bcp para importar los datos. Después de realizar estas operaciones, podemos verificar nuevamente el estado confiable de las restricciones.

Rastreando al culpable

Al configurar una sesión de Eventos Extendidos con los eventos adecuados y capturar campos de auditoría adicionales, podemos rastrear la fuente de las restricciones no confiables. El nombre de la aplicación cliente y el nombre del host cliente pueden ayudar a reducir el número de sospechosos. Si el proceso está anidado, podemos aprovechar las columnas tsql_stack y query_hash_signed para investigar aún más el problema.

Siguiendo estos pasos, puedes identificar y abordar las restricciones no confiables en tu base de datos de SQL Server, asegurando un rendimiento óptimo y la integridad de los datos.

¡Gracias por leer!