RCSI with foreign keys, NULL values and paramater sniffing behavior

In this blog post let’s go back to the roots (DBA concern) with a discussion with one of my friends about a weird transaction locking issue. In fact, this discussion was specifically around two questions. The first one was why SQL Server continues to use shared locks in RCSI mode leading to blocking scenarios and the second one was about compiled objects with weird NULL value parameter sniffing behavior. This discussion was very funny for me because it included very interesting topics that we had to go through to figure out what happened in his case and I think this was enough funny to share it with you.

Let’s set the context: 2 tables (dbo.t1 and dbo.t2) in a parent-child relationship with a foreign key that allows NULL values. Transactions performed against these tables are performed in RCSI mode.

USE master;
GO

CREATE DATABASE test;
GO

-- Change default transaction isolation level to RCSI
ALTER DATABASE test SET READ_COMMITTED_SNAPSHOT ON;
GO

USE test;
GO

CREATE TABLE dbo.t1 
(
	id INT NOT NULL PRIMARY KEY,
	col1 CHAR(2) NOT NULL
);

-- Create table t2 with FK (id_parent) that references primary key on t1 (id)
CREATE TABLE dbo.t2 
(
	id INT NOT NULL PRIMARY KEY,
	id_parent INT NULL FOREIGN KEY REFERENCES dbo.t1 (id),
	col1 CHAR(2) NOT NULL
);
GO

-- Insert values in parent table t1 
INSERT INTO dbo.t1 VALUES (1, 'TT');

Let’s insert 2 rows in the child table dbo.t2 in different scenarios.

The first one concerns insertion to the dbo.t2 table with a non-empty value in the FK column. The second one concerns insertion to the same table and same FK column with an empty / NULL value:

-- Insert values in child table t2 (non NULL value in FK column) 
INSERT INTO dbo.t2 VALUES (1, 1, 'TT');

-- Insert values in child table t2 (non NULL value in FK column) 
INSERT INTO dbo.t2 VALUES (2, NULL, 'TT');

And here their respective execution plans:

• Insert into dbo.t2 with a non-empty value in the FK column

In this first scenario, insert is performed by checking first any existing reference in the parent table (dbo.t1). This action is materialized by the clustered index seek operator and Nested Loop in the execution plan.

• Insert into dbo.t2 with a NULL value in the FK column

In the second scenario, there is no need to check values in the dbo.t1 parent table due to the empty value in the FK column.

In both cases, this is an expected behavior. But let’s now consider locks that are supposed to be taken in this first scenario. 2 different structures must be accessed (and locked) in different modes with an X mode lock to access and update the clustered index of the dbo.t2 table. But what about the dbo.t1 table here? The cluster index structure must be accessed as part of the FK validation. As we are running in RCSI we may suppose in a first place no shared lock (S lock) should be held by the lock manager.  

Let’s configure an extended event to track locks acquired in this specific scenario:

USE test;
GO

SELECT DB_ID('test')
SELECT OBJECT_ID('dbo.t1') -- 1205579333
SELECT OBJECT_ID('dbo.t2') -- 1237579447

DROP EVENT SESSION [locks_check] ON SERVER 

CREATE EVENT SESSION [locks_check] 
ON SERVER 
ADD EVENT sqlserver.lock_acquired
(
	SET collect_resource_description=(1)
    WHERE 
	(
		[package0].[equal_uint64]([database_id],(10)) 
		AND [package0].[not_equal_boolean]([sqlserver].[is_system],(1)) 
		AND ([package0].[greater_than_equal_uint64]([associated_object_id],(1205579333)) 
		     OR [package0].[greater_than_equal_uint64]([associated_object_id],(1237579447))
			) 
		AND [sqlserver].[session_id]=(54)
	)
)
ADD TARGET package0.ring_buffer(SET max_memory=(65536))
WITH 
(
	MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
	MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,
	MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF
)
GO

ALTER EVENT SESSION [locks_check] 
ON SERVER STATE = START;
GO

Here the XE output generated for the first scenario:

;WITH target_data_xml
AS
(
	SELECT 
		CAST(t.target_data AS XML) AS target_data
	FROM sys.dm_xe_session_targets AS t
	JOIN sys.dm_xe_sessions AS s ON t.event_session_address = s.address
	WHERE s.name = 'locks_check'
),
target_data_output
AS
(
	SELECT 
		DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), T.X.value('(./@timestamp)', 'DATETIME')) AS [timestamp],
		T.X.value('(./data[@name="resource_type"]/text)[1]', 'sysname') AS lock_resource_type,
		T.X.value('(./data[@name="mode"]/text)[1]', 'sysname') AS lock_mode,
		T.X.value('(./data[@name="resource_0"]/value)[1]', 'INT') AS resource_0,
		T.X.value('(./data[@name="object_id"]/value)[1]', 'BIGINT') AS [object_id],
		T.X.value('(./data[@name="associated_object_id"]/value)[1]', 'BIGINT') AS [associated_object_id],
		T.X.value('(./data[@name="resource_description"]/value)[1]', 'sysname') AS [resource_description]
	FROM target_data_xml AS x
	CROSS APPLY target_data.nodes('//event') AS T(X)
)
SELECT 
	t.timestamp,
	t.lock_resource_type,
	t.lock_mode,
	CASE t.lock_resource_type
		WHEN 'OBJECT' THEN OBJECT_NAME(t.associated_object_id)
		ELSE (SELECT OBJECT_NAME(p.object_ID) FROM sys.partitions AS p WHERE hobt_id = t.associated_object_id)
	END AS [object_name],
	t.resource_description
FROM target_data_output AS t
GO

Well, not really what we might expect in this case because a shared lock (S) was taken on the parent table (dbo.t1) despite being in RCSI mode. In fact, this behavior is expected because for 2 tables in an FK relationship SQL Server switches automatically to locking read committed (shared lock) to ensure constraints are not violated by relying on eventual stale versioned reads. In other words, you may expect to face some potential blocking issues if there exist other sessions that try to access concurrently to the parent table for modification while running in RCSI mode.

For the second scenario there is no ambiguity because only the clustered index of the dbo.t2 table is accessed to insert data accordingly to what we saw in related execution plan above:

So now let’s go through to the second weird issue (NULL value is not “sniffed” correctly by the stored procedure) and let’s wrap the ad-hoc query into a stored procedure as follows:

CREATE PROCEDURE dbo.pr_test
(
	@id_parent INT = NULL
)
AS

INSERT INTO dbo.t2 VALUES (ABS(CAST(CAST(CHECKSUM(NEWID()) AS bigint) / 1000 % 2100000000 AS int)), @id_parent, 'TT');
GO

Let’s execute the procedure without specifying a parameter. In this case a NULL value will be inserted I the FK column of dbo.t2 table.

EXEC dbo.pr_test;

The corresponding execution plan:

First of all, the plan differs from what we’ve seen in the previous example with the ad-hoc query. The plan was compiled with a NULL value and we still continue to see operators related to the FK constraint check. At the first glance this plan shape was more related to the first scenario where we inserted a non-empty value in the FK column. This is not obvious but we may notice some differences compared to the first scenario.  With SQL Sentry plan explorer (v3 build 18.4.0.0) the relevant information is not displayed when you highlight the nested loop operator compared to SSMS execution plan but you may rely on the plan tree section by the adding Pass Thru column to retrieve the same information.

So, the question here is why SQL Server behaves different in this case? Well, when using a variable or parameter SQL Server needs to build a plan shape that will work correctly if reused for different values. However, the semi join operator has a pass-through predicate that skips the lookup if the runtime value is NULL (we may notice easily with SQL Sentry plan explorer that the lookup part of the plan is not used at the runtime in this case). With a constant NULL value (ad-hoc query scenario) the game changes because the optimizer is able to simplifiy the query and removes the join accordingly. In a nutshell, this an expected behavior by design and really related to a parameter sniffing issue. Thanks to  @SQL_Kiwi to help clarifying this last point and thanks to my friend for this funny troubleshooting game.

See you!

By David Barbarin