Distribution databases need to be administered and maintained like any other database, which includes indexes. Microsoft have used index hints in several stored procedures, which limits us slightly, but these indexes have worked well for me on a number of systems and continue to do so.
Indexes for transactional replication
USEdistribution;
GO
CREATE INDEX [IX_MSdistribution_history_runstatus]
ON [dbo].[MSdistribution_history] ([runstatus])
INCLUDE
(
[agent_id],
[timestamp],
[delivery_latency],
[time],
[xact_seqno]
);
GO
CREATE INDEX [IX_MSsubscriptions_publisher_id_publisher_db_publication_id_sync_type_status_ss_cplt_seqno]
ON [dbo].[MSsubscriptions]
(
[publisher_id],
[publisher_db],
[publication_id],
[sync_type],
[status],
[ss_cplt_seqno]
)
INCLUDE
(
[article_id],
[subscription_time],
[agent_id]
);
GO
CREATE INDEX [IX_MSlogreader_history_runstatus]
ON [dbo].[MSlogreader_history]
(
[runstatus],
[delivered_commands]
)
INCLUDE
(
[agent_id],
[timestamp],
[delivery_latency]
);
GO
CREATE INDEX IX_name_subscriber_id
ON [dbo].[MSdistribution_agents]
(
[name],
[subscriber_id]
)
INCLUDE
(
[id],
[publisher_id],
[publisher_db],
[publication],
[subscriber_db],
[subscription_type],
[local_job],
[job_id],
[profile_id],
[subscriber_name],
[anonymous_agent_id],
[offload_enabled],
[offload_server]
);
GO
CREATE INDEX IX_publisher_id_publisher_db_publication_subscriber_name_creation_date
ON [dbo].[MSdistribution_agents]
(
[publisher_id],
[publisher_db],
[publication],
[subscriber_name],
[creation_date]
)
INCLUDE
(
[id],
[publisher_database_id],
[subscriber_id]
);
GO
CREATE NONCLUSTERED INDEX [IX_agent_id_runstatus_xact_seqno_Included]
ON [dbo].[MSlogreader_history]
(
[agent_id],
[runstatus],
[xact_seqno]
)
INCLUDE
(
[time],
[delivery_latency]
);
GO
CREATE NONCLUSTERED INDEX [IX_agent_id_time_Included]
ON [dbo].[MSsnapshot_history]
(
[agent_id],
[time]
)
INCLUDE
(
[start_time],
[timestamp]
);
CREATE INDEX [IX_sysarticles_objid_type]
ON [dbo].[sysarticles]
(
[objid],
[type]
);
GO
CREATE INDEX [IX_sysarticles_pubid]
ON [dbo].[sysarticles] ([pubid])
INCLUDE
(
[artid],
[creation_script],
[name],
[objid],
[pre_creation_cmd],
[type],
[schema_option]
);
GO
CREATE NONCLUSTERED INDEX [IX_article_id_INC] ON [dbo].[MSrepl_commands] (
[article_id]
) INCLUDE (
[xact_seqno]
) WITH (PAD_INDEX=OFF, ALLOW_ROW_LOCKS=ON, ALLOW_PAGE_LOCKS=ON, IGNORE_DUP_KEY=OFF);
One thought on “Indexes for SQL Server replication”