Toad Turnpike: Tuning Database Object Access Patterns

  

Robbing Peter to Pay Paul on the Database Performance Teeter-Totter:  Part 1 of 2 ...

Within the previous month alone, I’ve had three different customers ask me the same identical question: “Does Quest have a solution that recommends indexes for my SQL Statement?”

It’s a common question, actually.  But I have learned to counter that specific question with “Yes we can, but is that what you really want to ask me?”   Did they really mean to use the word “statement” (singular) or “statements” (plural)?

Before the customer even has time to think about whether or not I’m trying to be flippant, I ask a second question to clarify my intentions:  “Have you ever made a database change that helped some users, but messed up others?”    One of those three customers, Alex, knew instantly what I was getting at.  His name is not real, but his DBA persona is real.  And his experience is real.

The Scenario.   “Yeah, wasn’t too long ago that I made some changes that put us in a spot,” Alex starts.  “We had a situation where one of our evening Materials Management reports was taking almost an hour to run every afternoon.  The Line Managers were constantly complaining about it.   We were getting pestered so much that we finally ran the report’s underlying SELECT query through our database vendor’s Tuning Advisor which suggested a new index on the Materials Inventory Master table.  We did some testing of the report SQL with the index, and we were happy to see the report completing consistently in about ten minutes.”

The production line managers were pretty happy with Alex’s team for shortening that report’s execution time, because it meant that they wouldn't have to stay overtime to validate the materials inventory for next day’s factory line quotas.  One of those guys (we’ll call him “Paul”… remember that name) even sent a nice e-mail to Alex’s boss and the boss’ boss, saying how much they appreciated Alex and his team’s help.

That euphoric feeling didn’t last long, however.   There’s more to the story.  

The Problem.   Alex’s manager got a call from the on-duty Operations Analyst (we’ll call him “Peter”… remember that name, too)  at the end of that index-tuning week.   It’s Saturday night and something is wrong.  It’s one of those calls.  Why does it always have to be on a weekend?   And late at night?  And to your boss?

One of the weekend batch order entry processes, which usually takes only about a half-hour, was now coming up on two hours.  The job was still running when Operations called Alex’s Manager.

For many readers who have lived through scenarios like this, you may have guessed what happened.  While Alex’s new index improved the SELECT query that yields data for the daily materials report, that same index was now causing a major snarl-jam in one of the weekend batch UPDATE jobs. 

We have here the classic proverbial case of robbing Peter of performance tokens (the Operations Analyst looking at the overall database) to pay Paul (the Line Manager concerned about only one report’s performance.)

A Stop-Gap Solution.   Alex and team quickly implemented a stop-gap resolution to simply disable that new index right before the batch update job starts.  Then, the table gets re-indexed after all updates are complete.  Simple enough fix.  But that didn’t stop a few other INSERT/UPDATE process jobs from having similar trouble later that night.  To make matters worse, the same issue happened a few weeks later, during the month-end processing window, where a few more jobs were affected.   All told, Alex logged over 90 total man-hours of effort to investigate, band-aid, and test other jobs whose SQL runtimes were affected adversely by the new index on that one master table.

The Real Solution.   Think about this… Alex spent (maybe) an hour testing and implementing a suggested index that teetered in favor of one man’s report performance.  But with that teeter came a totter that negatively affected other processes and caused Alex and team 90 times that effort to fix.    90 times!  Could he have avoided that pain?   Could he have foreseen which processes might be affected by the original change, and by how much?

Quest has a solution that can minimize the pain or avoid it altogether in scenarios like Alex’s.   It’s called SQL Optimizer.   Can Quest’s solution suggest indexes for your one query?  Of course it can.  But is that the question you really want to ask?   Quest takes a more holistic approach to index tuning, one that extends the tuning scope to multiple SQL statements, not just one, or two.

Here’s the 90-man-hour question (whatever pricetag those hours add up to in your organization):  would you know which SQL statements to consider to index-tune?   If you don’t know WHAT they are—or WHERE they are—how can you come up with the right list to tune?   How could Alex have identified up front which SQL statements should be looked at?   Turns out that Quest's SQL Optimizer can help here too.   In Part 2 of this blog, I’ll describe how SQL Optimizer can be used to identify appropriate SQL workloads to shape some important index tuning objectives for your database.

 


 

Toad Turnpike is a blog series about real accounts of real people in real situations.  Only names have been changed to protect identities.

About the Author
Gary.Jerep
As a Software Consultant within Quest, Gary Jerep has over 16 years of experience assisting DBAs, Developers and Analysts with relational database needs. Prior experience includes systems design and analysis...