Application vendors support many customers, and they often need to impose some control over variables in order to do so. For example, for me to help someone with the performance of their SentryOne installation, I need to know that minimum requirements have been met for the compute environment and supporting OS and SQL Server versions.
Sometimes, vendors take this much further than we feel is necessary. I’ve heard many stories related to data architects who discovered opportunities to improve performance through indexing strategies only to be told by the vendor that it would void their support contract.
This leaves us with a vexing question: How can DBAs and data architects improve performance for vendor-locked systems that won’t allow them to fully ply their craft? It can be done, but we must consider the application to be a black-box and look outside of it for opportunities. The three things I mentioned in my example—compute environment, operating system, and SQL Server itself—are a great place to start.
Compute Environment
If you are reading this, you are likely already monitoring performance, or you are interested in it. That is the first step, because you can’t improve what you haven’t measured. Knowing what the performance pattern is on a good day vs. a bad day will help you understand whether your compute environment is sufficient for the workload.
These days, the computing environment could be:
- Platform as a service (PaaS) in the cloud
- Virtual machines
- Containers
- Infrastructure as a service (IaaS)
- Bare metal servers running in a hosted location or your company’s data center (where it all started decades ago)
With all these options, it isn’t difficult to get a little confused.
In order to normalize the playing field, you need to go back to fundamental concepts that all these environments have in common. They are all computing environments governed by processing (CPU), memory and storage (I/O), and network infrastructure. You may or may not be able to see the specific details of these components in every environment (DTUs anyone?), but they are there in every case. Understanding the footprint your vendor-locked application leaves on these basic computing components will let you know whether adjusting them up or down can make a difference.
This is not to advocate “throwing hardware” at the problem, but this discussion would be incomplete if we did not consider it as an option. There are many cases where not having enough, or even having too much, of one or more of the basic computing components will have a negative impact on the performance of your vendor-locked application.
If you are running in public cloud, you have the benefit of generally being able to click a few buttons to adjust the relative power of the compute environment. This makes dialing into the sweet spot for performance faster than ever! Having granular low latency performance monitoring will reduce the time to performance (TTP) even further.
Operating System
Many years ago, I moved myself and my family into a very old home with the intention of modernizing it. The home was fitted with electrical outlets that did not include the ground slot (the third prong). All my computing equipment needed the ground prong, so I began a long and expensive journey to update this “feature” of the home.
In the technology world, we call this upgrading, and while some vendors lock us into specific versions of an operating system, it is less common than having them lock us into specific SQL Server versions for compatibility reasons. If you are running on operating systems that aren’t patched or are years past their prime, you should consider upgrading. For server operating systems, performance improvements are generally made with each iteration. For a quick example, you can review some of the improvements made with Windows Server 2019 here.
If you are running PaaS in the cloud, you’re in luck! The cloud provider is going to perpetually ensure that you have the latest and greatest without you lifting a finger (in theory). This is an attractive benefit of using cloud resources. If you are running IaaS in the cloud, they’ll constantly work to improve the compute environment, but you are still responsible for maintaining your OS. Don’t forget to maintain your OS if you are running IaaS in the cloud!
SQL Server
We can often find opportunities within the SQL Server database engine. Think of it this way, if the operating system is the compute environment for SQL Server, then SQL Server is the compute environment for your vendor-locked application. Adjusting that environment can create great impact, negative or positive, on how the application operates and performs. While adding or changing indexes or stored procedures might be considered adjusting the application itself, which is often frowned on by vendors, adjusting the environment is more likely to be at your discretion.
Microsoft has provided us with a wide array of features and settings to help in adjusting the engine to improve the workloads of our applications. Here are just a few:
- Server Configuration Options
- Database Configuration Settings
- Plan Guides
- Partitioning
- Cardinality Estimator
- In-Memory OLTP
- Snapshot Isolation
The list above is short and far from detailed or complete. It is included to illustrate my point that SQL Server provides many options for adjusting to vendor-locked workloads within the SQL Server engine itself, and you can use these even if you aren’t able to adjust database schema or source code.
Conclusion
Supporting a vendor-locked application can be frustrating. After all, you’re running it for your company, and you truly do know what works for you better than the vendor does. At the same time, it is important to maintain vendor relationships to ensure you can get support if you do have a problem the vendor has to help with. If you feel stuck, start by monitoring the compute environment to see if you can change its basic composition to improve performance, then ensure you have the latest operating system optimizations, and finally, be sure to take advantage of settings and features in SQL Server that allow you to influence application performance without changing the application.
If all else fails, I suggest contacting the vendor and holding their feet to the fire. You’ve worked hard to make sure your support contract is still valid, and if you’ve done all you can, you should be able to make good use of it.
