Adding FK Index to existing table in Merge Replication Topology

Question

I have a table that has grown quite large that we are replicating to about 120 subscribers. A FK on that table does not have an index and when I ran an Execution Plan on a query that was causing issues it had this to say -->

/*
Missing Index Details from CaseNotesTimeoutQuerys.sql - mylocal\sqlexpress.MATRIX (WWCARES\pschaller (54))
The Query Processor estimates that implementing the following index could improve the query cost by 99.5556%.
*/

/*
USE [MATRIX]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[tblCaseNotes] ([PersonID])

GO
*/

I would like to add this but I am afraid it will FORCE a reinitialization. Can anyone verify or validate my concerns? Does it even work that way or would I need to run the script on each subscriber?

Any insight would be appreciated.

@sims: Done and Thanks. First response SF = 15 hours. Not bad. First response SO = 5 MINUTES, sweet! http://stackoverflow.com/questions/2965910/adding-fk-index-to-existing-table-in-merge-replication-topology — Refracted Paladin, Jun 03 '10 at 13:03

score 1 · Accepted Answer · answered Jun 03 '10 at 06:50

1

you should be able to add the index to the publisher & subscriber without it forcing a re-initialization

answered Jun 03 '10 at 06:50

Nick Kavadias

10,758
7
36
47

Will I need to add it, manually with that script, to each subscriber? It will not propagate down, correct? – Refracted Paladin Jun 03 '10 at 12:34
yes, you need to re-initialize with a snapshot & specify to copy non-clustered indexes, or apply it manually a the subscriber – Nick Kavadias Jun 04 '10 at 04:47

Adding FK Index to existing table in Merge Replication Topology

1 Answers1