Zooming into the performance trend
In the previous post we saw how to measure performance with trends and workloadtables. Before we go into some complexities and refine this method, let’s see how this can help us with performance problems by detecting changes in the workloadpattern.
From trend to comparing workloadtables
This is a daily trendgraph of a system with a weekly businesscycle. Every weekday has about the same workload, meaning that we can only compare mondays to mondays, fridays to fridays etc. I pick out the wednesdays here as there seems to be a change in the TET number: the second wednesday is larger due to worse performance or more workload. Or a combination of both, we don’t know yet. Now let’s have a look at the 2 workloadtables of those wednesdays and see the differences. Which SQL’s eat more time, and why? In order to spot that quickly I use a ranking report. A sort of Top of the Pops for SQL statements:
In the first column we can see is the workunit, in this case identifiers for SQL statements. The second column shows how many places this particular SQL statement has moved up on the chart. So SQL_id 18 has moved 12 places up in the workload table, grabbing 10.23 percent points more of the total execution time. This can be due the fact this SQL is much slower or ran faster, but we now have a suspect we need to focus on. Looking at this ranking list you may also have a look at SQL_id 946, as this moved 18 place up, but having only .34% point more execution time you can safely discart this. The next step is to focus on the SQL18 and create a trendline. If the change is due to performance you need to tune, and if it is because of more workload you may check this with the business.
Wrapping it up
We’ve now seen how to make a trend on performance and how to zoom to the workloadtable. A tuning target can be found of which we need to gather diagnostic data. Before we go into the creation of such workloadtable and SQL trendlines, we need to be aware of the type of workload you are dealing with. And that is the subject of the next post.
