IT staff are sometimes reluctant to make any changes to third party databases, as they are worried that doing so will invalidate their support agreement. But sometimes it’s essential to create indexes for third party databases to improve performance/blocking.
You might ask, “Why doesn’t my third party database vendor have all the required indexes in place already?”
There are several reasons:
1) The third party’s developers and testers don’t always have access to large enough data sets.
2) End users (you) might not all have the same usage patterns, data distributions or data volumes.
3) End users might not have channels to give the vendor performance feedback.
Assuming we have identified an index (or indexes) that would increase performance, we want to apply these but not interfere with any future upgrades supplied by the vendor.
The simple solution is to create two scripts in source control: one to create required indexes, and another to remove those indexes prior to any vendor upgrades.
After a vendor upgrade, we examine the database’s indexes, and if the vendor still hasn’t created the indexes we require for our workload, we simply re-create our own version again.