Why is query performance of Replication Subscriber slower than Publisher?


 


Time to compare indexes on Publisher vs. Subscriber.


 


We encounter 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 customer made a 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.  We’re still looking into the “why” part.


 


Server: Msg 2714, Level 16, State 4, Line 1


There is already an object named ‘PK_Customers’ in the database.


 


So, not getting the performance your expecting when running queries on your subscriber?  Time to check for missing indexes.


 


Thanks,


Chris Skorlinski


Microsoft SQL Server Escalation Services


Comments (0)

Skip to main content