So it seems that the default behavior for transaction replication is to mark several things as NOT FOR REPLICATION. This includes foreign key constraints. However, by doing this, the foreign key is marked as is_not_trusted in sys.foreign_keys.

From my understanding, the query optimizer uses foreign keys during generation of query plans which isn't done when the foreign key isn't trusted (assumedly because referential integrity cannot be guaranteed).

I'm trying to get the subscription to not mark the foreign keys as NOT FOR REPLICATION by modifying the schema options that are set during sp_addarticle to not include 0x20000, which does not seem to work. And my search on the internet is not coming up with any solutions, possibly because a search for NOT NOT FOR REPLICATION (or any other negation of the word NOT) doesn't really add anything to the search at all or because nobody out there doing replication thinks this is a good idea.

I'm looking for some input on the subject. Does anybody have either a qualified view point that says that this is a bad idea or an idea of what I may be doing incorrectly? Just in case somebody is interested, the schema option that I am passing in for tables is 0x0000004448DC7FDF which results in the following (un)bitmask used during sp_addarticle:0x00000000000000010x00000000000000020x00000000000000040x00000000000000080x00000000000000100x00000000000000400x00000000000000800x00000000000001000x00000000000002000x00000000000004000x00000000000008000x00000000000010000x00000000000020000x00000000000040000x00000000000400000x00000000000800000x00000000001000000x00000000004000000x00000000008000000x00000000080000000x0000000040000000

Hi I havn't messed around with transactional replication on 08 and was unaware that FK constraints were used by query optimiser, I will look into that. But the easiest way to solve your problem is to drop all the constraints and recreate them, this should not have any affect on the replication, because it is transactional and unidirectional. Just make sure you stop the replication agent prior to the update.

Hello Henners,I was also unaware until recently, and am not sure how much it is used by the optimizer (the only thing I can think of that this would allow would be to determine if a relationship is one to one that a seek could stop after a single record is found). I just found it to be an iteresting fact. It also seems that changing the options on replication doesn't work as advertised because regardless of what I do, I can't turn on the subscriber without the foreign keys getting set to NOT FOR REPLICATION. This is regardless of how replication is set up.

Thanks for your reply. I am thinking that recreating the foreign keys is my only option. It just seems that this should be unnecessary since the foreign keys are valid until replication is activated and then they are all marked as not trusted.

Does anybody have any idea of how how optimization is performed on foreign keys? It seems to me that maybe it doesn't really matter enough to really care about it. If simply replicating a database caused a significant performance hit, that would seem to make replication useless.