Home > Performence Tuning, Replication > Handle performance issue if SQL server 2008 database is involved in Marge Replication

Handle performance issue if SQL server 2008 database is involved in Marge Replication


If database is involved in replication then we have to troubleshoot the performance issue in a little bit different way. We can improve the Merge queries and it will improve the overall system performance for synchronizing data. Identify high IO or high CPU SPs then explore how to tune. Here I have posted the article along with query which will be definitely help out to tune the query

Use Replication Monitor to get overall view of Replication performance. Add –OUTPUT to Merge Agent to identify which step (stored procedure) in the Merge processes is failing or taking longest time.

ReplMerge.log

Merge Replication automatically generates ReplMerge.log file which is ON by default. The minimal default logging may provide enough detail to resolve issue without needing to collect verbose log or Profiler trace.

C:\Program Files\Microsoft SQL Server\90\COM\replmerge.log

sys.dm_exec_query_stats can be used to examine query plans for the Merge Replication created SPs used to evaluate Publisher updates and to move data throughout the Merge topology. Looks for high CPU and high IO queries.

–Find TOP Top LogicalReaders for Merge Replication

–Examine Query Plans for SCANS

–Update STATS and/or REINDEX to see if better query plan is generated

SELECT TOP 25

st.text, qp.query_plan,

(qs.total_logical_reads/qs.execution_count) as avg_logical_reads,

(qs.total_logical_writes/qs.execution_count) as avg_logical_writes,

(qs.total_physical_reads/qs.execution_count) as avg_phys_reads,

qs.*

FROM sys.dm_exec_query_stats as qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) as qp

WHERE st.text like ‘%MSmerge%’

– WHERE st.text like ‘%sp_mergemetadataretentioncleanup%’

– WHERE st.text like ‘%sp_MSenumgenerations90%’

– WHERE st.text like ‘%sp_MSmakegeneration%’

ORDER BY qs.total_logical_reads DESC

Need to review the below system tables:

Often unexpected Synchronization performance can be contributed to unexpected high data volume. After all Merge is just queries against SQL table in order to determine which data to upload/download. Examine Merge system tables for counts 10 million (for example).

SELECT COUNT(*) FROM MSmerge_contents (nolock)

SELECT COUNT(*) FROM MSmerge_tombstone (nolock)

SELECT COUNT(*) FROM MSmerge_genhistory (nolock)

SELECT COUNT(*) FROM MSmerge_partition_groups (nolock)

SELECT COUNT(*) FROM MSmerge_current_partition_mappings (nolock)

SELECT COUNT(*) FROM MSmerge_past_partition_mappings (nolock)

Note: Try updating STATS or REINDEX on key Merge system tables then see if a better query plan (lower IO and CPU) is generated by Query Optimizer.

Replication History Tables

High sync duration could be result of unexpected high data volume. Use query below to see which date had mass amount of changes.

SELECT COUNT(*) ‘generation count’, sum(changecount) ‘change count’,

datepart(dd,coldate) ‘day of month’, datepart(hh,coldate) ‘hour of day’

FROM MSmerge_genhistory (nolock)

group by datepart(dd,coldate), datepart(hh,coldate)

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: