Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
1834 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  
 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.03 seconds. Powered By: Snitz Forums 2000