While profiling my application against SQL Server 2008 running against a JDBC Application most of the time consumed were by following

  2. 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.

Please advice.

2 Answers 2


Cursors may be the right solution to a given problem, but usually not in SQL Server. Try to use set-based solutions instead of an iterative one if at all posible. Worst case scenario, you can look at using an iterative SQL solution. I've done that before and ended up an order of magnitude faster than an equivalent cursor implementation.

You can return BLOBs from the DB in normal SQL statements so you don't need a cursor for that - granted, if your application can't handle the incoming data stream, you might be changing cursor waits for async_network_io waits (meaning SQL is done, but your app is too slow - Access does this all the time).

If you post more details about what your app is doing or more specifics, we might be able to get more focused advice.

  • 1
    we are using normal SQL statements to return the blobs. A API Cursor gets created however on the server side to process the results. We are using a forward only result set from the JDBC layer.
    – praveen
    Commented Dec 29, 2011 at 14:38

Yes, cursors are evil. Probably - most evil thing on the Earth 8-)

For proper helping - what recovery model you use (simple, full?)

If you sure, that the most of the time eats COMMITs - then try to add more big log files to the your DB AND to the tempdb.


try to shrink current log files to its minimum and immediately expand to relative big size in one step. It helps to create bigger virtual log files inside your log-files, which performs a little bit faster


adding more logs removes hot-spot of your IO from one log-file and spreads it across several.

Of course

log files should reside on faster disks. Depending on your load - may be more preferable to place tempdb to dedicated fastest disks.

  • Adding more log files won't help as you suggest. SQL Server will always write sequentially to a specific part of the log. It won't paralellize writes across the different files. Commented Dec 28, 2011 at 16:04
  • Yes, BUT - adding more space to full log file is time-consuming, and if you already has one ready to use - its much better!
    – Oleg Dok
    Commented Dec 28, 2011 at 16:11
  • If you're running into full log issues, your problem is that you need to be backing up the log file so that the VLFs can be reused, not making it bigger.
    – Brandon
    Commented Dec 28, 2011 at 16:53
  • Looks like you never run out of space in log in single transaction 8-) Nevermind, it was a long time ago, but still hurts 8-)
    – Oleg Dok
    Commented Dec 28, 2011 at 18:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.