It seems that based on your comments you really aren't interested in free help from an online forum. So I will offer my considerable performance tuning talents to you...

chandan_jha18 (8/3/2011)

---

TheSQLGuru (8/3/2011)

---

Jonathan Marshall (8/1/2011)

---

I'm looking to set up a HA solution between databases that are about 1000 miles apart and also the network is less then reliable. Reading through...

Even with a HUGE amount of RAM on the box you have absolutely no hope of good IO from your cheap IO subsystem. I have a 2 year old...

Ninja's_RGR'us (8/3/2011)

---

TheSQLGuru (8/3/2011)

---

It is true that the optimizer and engine CANNOT use parallelism when one of those nasty old scalar UDFs is in play - it is just a limitation...

I will add that you do NOT have 4GB left over for the OS. Those memory settings for each instance are only to limit the BUFFER POOL. SQL...

dbcc memorystatus IS documented online, by Microsoft. It will have LOTS of useful information for you if you are having memory problems. There are also some support KBs...

Check out this blog series for deadlock troubleshooting: http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx

Actually forcing a plan will screw you MUCH more often based simply on the sproc input parameters rather than based on data skew or changes.

Ninja's_RGR'us (8/2/2011)

---

TheSQLGuru (8/2/2011)

---

1) I would shut down sql server and other processes on the box and run chkdisk.exe (you can use -? to get options) to find possibly bad sectors...

Jonathan Marshall (8/1/2011)

---

I'm looking to set up a HA solution between databases that are about 1000 miles apart and also the network is less then reliable. Reading through articles mentions...

Just as a minor point you should never use varchar(1) anyway - it takes up more storage than a char(1), regardless of if they both have a value in them...

It is true that the optimizer and engine CANNOT use parallelism when one of those nasty old scalar UDFs is in play - it is just a limitation of the...

There is a HUGE range of potential causes for the issues you are seeing. Best bet is to get a professional tuner on board to give your systems a...

as2higpark (8/1/2011)

---

To all those who helped, we did lick this one for our affected DB, but with results that we are not super happy about.

We finally moved the DB...

"cheap" and "high performance" just don't go together. I would bet your hardware is not up to the task you are asking either. If other database platforms offer...