{"id":12344,"date":"2019-04-04T13:58:48","date_gmt":"2019-04-04T11:58:48","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/rcsi-with-foreign-keys-null-values-and-paramater-sniffing-behavior\/"},"modified":"2019-04-04T13:58:48","modified_gmt":"2019-04-04T11:58:48","slug":"rcsi-with-foreign-keys-null-values-and-paramater-sniffing-behavior","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/rcsi-with-foreign-keys-null-values-and-paramater-sniffing-behavior\/","title":{"rendered":"RCSI with foreign keys, NULL values and paramater sniffing behavior"},"content":{"rendered":"<p><span lang=\"EN-US\"><span style=\"color: #000000;font-family: Calibri\">In this blog post let\u2019s 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.<\/span><\/span><\/p>\n<p><span lang=\"EN-US\"><span style=\"color: #000000;font-family: Calibri\">Let\u2019s 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. <\/span><\/span><\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">USE master;\nGO\n\nCREATE DATABASE test;\nGO\n\n-- Change default transaction isolation level to RCSI\nALTER DATABASE test SET READ_COMMITTED_SNAPSHOT ON;\nGO\n\nUSE test;\nGO\n\nCREATE TABLE dbo.t1 \n(\n\tid INT NOT NULL PRIMARY KEY,\n\tcol1 CHAR(2) NOT NULL\n);\n\n-- Create table t2 with FK (id_parent) that references primary key on t1 (id)\nCREATE TABLE dbo.t2 \n(\n\tid INT NOT NULL PRIMARY KEY,\n\tid_parent INT NULL FOREIGN KEY REFERENCES dbo.t1 (id),\n\tcol1 CHAR(2) NOT NULL\n);\nGO\n\n-- Insert values in parent table t1 \nINSERT INTO dbo.t1 VALUES (1, 'TT');<\/pre>\n<p>&nbsp;<\/p>\n<p><span lang=\"EN-US\"><span style=\"color: #000000;font-family: Calibri\">Let\u2019s insert 2 rows in the child table dbo.t2 in different scenarios.<\/span><\/span><\/p>\n<p><span lang=\"EN-US\"><span style=\"color: #000000;font-family: Calibri\">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:<\/span><\/span><\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">-- Insert values in child table t2 (non NULL value in FK column) \nINSERT INTO dbo.t2 VALUES (1, 1, 'TT');\n\n-- Insert values in child table t2 (non NULL value in FK column) \nINSERT INTO dbo.t2 VALUES (2, NULL, 'TT');<\/pre>\n<p>&nbsp;<\/p>\n<p><span lang=\"EN-US\"><span style=\"color: #000000;font-family: Calibri\">And here their respective execution plans:<\/span><\/span><\/p>\n<ul>\n<li><span style=\"color: #000000\"><span lang=\"EN-US\"><span style=\"font-family: Calibri\">Insert into dbo.t2 with a non-empty value in the FK column <\/span><\/span><\/span><\/li>\n<\/ul>\n<p><a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/154-1-insert-fk-non-null-exec-plan.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-31843\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/154-1-insert-fk-non-null-exec-plan.jpg\" alt=\"\" width=\"465\" height=\"229\" \/><\/a><\/p>\n<p><span lang=\"EN-US\"><span style=\"color: #000000;font-family: Calibri\">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. <\/span><\/span><\/p>\n<ul>\n<li><span style=\"color: #000000\"><span lang=\"EN-US\"><span style=\"font-family: Calibri\">Insert into dbo.t2 with a NULL value in the FK column <\/span><\/span><\/span><\/li>\n<\/ul>\n<p><a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/154-2-insert-fk-null-exec-plan.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-31844\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/154-2-insert-fk-null-exec-plan.jpg\" alt=\"\" width=\"284\" height=\"117\" \/><\/a><\/p>\n<p><span lang=\"EN-US\"><span style=\"color: #000000;font-family: Calibri\">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.<\/span><\/span><\/p>\n<p><span lang=\"EN-US\"><span style=\"font-family: Calibri\"><span style=\"color: #000000\">In both cases, this is an expected behavior. But let\u2019s 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. \u00a0<\/span><\/span><\/span><\/p>\n<p><span lang=\"EN-US\"><span style=\"color: #000000;font-family: Calibri\">Let\u2019s configure an extended event to track locks acquired in this specific scenario:<\/span><\/span><\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">USE test;\nGO\n\nSELECT DB_ID('test')\nSELECT OBJECT_ID('dbo.t1') -- 1205579333\nSELECT OBJECT_ID('dbo.t2') -- 1237579447\n\nDROP EVENT SESSION [locks_check] ON SERVER \n\nCREATE EVENT SESSION [locks_check] \nON SERVER \nADD EVENT sqlserver.lock_acquired\n(\n\tSET collect_resource_description=(1)\n    WHERE \n\t(\n\t\t[package0].[equal_uint64]([database_id],(10)) \n\t\tAND [package0].[not_equal_boolean]([sqlserver].[is_system],(1)) \n\t\tAND ([package0].[greater_than_equal_uint64]([associated_object_id],(1205579333)) \n\t\t     OR [package0].[greater_than_equal_uint64]([associated_object_id],(1237579447))\n\t\t\t) \n\t\tAND [sqlserver].[session_id]=(54)\n\t)\n)\nADD TARGET package0.ring_buffer(SET max_memory=(65536))\nWITH \n(\n\tMAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,\n\tMAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,\n\tMEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF\n)\nGO\n\nALTER EVENT SESSION [locks_check] \nON SERVER STATE = START;\nGO<\/pre>\n<p>&nbsp;<\/p>\n<p><span lang=\"EN-US\"><span style=\"color: #000000;font-family: Calibri\">Here the XE output generated for the first scenario:<\/span><\/span><\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">;WITH target_data_xml\nAS\n(\n\tSELECT \n\t\tCAST(t.target_data AS XML) AS target_data\n\tFROM sys.dm_xe_session_targets AS t\n\tJOIN sys.dm_xe_sessions AS s ON t.event_session_address = s.address\n\tWHERE s.name = 'locks_check'\n),\ntarget_data_output\nAS\n(\n\tSELECT \n\t\tDATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), T.X.value('(.\/@timestamp)', 'DATETIME')) AS [timestamp],\n\t\tT.X.value('(.\/data[@name=\"resource_type\"]\/text)[1]', 'sysname') AS lock_resource_type,\n\t\tT.X.value('(.\/data[@name=\"mode\"]\/text)[1]', 'sysname') AS lock_mode,\n\t\tT.X.value('(.\/data[@name=\"resource_0\"]\/value)[1]', 'INT') AS resource_0,\n\t\tT.X.value('(.\/data[@name=\"object_id\"]\/value)[1]', 'BIGINT') AS [object_id],\n\t\tT.X.value('(.\/data[@name=\"associated_object_id\"]\/value)[1]', 'BIGINT') AS [associated_object_id],\n\t\tT.X.value('(.\/data[@name=\"resource_description\"]\/value)[1]', 'sysname') AS [resource_description]\n\tFROM target_data_xml AS x\n\tCROSS APPLY target_data.nodes('\/\/event') AS T(X)\n)\nSELECT \n\tt.timestamp,\n\tt.lock_resource_type,\n\tt.lock_mode,\n\tCASE t.lock_resource_type\n\t\tWHEN 'OBJECT' THEN OBJECT_NAME(t.associated_object_id)\n\t\tELSE (SELECT OBJECT_NAME(p.object_ID) FROM sys.partitions AS p WHERE hobt_id = t.associated_object_id)\n\tEND AS [object_name],\n\tt.resource_description\nFROM target_data_output AS t\nGO<\/pre>\n<p>&nbsp;<\/p>\n<p><a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/154-3-insert-fk-non-null-locks.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-31845\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/154-3-insert-fk-non-null-locks.jpg\" alt=\"\" width=\"675\" height=\"201\" \/><\/a><\/p>\n<p>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. <strong>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.<\/strong><\/p>\n<p>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:<\/p>\n<p><a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/154-3-insert-fk-non-null-locks-1.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-31846\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/154-3-insert-fk-non-null-locks-1.jpg\" alt=\"\" width=\"675\" height=\"201\" \/><\/a><\/p>\n<p><span lang=\"EN-US\"><span style=\"color: #000000;font-family: Calibri\">So now let\u2019s go through to the second weird issue (NULL value is not \u201csniffed\u201d correctly by the stored procedure) and let\u2019s wrap the ad-hoc query into a stored procedure as follows:<\/span><\/span><\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">CREATE PROCEDURE dbo.pr_test\n(\n\t@id_parent INT = NULL\n)\nAS\n\nINSERT INTO dbo.t2 VALUES (ABS(CAST(CAST(CHECKSUM(NEWID()) AS bigint) \/ 1000 % 2100000000 AS int)), @id_parent, 'TT');\nGO<\/pre>\n<p>&nbsp;<\/p>\n<p><span lang=\"EN-US\"><span style=\"color: #000000;font-family: Calibri\">Let\u2019s execute the procedure without specifying a parameter. In this case a NULL value will be inserted I the FK column of dbo.t2 table.<\/span><\/span><\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">EXEC dbo.pr_test;<\/pre>\n<p>&nbsp;<\/p>\n<p><span lang=\"EN-US\"><span style=\"color: #000000;font-family: Calibri\">The corresponding execution plan:<\/span><\/span><\/p>\n<p><a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/154-5-insert-fk-null-sp-exec-plan-e1554346879476.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-31847\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/154-5-insert-fk-null-sp-exec-plan-e1554346879476.jpg\" alt=\"\" width=\"900\" height=\"514\" \/><\/a><\/p>\n<p>First of all, the plan differs from what we\u2019ve 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. \u00a0With 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.<\/p>\n<p>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. <b>In a nutshell, this an expected behavior by design and really related to a parameter sniffing issue.<\/b> Thanks to \u00a0<a href=\"https:\/\/twitter.com\/SQL_Kiwi\">@SQL_Kiwi<\/a> to help clarifying this last point and thanks to my friend for this funny troubleshooting game.<\/p>\n<p>See you!<\/p>\n<p><span style=\"float: none;background-color: #ffffff;color: #333333;cursor: text;font-family: Georgia,'Times New Roman','Bitstream Charter',Times,serif;font-size: 16px;font-style: normal;font-variant: normal;font-weight: 400;letter-spacing: normal;text-align: left;text-decoration: none;text-indent: 0px;text-transform: none\">By David Barbarin<\/span><\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In this blog post let\u2019s 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 [&hellip;]<\/p>\n","protected":false},"author":26,"featured_media":12345,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[368,99],"tags":[1599,348,643,741,67,1600,746],"type_dbi":[],"class_list":["post-12344","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-development-performance","category-sql-server","tag-blocking","tag-execution-plan","tag-locks","tag-parameter-sniffing","tag-performance","tag-rcsi","tag-transaction-isolation-level"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.2) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>RCSI with foreign keys, NULL values and paramater sniffing behavior - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/rcsi-with-foreign-keys-null-values-and-paramater-sniffing-behavior\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"RCSI with foreign keys, NULL values and paramater sniffing behavior\" \/>\n<meta property=\"og:description\" content=\"In this blog post let\u2019s 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 [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/rcsi-with-foreign-keys-null-values-and-paramater-sniffing-behavior\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2019-04-04T11:58:48+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/154-1-insert-fk-non-null-exec-plan.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"465\" \/>\n\t<meta property=\"og:image:height\" content=\"229\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Microsoft Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Microsoft Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"7 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/rcsi-with-foreign-keys-null-values-and-paramater-sniffing-behavior\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/rcsi-with-foreign-keys-null-values-and-paramater-sniffing-behavior\/\"},\"author\":{\"name\":\"Microsoft Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4\"},\"headline\":\"RCSI with foreign keys, NULL values and paramater sniffing behavior\",\"datePublished\":\"2019-04-04T11:58:48+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/rcsi-with-foreign-keys-null-values-and-paramater-sniffing-behavior\/\"},\"wordCount\":887,\"commentCount\":0,\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/rcsi-with-foreign-keys-null-values-and-paramater-sniffing-behavior\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/154-1-insert-fk-non-null-exec-plan.jpg\",\"keywords\":[\"blocking\",\"Execution plan\",\"Locks\",\"parameter sniffing\",\"Performance\",\"RCSI\",\"transaction isolation level\"],\"articleSection\":[\"Development &amp; Performance\",\"SQL Server\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/rcsi-with-foreign-keys-null-values-and-paramater-sniffing-behavior\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/rcsi-with-foreign-keys-null-values-and-paramater-sniffing-behavior\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/rcsi-with-foreign-keys-null-values-and-paramater-sniffing-behavior\/\",\"name\":\"RCSI with foreign keys, NULL values and paramater sniffing behavior - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/rcsi-with-foreign-keys-null-values-and-paramater-sniffing-behavior\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/rcsi-with-foreign-keys-null-values-and-paramater-sniffing-behavior\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/154-1-insert-fk-non-null-exec-plan.jpg\",\"datePublished\":\"2019-04-04T11:58:48+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/rcsi-with-foreign-keys-null-values-and-paramater-sniffing-behavior\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/rcsi-with-foreign-keys-null-values-and-paramater-sniffing-behavior\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/rcsi-with-foreign-keys-null-values-and-paramater-sniffing-behavior\/#primaryimage\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/154-1-insert-fk-non-null-exec-plan.jpg\",\"contentUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/154-1-insert-fk-non-null-exec-plan.jpg\",\"width\":465,\"height\":229},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/rcsi-with-foreign-keys-null-values-and-paramater-sniffing-behavior\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"RCSI with foreign keys, NULL values and paramater sniffing behavior\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4\",\"name\":\"Microsoft Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g\",\"caption\":\"Microsoft Team\"},\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/microsoft-team\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"RCSI with foreign keys, NULL values and paramater sniffing behavior - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/rcsi-with-foreign-keys-null-values-and-paramater-sniffing-behavior\/","og_locale":"en_US","og_type":"article","og_title":"RCSI with foreign keys, NULL values and paramater sniffing behavior","og_description":"In this blog post let\u2019s 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 [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/rcsi-with-foreign-keys-null-values-and-paramater-sniffing-behavior\/","og_site_name":"dbi Blog","article_published_time":"2019-04-04T11:58:48+00:00","og_image":[{"width":465,"height":229,"url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/154-1-insert-fk-non-null-exec-plan.jpg","type":"image\/jpeg"}],"author":"Microsoft Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Microsoft Team","Est. reading time":"7 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/rcsi-with-foreign-keys-null-values-and-paramater-sniffing-behavior\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/rcsi-with-foreign-keys-null-values-and-paramater-sniffing-behavior\/"},"author":{"name":"Microsoft Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4"},"headline":"RCSI with foreign keys, NULL values and paramater sniffing behavior","datePublished":"2019-04-04T11:58:48+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/rcsi-with-foreign-keys-null-values-and-paramater-sniffing-behavior\/"},"wordCount":887,"commentCount":0,"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/rcsi-with-foreign-keys-null-values-and-paramater-sniffing-behavior\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/154-1-insert-fk-non-null-exec-plan.jpg","keywords":["blocking","Execution plan","Locks","parameter sniffing","Performance","RCSI","transaction isolation level"],"articleSection":["Development &amp; Performance","SQL Server"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/rcsi-with-foreign-keys-null-values-and-paramater-sniffing-behavior\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/rcsi-with-foreign-keys-null-values-and-paramater-sniffing-behavior\/","url":"https:\/\/www.dbi-services.com\/blog\/rcsi-with-foreign-keys-null-values-and-paramater-sniffing-behavior\/","name":"RCSI with foreign keys, NULL values and paramater sniffing behavior - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/rcsi-with-foreign-keys-null-values-and-paramater-sniffing-behavior\/#primaryimage"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/rcsi-with-foreign-keys-null-values-and-paramater-sniffing-behavior\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/154-1-insert-fk-non-null-exec-plan.jpg","datePublished":"2019-04-04T11:58:48+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/rcsi-with-foreign-keys-null-values-and-paramater-sniffing-behavior\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/rcsi-with-foreign-keys-null-values-and-paramater-sniffing-behavior\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/rcsi-with-foreign-keys-null-values-and-paramater-sniffing-behavior\/#primaryimage","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/154-1-insert-fk-non-null-exec-plan.jpg","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/154-1-insert-fk-non-null-exec-plan.jpg","width":465,"height":229},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/rcsi-with-foreign-keys-null-values-and-paramater-sniffing-behavior\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"RCSI with foreign keys, NULL values and paramater sniffing behavior"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4","name":"Microsoft Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g","caption":"Microsoft Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/microsoft-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/12344","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/26"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=12344"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/12344\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media\/12345"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=12344"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=12344"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=12344"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=12344"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}