I'm responsible for administrating an instance of SQL Server 200x which has the misfortune of serving as a backend to a turnkey application written with a disgusting performance problem. It repeatedly uses the pattern of preparing a static cursor for which it will only use the first few entries of, and often that takes tens of milliseconds. There are thousands of these queries in a continuous stream.
Truth of the matter is that due to the curious design of the application data integrity isn't an issue at all with any of these queries, so the fact that they're static is miserable since performance would be about thirty times faster if they were dynamic.
After dealing with the vendor for weeks I've been getting nowhere. Truth is all I really need to do is change one parameter in the way the application uses sp_cursorprepexec. I could even do it in a global way across the entire server if necessary.
I'm looking at any and all solutions, whether it be using a feature in SQL Server 200x that I haven't been able to find, writing a small app that sits in between SQL Server and the client and modifies the data (although I wouldn't be looking forward to figuring out how to deal with the TDS protocol in such a way), or somehow renaming sp_cursorprepexec and replacing it with a wrapper.
Sky's the limit.
(FYI, we're running SQL Server 2005 right now, but if there's a convincing reason to shell out the extra money to upgrade the license it could happen.)