Lots of people out there manage 3rd party vendor apps. However as anyone who has managed a vendor app can tell you, you don’t really manage the app. You simply backup the vendor database (assuming that they allow you to backup the database) and call the vendor when there are performance problems. Doing anything other than this will basically invalidate your support agreement with the vendor and prevent you from upgrading in the future.
Now in reality many of these 3rd party vendors don’t have anyone no staff who knows much about SQL Server, or databases in general. You sure don’t get the ability to talk to anyone that these 3rd party vendors have on staff that actually do know anything about databases. So when you start seeing performance problems you basically have a few different options.
1. Call their support line and talk to someone who thinks there is an “E” in SQL (they spell it sequel).
2. Leave the system with performance problems.
3. Fix the performance problem and hope that the vendor doesn’t notice the indexes that you created.
Obviously option #2 probably isn’t the one that the business unit that uses the application is going to accept. #1 is what they want you to do, but that’s probably going to be less than useful possibly taking days or weeks in order to get the issue resolved.
In reality #3 is the option that you want to go with, but there is that little problem of the support agreement to deal with. Obviously I would never recommend invalidating your support agreement … but when you need to get your production application back up and running, you need to get the system back up and running. Now you can’t do crazy things like change the database schema but usually that isn’t needed to get everything back up and running.
Usually it’s something as simple as updating statistics or creating a new index. Now you need to hide this index from the vendor so that they don’t complain that you’ve “broken” the system by fixing it. That’s why I recommend creating the indexes so that they are easy to identify later. When I create indexes in a vendor system I prefix them with something that the vendor will never use, like my name. This way I can quickly query for all the indexes and drop them the next time I need to let the vendor into the database (don’t forget to script them out first so you can but them back afterwards).
While the vendor will probably complain that you’ve added these indexes they can’t actually say that you’ve done anything damaging if you’ve removed them as well. Not to mention they probably won’t actually be able to find out if you’ve created and dropped indexes if you’ve removed them already.
While this probably isn’t the best way to have to deal with 3rd party vendor applications it is the reality of dealing with 3rd party vendors.
Denny
One Response
Another option that is occassionally available is to use Plan Guides, telling SQL Server to use a plan that I provide for it rather than one that the vendor uses. I wouldn’t use plan guides in SQL2005 because they’re too brittle and break to easily. But they’re a useable alternative in SQL2008 or later versions.
One other recommendation I like to give with regards to third-party applications is to document bad performance and complain LOUDLY every time it comes up for maintenance renewal. Maybe even drag your feet on paying, email the Sales team and cc the Sales executives, and otherwise make yourself the squeaky wheel. The R&D organization of most vendors are someone tone-deaf to complaints, but Sales leaves and breaths by positive referrals. Use that power to your advantage to get the Sales team to put internal pressure on the R&D team to make fixes and improve performance.
-Kevin Kline
[A href=”http://twitter.com/kekline”]Twitter @kekline[/A]