Maxtransfer Size ( SQL script ) - 6 ( 4 MB) and Communication buffer size in client properties is 128 KB.
Both the value is set for same client in SQL script and client properties.
Is this two parameter is interlinked. If so which will take precedence.
My understanding is that the Maxtransfer size is what the SQL database reads/writes with on the disk(s) and the Communication buffer size is for the network communication from the client to the media server
Hi Micheal,
Thanks for thr update. If we keep the Communication buffer size also same as Maxtransfer Size will the performance would be good.
Hi Mansoor - there are too many other variables in any infrastructure to be able to say yes to the question of "If we keep the Communication buffer size also same as Maxtransfer Size will the performance would be good.?"
In fact, the opposite is more likely true... I mean... ask oneself a slightly different question of... Given the fact that I have these two tuning options (and there are others too) and the fact that they can accept quite a different range of values, then is it likely that keeping them at the same value will always give me the best performance?". And I think the answer to that will generally actually be no. I'm not saying that keeping them the same will definitely not help, but then I'm also not saying that keeping them different will not help.
As ever, one shoe size does not fit all. Your best approach to tuning is... firstly to realise that there is rarely ever a magic silver bullet to this. The defaults are defaults for a reason, because the vendors have found that they usually give the best results.
And remember... the SQL backup tuning settings for client X... may not be at all appropriate for client Y.
1) Take a baseline, capture the current settings *and* capture several days (usually a minimum of a week) of real world performnce throughput values from real backups. This is your baseline against which you will compare all improvements.
2) Do some research, and do some thinking, I mean... have a reason to change a tuning parameter, i.e. try to rationalise why you think changing a tuning paramterer is going to help... e.g. if I change tuning parameter A from value X to value Y then I expect to see behaviour model 1. Never change a tuning parameter on a whim, or based on one posting in some forum somewhere, or because you are looking for a super quick win.
3) Change one thing / one parameter, and only one tuning parameter at a time. Usually we would not change two tuning parameters at the same time, unless the vendor documentation explicitly says so. And then again monitor for at least seven days. Remember sometimes the tuning effects are not seen after the first night. Sometimes it can take several days before the true effects are seen.
4) Rule: If no discernable improvement *and* no discernable worsening is seen, then change the value back to what it was - because otherwise you will have a non-standard setting and no reason to have it so.
5) Keep a detailed diary of what was changed, when, and on what server/software, and why. Do not fail to keep this diary updated - otherwise you will loose all track of the whole process.
6) It's not so much about worrying how to get it right, it's much more about worrying how not to get it wrong.
7) And it doesn't matter (fingers crossed) if your tweak has no discernable benefit. Just change it back. And monitor again for several days (a week is best).
.
Once you have got used to the fact that it takes a long time to get right... then you can consider going back and looking the tuning of the parameters that had no discernable benefit. Then maybe reconsider the rule of one change at a time, and maybe combine two changes. But this can quickly much more complex to explain and understand what got better and why, or what got worse and why.
HTH.
