Administering COTS databases (ISVs / Third Party Vendors)

I recently received a question from a vendor about databases created by software vendors.

I just moved from an in-house software development company to a new environment that most of the software used here are COTS (Commercial off-the-shelf)

This is totally new to me. I’m a little bit lost since I don’t know anything on the applications, users (security), or the schema. What is your recommendation to manage such an environment and perform performance tuning?

Part of the confusion is terminology

I hadn’t heard the acronym “COTS” before – but that doesn’t surprise me.  Companies have wildly different terms they use for this. If you manage these types of databases, you many need to use multiple search terms to find more resources:

• Third party vendor database / application
• Independent Software Vendor database / application (ISV)

And also COTS!

Here’s a great article on administering COTS databases for DBAs

Tim Ford wrote a terrific article for this - it just doesn’t come up if you search for “COTS SQL Server”, because he used the term “third party” in the heading. But it’s terrific advice, and it covers a ton of bases:

Read 17 Questions Every SQL DBA Should Ask Before Supporting a New Third-Party Database by Tim Ford over on SQLMag.com.

What about Performance Tuning these databases?

Some additional questions I ask when getting to know a COTS environment is:

1. How important is performance for each application – ranked by the users.  Performance tuning can be time consuming, and this will help you prioritize and focus your efforts.
   • List out all the applications and ask users to rank how important performance is for them where one is highest and one is lowest (no ties for rank)
   • You’ll need to figure out the name of the applications and how they map to the database names to do this (often they are different). That’ll come in VERY handy for you down the road when someone reports that “the Zebra app is slow”, so it’s totally worth it.
2. Which vendors let you create non-clustered indexes in the databases, and which don’t? Index tuning is a major tool, and some vendors don’t mind if you add non-clustered indexes, but some forbid it.
   • First, I’d check if I have documentation from the vendor and if it covers this. If it doesn’t tell you, I’d ask the vendor.
   • Even if a vendor says “no indexes allowed”, if you hit a case where an index fixes a major performance problem when run against a restored backup of the database where you made the change, you can bring it up again with them then. Sometimes “no” turns into “just this once.”
3. Which vendors let you create plan guides in the databases, and which don’t? Plan guides are like duct tape for query plans – they can help you change a behavior when you can’t change the query itself.
   • Many vendors may not know what a plan guide is and react with “what?” to this question.
   • If you do create plan guides without permission, make sure to remove them before running any upgrade scripts. Not that I suggested you do that ;)
4. Does the vendor make suggestions for parallelism settings, or other SQL Server configuration settings?
   • Don’t assume that the suggestions are being honored on the instance. It’s worth checking what the vendor recommends, comparing it to existing settings, and investigating if it is different
5. Does the vendor forbid you from turning on Query Store (SQL Server 2016+)?
   • Honestly, I wouldn’t even ask this question straight out – I’d review the vendor documentation and just see if turning on Query Store is explicitly forbidden. If it’s not forbidden, I’d enable Query Store. If it is forbidden, I’d ask why.
   • Query Store is a little different from a schema change like creating indexes – you should be careful about plan freezing / pinning on sensitive vendor databases, but I can’t think of a reason why a vendor wouldn’t allow you to turn this on. It’s basically like asking, “Can I monitor this database?” Generally, yes, absolutely.

When performance tuning COTS databases (aka third party databases), I use the same performance tuning methodology I do for other databases – but I know that re-writing the queries is typically a last resort as a fix, as it’s something I’d need to request from the vendor which may take a very long time to roll out (if it happens at all).