Earlier this year, we presented a SQL performance webinar which discussed management of sub-optimal SQL, access path regressions, new applications, new CPU peaks, and more. We have since received these questions:
- “How do I measure how my performance has improved?”
- “How do I know what that improvement has saved my business?”
Both questions are relatively simple to answer, if you have made some preparations; otherwise the answers are almost impossible to find.
How do I measure improved performance?
The first step in measuring performance improvements is to have proof/documentation about performance before you started improving it. Everyone should have a performance database of some sort. It doesn’t need to be the most complex database on the planet, but it does need to allow you to compare performance at two distinct points in time.
Db2 can make it tricky to compare SQL statements directly. Although program names typically stay the same, every change to application code can change the statement number that Db2 uses to track each individual statement. If this statement number changes, it can be difficult to compare the performance of the new statement to an earlier version. The long-term solution is to tag every SQL statement with a static number using the “QUERYNO” clause. If the clause is omitted, the number associated with the SQL statement is the statement number assigned during precompilation. This cannot be implemented overnight, but once done, you can track any SQL statement for performance changes regardless of what application changes have been made – it’s QUERYNO will be consistent. Now it’s possible to track the performance impacts of major changes (such as a new application version or even a new release of Db2) as well as minor changes (tuning individual SQL statements). One customer told me that after they made static identification of SQL statements part of their standards, they were easily able to analyze performance changes even down to the SQL statement level.
Without this level of granularity, it is still possible to track the performance improvements at the application/program level. So, if you don’t have static statement numbers assigned to all SQL you can simply compare the performance of named programs between two points in time
Also, if you suspect you are seeing a performance degradation after moving to a new version of Db2, one of the first things IBM will ask you for is documentation that shows the prior performance as well as your reported poor performance statistics. If you have a complete performance database, then you have those answers readily available. But, if you can’t prove what the performance was before you migrated, IBM is unlikely to be able to help you.
How much did my business save by the performance improvement?
For this second question you may need to involve your colleagues that handle the license payments to IBM (if you are paying MLC charges based on the rolling four-hour average). This may be your capacity planners, your systems programmers, or some other group. These are the people who know which components are making up the CPU usage, when during the month your peak CPU consumption is happening and the value of saving certain percentages at certain points in time. They should be able to advise on what sort of financial saving could be expected from your savings in CPU consumption.
Unfortunately, saving CPU at random points during the month may not actually translate into any financial savings at all, so it is important to understand where any tuning activity could make a difference. Simply improving performance can be addressed by tuning ANY SQL statement, but tuning to save license charges needs to be focused on the SQL that is consuming the most chargeable CPU. This will be SQL that is executing in your 4-hour average peaks.
To learn how BMC can help you improve SQL performance, please visit our website and be sure to look out for future Db2 webinars. Pencil in December 6 on your calendars for “Intelligent Automation is the Key to Your Digital Transformation” – more information to follow.