insure/SQL TUNING METHODOLOGY FAQ

The insure/SQL toolset (insure/INDEX, insure/ANALYSIS, insure/MONITOR and insure/RESOURCES) employs a tuning methodology that allows you to maximize the overall performance of your database by helping you focus your time and energy on the data access activities that matter most on your system. Whether you are planning for data warehousing projects, debugging existing performance problems, or just trying to get the most out of your system, the methodology employed by insure/SQL can help you.

Why tune your database and queries?

If your end users can glean more critical information faster than your competitors with little or no additional cost to your company, you have a competitive advantage. Business intelligence and data warehousing initiatives done correctly are taking companies into an era in which business analysts can make faster and more precise decisions. Tuning the access to your data can give you an additional advantage if you have already deployed a data warehousing solution, or help level the playing field if you have not.

What types of data access tuning can you do?

Data access tuning really involves two types of work, individual query tuning and system-wide.

Individual query tuning is done by analyzing each implementation step of a single query and only taking action when you can identify a step that you can improve. On the iSeries, this is typically done by running a query under debug and interpreting the query optimizer debug messages in the iSeries job log or by using output created by the iSeries database monitor.

System-wide query tuning is tuning by:

1. Sampling all query activity on the system
2. Ranking the activity on the system by some criteria (typically, either by impact to the system
     or by using end user response time)
3. Individually tuning highly ranked activities
4. Repeating the process

Historically on the iSeries, we have done virtually all tuning using the individual query tuning methods. Why? It is easy to identify the single long running queries because our users complain about them. Another reason we have always individually tuned our queries is because the tools that are available for doing system-wide query tuning either have been unavailable or have been difficult to use. While system-wide tuning is seldom used, it provides the most benefit to your environment, if the tools are available to facilitate the analysis.

How do you know what to tune?

The most effective tuning requires tools that help you do both system-wide and individual query tuning. The tools should help you identify what data access activities need to be focused on, and then either provide you with insight as to what should be done to improve the access, or take some predefined actions to improve it for you.

At a minimum, the tools used for tuning should help you identify:

· What queries provide the longest delays for your end users
· What queries have the largest impact on your system
· What data is accessed most often within your system and when
· Which users or groups of users are accessing your data most frequently
· How users are getting the information that they want to see

Given these pieces of information, you can focus on the queries, objects and users that will give the most return for the changes that you make. The actions that you take to improve your data access performance may vary based on:

· The transactional uses of your data that is being queried
· The tools being used to run queries on your system
· The level of control you have over the queries being run on your system
· The priority that query activity should have on your system
· The amount of disk resource you have available

On the iSeries, the activities that have traditionally had the largest positive effects on data access performance are:

· Creating new indexes that provide needed access paths for the iSeries Query Optimizer
      to implement queries in the most optimal ways
· Deleting one or more existing indexes that prevent the iSeries Query Optimizer from examining
      better access paths and selecting the most optimal implementation of a query
· Changing a query to a form that allows the iSeries Query Optimizer to better determine
      the most optimal implementation of a query
· Making program compilation changes to influence the iSeries Query Optimizer's
      implementation of a query
· Making program or system changes to influence the operating system's caching and
      parallel execution algorithms
· Creating a periodically refreshed summary table to simplify and optimize access
      to the same information
· Preventing estimated long running queries from being run

Specifically, how does insure/SQL help me?

insure/SQL on the iSeries gives you the ability to develop, deploy and manage query activity on your iSeries with far less effort than any other method. The suite of tools has many features that can be used for several purposes depending on your need.

FOR THE PROGRAMMER

SQL Program Design and Implementation:

Programmers writing SQL to generate and retrieve new data on the iSeries will start by designing the layout of their program and possibly by adding new database files. While programmers are doing their design work, they should consider how the iSeries Query Optimizer would implement their queries. In a best-case scenario, the programmer should experiment with the Visual SQL Explain feature to understand how each query will be implemented and understand its impact before finalizing the program design. The information that the feature provides can give valuable insight about needed indexes or may influence some design choices that otherwise may not have been considered.

System-Wide Testing, Deployment and Performance Tuning:

Once the programmer has completed the design and implementation of the project, system-wide tuning should be performed either with mock system loads or early in the deployment phase. To use insure/SQL for system-wide tuning, start by using the usage tracking feature of insure/INDEX or insure/ANALYSIS to sample representative time periods of activity on your system. The usage tracking feature allows you to sample activity either ad hoc or on a scheduled basis. If you choose to track on a scheduled basis, you can sample at specific times of the day, month or year.

After you have gathered enough data to be representative of a normal workload, you should run insure/INDEX to identify and create recommended indexes. As you gather more data, the index recommendations and their associated weights become statistically more accurate. Before acting on the index recommendations, you should consider what impact your changes will have on your transactional workload. insure/INDEX records which indexes were created using the tool, for easy removal or integration into a source control system.

You should perform more sampling into a new profile after each set of changes in order to understand the impact that your previous changes had and to discover potential new improvements. After you have created the most beneficial indexes and understand their impact, you can use the reports within insure/ANALYSIS to:

· Identify queries that have the longest delays for your end users
· Identify queries that have the greatest impact on your system
· Identify the most commonly run queries ranked by their cumulative impact
· Identify queries that may have selected a poor implementation because the
      iSeries Query Optimizer timed out choosing an implementation
· Identify the users generating the most query activity

The reports within insure/ANALYSIS can take you down several paths. You may choose to analyze individual SQL statements, using Visual SQL Explain to fine-tune specific high impact statements. You may choose to create summary tables for commonly run queries. You may use the Database Explorer feature to help you understand why specific queries are timing out. You may choose to use insure/RESOURCES policies to limit the impact of certain users or groups of users.

FOR THE SQL QUERY DESIGNER

Query Design and Implementation:

Query designers writing new queries to retrieve data on the iSeries will start by understanding the database and the data they intend to access. Typically, the query designer would use the DSPFD, DSPFFD, DSPDBR operating system commands to understand characteristics and relationships within their database. Database Explorer will help the query designer by showing programmer-specified creation detail associated with the different components of the database as well as relationships between the files and indexes within the database. Database Explorer allows the designer to see index search order, data types of fields, and existing logical files and SQL views using a quick and easy, point and click interface. The designer will use the information from Database Explorer to assist coding their SQL in the Visual SQL Explain editor or other favorite tool.

Visual SQL Explain provides all the functionality the query designer needs to design optimized SQL queries. Visual SQL Explain can import SQL query information from the most common applications that query designers may use, like Query/400, Query Manager, source files, or simply from a PC text file. It will help the query designer understand performance characteristics of an individual SELECT statement by displaying the iSeries query optimizer's implementation of the statement in a graphical and easy to understand way. The query optimizer debug messages that query designers have traditionally used are also provided for those power designers that prefer the traditional methods. The feature is designed to allow the query designer to quickly iterate through the query design process while continually providing easy to understand implementation feedback. As changes are made to the query, the immediate implementation feedback allows the designer to proactively see how their changes affect the query optimizer's implementation. Without Visual SQL Explain, the query designer is forced to analyze the query's implementation as a separate step…one that would often be forgotten or bypassed until end users complained about performance.

Visual SQL Explain surfaces the iSeries settings that affect the query optimizer's choices. The settings are easy to manipulate allowing the designer to quickly experiment with different settings, again using a quick and easy, point and click interface. By experimenting with the available settings, the query designer can learn how the iSeries settings affect a query in their environment. It allows the query designer to ensure that the settings are optimal for each query and each environment.

Many times the query optimizer will choose to build one or more temporary indexes to implement a query. If the query requires a temporary index, the time required to build the index is a majority of the overall time needed to run the query. By creating a permanent index to replace an index that the query optimizer chose to build temporarily, the time that was required to build the temporary index when the query is run can almost always be eliminated. Visual SQL Explain graphically surfaces the temporary indexes that the query optimizer believes are needed. If the query designer determines that temporary indexes needed by query optimizer should be created permanently, the wizard for creating indexes makes creating the index a simple process. The wizard starts the query designer in the right direction with default information gleaned from the query optimizer. It also allows the create index request to be done interactively, submitted for batch processing immediately, or scheduled at a specific time. The index create options give the query designer the flexibility to create the index with as small of an impact on the system as is appropriate for the environment and time of day.

Query Ongoing Maintenance:

Once the query designer has designed and tuned the query, insure/MONITOR and insure/RESOURCES can help the query designer understand query usage patterns and can be valuable for trouble-shooting any problems.

The insure/MONITOR and insure/RESOURCES products assist the system support group in maintaining control over queries and allow the group to monitor and diagnose the system in real time. With insure/RESOURCES policies, query users can be tracked and their work limited to prevent impact to more important production work. Because policies are enforced automatically, the IT personnel can spend time on issues yielding higher value than tracking down runaway queries.

With insure/MONITOR, systems operators can drill into individual jobs and see what jobs are running SQL or green screen queries (e.g. Query/400). This capability allows them to react to issues related to ad hoc activity more quickly and with more precision. The policies built into insure/MONITOR also allow IT to define when and where additional information is generated to automate the capture of messages, joblogs and query activity needed to troubleshoot applications or reports.

SUMMARY

The insure/SQL toolset provides a closed-loop approach to capturing critical performance data, do prioritized drill-down data analysis, recommend indexes, enforce end-user controls, and monitor jobs in real-time. These are the necessary components of a sound methodology to manage complex application and data intensive environments.

ROCHESTER, MN USA
www.centerfieldtechnology.com