Optimization around static cursor use

Question

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

Answer 1

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