Manage your Maintained Temporary Indexes (MTIs)

Temporary storage and the database, Part 2

Keep temporary storage under control by tending your system’s MTIs

Maintained Temporary Indexes briefly explained

In part 1 of this series, we described the kinds of temporary storage that the SQL
optimizer uses. We showed how the QSYS2.SYSTMPSTG system view provides a high-level overview
of temporary storage across the system. There are five global buckets that directly
reflect usage by the SQL optimizer. Of these buckets, this article focuses on the one
labeled, *DATABASE DSI SQE MTI. This bucket reports the amount of storage
used across the system by maintained temporary indexes.

Maintained Temporary Indexes (MTIs) are indexes that the optimizer creates without
any user intervention. They function like any other index on the system. Internally, they
are radix indexes, just like permanent (that is, user created) indexes, and they provide the
ability to read rows in keyed order or to probe for specific values. Like any permanent
index, the actual size of an MTI is heavily dependent on the keys included in the index and
on the underlying data. They are actively maintained, meaning that any change in the
dataspace is immediately reflected in the MTI. In many cases, MTIs can be shared between
queries, just like permanent indexes. But they differ from permanent indexes in two
important ways: they use temporary storage, and they are managed entirely by the optimizer.
They are created whenever the optimizer needs them and deleted when the optimizer is
finished with them.

In simplest terms, there is only one reason for the optimizer to build an MTI: no suitable
permanent index exists to meet the requirements of a query. If you have the right indexes on
your tables, the optimizer won’t need to build an MTI. But, if we drill down into this
single basic reason, we’ll find a couple of distinct circumstances that cause the
optimizer to need an index. Understanding each circumstance—and whether it applies to your
workload—is crucial to effectively managing your usage of MTIs.

The first circumstance that might produce MTIs is when the optimizer decides that an MTI
can provide a more efficient implementation for a query than existing indexes can provide.
If the estimated cost to build the MTI seems reasonable to the optimizer, it will generate
the MTI and then use it to run the query. These MTIs are shared: they will be seen by and
can be used by other queries. The lifecycle of shared MTIs is tied to the existence of the
underlying dataspace and to the presence in the SQL plan cache of one or more queries that
use the MTI. If the dataspace is deleted or if all related plans are evicted from the plan
cache, the MTI will also be deleted. (Because they use temporary storage, MTIs also do not
exist beyond the current IPL.)

The second circumstance that might produce MTIs is when queries perform ordering or
grouping in an environment that uses a sensitive cursor or that has the ALWCPYDTA parameter set to *NO. When the query also has
a predicate (WHERE clause), the optimizer may build the MTI as a
sparse index, meaning that only those rows that match the predicate are included
in the index. Sparse MTIs have a lifetime that is tied to the query that created them. When
that query is hard closed, the MTI is deleted. While this behavior might seem beneficial in
preventing MTIs from accumulating, it also means that sparse MTIs cannot be shared across
queries. This can be a problem if a workload has hundreds or thousands of these queries.
Each query will build its own sparse MTI, filling temporary storage. This becomes even more
problematic if the applications do not close their cursors promptly. IBM support has seen
more than one critical client situation like this, which could easily have been prevented by
modifying the execution environment or by building a handful of permanent indexes.

Most of the time MTIs just work and require little attention from a database
engineer or a system administrator. MTIs are just one more tool that the optimizer uses as
it silently pursues its goal of giving your queries the fastest possible execution. Still,
there are two important reasons to monitor MTI usage on your system.

Monitoring
for temporary storage

The first reason for monitoring MTIs is because too many and too large MTIs can have a
negative effect on system capacity. Because MTIs are built on temporary storage, they take
up space in the system auxiliary storage pool (ASP), even when the associated dataspace
resides in another storage pool. As your system nears its temporary storage limits,
processes slow or stop and effective recovery might require an initial program load
(IPL).

Monitoring MTIs is especially important if your system allows users to run custom queries.
Is your workload limited to applications running a predictable set of queries, or can some
users generate their own ad hoc queries, in raw SQL or through use of a reporting or BI
tool? If ad hoc queries are permitted, your system is more susceptible to variation in MTI
usage. A query that is poorly written—or that simply does not have adequate supporting
indexes—might kick off the creation of an MTI. The more this happens, the larger your MTI
temporary storage usage will grow.

You can monitor your current MTI usage by consulting the QSYS2.SYSTMPSTG view and looking for the GLOBAL_BUCKET_NAME of
*DATABASE DSI SQE MTI. This will tell you how much
storage MTIs are using.

You can also evaluate how many MTIs are currently active on your system. Point in
time information is available from the Plan Cache tab of the IBM i Access Client Solutions
SQL Performance Center, under the Plan Usage Summary section. The Total Number of
Temporary Indexes Created entry counts the number of times an MTI has been
created since the last IPL. The Current number of Temporary Indexes entry
tells how many MTIs are currently active on the system.

Figure 1. Plan cache details from the SQL
Performance Center

You can find historical information about the number of MTIs with the performance data
available from IBM Navigator for i. Data is gathered by collection services. So, make sure that this is enabled on any system you want to
monitor. Within IBM Navigator for i, the following path will give you a graph as shown in
Figure 2.
Performance -> Investigate Data -> Database -> SQL
Performance Data -> Collection Services -> Maintained Temporary Indexes (MTIs).

Figure 2. MTI graph from IBM Navigator for
i

Note: The MTIs Deleted (shown in Figure 2) is not accurate in releases prior to IBM i
7.3.

By looking at data across multiple days and weeks you can get an idea of the baseline
behavior for your system. The bar graph shows MTIs as they are created and deleted, and the
line graph displays the total number of active MTIs. Because the size of MTIs might vary
from workload to workload, there is no correct or optimal number of MTIs
to achieve. But if your system is running low on temporary storage and you see that *DATABASE DSI SQE MTI is a large contributor, this graph is a good
place to start. The performance data helps you understand and identify spikes in MTI usage
that correlate to increased storage usage. Once you identify a time interval with an
anomalous number of MTI creations, you may then be able to identify certain jobs or
applications that triggered the MTI activity.

Monitoring for optimal query performance

The second reason for monitoring MTIs is that they can be a clue that the optimizer is
running under unnecessary constraints. This constraint can be a cursor sensitivity or an
ALWCPYDTA setting that is overly strict. It may also be that the optimizer does not have the
permanent indexes that it needs to do its job well. Addressing these constraints in depth is
beyond the scope of this article, but the strategies and resources in the following sections
are a good place to start.

Drilling down for
details

After determining that your system has a problem with over-use of MTIs, a good place to
start is the Index Advisor. This is because index advice is generated every time an MTI is
created or used. (Advice may be generated under other conditions too, but those are not of
immediate interest here.) The following steps show you how to use IBM i Access Client
Solutions (Index Advisor to find the most frequently used MTIs. (Make sure you’re
running the latest version of IBM i Access Client Solutions. See the “Related topics” section at the end of
this article. IBM Navigator for i shows similar information in the Database -> All Tasks -> Health and Performance -> Index Advisor path.

Within IBM i Access Client Solutions, select your system and click the
Actions. Then, under Database, click
Schemas.

Figure 3. IBM i Access Client Solutions

On the menu bar, click Actions -> Index Advisor -> Advised Indexes.

Figure 4. Schemas window within IBM i Access
Client Solutions

Within the list of advised indexes, the MTI Used, MTI Created, and MTI Last Used columns
will help you determine which schemas and tables are most frequently using MTIs. If the
performance graph (shown in Figure 2) is showing spikes in MTI creation, finding correlated
times in the First Advised for Query Use or the MTI Last Used columns may help you identify
new or changed workloads that have begun using MTIs.

Figure 5. Index Advisor

Note: In the Index advisor (shown in Figure 5), some of the columns have been hidden for
clarity.

If this is your first time looking at the Index Advisor, you may find yourself overwhelmed
by the amount of information it displays. Because the Index Advisor is continually
accumulating advice as queries run, it is possible to have multiple years’ worth of
data to dig through. Helpfully, there are filtering options available to narrow down the
list (for example, certain time intervals). A better option, if you don’t need the
index advice right now, is to clear the index advice (click the Clear All Advised
Indexes… menu option shown in Figure 4) and then to let the advice accumulate
over several days or weeks, as appropriate for the system’s workload. This gives you a
clearer picture of how your system is performing now and a more accurate picture of the MTIs
that are affecting your current configuration.

The system does not provide details on individual MTIs, but you can see the total size of
all MTIs associated with a specific table. Right-click the Index Advisor entry naming the
table you are interested in and click Table -> Work
With -> Indexes. At the top of the list, above
any permanent indexes belonging to the table, you may see a line summarizing the MTIs.
(Remember that the index advice is a historical record and not a snapshot of the current
system state. This means you may sometimes find MTIs listed in the advice but see no
corresponding MTIs on a table.)

Figure 6. The size of all the MTIs associated
with QSMART.BATCH_MONITOR

Note: Some of the columns in Figure 6 have been hidden for clarity.

Combining this information with the MTI usage information and the historical graph
described earlier can help you decide which MTIs are the most egregious consumers of
temporary storage.

Managing MTIs

Because MTIs are entirely managed by the optimizer and have no user-accessible controls,
reducing MTI temporary storage usage is largely a matter of indirectly influencing the
optimizer away from MTIs. Remember that the optimizer only builds MTIs when it can’t
find a better way to do its job.

For example, you cannot explicitly drop MTIs (without dropping the underlying table—rarely
a feasible solution.) But you can provide a permanent index that can take the place of the
MTI. Index Advisor makes this easy to do by right-clicking the line of interest and clicking
Create Index. The next time the optimizer processes a query against the
underlying dataspace, it will see the permanent index and use this instead of the MTI. As
the MTI falls out of use, it will eventually be deleted. However, note that in the case of
shareable MTIs “eventually” may be a relatively long time. In the meantime, you
will be paying the storage cost of both the permanent index and the MTI. Permanent indexes
should not be considered a silver bullet for a system that is at or near capacity.
In such critical conditions, the best strategy is to end or hold the workloads that are
generating the MTIs. You can use the information gathered with the steps above together with
other system data to identify and gain control of these jobs.

What this ultimately means is that the best time to recover from an MTI storage consumption
problem is long before it happens. A well-considered indexing strategy combined with SQL
best practices will go a long way to eliminating an over-reliance on MTIs. You can find more
information about optimization settings and index advice in the database performance section in IBM Knowledge Center and by consulting the always
useful Indexing Methods and Strategies white paper.

Solve a scenario

Before we end this article, let’s put these pieces together in an example
scenario.

At 4:00 PM you receive an alert that your production system is reaching a critical level of
temporary storage. You quickly connect to the system and query QSYS2.SYSTMPSTG. The
*DATABASE DSI SQE MTI is clearly the largest bucket, and it is bigger than what you’ve
ever seen before. Your next stop is the Performance view of IBM Navigator for i where you
open the graph for Maintained Temporary Indexes. You see a small but noticeable upward tick
in the MTIs created beginning about 2:00 PM. Moving over to Index Advisor, you scan the list
for MTIs first advised around 2:00 PM. There are several MTIs, and they all cover columns
belonging to your ERP application’s main fact table. You’ve spent a lot of time
creating good indexes for this table, so it is surprising to see index advice here, but you
notice that the advised keys are for columns that you don’t normally see together. To
confirm that these are really the source of your storage problem, you show indexes for this
table. The top line shows 20 MTIs totaling 150 GB in storage on your system, which is enough
to push your temporary storage over the critical line.

Now you need to figure out who or what is causing these MTIs to be built. This requires a
trip to the SQL Plan Cache statements, available from the IBM i Access Client Solutions SQL
Performance Center. There, you can add filters for statements that ran after 2:00 PM and
that referenced the fact table you identified earlier. That’s still too much to dig
through, so you also filter for statements for which indexes have been advised. That narrows
it down to a little over 20 queries, all of which have been run by the same job and user.
With this information in hand, you track down the user behind these queries. You find that
the user is trying out some analytics reports over the ERP data. But unknown to the user,
the reporting tool is running with a sensitive cursor and is leaving the cursor open as the
user opens a new browser tab for each report. This is the source of the MTI problem.

The first thing to do is to convince the user to stop running new reports, and (if
possible) close the existing ones. Next, (if possible) you change the cursor sensitivity to
asensitive. Then you can determine whether these queries will be run frequently
in the future. If so, the best plan may be to identify common sets of keys and create
permanent indexes to cover these keys.

Maintenance-free
MTIs

Now that you understand how and why MTIs appear and what tools you possess to manage them,
you’re better equipped to keep ahead of your system’s indexing needs. This in turn
means better use of your system’s temporary storage resources. With the right
understanding, you can help guide the IBM Db2® for i SQL optimizer as it works to make
your system run as efficiently as possible.