DBA Cockpit for SAP Adaptive Server Enterprise – Screen Missing Statistics

Introduction

In my previous article DBA Cockpit for SAP ASE – New Screens and Enhancements, I introduced a new screen called Missing Statistics for the DBA Cockpit for the SAP Adaptive Server Enterprise. Missing Statistics can be a useful indicator to improve and to optimize the query performance. The more accurate information about the data and the database the optimizer has, the better the achieved performance. However, capturing missing statistics will not always lead to different or better query plans and comes with a cost, because it consumes system resources such as CPU, buffer pools, sort buffers, and procedure cache. In addition to it, enabling the server to capture missing statistics is primarily intended for a development environment. Due to performance and stability implications capture missing statistics is not intended to be used in a production environment. At least, it should be carefully considered when it is used in a productive environment.

Overview

In this article, I would like:

• to provide more information about Missing Statistics in general,
• to introduce the new screen Missing Statistics in the DBA Cockpit for SAP Adaptive Server Enterprise in-depth,
• to explain how to analyze missing statistics and,
• to provide recommendations for additional statistics.

Missing Statistics

There are two types of statistics: Object Level Statistics and Column Level Statistics. In this article, we will solely focus on Column Level Statistics. Those statistics describe the distribution of values in the column. It consists of the column’s histogram and density values. It is updated when an index is created or an update statistics command is run. In general, Column Level Statistics needs to be updated from time to time.

How to Collect Missing Statistics

Missing Statistics can be collected with Capture Missing Statistics. Capture Missing Statistics stores the data in the system table sysstatistics. A detailed description how to collect missing statistics can be found here.

Analysis of Missing Statistics

The new screen Missing Statistics is designed to help you to investigate a negative change in performance quickly and to determine when to create additional statistics or when to update statistics. After Capture Missing Statistics is enabled, you will see a single column or a list of columns which were counted as missing. For example, the column TASKNAME of the table /BDL/TASKS were counted 1860 times as missing.

Figure 1: Missing Statistics for Column TASKNAME

The fact that this column was missing 1860 times doesn’t mean that you need to update statistics for this column. It is required to investigate it further. To start your investigations, you can check the Cached Statements or Top SQL Statements screen whether you find a query related to it. Those screens are available in the Performance Menu > SQL Statement of the DBA Cockpit for SAP Adaptive Server Enterprise. To investigate the Missing Statistics further, you will also find more information related to the affected table when scrolling down to the Details screen of Missing Statistics. The Overview screen Missing Statistics and all Features related to this screen are explained in my first article here. In this article, will focus on the details screen of Missing Statistics.

Key Metric – Data Change

Data Change is an indicator for how much data has changed in the data distribution since update statistics last ran. In particular, this indicator provides the number of inserts, updates, and deletes on a  given object, partition, or column. It is summarized as a percentage of the total number of rows in the table or partition (if the partition is specified). This value can be greater than 100 percent because the number of changes to an object can more than the number of rows in the table. Data Change is used to estimate the cost of an additional index.

• If there is no or less data change, additional index is inexpensive.
• If there is a lot of data change, additional index is expensive.

Figure 2: Data Change for the affected Table

Other Useful Metrics

Number of Rows

Number of Rows is used to estimate the cost of additional statistics.

• It is more feasible to create additional statistics for smaller tables than for larger tables.
• If a table is empty, then it is less feasible to create additional statistics.

Figure 3: Number of Rows for the affected Table

Forwarded Rows

Forwarded Rows is used to determine forwarding record pointer on the original page to point to the new page.

• Forwarded records are rows in a heap that have been moved from the original page to a new page.
• Updates can cause forwarded records if the updated data does not fit on the page.

Figure 4: Forwarded Row for the affected Table

Data Page Cluster Ratio

Data Page Cluster Ratio is used to estimate how much fragmentation is in the table and the cost of using a large I/O:

• Perfectly clustered means that pages can be read sequentially without jumping back and forth among extents. A large I/O will be more efficient.
• Less clustered means that pages cannot be easily read without jumping back and forth among extents. A large I/O will be less efficient.

Figure 5: Data Page Cluster Ratio for the affected Table

Large I/O Efficiency

Large I/O Efficiency is used to reduce access time to and from disk into memory.

• High value means that more useful pages are brought in by large I/O. It is an indication that the index doesn’t need to be recreated.
• Low value means that less useful pages are brought in by large I/O. It is an indication that the index should be recreated.

Figure 6: Large I/O Efficiency for the affected Table

Space Utilization

Space Utilization is used to compare the expected minimum number of data pages to the current number of pages.

For low values of space utilization is low, a run reorg rebuild on the table or drop and recreate the clustered index might reduce the amount of empty space on data pages, and the number of empty pages in extents allocated to the table.

Reserved Total is used to determine the allocated space.

Figure 7: Reserved Total for the affected Table

Logical Reads

Logical Reads are used to count the page read request for each table and index included in a query.

Figure 8: Logical Reads for the affected Table

Physical Reads

Physical Reads are used to examine how the query executes. The first time a query is run, the number of physical reads will be generally high, because the required pages are not in cache.

Figure 9: Physical Reads for the affected Table

Recommendations for Additional Statistics

Statistics should be an accurate reflection of the data in the table. If data is changed often, statistics may need to be updated. Based on the decision to run update statistics on a set of key parameters, ask yourself the following questions:

• How much data changed since update statistics was last run?
• Are there sufficient resources available to run update statistics?
• When does update statistics not block the system?
• When is the best time to run update statistics?

Conclusion

This was a more in-depth overview of Missing Statistics for the DBA Cockpit for SAP Adaptive Enterprise. If you wish to read more about Missing Statistics, please visit this SAP Help website. What do you think about Missing Statistics in general? What do you think about the Screen Missing Statistics? Let me know in the comments. If you wish to read more articles related to the DBA Cockpit for SAP Adaptive Server Enterprise, please consider following me. To stay current about SAP Adaptive Server Enterprise, please consider following the topic page here. Or you can also read other blogs related to SAP Adaptive Server Enterprises here. If you have a question related to this topic, you can post it here.