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

share|improve this question
add comment

1 Answer 1

up vote 8 down vote accepted

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,

  1. Throw hardware at the problem (SSDs, bigger server, more RAM etc.), or

  2. Talk to whomever manages the relationship with your vendor. Push the responsibility for system performance back on to the vendor, and/or

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

share|improve this answer
    
Amen to that. –  Mark Storey-Smith Dec 18 '11 at 3:01
1  
I truly dislike this answer, but it's the correct one. I come from a world of software development where I can fix problems with my own technical expertise which other parties refuse to. I'm now placed in a world where I cannot, and it's been a grating experience in some respects. –  Kaganar Dec 20 '11 at 14:32
    
@Kaganar - such are the joys of packaged software. Unfortunately your SLA is the only leverage you have with the vendor, and you can't jeoparise it by frigging with the system. You have my sympathy, but this really is out of your hands. –  ConcernedOfTunbridgeWells Dec 20 '11 at 14:38
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.