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.

I'm currently in-house support for a vendor-supplied application which uses static cursors to crawl through a list of data. I've leaned on the vendor as much as I can to fix their cursor use with no affect, and this software is critical to our operations.

My only recourse in the meantime is to find a way to configure SQL Server that may relieve some of the pressure.

Static cursors that I cannot control are opened with queries that look like:

SELECT {manyColumns} from {table} with (nolock)
WHERE {codeFieldName} > @param
ORDER BY {codeFieldName}
option (fast 1)

The indices are set up well and the result set that ends up being built in tempdb is generated just about as quickly as it can it seems. However, this is where it gets ugly.

Most of the resulting table isn't used. In fact, usually only the first item is used, but these tables are typically thousands of entries (if not tends of thousands) long. What's worse, typically there's no actual need for a static cursor as the underlying data most of the time won't change while the cursor is being accessed. And to make matters even worse, this is the technique that's used to scan an entire subset of the table since these queries are being used only to fetch information about the next item -- create and use cursor, read the top entry, discard the cursor, then create and execute the cursor again... (Yes, this leads to O(n^2) scans, amazingly.)

Is there some way I can force SQL to handle these cursors as dynamic cursors? Or is there a way I can have static cursors not generate a full table in tempdb until the data is about to change? Any other solutions?

Edit: SQL Server 2005 presently, but moving to 2008/2008R2 isn't entirely out of the question. (I have a test environment with 2008.)

Tried so far:

  • Threw tempdb on a RAM disk. No performance increase. Looks like SQL Server isn't doing much in the way of I/O during these queries and post-2000 versions of SQL have better memory vs. tempdb management. (Note the RAM disk option was taken out after 2000, likely for this reason.)
share|improve this question
3  
I believe I would look for a new vendor as the sheer incompetence of this leads me to believe the whole application is unreliable. –  HLGEM May 10 '12 at 17:47
    
Not a bad conclusion. Also not an option I can currently exercise, not for lack of trying. –  Kaganar May 10 '12 at 17:51
2  
The answer for management or decision makers needs to be: There are issues in the application architecture that cannot be solved at the database level. –  JNK May 10 '12 at 17:55
2  
Get some SSD's for tempdb if you're stuck with this mess. –  SqlACID May 11 '12 at 11:19
    
@SqlACID: That's not a bad idea, although this computer has no shortage of RAM. That being the case, would tempdb be doing that much disk IO? (Although I suppose any IO slowdown is a disaster with this many cursors. And I suppose I could measure this, I just need to figure out how.) Also, can tempdb be thrown into a RAM disk? –  Kaganar May 11 '12 at 13:42
show 1 more comment

1 Answer

There's really only a few things that I can think of that might help here, without actually changing the cursor code:

  1. Make sure that your tempdb resides on different physical drives from your database files. This won't make the temp/static rowset any smaller, but it will make it faster.

  2. Adjust the CURSOR_THRESHOLD configuration setting: This is a tricky thing and not normally recommended, however, it can have a big impact on this very situation (just not always a positive impact). My guess would be that you'd want to adjust it from the default of -1 to something like 1000. But play around with it, and be careful, as it's server instance-wide and can affect other things as well.

  3. Plan Guides: I think that you need 2008+ for this, and I am not sure if it works on cursor SELECT's. They are also complicated to use, but if they do work, they can really do amazing things to a repeatable query that is using sub-optimal query plans.

That's all that I can think of ...

share|improve this answer
    
Thank you for the thoughts. I've tried tweaking CURSOR_THRESHOLD with no gains (and as expected a few slowdowns), and moving tempdb into a RAM disk didn't help (the server has plenty of free memory, so so decreasing SQL Server's cache space isn't an issue). As for plan guides, I'm going to have to spend a bit of time with that to test it, but at this point optimizing the plans isn't really the issue -- good execution strategies are taken, but copying the entire result set is the bottle neck (AFAICT). –  Kaganar May 11 '12 at 17:26
    
Yep. Sorry, I really cannot see anything else that you could do without changing something in their product. –  RBarryYoung May 11 '12 at 17:32
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.