Investigation into why Query Performance was slower on Merge Subscriber
Are the queries to pull data from the Merge Subscribe taking longer then when executed on the Publisher? Looks like it is time to review the index structure for these tables.
We encountered an unusual problem in SQL 2005/2008 where the indexes didn’t get created on the Subscriber when the Snapshot was pushed by the Merge Agent. Without critical indexes, query optimizer was forced to perform Table Scan. Solution was easier, add the missing indexes. The root cause was a bit tricky.
Turns out the DBA copied a table on the subscriber to a new name “Customers_BACKUP”, but kept the same name for the indexes “PK_Customers”. When the Snapshot was pushed, the new “Customers” table was created, however the “ADD CONSTRAINT” step failed.
On Publisher
———————–
CREATE TABLE [dbo].[Customers](
[CustomerID] [nchar](5) NOT NULL,
[CompanyName] [nvarchar](40) NOT NULL,
[ContactName] [nvarchar](30) NULL,
CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED
(
[CustomerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Backup table on Subscriber
———————————–
CREATE TABLE [dbo].[Customers_BACKUP](
[CustomerID] [nchar](5) NOT NULL,
[CompanyName] [nvarchar](40) NOT NULL,
[ContactName] [nvarchar](30) NULL,
CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED
(
[CustomerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Step which failed
—————————-
ALTER TABLE [dbo].[Customers] ADD CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED
(
[CustomerID] ASC
)
GO
The error appears in the SQL Profile Trace, however, for SQL 2005 and 2008 the Merge agent skipped the error, continued to apply the snapshot, then started replicating changes without logging the index was skipped.
Server: Msg 2714, Level 16, State 4, Line 1
There is already an object named ‘PK_Customers‘ in the database.
Recommendation: For Merge Replication, if you see significant performance difference between queries running on Publisher v. Subscriber(2), check the Indexes.
