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.)
Best Answer
This is not a technical problem, it's a contract management problem. Don't frig with the turnkey application. It gives the vendor a get-out-of-jail-free card that allows them to ignore their service level agreement.
Either,
Throw hardware at the problem (SSDs, bigger server, more RAM etc.), or
Talk to whomever manages the relationship with your vendor. Push the responsibility for system performance back on to the vendor, and/or
Investigate the business case for moving to a competitor's product.
Messing with the application is the last thing you should do, particularly if relations with the vendor are getting tense.
You need to make sure your management knows that the performance issue is down to the architecture of the system. Get that in writing somehow; write up a report describing the issue and the reasons you can't fix it unilaterally. Make sure it's emailed to all of your internal stakeholders and you have a record of it being sent. Arrange a meeting to follow that up and explain it. That covers your arse - this is quite important if the vendor (or the party that authorised the purchase) tries to get political.
Now, escalate the issue with your I.T. management function, making sure it's well documented. As you understand the root cause of the problem, this shouldn't be an issue. The vendor has sold you a system that is not fit for purpose. It's now a management issue. Light the blue touch paper and stand back.
If you've already done this, then you've done everything that you can do without interfering with the application itself. Again, I can't say this strongly enough: do not do anything to the application that could void the SLA.