SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 MSMERGE tables on a different FILEGROUP
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jjradha
Starting Member

United Kingdom
5 Posts

Posted - 11/12/2012 :  09:42:53  Show Profile  Reply with Quote

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

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1754 Posts

Posted - 11/12/2012 :  15:11:05  Show Profile  Reply with Quote
If your replication activity is your bottleneck, have you considered putting the distributor on a separate SQL Server?

=================================================
We are far more concerned about the desecration of the flag than we are about the desecration of our land. -Wendell Berry
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000