Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

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.

share|improve this answer
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 Dec 29 '11 at 14:38
add comment

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.

share|improve this answer
    
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. –  Martin Smith Dec 28 '11 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 Dec 28 '11 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 Dec 28 '11 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 Dec 28 '11 at 18:02
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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