“All I have to do is keep our Oracle databases and SQL Server databases humming along so everybody can work from home,” you tell yourself. “Think of all the pressure if I had to manufacture ventilators or keep shelves stocked with bathroom tissue.”
But wait. Most database administrators are working from home right now to support those functions, and a lot of them are under pressure. Maybe you’re one of them, managing database environments and mitigating the risks of database changes so your co-workers can manufacture ventilators and keep shelves stocked.
You’re squarely in the supply lines in the battle against the pandemic. Your work is just as business-critical and every bit as important. Only now, you and your fellow DBAs are doing it in the era of #WorkFromHome.
How does it feel to have all of your company’s developers, customers and vendors relying on you to keep every database up and running smoothly? Can you do it when you (and most of them) are working from home?
Now you start to feel the pressure.
Are you sure about your database performance?
There’s enough stress in the switch to working from home (WFH) as it is, without your organization slowing down because databases are running poorly. This is no time for sand in the gearbox.
Look at two areas of database administration for issues that can affect performance:
- Benchmark your databases now, early in your company’s WFH curve. Benchmarks based on simulated or real-world workloads will indicate trends and show how your databases are responding to changes like reconfigurations, security patches and spikes in transaction volume.
- Inefficient SQL statements have a way of gumming up even the smoothest-running databases. If you can tune the most-inefficient 20 percent of your statements, you can probably solve 80 percent of your performance problems. Use SQL optimization to locate, classify and tune statements that slow down your database operations.
I’ll go into each of those a bit deeper.
1. Why would you benchmark databases in the era of WFH?
Most DBAs know of several good reasons to benchmark databases, including these:
- Determining system throughput and capacity — It’s always a good idea to have a baseline; you just don’t want it to be the number of trouble tickets against database performance. The coronavirus outbreak is causing a lot of business change for who knows how long; in a few short weeks you’ll be glad you captured metrics when you did.
- Simulating thousands of concurrent users — Given the WFH shift in your development effort, benchmarks help you see how well your database infrastructure will keep up as needs increase.
- Hunting for applications that do not scale well — Infrastructure isn’t the only potential source of pain. Benchmarks measure the way applications themselves are performing.
- Finding breaking points, weak links and bottlenecks — How are you going to know what any system can do if you don’t push it? Simulating production workloads on non-production databases saves you from having to deal with unpleasant surprises when real-world volume spikes.
It’s not business as usual right now, so database benchmarks and performance testing are valuable, timely exercises. Your developers are changing applications while getting the hang of being completely out of the office; make sure your databases can keep up.
What’s the best way to impose an extremely high workload on your database system? Every testing environment wants to do that, but it’s not easy. With Quest® Benchmark Factory® for Databases you can find system limits and performance bottlenecks before your databases do.
Benchmark Factory is made to capture and replay database workload under industry-standard benchmark conditions like TPC-H, TPC-C, TPC-D, TPC-E and ASP3AP. You can simulate real-world workloads from production volume, then replay them in test or development environments.
All while you work from home.
2. Why would you optimize SQL at a time like this?
If those benchmarks don’t help you solve your database performance problems, then consider tuning some of the inefficiency out of your SQL statements.
That inefficiency is not intentional; after all, nobody lives to write sloppy SQL. But database performance is at a premium with all the other stress in your business, so SQL tuning is in order.
“Not me,” you say. “I use the cost-based optimizer in Oracle/SQL Server/DB2 to take care of inefficient SQL. It determines how well a SQL statement will execute based on the cost of the execution plan. We’re good, thanks. Next problem, please?”
The next problem is that the optimizer built into your RDBMS can still leave you with under-performing SQL statements. Its internal rewrite feature is designed to improve performance when inserting, updating, deleting and retrieving records. But many DBAs have to slog through manual rewrites anyway to get better performance, and some database platforms make that harder than others.
We looked at both problems — the inherent deficiencies of most optimizers and the difficulty DBAs have in correcting for them — and built Quest SQL Optimizer. It uses artificial intelligence to mimic human expertise, then rewrites SQL statements so that your database’s internal SQL optimizer makes better decisions.
Our SQL Optimizer is a feature of Toad® for Oracle, Toad for SQL Server and Toad for IBM DB2 (Xpert Editions and higher). It automates the process of trying every possible rewrite, then suggests the statements most likely to improve performance on databases. SQL Optimizer test-runs alternates, displays execution statistics and generates alternate indexes. Plus, it connects to Benchmark Factory (see above) so you can test alternate SQL statements under simulated workload conditions.
If you have to move as quickly right now as most DBAs do, you’ll find SQL Optimizer the fastest way to rewrite your SQL statements for better performance and less workload on your databases.
Next steps
Whether they’re in the Oracle cloud or on-premise, when your databases slow down, they cost you more money and they cost your co-workers productivity. Benchmark Factory and SQL Optimizer do the heavy lifting of simulating database workloads and tuning your SQL statements, while you focus on keeping your WFH co-workers happy.
Other resources:
- For Benchmark Factory, read Top 10 Fallacies in Benchmarking, a short paper for DBAs and developers on getting the inefficiencies out of your benchmarking efforts.
- For SQL Optimizer, read Solving the SQL Tuning Problem. It’s a deep dive inside the algorithms and processes we’ve built into identifying and presenting the best alternatives to your SQL statements.
Look for more posts in this series about keeping your infrastructure stable and your enterprise workforce productive during this global medical emergency. Stay home, and don’t forget to take walks. They help ease the pressure.
How to auto-optimize SQL code with Toad for Oracle Xpert EditionWhat’s the last thing a DBA wants to do when a virus outbreak causes the office to close and makes most of the company work from home? Tune SQL by hand, of course. But tuning SQL is a big step toward better database performance and a good way to keep your co-workers productive. This video walks you through the automated SQL optimization feature of Toad for Oracle Xpert Edition. You’ll see the performance metrics you choose from and the control you maintain over the process. Compress hours of tedious rewrites into a few mouse clicks.
Click the image above to watch a video. |
Related information
Blog: Database development and management while working from home [IT and Covid-19]
Information Management Remote Solutions: Database Management While Working from Home
Have questions, comments?
Head over to the Toad World forum and chat with our expert developers, and lots of experienced users.
Help your colleagues
If you think your colleagues would benefit from this blog, share it now on social media with the buttons located at the top of this blog post. Thanks!
*Indicates you need to be signed in and under a current maintenance contract to view this article.
Start the discussion at forums.toadworld.com