The problem
In this scenario, you have discovered that one of your Check constraints or Foreign keys is not trusted.
Maybe you’ve detected it with a sp_Blitz, dbachecks or out of curiosity, with ad-hoc queries like these.
/* Untrusted Check constraints */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
/* Untrusted Foreign keys */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
(Should be run in the context of database)
We want to fix those untrusted constraints because the optimizer won’t use them when coming up with a query plan
Repeating offender
Okay, so you went through the effort of fixing them, but the next day your constraints are not trusted again. What gives?
If you are sure none of the DBAs or developers is doing this to spite you, the most common culprit is a BULK INSERT
One of its parameters is -h(hints)
and one of those hints is CHECK_CONSTRAINTS
CHECK_CONSTRAINTS
Specifies that all constraints on the target table or view must be checked during the bulk-import operation. Without the CHECK_CONSTRAINTS hint, any CHECK, and FOREIGN KEY constraints are ignored, and after the operation the constraint on the table is marked as not-trusted
If you skip this parameter, bcp will mark the constraints as untrusted.
And the bulk operation can be run from several different sources.
- TSQL (
BULK INSERT) - bcp command line
- .NET application
- SSIS
No wonder you might have trouble finding the culprit.
Extended Events to the rescue
We’ll set up an Extended Events (XE) session with three events to track each use case
- object_altered - if someone really is running the ALTERS against the constraint
- databases_bulk_copy_rows
- databases_bulk_insert_rows
For the bcp and bulk insert, respectively.
We’ll grab additional audit global fields to locate the responsible process/person more quickly.
Demo
First, the XE definition.
CREATE EVENT SESSION TrackConstraintTrust
ON SERVER
ADD EVENT sqlserver.databases_bulk_copy_rows
(ACTION
(
sqlserver.client_app_name
, sqlserver.client_hostname
, sqlserver.database_name
, sqlserver.query_hash_signed
, sqlserver.server_instance_name
, sqlserver.server_principal_name
, sqlserver.sql_text
, sqlserver.tsql_stack
)
)
, ADD EVENT sqlserver.databases_bulk_insert_rows
(ACTION
(
sqlserver.client_app_name
, sqlserver.client_hostname
, sqlserver.database_name
, sqlserver.query_hash_signed
, sqlserver.server_instance_name
, sqlserver.server_principal_name
, sqlserver.sql_text
, sqlserver.tsql_stack
)
)
, ADD EVENT sqlserver.object_altered
(ACTION
(
sqlserver.client_app_name
, sqlserver.client_hostname
, sqlserver.database_name
, sqlserver.server_instance_name
, sqlserver.server_principal_name
, sqlserver.sql_text
, sqlserver.tsql_stack
)
WHERE ddl_phase = 'Commit'
)
For the demo, Watch Live Data will do. But in your environment, you want to add an event_file target.
Start the event session and open the Live data window. We’ll get to the output later.
Now the environment - table and a child table with a Foreign key and Check constraint.
CREATE DATABASE InConstraintWeTrust
GO
USE InConstraintWeTrust
GO
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)
)
GO
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)
)
GO
INSERT INTO dbo.[Order] DEFAULT VALUES
GO 10
INSERT INTO dbo.OrderItem (OrderId, ProductName, Qty)
VALUES
(1, 'abc', '2')
, (1, 'bcd', '4')
, (2, 'abc', '1')
, (3, 'bcd', '2')
We can check the trustworthiness state with the two queries from earlier.
They both return an empty result set, meaning they are trusted.
ALTER TABLE
Let’s test this scenario.
/* Disable FK constraint */ALTER TABLE dbo.OrderItem NOCHECK CONSTRAINT FK_OrderItem_Order_Parent
/* Do something and enable constraint */ALTER TABLE dbo.OrderItem CHECK CONSTRAINT FK_OrderItem_Order_Parent
GO
/* Disable CK constraint */ALTER TABLE dbo.OrderItem NOCHECK CONSTRAINT CK_OrderItem_PositiveQty
/* Do something and enable constraint */ALTER TABLE dbo.OrderItem CHECK CONSTRAINT CK_OrderItem_PositiveQty
After reenabling the constraints, they are not trusted.
We can make them trusted again with this code. Notice the WITH CHECK.
ALTER TABLE dbo.OrderItem WITH CHECK CHECK CONSTRAINT FK_OrderItem_Order_Parent
ALTER TABLE dbo.OrderItem WITH CHECK CHECK CONSTRAINT CK_OrderItem_PositiveQty
BULK INSERT
For the other two tests, I have created a simple .csv file and saved it at this path
D:OrderItems.csv
,4,abcd,42
,4,asdasd,42
,5,asdasdasd,42
,5,dhdrh,42
,5,dasd,42
,6,fdhg,42
I’ll run the BULK INSERT statement from SSMS. The CHECK CONSTRAINTS hint is commented out on purpose.
BULK INSERT dbo.OrderItem
FROM 'D:OrderItems.csv'
WITH
(
FIRSTROW = 1,
FIELDTERMINATOR = ',', --CSV field delimiter
ROWTERMINATOR = 'n', --Use to shift the control to next row
TABLOCK
--, CHECK_CONSTRAINTS
)
Let’s check the trusted status again.
Is the result the same?
Yes.
Did I reuse the same image?
Also, yes. But who cares?
Reenable the constraints WITH CHECK again for the final test.
bcp Command line
This time I’ll run the utility from the Windows Terminal. Change the parameters accordingly to your DEV environment.
bcp dbo.OrderItem in "D:OrderItems.csv" -S localhost -d InConstraintWeTrust -T -c -t ','
And let’s check the trusted status for the one last time.
Tracking the culprit
Let’s get back to the XE output.
We can see all the events were successfully captured. The green highlighted events are when I restore the trusted status.
To track the source, we can use the client_app_name and client_hostname to narrow down the suspect.
If the process is nested somewhere, we can leverage the tsql_stack and query_hash_signed columns as I blogged here:
I hope this will help you find the runaway process and explain the situation.
Thank you for reading.