When you add a primary key constraint to a table in SQL Server, an index will be created automatically. When you add a foreign key constraint no index will be created. This might cause issues if you don't know that this is the behavior in SQL Server. Maybe there should be an option to automatically index the foreign keys in SQL Server, what do you think?
The other day some deletes on a newer table in the test environment became really slow. We had a primary table with a couple of hundred rows, we loaded up between 200 and 300 million rows into the child table. Then we deleted the child rows, this was fast. After this, we deleted one row from the primary table and this took several seconds.
When I looked at this I noticed something interesting, the most time during the delete was spent doing a lookup at the child table. Then I noticed that the foreign key was not indexed. After we added the index the delete became thousands of times faster
Let's try to replicate that behavior here
First create these primary table, we will add 2048 rows to this table
Now create the child table and add 500K rows, this might take up to 1 minute to run since the rows are pretty wide.
Now it is time to add the foreign key constraint and index this foreign key constraint
We will create another set of tables, let's start with the primary table, we will just insert into this table all the rows from the primary table we created earlier
For the child table, it is the same deal, we will add all the rows from the child table we created earlier into this table
Let's add the foreign key constraint, but this time we are not indexing the foreign key constraint
Let make sure that the tables have the same number of rows
|GoodsSold||500000||4024976 KB||4000000 KB||22520 KB||2456 KB|
|GoodsSoldFKNoIndex||500000||4015440 KB||4000000 KB||14936 KB||504 KB|
Now that we are setup, let's wipe out all the rows from the child table for a specific StoreID, the SELECT statements should return 0 rows
Now we are getting to the interesting part, turn on Include Actual Execution Plan, run statistics IO or run this in Plan Explorer
You will see something like this
SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 16 ms.
SQL Server Execution Times:
CPU time = 561 ms, elapsed time = 575 ms.
Now it looks much worse
What about statistics io?
Table 'GoodsSold'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Store'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'GoodsSoldFKNoIndex'. Scan count 1, logical reads 501373, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'StoreFK'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
That is terrible....
Here is also the view from Plan Explorer, look at Est. CPU Cost and Reads
There you have it, not indexing foreign keys can have a big impact even though the child table might not have any data at all
See also When did SQL Server stop putting indexes on Foreign Key columns? by Kimberly Tripp