Performance Management Tools for SAP HANA

Performance Management Tools for SAP HANA

SAP HANA CAPTURE AND REPLAY
What is capture and replay? Current features & product benefits.

Features

Benefits

• It enables in real simulation of workload without the need of other 3rd party tools
• It Provides competitive replay and does analysis for SAP HANA database
• Helps in ensuring that the landscape changes will not effect or do not degrade system performance and its efficiency
• Minimizes or Reduces manual effort for testing the changes in customer deployments

When should Capture and replay to be Used?
When to need to improve the focus on stability and improve the focus on performance of your production servers SAP HANA Workload Analyzer.

What is workload analyzer? Current features & product benefits

Features

• In this tool the Web-based UI is integrated into SAP HANA cockpit
• Provides flexible, chart-based analysis options and gives further insight into system and the workload
• Provides Solution for analyzing database performances
• The Database performance is analyzed using thread samples and engine instrumentation
• Gives a sophisticated approach and Provides timeline analysis
• It also helps in filtering capabilities

Benefits

• It Allows administrators to view the current state and health of a system at a glance
• In case of bottlenecks, it supports multi-dimensional performance analysis to evaluate them
• Helps to Drill-down or to evaluate possible root cause of performance issues which is carried out either by analyzing real-time, historical or captured data

SAP HANA SQL Analyzer

What is SQL ANALYZER?

Features
In HANA SQL Analyzer the Web-based UI integrated into SAP HANA cockpit Helps to analyze different aspects of executed SQL query

Benefits

• Provides start point for each and every query-level performance analysis
• Has similar functional scope as PlanViz view present in the SAP HANA Studio
• Provides deeper insights into SQL-based queries and SQL Script-based queries
• Designed to be dedicated web-based application
• Works in collaboration with other Performance Management Tools
• Complements full end-to-end view of system performance

SAP HANA Workload Management

What is workload in the context of SAP HANA?

• It is a set of requests with common characteristics, e.g.
• request source (user, application, etc.)
• Types of query (analytical, transactional, etc.)
• Hierarchy of Business (high, low, etc.)
• System Level Objectives (SLOs) (response time, throughput, etc.)

Workload has certain resource demands to the system

• CPU
• Memory
• Disk I/O
• Network bandwidth

Mixed workloads compete for shared system resources…

• If a request consumes a significant amount of system resources, other
• Requests are subject to queuing effect
• Waiting workloads might fail to meet their expected performance

Options for managing workload Settings on operating system level

CPU-binding is a resource pooling technique on OS-level and outside the view of the SAP HANA database. We recommend to use SAP HANA parameter settings first

Features

Partitioning CPU resources on process-level

• –within single SAP HANA instance
• –between SAP HANA instances
• –between SAP HANA tenants

Binding CPU resources to processes

• –Guaranteed CPU resources on process-level
• –Ensures data locality per CPU-socket
• –Keeps specific resources dedicated to specific applications or use cases

Options for managing workload Settings on SAP HANA system level
SAP HANA global settings can be done as parameter changes in the database .ini files. These parameters affect the complete SAP HANA system.

SAP HANA supports NUMA-aware data placement, hash-distributing tables and partitions across NUMA nodes; this ensures faster processing between memory and CPU. SAP HANA also supports NUMA-aware job scheduling, taking into account the NUMA location of tables when scheduling jobs.

CPU
–thread pool size for complex, long-running statements (e.g. OLAP) and SAP HANA system tasks and for short-running requests (e.g. OLTP)

Degree of Parallelism
–statement thread limit for the SAP HANA system

Memory
–global allocation memory limit for the SAP HANA system
–statement memory limit for the SAP HANA system

Options for managing workload Settings on SAP HANA session level
SAP HANA workload classes allow the dynamic mapping of SAP HANA resources to SAP HANA DB clients based on workloads. Workload classes define resource properties on statement level granularity

Administrator Tasks:

• Create workload classes defining:
• statement thread limit
• statement memory limit
• statement priority

Define mapping rules based on session variables

• APPLICATION NAME
• APPLICATION USER NAME
• USER NAME
• CLIENT etc.

System Monitoring

Options for managing workload Limit resources per application, user or workload class

Option to limit CPU/memory prior to SAP HANA 2.0 SPS02:

Options on system/global level:

• –Statement thread limit
• –Statement memory limit

Options on session level:
–Workload classes can be used to assign resource properties to incoming connections
–Resource limitations offer statement thread limit, statement memory limit, statement priority
–Mapping is done via sessions variables (APPLICATION NAME, USER NAME, CLIENT, etc.)

Only work on statement level; multiple statements from one application, user or workload class can not exceed their limits, but parallel statements cannot be limited

Options for managing workload Settings on SAP HANA session level – session variables

This is used to pass the SAP HANA session variables with their client-specific values to SAP HANA incase SAP HANA client-application wants to get benefitted by SAP HANA workload classes

SAP HANA 2.0 SPS00: setting of SAP HANA session variables for use with workload classes is improved

In order to ensure that workload classes can be mapped and used correctly, there are several options for JDBC/SQLDBC connections to set session variables implicitly

First option: In the first phase we need to get meaningful default values from OS. This is possible and ensures that some variables are always passed and set.

Second option: Optional passing of variables through connection string. These variables overwrite the variable values coming from the first option.

This can be set in the connection string by the user, but not by the developer or partner or vendor.

jdbc:sap://ykfl00540545a:30115/?autocommit=false&sessionVariable:APPLICATION=myapp&sessionVariable:APPLICATIONUSER=user1&sessionVariable:myvar=myval&distribution=connection

Third option: Explicit setting of session variables via setClientInfo()

This can only be done during the development phase.

If the partner/vendor chooses to call the setClientInfo() function, this overwrites any values coming from the first and the second option.

Dynamic Admission Control

Used to handle short peak-load situations
Correctly sized hardware
We can avoid or remedy the contention issues by rejecting incoming request
Helps in tracking of recent CPU and memory consumption
Handles the requests only at Session Layer
Request via HTTP to XSC, TrexNet, and other internal calls are not considered
Affects newly-invoked statements,and doesn’t show any impact on running statements
By default it is disabled, and can be configured via ini-parameters in indexserver.ini