The query processor ran out of stack space during query optimization. For example, I get the following error when attempting to delete from a parent table with about 2000 incoming references: It’s certainly possible to get into trouble with too many incoming references.
#REFERENTIAL INTEGRITY UPDATE#
On SQL Server 2016 or later with compatibility level 120, it’s possible to create a query plan for a delete or update against a table with more than 253 incoming references. So did generating a plan to delete a row from the parent table, which is a bit surprising. Now I try to create one more incoming reference: CREATE TABLE dbo.JUST_ONE_MORE_FK ( After creating the tables, the next step is to set the compatibility level to 120: ALTER DATABASE tempdb SET COMPATIBILITY_LEVEL = 120
#REFERENTIAL INTEGRITY CODE#
The code to create those tables is too tedious and long for this blog post, and that’s saying something. Running Out Of Stack Spaceįirst I’ll create tables similar to before, but CHILD_TABLE_BIG will have 253 references to FK_PARENT_TABLE. Perhaps this was the only practical way to increase the number of incoming references. It’s unfortunate that the referential integrity operator is an “all-in-one” operator. That function was implemented with a combination of existing operators. For example, when STRING_AGG() was released in SQL Server 2017 it wasn’t necessary for Microsoft to create a new query plan operator. Many of SQL Server’s query plan operators are designed to be small in scope and reusable for lots of different purposes. This results in much lower compilation times for such plans and comparable execution times. The new query execution operator does the referential integrity checks in place, by comparing the modified row to the rows in the referencing tables, to verify that the modification will not break the referential integrity. SQL Server 2016 introduces a new Referential Integrity Operator (under compatibility level 130) that increases the limit on the number of other tables with foreign key references to a primary or unique key of a given table (incoming references), from 253 to 10,000. From a blog post by Gjorgji Gjeorgjievski: All of the joins are collapsed into a single operator which can reduce compile time and avoid errors.
The referential integrity operator introduced with compatibility level 130 raises the limit from 253 to 10000.
That restriction won’t be hit often but could be pretty inconvenient to work around. That’s why I assume a table is limited to 253 incoming foreign key references in SQL Server 2014. For example, I created a simple query with 2500 joins and it still hadn’t finished compiling after 15 minutes. That query plan could take a long time to compile or could even time out. Creating a query plan for that statement is equivalent to creating a query plan for a query containing hundreds or even thousands of joins. What would happen if a parent table was referenced by hundreds of child tables, such as for a date dimension table? Deleting or updating a row in the parent table would create a query plan with at least one join per incoming foreign key reference. CHILD_TABLE_BIG is currently empty so this isn’t a problem. The query optimizer does a table scan for each referencing row. In this example, we have two unindexed columns in the child table. The check can be seen in the execution plan for the delete query: What happens if we need to delete a row from the parent table? SQL Server has to check that none of the children table values match the value we want to delete. Let’s start with a simple parent table and a simple child table that has two columns which both reference the parent table: DROP TABLE IF EXISTS dbo.FK_PARENT_TABLE ĭROP TABLE IF EXISTS dbo.CHILD_TABLE_BIG ĬONSTRAINT FK_CHILD_TABLE_BIG_1 FOREIGN KEY (FKey1)ĬONSTRAINT FK_CHILD_TABLE_BIG_2 FOREIGN KEY (FKey2) It’s described as the referential integrity operator in most of the docs so I’ll also describe it that way here for the rest of the post. I’m not a foreign key guy, but a new operator is interesting enough for me to poke around. It doesn’t even show up in the list of operators: SQL Server 2016 introduced a new query plan operator that mostly flew under the radar: foreign key references check.
0 kommentar(er)
