While profiling my application against SQL Server 2008 running against a JDBC Application most of the time consumed were by following
- COMMIT TRAN
- FETCH API_CURSOR & exec sp_cursorfetch
I cannot reduce the frequency of commits in my app - so is there any setting/configuration like increasing the redo log buffers or log file size that can provide relief ?
And should I look at avoiding server side cursors as it eats up most of the time?
But since most of those statements involve blobs probably batch returning it makes more sense.