Software Vendor Mistakes With SQL Server: Not Letting Customers Add Indexes

Posted on by Erik Darling

I have ESPN

If your developers could intuit every possible way that your application could be used, and were really good at performance turning SQL Server, you probably wouldn’t be reading things like this.

But here I am, and here you are, and here we are, staring at each other desperately searching for a reason to go on.

Many times when I’m trying to help people achieve better performance, indexes are a big part of my analysis. Unless you have a brood of queries that are just so awful they’d thwart any and every index you could possibly throw under them, you can’t avoid this inevitability.

At the end of these calls, what I often get met with is: This is great, we just have to run it by our vendor.

Lemme explain why this is wrong, because I’ve seen that end result. Things don’t get better when you make then wait.

Sycamore Tree

You are a vendor. You vend software. The way clients use that software is up to them. They may customize it in some weird way, or they may have way more data stuck in their local site than other people.

It’s sort of like being a parent: you vend life to your kids, and you can teach them how you think they should use it, but there’s a really good chance they’re gonna make different choices.

When this happens, you need to be okay with the fact that your definition of how the software gets used is no longer applicable. This goes double for vendors who don’t bring much SQL Server performance tuning expertise to the table to begin with, because the indexes you had for your ideal usage pattern probably weren’t great either.

Running the new indexes they need by you for their usage of the software is a lot like you running what you wanna have for breakfast by me. I have no idea how you plan to spend your day. A feedbag of eggs and bacon might be totally reasonable.

The one exception to this is that the vendor might be able to take my suggestions and apply them to other installations — but this stinks of a different problem than hyper-specific customization — it means you have a lot of unhappy customers out there and you just got lucky that one was willing to pay for real help.

Plastic Surgeon

I’m going to spend a number of post talking about index follies I see all the time when looking at software vendor design patterns. For now, watch this video from my paid training to learn more about why you need nonclustered indexes:

Things like not cleaning up old indexes, not adding sufficient new indexes, general index design patterns, clustered indexes, and all the silly index settings I see people changing hoping to make a difference.

This post is to prepare you for the fact that indexes need to change over time, as your application grows. Index tuning is something you need to stay actively engaged with, otherwise you’re leaving a lot of performance on the table.

Especially for folks in the cloud, where hardware size is a monthly bill, this can be an expensive situation to end up in.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.

Related Posts

8 thoughts on “Software Vendor Mistakes With SQL Server: Not Letting Customers Add Indexes”

  1. Yup, I agree.

    I would be happy if software vendors had some kind of baseline of how they expect the supplied indexes to be used. Not to mention something like expected query plans. Everyone’s life would already be so much easier.

    Instead, customers repeatedly hear the statement, “You are the only one with the problem. Our SQL experts have looked at their server. It’s way too small!”

      1. On the other hand, these vendors are plenty of potential customers for a good old “An optimizer is not a compiler, no, really it’s not!” training. 😉

  2. Having been on both sides of the fence I can see merit in both positions.
    On a couple of occasions I’ve added a new non-clustered index and suddenly had a massive deadlocking problem, if I was the vendor the first thing I’m going to say is a) what did you change & b) please take away that new index.
    This is the most extreme side effect for additional indexes but Inserts Updates & Deletes are all going to slow down with extra indexes.
    If you start to reassign the clustered index you take things to a whole other level. Similarly with columnstore indexes.
    Having said that if you’ve load tested the change in a test environment and keep up to date documentation of your changes ready to supply to the vendor’s support team it should probably be OK.

      1. I would say that all great vendors use load testing & that there are a lot of affordable ways to do it now (JMeter, workloadtools, RML Utilities (sadly neglected)).

        1. I’m not sure how to break it to you, but this series of posts isn’t aimed at “great vendors”. It’s aimed at vendors who need help working with SQL Server.

          On top of that, workload collection and replay with those tools can be a significant time investment. Even paid tools like Quest Benchmark Factory have a rather steep learning curve.

          If you need all that to add obviously helpful indexes, you’re part of the problem.

Comments are closed.