variations

--initially Users contains 300k rows
--deleting 100k from Users still repros, deleting 150k no longer repros
--adding 500k random rows to Users no longer repros
--adding 2k rows to Universities still repros (now a merge join), also with 10k (now a hash join)

--first execute a DML statement, then execute the "reset"


--reset
ALTER INDEX [PK_Universities] ON Universities REBUILD
ALTER INDEX [PK_Users] ON Users REBUILD
ALTER INDEX IX_Users_ImageObjectID ON Users REBUILD
CHECKPOINT
DBCC freeproccache
DBCC DROPCLEANBUFFERS
GO
DBCC checkdb(CheckdbRepro3) WITH no_infomsgs, ALL_ERRORMSGS, TABLERESULTS, extended_logical_checks, data_purity


--create rows in Users
INSERT INTO Users (ID, ImageObjectID)
SELECT TOP 500000 NEWID(), NULL
FROM master.dbo.spt_values sv1
CROSS JOIN master.dbo.spt_values sv2


--delete rows from Users that would not pass the join
DELETE TOP (50000)
FROM Users
WHERE NOT EXISTS (SELECT * FROM Universities WHERE AdminUserID = Users.ID)


--create rows in Universities
INSERT INTO Universities (ID, AdminUserID)
SELECT TOP 1000 ABS(BINARY_CHECKSUM(NEWID())) AS ID, ID AS AdminUserID
FROM Users
ORDER BY NEWID()


--delete rows from Universities to make a NLJ more attractive - now it doesn't repro
DELETE TOP (600)
FROM Universities