SQL Performance Center's Early Christmas Gift

If you have kids, there’s a pretty good chance they’ve asked you at some point to open Christmas gifts early. The anticipation and excitement associated with their gifts makes it difficult to wait until Christmas to open gifts. Why wait, when you can do it now!

 

Speaking of new and exciting things, the 1.1.9.7 version of IBM i Access Client Solutions (ACS) includes some significant enhancements to the SQL Performance Center. The good news is that you don’t have to wait until Christmas because this update is available now!

 

These enhancements enable you for the first time to perform all your SQL performance analysis and tuning using just the SQL Performance Center. In prior versions, the ACS Schemas tool had to be used any time that you wanted to use the Index Advisor. As you can see in the figure below, a launchpad (i.e., green box) has been added to the top of the SQL Performance Center that allows easy access to index advice.

The red box in this figure highlights that other data related to SQL performance can also be readily accessed from the launchpad.  The Maintained Temporary Indexes option makes it simple to find all the temporary indexes on your system using the MTI_INFO service that I’ve highlighted in the past. The Active Query Info option streamlines the process of identifying active queries on your system by returning the results from the ACTIVE_QUERY_INFO service which also has been recommended on this blog.

The performance data returned by these two new options can be sorted by clicking on a column.  However, the output data cannot be filtered at all. To counteract this limitation, the output for these two new interfaces (as well as other ACS interfaces) has been enhanced to include a new SQL widget button highlighted in the figure below. This SQL widget option allows you to easily access the SQL statement used to return the output. This SQL statement text can be copied into a Run SQL Scripts window. Then, you can add a WHERE clause to the query to return a filtered set of performance data (e.g., by job or by table).

The updated SQL Performance Center also has a more integrated solution when it comes to analyzing the Plan Cache entries at an ASP (auxiliary storage pool) level. A couple of years ago, I wrote about the need to consider Independent ASPs when reviewing Plan Cache entries or creating a Plan Cache Snapshot. The default setting was to only show queries run against tables created in the System ASP.  The default setting is still the same with the latest SQL Performance Center, but now the ASP selection only appears when you are accessing an IBM i server that has an Independent ASP attached. When an active Independent ASP exists, the ASP (i.e., Database) selection is displayed next to the button to display statements in the Plan Cache.

Notice that the first figure above had no selection option next to the Plan Cache Statements button. I think this visual clue is a great reminder to consider which ASP you want to focus your query performance analysis efforts on.

Now that you have a better understanding of the SQL Performance Center enhancements, I hope you agree that Christmas did come early with this ACS update. I also hope that your corporate IT policies allow you to access this gift right away:)  Merry Christmas and I look forward to talking to you in 2025!