I recently read an a blog on MSDN that covered new features for the query optimizer in SQL Server 2016. One of the topics covered was a new operator for foreign key checks.
This new operator certainly looked like it simplified the graphical plan considerably; so, I thought I’d test it out. Here’s what I found:
Rewinding a bit for a simple explanation…
Foreign key constraints are used to validate that data exists in another related table before you add a row with that data to your table. The same goes for removing data. If you attempted to remove a row and the related table still had data the operation would be blocked. This is an over-simplification of the process and there are probably 100 blog posts you could write and only cover half of what constraints are used for. If you’re still not sure and would like to know more you can read about FK constraints here:
https://technet.microsoft.com/en-us/library/ms175464.aspx
Back to the task at hand…
I noticed that some commenters on the blog had posted that the authors example doesn’t work. I copied the example and sure enough. There was no change between 2014 and 2016.
CREATE TABLE Customer(Id INT PRIMARY KEY, CustomerName NVARCHAR(128))
CREATE TABLE ReferenceToCustomer1(CustomerId INT FOREIGN KEY REFERENCES Customer(Id))
CREATE TABLE ReferenceToCustomer2(CustomerId INT FOREIGN KEY REFERENCES Customer(Id))
CREATE TABLE ReferenceToCustomer3(CustomerId INT FOREIGN KEY REFERENCES Customer(Id))
…
DELETE Customer WHERE Id = 1
2014 and 2016 results were the same:
I tested a bit more and then something caught my eye. There was a “…” in the example. I wondered if increasing the number of tables would make a difference.
After reading further I saw that the number of FK references increased in 2016 from 253 to 1000. Could that be the case? Did I need more than 253 references?
I opened Excel and cobbled together a set of create table statements out to 100 tables. Still the same results. 200 tables. Same results. 300 tables. There it was! I then reduced the table count down to 253 tables and noted that the results were back to the original.
It seems that the FK operator only appears when you have 254 or more tables involved in the constraint check operation.
2014 result with 300 tables:
2016 result with 300 tables:
So what is this so called bug?
All that is helpful to understand when we will see this new operator; but, that’s not the bug. So what is?
When I was troubleshooting the plan in an attempt to see this new operator I turned on the STATISTICS IO set option. What I found was really interesting.
Test script (newly created database with 1 table plus 254 join tables)
USE [master]
GO
ALTER DATABASE DB2016 SET COMPATIBILITY_LEVEL = 120
GO
USE DB2016
GO
set statistics time on
set statistics io on
BEGIN TRAN
DELETE Customer WHERE Id = 1
ROLLBACK
set statistics time off
set statistics io off
GO
USE [master]
GO
ALTER DATABASE DB2016 SET COMPATIBILITY_LEVEL = 130
GO
USE DB2016
GO
set statistics time on
set statistics io on
DELETE Customer WHERE Id = 1
set statistics time off
set statistics io off
GO
2014 compatibility Results:
2016 compatibility Results:
As you can see the two sets of results are quite different. When run under SQL Server 2014 compatibility mode the statistics report a single work table and under SQL Server 2016 compatibility mode the statistics report each individual reference table and no work table. The elapsed time is quite a bit higher but I suspected that’s from having to print all the statistics data.
After removing the STATISTICS IO and turning off the actual plan, I ran the test query twenty to twenty five times to see if the trend was still in favor of the 2014 mode. Guess what; it was! Consistently the elapsed time was 1 to 2 ms slower for the SQL 2016 plan.
Running the DELETE statement by itself under each compatibility mode made this even more clear. Under SQL 2014 compatibility the elapsed time averaged less than 1 ms with a maximum of 2 ms. SQL 2016 compatibility mode on the other hand had an average of 5 ms and a maximum of 31 ms. This is with no data!
I leave you with this question. Does this sound like a bug? Hopefully Microsoft will take note and we will find out if this was intended or not.
You can read the entire blog post that I referred to at the beginning of this post here: https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/05/23/query-optimizer-additions-in-sql-server/
Also, be sure to check out my other blog posts here: http://www.sqlservercentral.com/blogs/confessions-of-a-microsoft-addict/
or find the same posts on my blog: http://www.sqltechblog.com
