SAP HANA SPS 07 Adm Ad m i n i s t r ati at i o n and an d Mon Mo n i t o r i n g January 2014
Agen Ag end da •
Introduction to the SAP HANA Studio
•
The Administration Console of the SAP HANA Studio •
The SAP HANA Systems View
•
The Administration Editor •
Overview
•
Landscape
•
Alerts
•
Performance
•
Volumes
•
Configuration
•
System Information
•
Diagnosis Files
•
Trace Configuration
•
SAP HANA Table Administration
•
The SQL Console
•
More Information
Agen Ag end da •
Introduction to the SAP HANA Studio
•
The Administration Console of the SAP HANA Studio •
The SAP HANA Systems View
•
The Administration Editor •
Overview
•
Landscape
•
Alerts
•
Performance
•
Volumes
•
Configuration
•
System Information
•
Diagnosis Files
•
Trace Configuration
•
SAP HANA Table Administration
•
The SQL Console
•
More Information
Intro nt rodu duct ctio ion n to t o the t he SAP HANA ANA Studio
Intr ntrodu oducti cti on to t o the SAP SAP HAN HANA A Studio The Administration Administrati on Tool Tool for f or SAP HANA The SAP HANA studio runs on the Eclipse platform and is both the central development environment and the main administration tool for SAP HANA. Administrators can use the SAP HANA studio, for example, to start and stop services, to monitor the system, to configure system settings, and to manage users and authorizations. The SAP HANA studio accesses the servers ser vers of the SAP HANA database by SQL. The SAP HANA studio presents its various tools in the form of perspectives. Database administration and monitoring features are contained containe d primarily within the Ad mi ni st r ati on Con so le perspective.
Intr ntrodu oducti cti on to t o the SAP SAP HAN HANA A Studio Screen Areas Areas of the Administration Console
1.
SAP HANA Systems view
2.
Editor area, for example:
3.
Administration editor
SQL console
Table editor
2
1
Other views, for example:
Error Log
Properties
Progress
3
The Administration Console of the SAP HANA Studio
Administration Console The SAP HANA Systems View The SAP HANA Systems view provides you with a hierarchical view of all the SAP HANA systems managed in the SAP HANA studio and their contents (database catalog, users, roles). This view allows you to see the status of your systems at glance. It is also the central access point for performing systemspecific administration and monitoring activities. From the context menu of the SAP HANA Systems view, you can access a range of both system-specific and object-specific functions, for example:
Add system Log On to / Log Off from system Stop, start, restart system Open system properties Back up and recover the system Import and export catalog objects Open SQL console Find table Open table definition
SAP HANA Systems View SAP HANA System Log on and Log off Enabled user log on, log of f, and system delete functionality with automatic log off system functionality The user can log off actively from a SAP HANA system via the context menu. Every connection (JDBC/HTTP) is closed after the user executes the Log Off on a selected system (1). If no connection is established the context menu provides the possibility to log on to a system or to delete it (2). The start-up behavior of the SAP HANA Studio can be configured. Under Windows > Preferences the user can decide to res tore the logged-on/logged-off status of systems on startup (3).
1
2
3
SAP HANA Systems View Linking a centrally-stored archive of SAP HANA Systems Allo w users to conn ect eff ic ientl y to mu lt ip le SAP HANA s ystem s A centrally-stored archive of SAP HANA systems is an efficient way to deploy system information to all users of the SAP HANA studio. It avoids users having to obtain t he connection details of all systems individually and then having to add them all individually. One user can manage the list of all systems in a centrally-accessible archive (viaFile > export > SAP HANA > landscape) Other users can then simply link to this archive (read-only mode)
2. Specify the link details: 1
a) Link name b) Path to the system archive containing the system information
1. In the context menu of the SAP HANA Systems view, choose Add System Archive Link.
2 a) b)
c) Optional: A folder in the SAP HANA Systems view
3. Choose Finish.
c)
3
SAP HANA Systems View Resource Utilization The Resource Utilization editor enables you to visualize and explore the usage history of the following key system resources:
CPU
Memory
Disk
The editor can help to analyze bottlenecks, identify patterns, and forecast requirements. The editor can be opened via the contextmenu on the specific SAP HANA system.
SAP HANA Systems View Memory Overview The Memory Overview editor provides a detailed graphical breakdown of the following main categories of memory usage:
Physical memory
SAP HANA database
Table data
The Memory Overview editor is available in the context menu of the Systems view.
Database management
Other information regarding the current size of used resources can be seen on the Overview tab of the Administration editor.
Administration Console The Administration Editor The Admi nis tr ati on edi tor is available in the Administration Console perspective and is the main tool for performing administration and monitoring activities. You can open the Administration editor for a system by simply double-clicking the system in the SAP HANA Systems view. The functions of the Administration editor are available across several tabs:
Administration Editor Diagnosis Mode The SAP HANA studio normally collects information about the system using SQL. However, when the system has not yet started or is down, no SQL connection is available. In this situation, the SAP HANA studio collects information about the database using the connection of the SAP start service (sapstartsrv). You can view this information in the Admi nis tr atio n edit or in di agno si s mod e. In this way, you can analyze any problems that may occur during startup or while the system is stopped. You can also access diagnosis files. Note: To open the Administration editor in diagnosis mode you need the credentials of the operating system user adm.
Administration Editor Overview Tab The Overview tab provides you with the most important information about a system at a glance:
System status
General system information
Current alerts
Memory usage
CPU usage
Disk usage
From the Overview tab, you can navigate to more detailed information.
Administration Editor Landscape > Services Tab The Landscape tab contains several sub-tabs. The Services sub-tab provides you with:
A list of all services (nameserver, indexserver, preprocessor, statisticsserver), their status, and detailed resource consumption A context menu with functions including:
Stop, kill, reconfigure service Reset memory statistics
Memory Allocation Statistics
Administration Editor Landscape > Services Tab > Memory Allocation Statistics I The new Memory Al location Statistics editor enables you to visualize and explore the memory allocation of every service.
In the Administration editor, choose the Lands cape -> Services tab. Right-click the service whose allocation history you want to investigate and choose Memory Allocation Statistics The following information is displayed in screen areas identified above:
1
2
3
4
1. The components of the selected service listed in descending order of current used memory (default) 2. Current breakdown of SAP HANA used memory displayed as a pie chart 3. Allocators of the selected component listed in descending order of current used inclusive memory (default) 4. Current breakdown of memory usage of the 10 highest consuming allocators displayed as a pie chart
Administration Editor Landscape > Services Tab > Memory Allocation Statistics II Analyze t he u sed mem ory hi st or y of th e component and its allocators by enabling the history graph (Show Graph) and exploring the data. Several options are available, for example:
To change the visualized time period, adjust the From/To values. To visualize the memory usage of individual allocators over the same time period as the select ed component, simply select them in the allocators table. To see time-specific values, move the mouse over the graph. To see the visualized values in table format, c hoose (Show history data in a table)
Administration Editor Landscape > Hosts Tab The Hosts sub-tab provides you with:
An overview of all the hosts in a distributed system Detailed information about the auto-failover status and configuration of hosts Host re-configuration options Option to remove a host from the system
Administration Editor Landscape > Redistribution Tab In a distributed system, tables and table partitions are assigned to an index server on a particular host at their time of creation, but this assignment can be changed. In certain situations, it is even necessary. SAP HANA supports several “redistribution operations” that use complex algorithms to evaluate the current distribution and determine a better distribution depending on the situation. The Redistribution sub-tab allows you to execute redistribution operations if you need to: Redistribute data before removing a host from the system Redistribute data after adding a new host to the system Optimize current table distribution Optimize table partitioning
Note: It is always recommended that you save the current distribution before executing a table redistribution operation in case you need to restore it.
Administration Editor Landscape > System Replication Tab I System replication is a mechanism for ensuring the high availability of an SAP HANA system. Through the continuous replication of data from a primary to a secondary system, including in-memory loading, system replication facilitates rapid failover in the event of a disaster. Productive operations can be re sumed with minimal downtime. On the System Replicatio n sub-tab, you can:
Perform the initial set-up, that is ena ble system replication and establish the con nection between two identical systems. Monitor the status of system replication to ensure that both systems are in sync. Trigger failover to the secondary system in the event of a disaster and failback once the primary system is available again.
Administration Editor Landscape > System Replication Tab II
Administration Editor Alerts Tab I As one of the main components of the monitoring infrastructure of the SAP HANA database, the statistics server performs regular checks and issues an alert when an alert condition is fulfilled. The priority of the alert indicates the severity of the problem and depends on the nature of the check and configured threshold values. For example, if 90% of available disk space is used, a low priority alert is issued; if 98% is used, a high priority alert is issued. The summary header on the Alert s tab shows the total number of current alerts by priority and the corresponding list of alerts
Administration Editor Alerts Tab II The Alert s sub-tab provides you with: • An overview of: • All current unresolved alerts (default view) • Alerts sorted by time period (last 15, 30, 60, and 120 minutes, today, yesterday, last week)
• Detailed information about individual alerts • Alert filtering options by: • • • •
•
Specific word Priority Check ID Time frame
Configuration options for: • E-mail notification • Alert thresholds • Check start times
Statistics Service New implementation design as of SAP HANA SPS 07 The new statistics service design leads to performance and flexibility improvements The statistics server is the component of the SAP HANA database that provides internal monitoring functions. It continuously collects information about system status, performance, and resource usage, and issues alerts in the event of problems. As of SPS 07, it is possible to switch to a new mechanism whereby data collection and alerting are implemented through the execution of SQLScript procedures. This has the following advantages:
The statistics server component is no longer required. By replacing the st atistics server with internal procedure calls, overall system performance is improved with reduced disk usage, memory usage, and data transfer. The statisticsserver.ini properties file is no longer required to contain all configuration information relating to data collection and alerting. Instead this information is contained in new tables in the _SYS_STATISTICS schema. This increases the overall flexibility with which data collection and alerting can be configured. For example, data collectors and checks can be sc heduled more flexibly, new collectors and checks can be added more easily, and data collectors and checks can be deactivated. For more information about how to migrate to t he new mechanism after an upgrade, see SAP Note 1917938
Administration Editor Performance Tab You can monitor the following detailed aspects of system performance on the Performance tab: •
Threads
•
Sessions*
•
Blocked transactions
•
Expensive statements*
•
SQL plan cache*
•
Job progress*
•
Load
*The information displayed on these sub-tabs of the Performance tab is detailed and highly customizable. To support administrators performing complex analyses, user-specific column and filter settings are saved when the Administration editor is closed. These settings are restored the next time the tab is opened, independent of system.
Administration Editor Performance > Threads Tab I The Threads sub-tab allows you to monitor all running threads in your system. It may be useful to see, for example, how long a thread is running, if a thread is blocked for an inexplicable length of time. On the Threads sub-tab: •
Transactionally blocked threads are identified with a warning icon
•
The Group and sort filter provides a meaningful and clear structure for thread analysis: •
Threads with the same connection ID are grouped.
•
Within each group, the call hierarchy is depicted.
•
Groups are displayed in order of descending duration.
Administration Editor Performance > Threads Tab II On the Threads sub-tab, you can also perform the following actions: • End the operations associated with a thread • See the full details of a thread • Jump to the related calling and called threads, session or blocked transaction of a thread by right-clicking the thread • View the call stack for a specific thread • Configure the SQL, performance or expensive statement trace
Administration Editor Performance > Sessions Tab The Sessions sub-tab allows you to monitor all sessions in the current landscape: • Active/inactive sessions and their relation to applications • Whether a session is blocked and if so which session is blocking • The number of transactions that are blocked by a blocking session • Statistics like average query runtime and the number of DML and DDL statements in a session You can also cancel sessions.
Administration Editor Performance > Blocked Transactions Tab If there are blocking situations (indicated on the Threads sub-tab), you can see another representation of the information about blocked and blocking transactions on the Blocked Transactions sub-tab. Sort by transaction ID to see the relationship between the two views better.
Administration Editor Performance > Threads, Sessions and Blocked Transactions Tab I Easy navigation b etween the edito rs: Threads, Sessio ns, and Bloc ked Transaction
As of SPS07 it is possible to navigate between Performance Threads, Sessions and Blocked Transaction view by using the connection ID. This functionality is reachable from the context menu from the view under Navigate To. In the threads view it is possible to navigate from/to callee/caller by using the thread I D. In the Sessions view it is possible to navigate to the connection which is blocking the selected connection. The application information (name and user) which is available in the performance threads, sessions and blocked transaction view can also be used to open a pre-configured dialog for SQL, performance and expensive statement trace.
Administration Editor Performance > Threads, Sessions and Blocked Transactions Tab II Filter to hide idle sessions, as w ell as sessions or iginating in the Administration editor or other application s in t he SAP HANA studio
As of SAP HANA SPS 07 it is possible to hide different session types from the Performance > Threads, Sessions and Blocked Transaction view. Each option can be select ed in the drop down menu Hide Sessions. If an option is selected a new SQL statement is executed to display only the filtered rows. This filter is not only operating on display level. When an option is selected, the database is queried again for the relevant sessions.
Administration Editor Performance > SQL Plan Cache Tab You can use the SQL plan cache to get an insight into the workload in the system as it lists frequently executed queries. Technically, the plan cache stores compiled execution plans of SQL statements for reuse, which gives a performance advantage over recompilation at each invocation. For monitoring reasons, the plan cache keeps statistics about each plan, for instance number of executions, min/max/total/average runtime, and lock/wait statistics. Analyzing the plan cache is very helpful as one of the first steps in performance analysis because it gives an overview about what statements are executed in the system. Note: Due to the nature of a cache, seldom used entries will be evicted from the plan cache. You can view the SQL plan cache on the SQL Plan Cache sub-tab.
Administration Editor Performance > Expensive Statements Trace Tab Expensive statements are individual SQL queries whose execution time was above a configured threshold. Expensive statements may reduce the performance of the database. The expensive statements trace records information about these statements for further analysis and displays them on the Expensiv e Statements Trace sub-tab. You can configure for which user, table/view or application statements should get recorded.
Administration Editor Performance > Job Progress Tab Certain operations typically run for a long time and may consume a considerable amount of resources, for example, delta merge operations, data compression, and delta log replays. You can monitor the progress of these long-running transactions on the Job Progress sub-tab. You can determine whether or not they are responsible for current high load, see how far along they are, and when they will finish. The following information is available, for example:
Connection that triggered the operation
Start time of the operation
Steps of the operation that have already finished (CURRENT_PROGRESS)
Maximum number of steps in the operation (MAX_PROGRESS)
Administration Editor Performance > Load Tab
The Load sub-tab provides you with a graphical display of current performance, for example:
CPU usage
Memory consumption
Table unloads
You can compare the performance of different hosts.
Administration Editor Volumes Tab To ensure that the database can always be restored to its most recent committed state, you must ensure that there is enough space on disk for data and log volumes. On the Volumes tab, you can monitor:
Disk usage
Volume size
Other disk activity statistics
There are two views available for monitoring the size of volumes on disk: service and storage type. You can also filter by host.
Administration Editor Configuration Tab The properties of an SAP HANA system are defined in the parameters of its configuration files. Configuration files are separated into sections; sections bundle parameters of the same category. You can view and modify system properties on the Configuration tab.
Administration Editor System Information Tab The System Information tab lists several predefined SQL SELECT statements on system views. These statements provide you with easy access to important system information. If you have compiled your own SQL statements for monitoring purposes, you can save these statements on the System Information tab for convenient repeated execution
Administration Editor Diagnosis Files Tab Diagnosis files include log and trace files, as well as a mixture of other diagnosis, error, and information files. In the event of problems with the SAP HANA database, you can check these diagnosis files for errors. On the Diagnosis Files tab, you can:
Filter the list of diagnosis files (by keyword, host) Display individual files Review multiple files at the same time using the “Merge Diagnosis Files” function
Delete files
Download files
Collect and download diagnosis information
Administration Editor Trace Configuration Tab I You can configure the following traces on the Trace Configur ation tab. Different configuration options are available for each trace.
Database trace (including user-specific and end-to-end database traces) The database trace records information about activity in the components of the SAP HANA database. You can use this information to analyze performance and to diagnose and debug errors. Each service of the SAP HANA database writes to its own trace file. By default, the database trace is active with default trace level ERROR. SQL t race The SQL trace collects information about all executed SQL statements and saves it as an executable python program. This is good for recording a scenario. By default, the SQL trace is inactive. Expensive statements trace Expensive statements are individual SQL queries whose execution time was above a configured threshold. The expensive statements trace records information about these statements for further analysis. By default, the expensive statements trace is i nactive.
Administration Editor Trace Configuration Tab II
Perfor mance trace The performance trace is a performance tracing tool built into the SAP HANA database. It records performance indicators for individual query processing steps in the database kernel. By default, the performance trace is inactive. Kernel profiler The kernel profiler is a sampling profiler built into the SAP HANA database. It collects, for example, information about frequent and/or expensive execution paths during query processing. By default, the kernel profiler is inactive.
Note: Only SAP development support has the technical expertise required to interpret the information collected by the performance trace and the kernel profiler.
Administration Editor Trace Configuration Tab III
SAP HANA Table Administration
SAP HANA Table Administration Table Editor Some monitoring and problem analysis may require you to examine individual tables, for example, the many system views provided by the SAP HANA database. You can open tables and views in different ways. Several viewing options are available depending on what you want to do:
Table definition The table definition view provides you with information about the table's structure and properties (for example, schema, type, column properties, and indexes). Detailed information relating to the table's memory usage and size is available on the Runtime Information sub-tab. Table con tent Opening a table's content executes a SELECT statement on the table. The results set shows the actual records in the table. Data pr eview Opening the data preview of a table allows you to analyze the content of the table in different ways. Similarly to the table content view, this is particularly useful for analyzing system views.
SAP HANA Table Administration Opening the Table Definition
SAP HANA Table Administration Table Distribution Editor I To support the analysis and monitoring of performance issues in a distributed SAP HANA system, a table distribution editor is available in which you can see how tables and table partitions are distributed across the hosts. Detailed information about tables/partitions (for example, memory usage and size) is also available. You access the Table Distribu tion editor from the SAP HANA Systems view.
SAP HANA Table Admini stration and Monit oring Table Distribution Editor II In the Table Distribu tion editor, you can perform the following additional actions:
Move tables and partitions to other hosts in the system Partition non-partitioned tables using the supported partitioning specifications (hash, range, and round-robin)
Change a partitioned table into a nonpartitioned table by merging its partitions Note: Before moving tables or partitions, the system checks that the host has sufficient memory.
SAP HANA Table Administration Importing and Exporting Tables and Other Catalog Objects You can export catalog objects to a file system and then import them back into another database. For example, you want to move data from a test system to a productive system, clone your system, or provide the data to SAP Support so they can replicate a scenario.
You can export/import either meta data only or meta data and content Column-store tables, procedures, and sequences can be exported in either binary or CSV format. Rowstore tables can be exported only in CSV format imported
SAP HANA Table Administration Other Table Administration Options
Load into and unload from memory Under normal circumstances, the SAP HANA database manages the loading and unloading of tables into and from memory independently – the aim being to keep all relevant data in memory. However, you can manually load and unload individual tables and table columns if necessary. Perfor m manual delta merge The delta merge operation is related to the memory management concept of the column store, that is, the part of the SAP HANA database that manages data organized in columns in memory. You can trigger the delta merge operation for a column table manually in the SAP HANA studio, for example, if you need to free up memory.
SQL Console
SQL Console Executing SQL Statements in the SAP HANA Studio Some tasks may require you to work with SQL statements, for example, certain administration tasks can only be performed using SQL. You can enter, execute, and analyze SQL statements in the SQL console. There are several options available for customizing the behavior of SQL statement execution in the SQL console. From the main menu choose W indow > Preferences > SQL.
SQL Console Query Analysis Features The following functions for analyzing query execution are available in the SQL console:
Explain plan Visualize plan
Note: Visualize Plan is also available from the context menu of the SQL Plan Cache and Expensiv e Statements Trace sub-tabs of the Performance tab.
SQL Console Plan Visualizer To help you understand and analyze the execution plan of an expensive statement or an SQL statement further, you can generate a graphical view of its plan. A graphical representation of the query, with estimated performance, is displayed. You can then validate the estimated performance by choosing Execute in the context menu. Another similar high-level graphic is generated with execution time information for each of the parts.
Visualized Plan
Execution
Column Plan
Additional Information SAP HANA Documentation For more information about the administration and monitoring features of the SAP HANA studio introduced in this presentation, see the SAP HANA Admini stration Guide, which you can access: • Directly in the SAP HANA studio from the Help menu • On SAP Help Portal at http://help.sap.com/hana_appliance Other useful documentation available on SAP Help Portal: • SAP HANA Master Guide • SAP HANA Technical Operations Manual • SAP HANA Studio Installation Guide
