Hi all,
I have a 350+ GB database that is replicated using MERGE replication to over 350 sites accross the country (UK).
The MSMerge tables are constantly involved in any sort of operation and as a result of this, I have lots of WAITS suggesting a IO bottleneck, (also locking and blocking).
example of waits: PAGEIOLATCH_SH,PAGEIOLATCH_EX, LCK_M_S, LCK_M_X to mention a few.
example of Avg.Disk queu Length: 100, but it can be 165, and picks to 1000 sometimes on the D: drive only (DATA).
I have only one filegroup (D: drive)
I have 16 disks.
I was considering adding a filegroup, put the file on the Backup drive (only used at night) R: drive.
and put there all the INDEXES of the MSMERGE tables, or the MSMERGE tables themselves.
I wonder if anyone has had this sort of problem and how it has been dealt with.
regards
marcelo