Printer Friendly

An ounce of prevention is worth a pound of cure.

YOU CAN'T PREVENT every trip to the emergency room, but simple steps can keep you safe--or keep you alive when the worst happens. In America, there were more than 136 million visits to emergency rooms in 2011, according to the National Hospital Ambulatory Medical Care Survey. Among the most common causes cited by the Centers for Disease Control and Prevention are abdominal pain, chest pain, fever and acute upper respiratory infection, headache, cough, and a handful of other ailments including trauma from injury.

To stay out of the ER, most people take preventative measures to ensure they are healthy. Regular health exams and tests can help find problems before they start. Finding problems early increases your chances for treatment and possibly a cure. By proactively getting the right health services, you are taking steps that help your chances for living a longer, healthier life.

Your database is much like your own health. Proper maintenance can keep it running smoothly, minimizing emergency room "fixes" that can create unnecessary and expensive costs. Why wait for an emergency to happen when you can take proactive measures now? But, alas, we are only human, and we often find ourselves sitting in an emergency room wondering where things went wrong.


When your database is sick, and most likely getting sicker, you are forced to act fast--you need to evaluate and categorize the illness, and from that evaluation take the actions necessary to treat your database. You have no time to lose. If you were a triage nurse, you would take your patient's vital signs--heart rate, respiratory rate, temperature, and blood pressure. Being a triage DBA is very similar--why is the SQL query taking so long to run, why am I seeing a drop in transaction rate over time, or why is my entire system bogged down--and most importantly--why is this happening on the busiest day of the year when we stand to lose the most?!

In an emergency situation you have seconds to run through your triage questions to diagnose what is ailing your database. Without the right tools, you might as well slap a black tag on your database's toe. Given the care that you have available, your database will not be able to survive.


When the signs of database disease are no longer possible to ignore, and the dangers of doing business as usual are impossible to deny, you'll be tempted to try one of these strategies that will make things worse:

1. Run the bundled performance tuning tools.

They add significant overhead and you won't get the insights you need to make positively-impactful improvements--not to mention the loss of valuable time.

2. Have a DB2 "expert" come out and spend a week fixing the problem. You'll get a 1%-2% improvement and a slick report for your $15,000 that will document exactly why you need to hire him for another week at another $15,000 to get another 1%-2% improvement. Then lather/ rinse/repeat until you run out of money or the business grinds to a halt because many "experts" address symptoms rather than root-cause problems.

3. Buy more non-refundable CPUs, memory, licenses, as fast as you can.

Possibly spending millions of dollars --applying a band-aide to a gaping wound does not solve the root-cause problems.

This misdiagnosis just sets you up for a lot of explaining when these solutions fail--provided your company is still in business!


If you're confronted with a sick, slow database, keep in mind this triage checklist of potential database problems, and what causes them:

1. Bad indexing strategy.

Apart from hardware failure, this is the real heart of the problem. You search through far too much memory to find what you are looking for, when you really should be looking in a smaller area. It's the difference between finding a recipe by scanning an entire cookbook as opposed to just scanning the index. The index is easier and faster.

2. Your CPUs are working too hard.

You can have a dataset that easily fits into memory. But because it isn't indexed properly, your database has to look through the entire dataset every single time. Now it can do that fast enough, but rifling through the entire dataset makes the CPUs work harder, wasting energy and robbing you of margin. So when there's a Tweet that drives the world to your site, you don't have the excess capacity to handle the demand and your system crawls.

3. Your CPUs aren't set up efficiently. One or two people scanning a server is one thing. But when more users tap into the database, the CPU is going to start to get overwhelmed quickly. When that occurs, things start to move slower and slower, which can lead to a complete crash.

4. Bad scripts.

The ad hoc performance tweaks written to solve your database problems often cause more harm than good, because they mask rather than cure what's ailing the system. The free software provided by your vendor can actually make things worse because it requires system memory and CPUs at the very moment that your system needs them for other critical functions.

DBI was recently called into a situation where a major healthcare provider was experiencing a business-threatening slowdown of their major processes because their database was increasingly unresponsive. They were facing fines because they couldn't generate federally mandated financial reports on time. Desperate, they were contemplating a large emergency purchase of non-refundable CPUs, memory, and licenses to try and fix the problem. This was after spending tens of thousands of dollars on ineffective consulting. They found themselves in the ER with massive bleeding on the verge of a Code Blue.

After months of crippling performance, this company called DBI in to assess their illness, essentially performing database triage. Our patented, award-winning software diagnosed the root problem in 15 seconds! There was a statement that was driving over 10% of all the I/O in the database, scanning for a single row in a little 16,000 row table in memory. It seemed improbable--a single query that took 42 milliseconds--all while the system was running thousands and thousands of tables, and thousands more indices in SAP. But our performance optimization software said it was the problem, and we requested permission to fix it.

They agreed to implement our solution, and here's what happened:

* The statement that took 10% of the system's I/O now only took .5%.

* The statement that took 42 milliseconds became so fast that it couldn't be measured to six decimal places.

* Reports that took eight hours to generate now only took 1 hour.

* But the real magic was that the fix was useful not only for that statement, but others that had the same selection criteria from the database.

The result: mandatory reports were generated on time, fines for late reports were a thing of the past, and the unbudgeted expense for nonreturnable memory, CPUs, and licenses was no longer necessary! The modest investment in DBI's tools empowered this company to save millions of dollars and thousands of man hours.


It's a lot better to prepare for that day now than wait until you find yourself playing triage nurse to a flat-lining database as your systems are slammed and your frustrated consumers are going elsewhere. DBI Software helps companies do much more with the hardware, software, and licenses that they already have. If you've got 15 seconds, let us show you how.


Download your copy of The C-Level Emergency Guide to Database Problems.

You will learn:

1. How to control spiraling costs in an emergency.

2. How to solve business threatening problems instantly.

3. How to cut through the fog when no one knows what to do.


DBI is your trusted partner for breakthrough DB2 Performance Solutions that deliver invaluable results for organizations having the most demanding requirements and discriminating preferences.

Contact DBI today at (512) 249-2324 x101 to take the 15 Second Challenge. If we cannot deliver measureable performance improvements, then there is no charge for our services and we will donate $10,000 to the charity of your choice.

COPYRIGHT 2015 Information Today, Inc.
No portion of this article can be reproduced without the express written permission from the copyright holder.
Copyright 2015 Gale, Cengage Learning. All rights reserved.

Article Details
Printer friendly Cite/link Email Feedback
Title Annotation:Sponsored Content
Publication:Database Trends & Applications
Date:Oct 1, 2015
Previous Article:Database performance today: the need for speed and scale: five avenues that lead to high-performing databases.
Next Article:Lion Brewery (Ceylon) PLC taps into new regional markets with IBM and SAP.

Terms of use | Privacy policy | Copyright © 2018 Farlex, Inc. | Feedback | For webmasters