Tuning SQL Server Performance

Question

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

1. COMMIT TRAN
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.

Answer 1

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.

Answer 2

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.

And

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

And

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.