Consider two approaches to update data:
Approach 1: Update Table 1, commit Update Table 2, commit Update Table 3, commit Approach 2: Update Table 1, Table 2, Table 3, commit.
Now Approach 1 is not atomic and uses three transactions. However, it could be argued Approach 2 will hang onto locks longer so will not scale as well. In a scenario where you don't need true atomicity, can an argument be made that Approach 1 will perform better because it won't hang onto locks for so long.
The thing, is you need statistics and evidence to make good architecture decisions and not just speculation. I'd like to set up a trace for SqlServer so I can get an idea of how much performance different there ie between both approaches? I want to know if it is 1ms or 500ms?
I am guessing it is not that much, but wish to confirm my suspicions.