Foreign key constraints put the “relation” into relational databases. They enforce integrity by requiring that a key value referencing another table have an actual correspondence in the referenced table – in other words, that the key value actually exists. They guard against orphaned records by not allowing the referenced record to be deleted unless the referencing record is deleted first, or the CASCADE DELETE attribute is set to ON. They provide useful information on join columns to the query optimizer, as well, and are vital to creating database diagrams. Very useful constructs, all in all!

But there’s no such thing as a free lunch, as we all know, and it often happens that developers and DBA’s fail to recognize the price of a foreign key constraint, often with profound consequences to query performance.

For every record inserted into a child table (or a sibling, but I’ll concentrate on the parent-child relationship to keep things simple) with a foreign key constraint on the parent’s key, SQL Server will add an ASSERT operation to the execution plan, and will perform a lookup operation via the B+-tree index on that key in the parent. And therein lies the tale…

To qualify for a foreign key constraint, the referenced column must be the lead (or only) key column in a unique index. This may take the form of a unique clustered index or primary key, a nonclustered primary key, a unique constraint, or a unique index (full or filtered). SQL Server is well-programmed enough to try and choose the narrowest index available to fill the role, but often the only choice is a clustered primary key – which poses potential problems.

We have a table – I’ll call it “X” – that has seventeen (17) child tables. The table is clustered on a field imaginatively named X_ID, which is an IDENTITY and the primary key of the table, and thus the foreign key in each of the child tables.

X is quite a wide table: each row can consume up to just under 2400 bytes. At that level, there is room enough on a page for three records. As there are over 60 million rows, that’s quite a lot of pages. More pages mean more intermediary rows in the B+-tree between the leaf and the root, which means more logical reads are required to find the row containing a particular value: at present, four reads. So for every row inserted in a child table, there will be an ASSERT operation that will require four logical reads. Since we insert around a million such rows every day, you can see how the reads add up.

This is the price that was not taken into account when X and its children were being created. It is never an easy thing to project one’s imagination into the future, envisioning how a table and its related objects will grow, or to what volumes they will rise. Certainly whoever first designed the architecture was not considering the impact of foreign key checks when the foreign keys were created. I’m not here to judge: I’ve made many such design oversights myself: but I am here to tell you what I did to rectify the situation.

First, I dropped every foreign key constraint against X, from all its children (and a few siblings as well). Then I dropped the primary key constraint, created a unique clustered index on X_ID, then re-created the primary key constraint on X_ID, but this time as nonclustered. Finally, I recreated all the foreign key constraints in the child tables.

I then performed a few test inserts to a handful of the children, and examined the resulting execution plans. Sure enough, the ASSERT now uses the nonclustered PK as its source, resulting in three logical reads, versus the four required by the clustered PK. One less logical read doesn’t sound like much in isolation, but this happens around a million times each day, and that million logical reads we’ve saved looks a lot better at scale.

I’ve now added to our standards a requirement that parent tables be indexed in this manner, along with the explanation I’ve outlined here, and I’ll be going through the remainder of our OLTP databases looking for similar scenarios (there are quite a few, though many will be resolved in the course of a separate re-indexing project). After all, a logical read saved, is a logical read earned!