September 11, 2008 at 12:22 pm
DavidB (9/11/2008)
Richard - If you can really split easily then that is going to be your quickest gain. Additionally, you will immediately split the IO which again is a bonus. The scenario you described is possible especially if you can guarantee that they are going to go back to the same server then that makes it even easier.So, start planning if that is the route that you have to go.
I stated that I would provide a link to a tuning guide earlier. You can find it here - http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx
Just a thought, you might want to contact MS. The support cost is minimal and they will be able to analyze log and trace information for you to provide you some recommendations. The cost is pretty cheap when you consider it.
Ultimately at this point it seems like you are pretty focused on going the scale out road which is fine, just make sure that you do the tuning as well. That 30 - 40% that you are adding next week will be another 30 - 40% in another couple of months and will throttle both servers if you don't get things tuned up. Know what I mean?
I know that there are some on this site that consult so, that may be an option as well. Just a thought. It's a lot easier to help when eyes are on things in situations like yours.
David - thank you for confirming my thinking. It's nice to know that I am not barking up the wrong tree. The link to the tuning guide is also useful. I will indeed be tuning also.
Unfortunately they've already had three consultants in previously and so recommending another is not going to work unless I can get this quick win done.
September 11, 2008 at 10:44 pm
One thing I haven't seen mentioned is to ensure that your indexes are not fragmented and your stats are up to date ... rebuild at least the stats nightly if you have downtime. At least you will limit the chances of worsening your problem due to bad plans.
If your app uses tempdb heavily, getting a ram drive might help.
If a significant load on the app is due to reporting you could possibly offload that to the other server, or even use a snapshot.
You could also consider read committed snapshot isolation, but you probably need to do further analysis before you can determine whether that will help or hinder.
If you are seeing a large number of adhoc queries, forced parameterisation *might* help.
September 12, 2008 at 4:14 am
Matt, thanks for that. Those options are part of my ammunition toolbox now!
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply