Apr 6, 2020 3:30:00 AM by Quest Software
“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.
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:
I’ll go into each of those a bit deeper.
Most DBAs know of several good reasons to benchmark databases, including these:
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.
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.
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.
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 Edition
What’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.
Information Management Remote Solutions: Database Management While Working from Home
Head over to the Toad World forum and chat with our expert developers, and lots of experienced users.
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.
Written by Quest Software