As a CIO, you’re on the firing line when your organization’s cloud service costs are rising at the same time that your cloud application performance is falling.
“Wait, what?” you wonder. “Shouldn’t that be the other way around?”
Yes, as a matter of fact. It is possible for your application performance to be improving (or at least staying the same) while your costs are falling (or at least staying the same). But before you start trying to bring things back into line, make sure you go after the right culprit.
Poor application performance costs money
If your cloud-based applications are performing poorly, it’s costing you money one way or another. Externally, it’s costing you the customers and prospects who are taking their business elsewhere. And internally, slow page loads and long response times hinder employee productivity.
It’s bad enough to lose sales and disrupt how your employees work, but it can have additional impacts. Since cloud service providers charge for compute time, memory, storage and network bandwidth, you can easily spend a bundle yet suffer from poor application performance. Excessive resource consumption usually goes hand in hand with performance problems, and you can end up paying for it.
The chart below bears this out. It comes from the 2020 PASS Survey on Trends in Database Administration, reflecting the views of 312 database managers in a variety of industries.
The two primary concerns for those managers are costs and application performance. At the same time, they know that the path forward leads through the cloud, because cloud is top of mind when it comes to infrastructure, as indicated below.
Web server problem? Application problem? Could be a cloud database problem.
Badly written SQL queries and poor SQL execution plans are as likely to contribute to performance problems in the cloud as they are in your on-premises infrastructure. Inefficient SQL includes the use of wildcards — SELECT * — and data type conversions in a WHERE clause — WHERE DATE_FORMAT(inv.date, '%Y-%m-%d %T') = '2020-08-10';. Those can occur whether your databases are in the cloud or in your data center.
“What about the internal optimizer in our database?” you ask. “That’s supposed to make up for some of the problems with SQL and execution plans.” True enough; it does in some cases. But the optimizer doesn’t work well if the database statistics are out of date or table indexes aren’t being used.
In other words, your move to the cloud may have distanced you from problems like capital expenses, resource limitations and hardware obsolescence. But the risk of lousy SQL has followed you and is something your DBAs and developers still need to figure out — even in the cloud.
The two main keys to balancing cost and performance when migrating on-premises databases to the cloud are:
- workload monitoring and diagnostics to spot nascent performance problems, and
- automated SQL optimization to rewrite SQL statements and find better alternatives in seconds rather than in hours
Manual SQL optimization is tedious. It’s not like re-prioritizing a to-do list or editing an email message before clicking Send. It can take hours of trial and error. But because SQL statements are at the heart of so many performance problems, it’s imperative that the database professionals in your organization devote time to improving them.
The DBAs and database developers charged with improving cloud application performance have to weigh multiple factors across multiple lines of code:
- Which type of optimization — To generate SQL alternatives or deploy a new/updated execution plan?
- Depth — Basic SQL alternatives or alternatives using all available database hints?
- Where the SQL is used — In a report based on all records, in an online query that returns hundreds of records or in a query that returns a handful of records?
- How the SQL is used — Static or dynamic? Inside a SQL block or from an application?
- How often the SQL is executed — A few times a day or hundreds of times per minute?
- Optimization goal — Less CPU time, less I/O time or shorter run time?
- Dependency on other SQL statements that are running?
- Data cached in memory or not?
Know what to ask about
Application performance problems can occur anywhere in a cloud computing stack: your network, the cloud provider’s network, the cloud services, the infrastructure, the web server, APIs, the application, users’ devices. Most CIOs know each level of the stack and know to ask about problems there.
Smart CIOs make sure to ask about the database and SQL queries, too.