In-Depth
Five Steps to Performance Intelligence
Performance intelligence represents a new practice for database and application performance management
By Don Bergal
Historically, tuning IT application performance for the end user has been largely a guessing game. This is both surprising and unacceptable considering the relentless focus IT organizations place on cost-efficiency and productivity. Until recently, an organization's DBAs needed to collect large volumes of statistics and make trial-and-error changes to tune both applications and databases. This is prone to errors, consumes valuable resources, and delays benefits to the end user. To address this, DBAs are now looking beyond traditional server management and monitoring tools that deliver only "server-oriented" statistics. There is a need for analytics and methodologies that can translate into measurable and concrete benefits to the end user.
Current thinking, especially by leading consultants and senior managers responsible for large scale business performance management (BPM) initiatives, recognizes that the same transparent BPM practices proponents have been advocating should be available to DBAs. The introduction of performance intelligence brings a renewed focus on performance-tuning practices that are directly tied to end-user service levels and improvements in operating efficiency. This article provides an outline of five steps for performance intelligence that will help your organization operate more efficiently, reduce costs, and impress your boss, peers, and colleagues.
Step One: Capture and Store the Data
Just like any other business intelligence or performance management initiative, the first step is to begin to capture data to perform analysis. With storage being relatively inexpensive, it is recommended that a separate data mart be used to capture database, server and application performance data. Recognizing that there are several different dimensions of performance data that can be collected and measured, we recommend you collect at least 10 data attributes and dimensions to understand performance.
The more granular the data captured the better. To put it in perspective, if you are able to take measurements of each step of the database in processing a SQL statement, you may collect millions of data points, each representing a delay associated with an individual user request.
One critical data point is the measurement of "wait time" in the system command statement request. Wait time is captured as the measure of time it takes for specific commands to execute between databases and the applications that depend upon them. Log the Wait time associated with each operation, specific to individual users and programs, and this translates into an understanding of the delays in end-user service.
Step Two: Prepare the Data
Performance intelligence applies BI analysis techniques to the database domain. Now that your data warehouse has the performance data points in a multi-dimensional cube, prepare the data for analysis. Capture between 30 and 90 days' worth of system data for analysis. Although 30 days is sufficient, 90 days is better. For example, there could be a monthly process such as a batch payroll that is causing system bottlenecks that can only be spotted if multiple months of data are available for review. With this "performance" data captured, you can study the correlations of specific data points and identify trends across SQLs, sessions, users, programs, and more.
Step Three: Analyze Your Database Performance Data
Now that the data is collected and organized, it can be analyzed from the perspective of the end user and determine where the system hang-ups are. The rich pool of data and analysis points gives DBAs the ability to quickly identify where problems are and where performance improvements can be made. This begins with simple analysis of the historical data and understanding where activity spikes and bottlenecks are. More insightful analysis includes the ability to evaluate trends over time and pinpoint reasons for changing conditions.
Performance intelligence is distinct from traditional analysis of focusing on the server state at a given point in time. Performance intelligence focuses on the server state and how the data is changing for the entire database ecosystem. Performance intelligence was created to enable the IT staff to be cognizant of anomalies and be prepared to react to dynamic conditions.
Step Four: Share Your Analysis and Present Solutions
Performance intelligence allows DBAs to justify improvements and costs, regardless of where the problem resides. Within any organization, there will be groups and applications that do not fall under the jurisdiction of the DBA. With responsibility for databases and the performance of the applications that rely upon them, DBAs need to quickly identify those problems and present solutions. With performance intelligence, DBAs are well positioned to address each group, show the rationale, and resolve system efficiencies before they impact users and overall business.
Performance intelligence allows DBAs to be proactive with peers and complementary IT groups. When presenting system problems, provide performance intelligence data, analysis, and reports along with highlights of suggested remedies.
Step Five: Optimize, Implement, Repeat
Performance intelligence is more than a technique to gather, analyze, pinpoint, and solve performance problems. t is a system-wide approach that allows DBAs and IT groups to refine your applications, optimize their performance, and effectively implement changes. A best practice is to keep a log of the system's health and performance and monitor the impact that performance intelligence has over a year.
Conclusion
Performance Intelligence is a method of analysis that identifies the most important problems in IT systems and their root causes as it relates to the end user. By analyzing millions of granular data points and identifying which issues have the most impact on the IT end user, companies can proactively solve performance problems related to delays with database tuning rather than spending hundreds of thousands of dollars on hardware and software upgrades.
As you examine the data points along multiple dimensions, identify trends and correlate events that will expose the true conditions impacting end users. A successful program combined with the performance intelligence data to back up the improvements and impacts you make, will have a great impact on your organization, and perhaps your career.
The Questions PI Answers
Developing a Performance Intelligence framework enables the DBA, IT management and development team to look for, spot and answer difficult system and performance questions. Performance Intelligence can help provide answers to questions from various stakeholders in the IT value chain. For example:
Questions from DBAs
- How often does this SQL run (i.e., every Friday at 9am and 4pm)?
- When was it slow before?
- Is it always for the same reasons?
- What other SQL's are running when it's slow?
Questions from Developers
- Which code changes are now affecting database performance?
- Are there SQLs running at times I did not know they were going to run?
- Which components of my code interact with other applications to create problems?
Questions from Managers
- Which user communities are being affected by database bottlenecks?
- Have system changes impacted key customers?
- Has the DBA team demonstrably improved application service this month?
- Am I in danger of violating an SLA today?
Don Bergal is the chief operating officer at Confio Software. He is responsible for overseeing all of the company's sales, marketing, product management, and business development initiatives. Don has over 15 years experience in the software, services and data communications industries. He earned a BS in Engineering from the University of Michigan and an MBA from the Harvard Business School. You can contact the author at coo@confio.com