Client Success Story: Applying NonStop SQL Performance Best Practices into DevOps Production Support

CHALLENGES

Our client’s application environment is very data intensive. Millions of queries, stored in a large platform independent database, are used daily to process a vast amount of applications. While the database’s design evolved as the business grew and changed over time, support for the platform’s performance did not. Technical management understood the need to improve performance, but it did not have the tools or approach to provide the executive team with the right metrics to justify focusing on optimizing database performance.

The technical team approached Odyssey Information Services with the following goals:

• Deploy application and database changes within a timely and efficient manner without negatively impacting critical business operations

• Identify and prioritize performance problems

• Define a standard set of reporting metrics to measure the environment

  
  

SOLUTIONS

Building Client Trust

With more than a decade of experience improving database performances, our team immediately identified an opportunity to improve query performance by using standard measurement tools. Before we could achieve the technical solution, however, we knew we had to earn the client’s confidence in our delivery to enable us to make the changes. Performance improvement is an iterative process over time as part of production deployments. Therefore, we started with the basics by minimizing expensive, large data sorts that only returned a few records. We defined a process to validate the changes on their development environment, validated them to technical management and then scheduled them with normal application environment updates. We presented the first dramatic improvements with no disruption in normal release flow. We did this within their normal processes—not as part of an expensive disruptive project.

As progress continued, we reviewed the structure of the queries and found that controls were being used to circumvent the normal database engine query performance determinations. Some of the decisions to circumvent standard query engine performance was historic while others were based on the previous database administrators’ IBM db2 experiences.

We reviewed again what was appropriate and what could be improved with standard database engine determinations, which resulted in additional performance improvements and earning the company’s trust in our expertise and professionalism.

Adding SMEs to NonStop Support Team

Odyssey used its established team of NonStop subject matter experts (SMEs) with extensive expertise in data management and industry best practices to review physical and logical data structures, queries, and database and application parameters. Based on their findings, the team presented our client with cost-effective and practical long-term solutions.

Using Scorecards to Measure Key Performance Indicators

In order to provide continuous, long-term performance improvement, our team developed “scorecards” as a means of repeatedly and consistently measuring, monitoring and reporting Key Performance Indicators (KPIs).

RESULTS

• Using standard deployments, performance moved from a “good enough” approach to an integrated part of everyday application improvement.

• Over an 18-month period alone, our team reduced workload demand by 16.2 percent, resource consumption by 21.5 percent, and database access by 22.4 percent. Our team continues to monitor our client’s workload and recommend areas for improvement.

• Developing the scorecard and ensuring both technical and non-technical management understood what it showed has become an invaluable tool by clearly communicating targets for improvement and highlighting areas of the database and/or application that are suspected as causing performance issues. Using metrics from the Scorecard, our client has made positive changes to the database structure, application and database parameters, as well as restructuring many SQL queries—all without creating any changes to the applications.

• After implementing database structure changes and application queries and control parameters, we were able to reduce database and application changes from an average of 2+ hours down to on average of about 20 minutes. As a result of our parameter changes, we are now able to deploy many application and database changes with zero perceived outages by the client’s business user base.