csc-studentweb.lr.educsc-studentweb.lr.edu/swp/berg/document_sharing/article... · web viewit is...
TRANSCRIPT
Avoid SAP HANA System Surprises with a Standard Operating Procedure Checklist
by Dr. Bjarne Berg, VP SAP Business Intelligence, Comerit, and Professor at SAP University Alliance at Lenoir Rhyne University
Key Concept
Writing a standard operating procedure (SOP) checklist and creating periodic tasks lists for other activities can assure that you have a very high degree of business continuity in your SAP HANA system. SAP HANA is a highly scalable platform with a substantial amount of built-in fault tolerance at the hardware and software level. Although system failures are rather unusual, no amount of cleverness can totally prevent issues if the use is not monitored regularly and preventive measures are not taken well in advance.
Learning ObjectivesReading this article you will learn:
Learn what SAP tools are available to monitor your HANA environments and see what hardware options exists Get access to all of the 74 automatic HANA alerts and see when to schedule them and what to do when
triggered Learn how to keep you BW system on HANA as small as possible and how to periodically remove unneeded
data and internal files from your HANA system
There are many ways to install and operate your SAP HANA environments on premise. However, creating a daily, weekly, and monthly standard operating procedure (SOP) is a good way to ensure that the system stays well tuned and that potential issues are avoided. This is also known as the daily operations handbook. I explain what the different landscape options are and how you can start creating your own SOP for your data center.
The first decision you have to make when setting up your data center for your SAP HANA environments is to decide if you are going to place it on premise, as part of an outsourcing agreement, or in the cloud. The on-premise approach is currently the most common. It basically means that you will have to integrate the hardware into your current data center and possibly into an off-site data center if you are implementing a high-availability (HA) solution.
A major consideration for the on-premise approach would be to make sure that your hardware fits into your existing chassis, racks, power outlets, cooling plan, and the outlay of your data center. For example, many are not aware that some of the larger SAP HANA systems, such as Lenovo’s x3850 x6, require a four-rack unit (U) height in a data center, but if you use Lenovo’s x3950 x6, you need to double that size requirement (since that is basically two stacked 3850s). Other products, such as Cisco’s C880 M4 server, require a 10U height. Therefore, it is very important to decide what hardware deployment options you are going with. As of September 2015, the most common forms of certified hardware are shown in Figure 1 and described in Table 1.
1
2
Figure 1 Common SAP HANA hardware platforms for on-premise deployment
Scale-out
Scale-up
Business Suite
bullion S2 512-1536 GB 2 x 8890v3 x 2.5 Ghz x xbullion S4 1024-3072 GB 4 x 8890v3 x 2.5 Ghz x xbullion S8 2048-6144 GB 8 x 8890v3 x 2.5 Ghz x x
UCS B260 M4 512-1536 GB 2 x 4890v2 x x x xUCS C460 M4 128-3072 GB 4 x 8890v3 x 2.5 Ghz x x xUCS C880 M4 2048-6144GB 8 x 8890v2 x 2.8 Ghz x x x
Dell PowerEdge R930 128-3072 GB 4 x 8890v3 x 2.5 Ghz x x
PQ 2400 E/S/L 128-1024 GB 4 x 8890v3 x 2.5 Ghz x xPQ 2800B2/E2 128-6144 GB 8 x 8890v3 x 2.5 Ghz x xRX4770M2 128-3072 GB 4 x 8880v3 x 2.3 Ghz x x
CS-500 128-3072 GB 4 x 8880v3 x 2.3 Ghz x x xCS-900 1024-12288 GB 8 x 2890v2 x 2.8 GHz x x x
Hitachi CB520X B2 256-6144 GB 8 x 8880v3 x 2.3 Ghz x x x
FusionCube E9000 512-1024 GB 4 x 4890v2 x 2.8 GHz xRH5885H V3 128-3072 GB 4 x 8880v3 x 2.3 Ghz x x xRH8100 V3 128-6144 GB 8 x 8880v2 x 2.5 Ghz x x x
Flex x880 X6 128-6144 GB 8 x 8890v2 x 2.8 Ghz x x xx3850 X6 128-3072 GB 4 x 8880v3 x 2.3 Ghz x x xx3950 X6 512-6144 GB 8 x 8880v3 x 2.3 Ghz x x x
NEC Exp. 5800/A2040b 128-2048 GB 4 x 4890v2 x 2.8 GHz x x
Silicon Graphics UV 300H 256-6144 GB 8 x 8890v2 x 2.8 Ghz x xUnisys Forward! 4150-B 128-3072 GB 4 x 4880v2 x 2.5 GHz x xVCE UCS B460 M4 1024 GB 4 x 4890v2 x 2.8 GHz x
Target usage
HP
Huawei
Lenovo
Vendor System Memory(RAM)
Bull SAS
Cisco
Fujitsu
CPUIntel Ivy Bridge EX E7 15 Cores
(2014)
Max Number and Type
CPU Speed
Intel Haswell EX E7 18 Cores
(2015)
Table 1 Characteristics of the certified SAP HANA hardware options
Customer SAP HANA Admin and Support Responsibilities
As you start with your plan to write an SOP, it is important that you be aware of the normal support, install, and monitoring roles, as well as the responsibilities of SAP, your hardware vendor, and your own team. The normal support responsibilities can be summarized as shown in Table 2.
3
Area Task Hardware vendor Customer SAP
Hardware installation and health check xLinux OS installation xHANA platform installation xData source connectivity xAdding DB instances (MCOS) xSMD agent installation xHANA DB admin xThird party software installations xHANA system monitoring xHANA DB monitoring xBackup and recovery x"Bare metal" recovery xFirmware patching (x)* xLinux OS upgrades and patching (x)* xPeripheral components patching xHANA platform components updates & patching x
Support Issue resolution process (x)* x x* depending on support contract
Initial Setup
Operations
Maintenance
Table 2 Summary of key responsibilities
It is important to note that the responsibilities as outlined in Table 2 are based on an on-premise installation of SAP HANA and that no other support agreements are made with the hardware vendor, a cloud vendor, or outsourcing partners. Depending on how you write your support agreement with these vendors, some or all of the customer responsibilities may be assumed by these partners. The trick to making sure of what you are responsible for is to specify these activities in a service level agreement (SLA) if you are using other vendors to support your systems and landscapes.
There are also different cloud options that some companies might consider. For each of these options the responsibilities of the customer are significantly different. First, you can have your SAP HANA system and applications delivered as a software as a service (SaaS). Under this offering you can get software applications such as SAP Business Suite, SAP Business Warehouse (BW), and SAP Rapid Deployment solutions (RDS) as SaaS SAP HANA cloud solutions from several vendors who then take over all customer responsibilities for daily monitoring, support, and maintenance.
Another option is the platform as a service (PaaS). This is normally provided as a solution in which the database, operating system, connectivity, and hardware are supported by a cloud vendor, but daily operations and monitoring of the application are the customer’s responsibilities. Finally, the lowest level of cloud offerings is known as infrastructure as a service (IaaS). As the name implies, you are normally responsible for all tasks as shown in Table 2, except the hardware maintenance, which is then hosted in the cloud.
However, in this article I assume that the support is for an on-premise implementation; that the customer is assuming the normal support, maintenance, and monitoring roles; and that a cloud solution is not in place.
4
System versus Landscape Administration
There are several tools and procedures that should be developed that are different based on a system or landscape administration perspective. For example, for system administration you should leverage SAP’s HANA Administration guide that can be downloaded on help.sap.com. http://help.sap.com/hana/sap_hana_administration_guide_en.pdf This guide is maintained and updated by SAP on a release basis. It shows you how to use the SAP HANA cockpit (a Fiori launchpad application) and SAP HANA studio for the main system administration, the core functions of high-availability and disaster recovery, scalability (up and out), and security administration. It also details how to manage and monitor applications for data provisioning and custom applications built in the Extended Services (XS) framework.
You can also monitor the system through the database (DBA) Cockpit in Solution Manager and leverage the Trouble Shooting and Performance Tuning guide from SAP when issues arise. However, from a landscape administration perspective, you leverage the Technical Operational manual from SAP and the DB control center, as well as any respective application support for the systems you might be running. So, when you start developing your SOP or daily operating handbook, you should start by familiarizing yourself with these very important documents and tools and think about system administration as different from landscape administration. Table 3 shows the key SAP resources for SAP HANA system and landscape administration.
Area Tool Purpose Web Resource
HANA Administration guide
- How to use the HANA cockpit and HANA studio for system admin.- Core functions of high-availability, disaster recovery & scalability- Security administration- How to manage and monitor applications for data provisioning and custom applications built in the extended services (XS) framework.
tinyurl.com/AdminHana
DBA Cockpit for HANATool to manage system landscape connections and central management of DB configurations
tinyurl.com/DBACockpit
HANA Troubleshooting and Performance Analysis Guide
How to trouble shoot and fix DB performance issues and guidance on general optimization.
tinyurl.com/TroubleGuide
Multitenant DB GuideHow to monitor, setup and manage systems that have HANA multitenant DBs
tinyurl.com/HanaDBs
Technical Operations Manual How to operate and administrate a HANA landscape. tinyurl.com/TechOperationsSAP DB Control Center (DCC) Guide on how to use DCC to monitor HANA and other databases tinyurl.com/databaseCC
Landscape Admin
System Admin
Table 3 Key administration resources
SAP HANA System Monitoring Tools and Education [header 2]
You can also choose one or more ways to perform your system monitoring. For example, you can monitor system databases and also tenant databases (in MCOD/MCOS) Multiple Components One System – MOCS; and Multiple Components One Database – MCOD) by directly connecting to a database using the SAP HANA cockpit, the DBA Cockpit in Solution Manager, or through regular SAP HANA studio.
In SAP HANA studio in the administration perspective, you get access to most database and system information. There are several tabs that displays landscape, alerts (automatic scheduled monitoring jobs), performance statistics, disk volume information, configuration settings, overall system information, diagnostic files, and configuration of traces and trace files (Figure 2).
5
Figure 2 The SAP HANA studio Administration Console Perspective
Also, since Support Package Stack 9 of SAP HANA in late 2014, the enhanced SAP HANA cockpit is now a very interesting way to get access to a simple web-based monitoring application that shows you key statuses of your SAP HANA systems and databases.
As mentioned before, the SAP HANA cockpit is basically a Fiori launchpad site that you can also customize to show only the items you are interested in for daily operation monitoring (Figure 3).
6
Figure 3 Administration with the SAP HANA cockpit in Fiori
The customization of this application is a simple click-and-drag of the tiles (much like on your cell phone). You can also choose the refresh rate of the information in the SAP HANA cockpit. The application can run on a web browser and is therefore mobile and simple to deploy. The SAP HANA cockpit also has a Manage Databases app that allows you to monitor single and multi-tenant databases in SAP HANA.
As you click each of these tiles, a vast array of detail information is provided for your in-depth analysis and system monitoring. However, it is important to note that while the SAP HANA cockpit supports core administration of tenant databases (i.e., MCOS), SAP HANA studio and some command-line tools may still be required for key tasks for tenant databases. Frankly, the only minor drawback with the SAP HANA cockpit is that it may require additional licenses depending on what you bought with the initial license package.
At a higher level, the SAP Database Control Center (DCC) is also a Fiori application that allows you to monitor both SAP HANA and other types of databases from a central application. As you become more familiar with these tools, you probably will find it useful to start with one or two of these and choose the others as alternatives when you are stuck on a certain task. Most system administrators include SAP HANA studio and either the DBA or the SAP HANA cockpit for daily monitoring.
To start to learn about these tools, first download and study the guides outlined in Table 1. A five-day SAP course called HA-200 “SAP HANA - Installation & Operations” is available for experienced support staff as well as for beginners.
Solution Manager and Landscape Virtualization Management (LVM)
Many of the tools used for system monitoring are also used for database monitoring. First, you can conduct many of the individual database admin functions through SAP HANA studio and the SAP HANA cockpit from a web
7
browser. From there, you can make changes to the database system settings and also add users, privileges, and most standard database admin tasks. Also, just as you can for all SAP software, you can use Solution Manager for core monitoring, admin of multiple systems in your landscape, and as the backbone for Change and Transport System+ (CTS+) integration of transports between the systems in your landscape. Solution Manager can also be used to generate EarlyWatch reports periodically that show growth, use trends, and technical support information. You will also find the DBA Cockpit in Solution Manager (Figure 4). This tool allows you to monitor the SAP HANA database and exposes almost all the technical information you would otherwise find in the administrator console perspective in SAP HANA studio.
Figure 4 SAP HANA Admin and monitoring with the DBA Cockpit in Solution Manager
Solution Manager and the DBA Cockpit also support trace analysis, workload analysis, and exception analysis of SAP HANA databases. Most customers therefore find this tool invaluable when monitoring and managing SAP landscapes with both SAP HANA and other types of databases.
In addition to these tools, the LVM from SAP is also supported for SAP HANA (Figure 5). This tool allows you to conduct core operations of complex landscapes that are based on SAP HANA or non-SAP HANA servers. There is a standard edition of LVM that can be downloaded from SAP for free, and an Enterprise Edition equipped with more features requires a license before you can use it.
8
Figure 5 The LVM screen
When any of these administration and management tools are deployed, it is important that your support staff that is monitoring, maintaining, and operating an SAP HANA landscape have a good understanding of the capabilities of each of these.
Daily Operations SAP HANA Checklist
After you decide on your monitoring tool, download and study the available support documents in Table 1, and complete any of the other training methods you have selected, such as an SAP class, you are ready to start writing your SOP. The SOP should consist of daily operations, weekly jobs, and periodic upgrades and patches as supplied by SAP. In this section I look at the most common daily operations tasks that you will be doing.
While many prefer to have active or passive monitoring of systems, best practices are to have a combination of these. Passive monitoring usually means activating and scheduling some of the alerts available in SAP HANA studio. You can place thresholds on the alerts (i.e., when memory consumed exceeds a certain number of GB), and you can schedule how often the checks are performed on the database. When triggered, these alerts show up in the SAP HANA cockpit, DBA Cockpit, and SAP HANA studio in both detail and overview pages. Today, there are 74 standard alerts that come with the SAP HANA system (Figure 6).
9
Figure 6 SAP HANA alerts in SAP HANA studio
You can also set up email alerts if you have the system privilege CATALOG READ, the SELECT privilege on the _SYS_STATISTICS schema, and the system privilege INIFILE ADMIN. The first of these privileges is included in the standard SAP HANA role called MONITORING. This role can be assigned to non-system admin users. It allows other technical resources access to see what is happening inside the SAP HANA system without the ability to change anything.
There is also a list of historically executed alerts in SAP HANA studio, but be aware that this list is restricted to the last 1,000 occurrences from the last 30 days. Also, when an alert is triggered, a priority is assigned by the system. In general, there are four different priorities with different timing when action is recommended (Table 4).
Alert Priority Action Reccomended
High An Immediate action is required to reduce risk of corrupt or lost data and outages
Medium An action is required in the next hours/days to reduce downtime risk
LowAn action required in the next days/weeks to reduce downtime risks
Information An action can be taken to improve stability and performance of the system
Table 4 Alert priorities in SAP HANA
There are also 10 different categories of alerts relating to availability, backup, configuration, CPU, diagnosis files, disk, memory, security, sessions, and system. Deciding when to schedule these alerts and when to monitor them is a critical task of the SAP HANA administrator. By activating and monitoring the recommended daily and intra-day alerts through any of the tools outlined previously, you can detect any performance issues early.
To get started, take a look at Table 5 as the first step of your own tailored daily SAP HANA admin SOP. In this table you find all the available SAP HANA automated alerts, alert IDs (so that you can find them in SAP HANA studio), the suggested frequency when these alerts should be activated or monitored, descriptions, and SAP’s official recommendation on how to resolve any issues.
10
Check type ID Time Description SAP recommended admin action
Availa-bility
0 Intra-day
Identifies internal statistics server problem
Resolve the problem. For more information, see the trace files. You may need to activate tracing first.
3 Intra-day Identifies inactive services Investigate why the service is inactive, for example,
by checking the service's trace files
4 Intra-day
Restarted services- services that have restarted since the last time of the check
Investigate why the service had to restart or be restarted, for example, by checking the service's trace files
21 Daily Identifies internal DB eventsResolve the event and then mark it as resolved by executing the SQL statement ALTER SYSTEM SET EVENT HANDLED '<host>:<port>' <id>
22 Intra-day
Notification of all alerts - If any alerts since the last check is triggered
These alerts can trigger email blasts to specified recipients. Investigate the alerts.23 Intra-
day
Notification of medium- and high- priority alerts - Since the last check is triggered
24 Intra-day
Notification of high-priority alerts - Since the last check is triggered
31 Daily
License expiry - If the disks to which data and log files are written are full. A disk-full event causes the DB to stop
Obtain a valid license and install it. For the expiration date, see the monitoring view M_LICENSE.
41 Daily
In-memory DataStore activation - If a problem with the activation of an in-memory DataStore object exists
For more information, see the table _SYS_STATISTICS.GLOBAL_DEC_EXTRACTOR_STATUS and SAP Note 1665553
70 Periodic
Consistency of internal system components after system upgrade Contact SAP support
78 Daily
Connection between systems in system replication setup - Closed connections between primary or secondary system
If connections are closed, the primary system is no longer being replicated. Investigate why connections are closed (i.e., network problem) and resolve the issue.
80As
needed
Availability of asynchronous table replication - Monitors error messages related to async table replication
Determine which tables encountered the table replication error using system view M_ASYNCHRONOUS_TABLE_REPLICAS, and check the corresponding indexserver alert traces
Back-up
28 Periodic
Most recent savepoint operation- How long ago the last savepoint was defined; that is, how long ago a complete, consistent image of the DB was persisted to disk
Investigate why there was a delay defining the last savepoint and consider triggering the operation manually by executing the SQL statement ALTER SYSTEM SAVEPOINT
32 Periodic
Log mode LEGACY- If the DB is running in log mode "legacy." Log mode "legacy" does not
If you need point-in-time recovery, reconfigure the log mode of your system to "normal." In the "persistence" section of the global.ini configuration
11
support point-in-recovery and is not recommended for productive systems file, set the parameter "log_mode" to "normal" for
the System layer. When you change the log mode, you must restart the DB system to activate the changes. It is also recommended that you perform a full data backup.33 Perio
dic
Log mode OVERWRITE - If the DB is running in log mode "overwrite."Log mode "overwrite" does not support point-in-recovery (only recovery to data backup) and is not recommended for prod systems
35 Daily Existence of data backup Perform a data backup as soon as possible
36 Daily Status of most recent data backup Investigate why failed, resolve the problem, and perform a new data backup as soon as possible
37 Daily Age of most recent successful data backup Perform a data backup as soon as possible
38 Daily
Status of most recent log backups - If the most recent log backups for services and volumes were successful
Investigate why the log backup failed and resolve the problem
54 Periodic
Savepoint duration - Identifies long-running savepoint operations.
Check disk I/O performance
65As
needed
Run time of the log backups currently running - If the most recent log backup terminates in the given time
Investigate why the log backup runs for too long, and resolve the issue
66As
needed
Storage snapshot is prepared - If the period when the DB is prepared for a storage snapshot exceeds threshold
Investigate why the storage snapshot was not confirmed or abandoned, and resolve the issue
69 Periodic
Enablement of automatic log backup - If automatic log backup is enabled
Enable automatic log backup. For more details please see SAP HANA Administration Guide.
72 Daily
Number of log segments - Segments in the log volume of each service Check for number of log segments. Make sure that log backups are being auto-created and that there is enough space.
Check whether the system has been frequently and unusually restarting services. If it has, then resolve the root cause of this issue and create log backups as soon as possible.
Configuration
3As
needed
Discrepancy between host server times - Discrepancies in a scale-out system
Check operating system time settings
10 Periodic
Delta merge (mergdog) configuration - If the 'active' parameter in the 'mergedog' section of system configuration file(s) is 'yes'
mergedog is the system process that periodically checks column tables to determine if a delta merge operation needs to be executed. Change in SYSTEM layer the parameter active in section(s) mergedog to yes
16 Perio Lock wait timeout configuration - In the 'transaction' section of the indexserver.ini 12
dic
If 'lock_waittimeout' parameter in 'transaction' section of indexserver.ini file is between 100,000 and 7,200,000
file, set the 'lock_wait_timeout' parameter to a value between 100,000 and 7,200,000 for the System layer
26 Periodic
Unassigned volumes - Identifies volumes that are not assigned a service
Investigate why the volume is not assigned a service. That is, assigned service is not active, the removal of a host failed, or the service removal was performed incorrectly.
34 Daily If all volumes are available Investigate why the volume is not available
79 Periodic
Configuration consistency of systems in system replication setup - Identifies configuration parameters that do not have the same value on the primary system and a secondary system
The identified configuration parameter(s) should have the same value in both systems, adjust the configuration. If different values are acceptable, add the parameter(s) as an exception in global.ini/[inifile_checker].
CPU 5 Intra-day
Host CPU Use - Determines the % CPU idle time on the host and therefore if CPU resources are running low
Investigate CPU usage
Diag-nosis
Files
46As
needed
RTEdump files - Identifies new run-time dump files (*rtedump*) have been generated in the trace directory
These files These contain information about, for example, build, loaded modules, running threads, and CPU. Check contents of the dump files.
50 Periodic
Number of diagnosis files - Written by the system (excluding zip-files)
A large number of files can indicate a problem with the DB (i.e., problem with trace file rotation or a high number of crashes). Investigate the diagnosis files
51 DailySize of diagnosis files - Very large file sizes can indicate a problem with the DB
Check the diagnosis files in the SAP HANA studio for details
52 DailyCrashdump files - New files that have been generated in the trace directory Check the contents of the dump files
53 DailyPagedump files - New files that have been generated in the trace directory
56 Periodic
Python trace activity - If trace is active and for how long. Trace affects performance.
If no longer required, deactivate the python trace in the relevant configuration file
Disk
2 Intra-day
Disk Usage - Determines what percentage of each disk containing data, log, and trace files is used. This includes space used by non-SAP HANA files.
Investigate disk use of processes. Increase disk space, for example by shrinking volumes, deleting diagnosis files, or adding additional storage.
30 Intra-day
Check internal disk full event - If the disks to which data and log files are written are full. A disk-full event causes your DB to stop
Resolve the disk-full event: In the Admin Editor on the Overview tab, choose the \"Disk Full Events\" link and mark the event as handled. Alternatively, execute the SQL statements ALTER SYSTEM SET
13
and must be resolved.EVENT ACKNOWLEDGED '<host>:<port>' <id> and ALTER SYSTEM SET EVENT HANDLED '<host>:<port>'<id>.
60 Periodic
Sync/Async read ratio - Identifies a bad trigger asynchronous read ratio
This means that asynchronous reads are blocking and behave almost like synchronous reads. This might have negative impact on SAP HANA I/O performance in certain scenarios. Note 1930979.61 Perio
dic
Sync/Async write ratio - Identifies a bad trigger asynchronous write ratio
77 Intra-day
DB disk use - The total used disk space of the DB. All data, logs, traces and backups are considered.
Investigate the disk use of the DB. See system view M_DISK_USAGE for more details
Mem-ory
1 Intra-day
Host physical memory usage - The percentage of total physical memory available on the host
All processes consuming memory are considered, including non-SAP HANA processes. Investigate memory use of processes.
3 Periodic Row store fragmentation Implement SAP Note 1813245.
12 Intra-day
Memory use of name server - Determines what percentage of allocated shared memory is being used by the name server on a host
Increase the shared memory size of the name server. In the 'topology' section of the nameserver.ini file, increase the value of the 'size' parameter.
17 Periodic
Record count of non-partitioned column-store tables - Current table size is not critical
Partitioning need only be considered if tables are expected to grow rapidly. A non-partitioned table cannot contain more than 2,000,000,000 (2 billion) rows. Consider partitioning the table only if you expect it to grow rapidly.
20 Periodic
Table growth rate of non-partitioned column-store table
27 Periodic
Record count of column-store table partitions
29 Periodic
Size of delta storage of column-store tables
Investigate the delta merge history in the monitoring view M_DELTA_MERGE_STATISTICS. Consider merging the table delta manually.
40 Daily
Total memory usage of column-store tables - The percentage of the effective allocation limit being consumed by individual column-store tables as a whole
This is the cumulative size of all of a table's columns and internal structures. Consider partitioning or repartitioning the table.
43 DailyMemory use of services - The percentage of effective allocation limit a service is using
Check for services that consume a lot of memory
44 Periodic
Licensed memory use - Percentage used
Increase licensed amount of main memory. See the peak memory allocation since installation in the system view M_LICENSE, column PRODUCT_USAGE
45 Periodic
Memory usage of main storage of column-store tables - The percentage of effective allocation
Consider partitioning or repartitioning the table
14
limit consumed by column-store tables
55 Periodic
Column-store unloads - The number of columns that have been unloaded from memory
Can indicate performance issues. Check sizing with respect to data distribution.
58As
needed
Plan cache size - If the plan cache is too small
Increase the size of the plan cache. In the 'sql' section of the indexserver.ini file, increase the value of the 'plan_cache_size' parameter.
67 Periodic Table growth of rowstore tables Reduce the size by removing unused data
68 Periodic
Total memory use of row store used by a service
Investigate memory use by row store tables and consider cleanup of unused data
73 Periodic
Overflow ratio of rowstore version space
Identify the connection or transaction that is blocking version garbage collection. You can do this in the SAP HANA studio by executing the "MVCC Blocker Connection" and "MVCC Blocker Transaction" statements available on the System Information tab of the Administration editor. If possible, kill the blocking connection or transaction.
74 Periodic
Overflow ratio of metadata version space
75 Periodic
Rowstore version space skew - If rowstore version chain is too long
81 Periodic
Cached view size - How much memory is occupied by cached view
Increase size of the cached view. In the "view_cache" section of the indexserver.ini file, increase the value of the "total_size" parameter.
Secur-ity
57 Daily Secure store file system (SSFS) consistency regarding the DB
Check and make sure that the secure storage file system (SSFS) is accessible and consistent regarding the DB
62 Daily
User passwords - Identifies DB users whose password is due to expire with the PW policy. If it expires, the user will be locked. This may affect application availability.
Change password of the DB user. It is recommended that you disable the password lifetime check of technical users so that their passwords never expire (ALTER USER <username> DISABLE PASSWORD LIFETIME).
63 Daily
Granting of SAP_INTERNAL_HANA_SUPPORT role - If the internal support role is currently granted to any DB users
Check if the corresponding users still need the role. If not, revoke the role from them.
64 Periodic
Total memory use of table-based audit log - The percentage of the effective allocation limit is being consumed by the DB table used for table-based audit logging.
Consider exporting the content of the table and then truncating the table
Sess-ions 25 Daily
Open connections - The percentage of the maximum number of permitted SQL connections open
The max number of permitted connections is configured in the "session" section of the indexserver.ini file. Investigate why the maximum number is being approached.
Sess-ion 39 Daily Long-running SQL statements Investigate the statement. For more info, see table
_SYS_STATISTICS.HOST_LONG_RUNNING_S
15
andtransa-ctions
TATEMENTS.
42As
needed
Long-idling cursorsClose cursor, uncommitted transaction, or the serializable transaction in the application, kill connection, or by executing the SQL statement ALTER SYSTEM DISCONNECT SESSION <LOGICAL_CONNECTION_ID>. For more information, see the tables HOST_LONG_IDLE_CURSOR, HOST_LONG_SERIALIZABLE_TRANSACTION andHOST_UNCOMMITTED_WRITE_TRANSACTION (_SYS_STATISTICS).
47 Periodic
Long-running serializable transactions
48 Periodic
Long-running uncommitted write transactions
49 Periodic Long-running blocking situations Investigate the blocking and blocked transactions
and if appropriate cancel one of them59 Daily Percentage of blocked transactions
System 83 DailyTable consistency - The number of table consistency errors and affected tables
Contact SAP support
Table 5 Admin monitoring frequency, alert IDs, and SAP-recommended actions
Periodic and Active Monitoring
In addition to these passive alerts, you can also add active monitoring by the system administrator to start to predict when performance issues might arise. These active monitoring activities include tracking weekly or monthly increases in data files, CPU use, planned projects, memory consumption, and user activity. This data allows you to plan and budget resources for system upgrades, data archiving, near-line storage (NLS) implementations, and hardware changes.
In addition, it is imperative that you also monitor new security patches and software fixes available from SAP and determine if this is something that you should consider for rapid implementation, or you can bundle them into periodic upgrades, service packs, and patches on a monthly or quarterly basis. The list of tasks in Table 5 should get you started with the automatic checks, but you can also build your own additional monitoring process by accessing the system information in SAP HANA. This is available in the administrator perspective in SAP HANA studio under the System Information tab page (Figure 7) and is also mostly exposed in the SAP HANA cockpit and in the DBA Cockpit.
16
17
Figure 7 The System Information tab in SAP HANA studio
Periodic SOP Tasks for Keeping BW on SAP HANA System Smaller
In addition to the daily and periodic standard monitoring tasks, you might want to do an active monitoring of an SAP HANA system during a go-live of a new project, as well as during a hyper-care period shortly after new functionality has been added to the system. In this section I describe some useful tips that help you do active monitoring using available transactions in the SAP HANA system.
Since most SAP HANA systems are currently using SAP Business Warehouse (SAP BW) as an application (this will most likely change over time), I focus on keeping SAP BW 7.3 and 7.4 on SAP HANA systems as small as possible. However, some of the archiving tasks in this section also apply to a Business Suite on an SAP HANA system.
First, if you want to quickly get access to see the largest SAP HANA tables (and monitor their growth), you can use transaction code DB02. Using this transaction enables you to find the largest tables in memory and also their respective record counts. These tables and record counts are shown in Figure 8.
Figure 8 View large SAP HANA tables using transaction code DB02
In an SAP BW system there are also tables that are likely to grow faster than others. These include the application log tables BALHDR, BALHDRP, BALM, BALMP, BALDAT, BALC, and BAL_INDX as well as the tables for linking IDocs (IDOCREL and SRRELROLES) and the short dump table SNAP.
Other request administration data in SAP BW that you want to monitor includes data in the RSBMLOGPAR, RSBMLOGPAR_DTP, RSBMNODES, RSBMONMESS, RSBMONMESS_DTP, RSBMREQ_DTP, RSCRTDONE, RSDELDONE, RSHIEDONE, RSLDTDONE, RSMONFACT, RSMONICTAB, RSMONMESS,
18
RSMONRQTAB, RSREQDONE, RSRULEDONE, RSSELDONE, RSTCPDONE, and RSUICDONE tables, as well as the Dictionary logs found in DDPRS. In this section I explain how to best manage these tables to keep the SAP HANA system as small as possible.
In addition to these tables you should also monitor the size of the SAP BW workbook table RSRWBSTORE and the SAP BW statistics data found in RSDDSTATAGGR, RSDDSTATAGGRDEF, RSDDSTATCOND, RSDDSTATDELE, RSDDSTATDM, RSDDSTATEVDATA, RSDDSTATHEADER, RSDDSTATINFO, RSDDSTATLOGGING, and RSDDSTATDTP. All these tables tend to grow rapidly in active systems, but keeping them small is rather simple. For example, to clean up the statistics entries in these tables, you can run the program RSDDSTAT_DATA_DELETE after you execute transaction code SE38 and then schedule the job to run periodically by executing transaction code SM36 (Figure 9). In general, it may be useful to keep 12 months of statistical data, so you don’t want to remove it all.
Figure 9 Deleting statistics entries over 365 days old using the RSDDSTAT_DATA_DELETE program
You should also monitor the size of the Data Transfer Process (DTP) error log in RSBERRORLOG, the process chain logs (RSPCINSTANCE), and the SAP BW batch run-time data (RSBATCHDATA). These logs contain numerous warnings and errors in transformation recordings. You can delete these periodically using RSBM_ERRORLOG_DELETE and schedule it to run to remove entries over 60/90 days old.
Furthermore, you can also keep your system small by periodically removing data in RSPCLOGCHAIN, RSPCPROCESSLOG, and table RSPCINSTANCET. This is done by using the RSPC_LOG_DELETE report after you execute transaction code SE38 (Figure 10).
19
Figure 10 RSPC_LOG_DELETE of process chain data in SAP BW
In addition to the tips mentioned above, to keep the application log tables small, you should use transaction code SM36 to schedule a job to run periodically. In the screen shown in Figure 11, click the ‘Step’ button to define which program the job will run (pick SBAL_DELETE). After that, you can also choose a variant to decide how much data you want to keep. For example, in Figure 11 I delete case logs older than 60 days.
Figure 11 Cleaning SAP BW application log tables in SAP HANA
20
After this setup, you can then schedule the job to run periodically (Figure 12). This job proactively helps keep the memory use of the SAP HANA system smaller than it would be if all these application logs were not periodically cleaned.
Figure 12 Scheduling an archiving job to run periodically
You should also periodically archive IDocs to keep the SAP HANA system smaller. IDocs are used for communication between SAP BW and the source system. When SAP BW executes an InfoPackage for data extraction, a request IDocs, RSREQUEST, is sent to the source system’s application link enabling (ALE) inbox. The source system acknowledges the receipt of this IDoc by sending an info IDoc (RSINFO) back to the SAP BW system.
In addition the source system sends an IDoc with all the requested data using the message type (RSSEND). Therefore, these tables can grow fast over time. You should therefore consider archiving IDocs older than three to six months. You can do this by executing transaction code SARA and then clicking the Write button, as shown in Figure 13. After you have selected ‘Maintain’ you then click on the “attribute’ and select ‘RSSEND’ as the message type, as seen in Figure 14. Finally, you select ‘Created on’ and pick the variable ‘Current day – 30 days’ as seen in Figure 15. This will archive all IDocs with a message type RSSEND that have a create date that is more than 30 days old. Naturally, you can select other retention periods in the variable selection (i.e. 60/90 days).
Once, you complete the archiving of the IDocs with message type RSSEND, you can repeat the process in Figures 13,14,15 to archive the other types of IDocs as well (RSINFO and RSREQUEST).
21
Figure 13 Keeping SAP HANA small by archiving IDocs
22
Figure 14 Select tables for IDocs archiving
Figure 15 Maintaining the Created On filter dynamically to archive all entries older than 30, 60, or 90 days
Then you simply give the job a description and schedule the job to run periodically. However, it is important to note that your Basis team needs to periodically back up and delete the archiving file in the Directory folder. This is typically done annually. The archive administration transaction transaction is also available in the Business Suite on the SAP HANA system.
In addition to the jobs and programs outlined above, you should also consider periodic clean-up of obsolete IDocs links. Links are written in the ALE and IDoc environments, resulting in entries in the IDOCREL and SRRELROLES tables. The links are required for IDoc document trace and ALE audit monitoring.
You can delete links in IDC8 and IDCA regularly (they are not required after the IDocs are posted). To do this, delete the links, execute transaction code SE38, and report RSRLDREL. Under the ‘Selection’ mode, click ‘Select using relationship type’. Create a variant for the link types IDC8 and IDCA and then, under the ‘Deletion Criterion’, select ‘Without existence’ check. Finally, as you did in Figure 13 (the regular IDoc removal), you can now create a dynamic end date variable, and use transaction code SM36 to schedule the job to run periodically. This helps you maintain a small SAP HANA system and keeps the SAP BW system much cleaner.
Furthermore, you can make the Request Administration data in SAP HANA smaller. While you should never delete entries in theses tables, you can archive them and reload old entries if necessary. Before you start this, you should make sure that the reports RSSTATMAN_CHECK_CONVERT_DTA and RSSTATMAN_CHECK_CONVERT_PSA have been executed in the ABAP editor transaction (SE38) at least once for all objects. You start by executing transaction code SARA and selecting the archiving object called BWREQARCH. Schedule this to run to archive entries that are more than 90 days old (Figure 16).
23
Figure 16 Periodic maintenance by archiving request administration data in SAP BW
In addition to these archiving jobs, you can also remove data in the Dictionary Logs. Basically, the DDPRS dictionary log table contains all activities that make any change on the Data Dictionary objects in SAP BW. This can grow quite large over time, and you might want to remove some of these log entries on an annual basis. To clean up these log entries, execute transaction code SE38 and run report RADPROTA. From here you simple select the DDPRS and the time period you want to delete from such as 02/11/2013 to current date as seen in Figure 17. Just make sure you execute the job as a background job.
Figure 17 Remove Dictionary Logs in SAP BW to reduce SAP HANA size
You also want to make sure that non-reportable tables that have no daily loads going into them have their memory cleansed. You do that by executing transaction code RSHDBMON using the load/unload the data options, or flag it for Early Unload (Figure 18). The last option allows SAP HANA to decide when the data should be unloaded (i.e.,
24
when the data is not accessed, or data is not loaded). This process allows you to keep your SAP HANA memory use smaller, while still having access to the data when needed.
Figure 18 Unload nonactive data from SAP HANA memory with RSHDBMON
Also, occasionally you need to clean up short dump table SNAP in SAP BW. To do this execute transaction code ST22, and in the screen that appears, click the Goto button and select Reorganize from the list of options in the drop-down menu (Figure 19). From here you can select the run time errors you want to remove. For example, in Figure 20, we have selected to remove all errors older than 90 days. To execute the job, we simple select ‘reorganize’ again.
Figure 19 Periodic cleaning up of the short-dump table in SAP BW
Delete short dumps older than 90 days and executing the job by clicking ‘reorganize’(Figure 20).
Figure 20: Selecting time period for which to delete records in the short dump table SNAP
25
Finally, from a SAP HANA monitoring standpoint you may want to ensure that the delta merge is performed regularly. You can monitor this in the SAP HANA Admin perspective of Eclipse in SAP HANA studio (Figure 21). From here you can see each table in the schema of every host and see if memory merges are happening.
Figure 21 Monitoring delta merge processing in SAP HANA using SAP HANA studio
Administrating SAP HANA on a daily basis is a very interesting challenge. There are many technical aspects from hardware health, system connectivity, database performance, security, file management, backup, table management, memory consumption, disk utilization, and much more.
It is therefore very important that you plan a structured support approach to SAP HANA and that tasks are scheduled in as automated a fashion as possible. Using checklists assures that you are not surprised by activities that could easily be addressed if they are monitored in a very organized fashion.
26