Client Success Story: Using Scorecards to Improve Application and Database Performance

CHALLENGES

Our client’s legacy application and database environment were in desperate need of attention as the environment was the product of more than 25 years of ebbs and flows of product demands, business mergers and repeated changes in personnel. Limited resources were allocated over the years to ensure proper utilization of all available database features with each system upgrade. On top of that, the company’s only database administrator was nearing retirement.

As a result, our client’s production environment faced the following challenges:

Product production ran 24 hours a day by 6.67 days a week.

Change window was limited to 1.5 hours on Sunday mornings.

Management approval was required for application and/or database changes lasting more than 1.5 hours.

Extended database changes could have resulted in work outages.

Management and end-users experienced application inefficiencies.

The application development team knew of performance problems, but was unable to identify them.

SOLUTIONS

We provided a team of experts with more than 30 years of platform, database and performance experience to tackle the challenges. However, we knew we had to first develop a trusting relationship with our new client.

We began by identifying specific database features that were not being utilized that would minimize the impact of database changes. Additionally, a macro was created to automate the generation of database change scripts for consistency and clarity across requested changes.

After a rapport had been established with our client, focus shifted toward a proactive performance analysis with the use of scorecard analysis, which provides a holistic view of the environment by evaluating multiple metrics simultaneously during any analysis cycle.

After a couple months, the client claimed, “we always knew there were performance issues within their application, but we weren’t sure where to look.” Odyssey’s scorecard analysis process, which is still used to this day, specifically identified major issues in several parts of the client’s application.

RESULTS

• Every database change is scripted, logged and retained for internal auditors.

• Production downtime has been been reduced from an average of 4 hours to less than 30 minutes by utilizing advanced database parameters. Many changes now no longer require application downtime or a dedicated change window.

• Scorecard results and performance recommendation reviews are performed monthly, which are designed to educate the client about new performance discoveries and propose solutions to address them.

• Monthly performance has improved dramatically in the following areas:

SQL Busy Time – 15% reduction (17 hours) from 118 hours

Read Operations – 11% reduction (25 million) from 223 million

Rows Accessed – 15% reduction (7.6 billion) from 50.1 billion

Sort Operations – 17.5% reduction (11 million) from 60 million

Sort Busy Time – 12% reduction (4 hours) from 37 hours

SQL Calls – 16% increase (527 million) to 3.3 billion