Symptom
You want to perform a technical SAP HANA health check.
You are interested to understand the output of SQL: "HANA_Configuration_MiniChecks" (SAP Note 1969700).
You are interested to understand the output of SQL: "HANA_Configuration_MiniChecks" (SAP Note 1969700).
Environment
SAP HANA
Cause
You want to check for potentially critical technical issues in the
SAP HANA database. Usually these issues are raised and processed via SAP
HANA alerts. This SAP Note describes a different approach via SQL: "HANA_MiniChecks" provided in SAP Note 1969700 that is available for the following reasons:
- Ad-hoc execution possible
- Execution in different environments possible (SAP HANA Studio, DBACOCKPIT, other SQL interfaces to SAP HANA)
- Flexible adjustments and delivery possible
- Tailored checks for SAP support services
Resolution
The command SQL: "HANA_Configuration_MiniChecks" (SAP Note 1969700)
performs several mini checks and returns C = 'X' if a potentially
critical situation is found. You can then perform a more detailed
analysis to check if there is a real issue or if the 'X' can be ignored.
This SAP Note provides details that can help you to analyze and judge
potentially critical situations.
Be aware that multiple versions of SQL: "HANA_Configuration_MiniChecks" may exist, e.g.:
These mini checks can only be evaluated on SAP HANA databases. Oracle mini checks can be found in SAP Note 1615380.
ESS is an abbreviation for the embedded statistics server, SSS is the shortcut for the standalone statistics server. SR represents system replication environments.
When checks are related to "last day", the last 86400 seconds before the analysis time are considered, not the last calendar day.
Be aware that multiple versions of SQL: "HANA_Configuration_MiniChecks" may exist, e.g.:
- SQL: "HANA_Configuration_MiniChecks_SSS" (minimum set of checks that should work in any environment with the standalone statistics server)
- SQL: "HANA_Configuration_MiniChecks_Rev70+_SSS" (extended set of checks that only works as of Revision 70 and with the standalone statistics server)
- SQL: "HANA_Configuration_MiniChecks_Rev74+_ESS" (extended set of checks that only works as of Revision 74 and with the embedded statistics server)
- SQL: "HANA_Configuration_MiniChecks_Rev90+_ESS" (extended set of checks that only works as of Revision 90 and with the embedded statistics server)
- SQL: "HANA_Configuration_MiniChecks_Rev100+" (comprehensive set of checks that only works as of Revision 100)
- SQL: "HANA_Configuration_MiniChecks_Rev110+" (comprehensive set of checks that only works as of Revision 110)
These mini checks can only be evaluated on SAP HANA databases. Oracle mini checks can be found in SAP Note 1615380.
ESS is an abbreviation for the embedded statistics server, SSS is the shortcut for the standalone statistics server. SR represents system replication environments.
When checks are related to "last day", the last 86400 seconds before the analysis time are considered, not the last calendar day.
| Check ID | Area | Validity | Description | Analysis SQL statement (SAP Note 1969700) | Potential impact | SAP Note | Details |
| 12 | GENERAL | global | Revision level | SQL: "HANA_Configuration_PatchLevel" | Performance degradations, terminations, wrong results, unplanned downtime |
2021789 |
In general you should make sure that you are on a rather recent
revision level, optimally a maintenance revision or data center service
point. |
| 110 | GENERAL | global | Everything started | SQL: “HANA_Services_Overview” | Terminations, unplanned downtime | 2177064 | If SAP HANA services are not started, certain functionalities may not be available. Therefore check the related trace files and make sure that all services can be started successfully. |
| 111 | GENERAL | scale-out | Host startup time variation (s) | SQL: “HANA_Startup_StartupTimes” | Unplanned downtime | 2177064 | A varying host startup time can indicate problems on some hosts. Make sure you understand the reason for the varying start time and eliminate potentically critical root causes. |
| 115 | GENERAL | global | Service startup time variation (s) | SQL: “HANA_Startup_StartupTimes” | Unplanned downtime | 2177064 | A varying service startup time can indicate problems of some services. Make sure you understand the reason for the varying start time and eliminate potentically critical root causes. |
| 120 | GENERAL | global | Services not started | SQL: "HANA_Services_Overview" | Erroneous alerts, unplanned downtime |
2222249 |
Check if there is an inconsistency between nameserver topology and daemon configuration. |
| 208 | OPERATING SYSTEM | global | Supported operating system | SQL: "HANA_Hosts_Overview" | Lack of support |
|
Make sure that you use an operating system that is both supported by the operating system partner and SAP. |
| 209 | OPERATING SYSTEM | global | Recommended operating system kernel version | SQL: "HANA_Hosts_Overview" | Performance degradations |
2235581 |
Make sure that a reasonably new operating system kernel is implemented in order to avoid running into already fixed, critical issues. |
| 210 | OPERATING SYSTEM | global | Minimum CPU rate (MHz) | SQL: "HANA_Hosts_Overview" | Performance degradations |
1890444 |
A low CPU frequency is often the consequence of a CPU power-save mode. Make sure on OS side that no CPU runs in power-save mode. |
| 211 | OPERATING SYSTEM | scale-out | Hosts with varying CPU rates | SQL: "HANA_Hosts_Overview" | Performance degradations |
1890444 |
A varying CPU frequency is often the consequence of a CPU power-save
mode. Make sure on OS side that no CPU runs in power-save mode and that
the nodes of a SAP HANA scaleout scenario use the same type of CPUs. |
| 220 | OPERATING SYSTEM | global | Current CPU utilization (%) | SQL: "HANA_Resources_CPUAndMemory_History" | Performance degradations |
2100040 |
Investigate in the current top contributors to CPU time (system vs. user CPU, SAP HANA vs. non SAP HANA CPU, top HANA internal CPU consumers) and eliminate CPU bottleneck situations. |
| 221 | OPERATING SYSTEM | global | Peak CPU utilization (%, last day) | SQL: "HANA_Resources_CPUAndMemory_History" | Performance degradations |
2100040 |
Investigate in the recent top contributors to CPU time (system vs. user CPU, SAP HANA vs. non SAP HANA CPU, top HANA internal CPU consumers) and eliminate CPU bottleneck situations. |
| 222 | OPERATING SYSTEM | global | Time since CPU utilization > 95 % (h) | SQL: "HANA_Resources_CPUAndMemory_History" | Performance degradations |
2100040 |
Investigate in the historic top contributors to CPU time (system vs. user CPU, SAP HANA vs. non SAP HANA CPU, top HANA internal CPU consumers) and eliminate CPU bottleneck situations. |
| 226 | OPERATING SYSTEM | global | Peak system CPU utilization (%, last day) | SQL: "HANA_Resources_CPUAndMemory_History" | Performance degradations |
2100040 |
Check which processes are responsible for the high system CPU consumption and take appropriate actions to reduce the system CPU consumption (e.g. by optimizing the operating system configuration or by avoiding active waiting within SAP HANA). |
| 230 | OPERATING SYSTEM | global | Current memory utilization (%) | SQL: "HANA_Resources_CPUAndMemory_History" | Performance degradations, terminations |
1999997 |
Investigate in the current top contributors to memory allocation and take appropriate actions to reduce the memory consumption. |
| 231 | OPERATING SYSTEM | global | Time since memory utilization > 95 % (h) | SQL: "HANA_Resources_CPUAndMemory_History" | Performance degradations, terminations |
1999997 |
Investigate in the historic top contributors to memory allocation and take appropriate actions to reduce the memory consumption. |
| 235 | OPERATING SYSTEM | scale-out | Hosts with varying physical memory size | SQL: "HANA_Hosts_Overview" | Performance degradations, terminations | 1999997 | Check why the amount of physical memory varies between SAP HANA hosts and use hosts with an identical amount of physical memory unless there is a very specific reason for the deviation. |
| 240 | OPERATING SYSTEM | global | Current swap utilization (GB) | SQL: "HANA_Resources_CPUAndMemory_History" | Performance degradations, terminations | 1999997 |
Make sure that the total memory consumption of SAP HANA and other
software components doesn't exceed the available physical memory. Check
on operating system level why a significant amount of swap space is used
and take appropriate actions to reduce the swap space utilization. |
| 241 | OPERATING SYSTEM | global | Time since swap utilization > 1 GB (h) | SQL: "HANA_Resources_CPUAndMemory_History" | Performance degradations, terminations | 1999997 |
Make sure that the total memory consumption of SAP HANA and other
software components doesn't exceed the available physical memory. Check
on operating system level why a significant amount of swap space was
used and take appropriate actions to reduce the swap space utilization. |
| 245 | OPERATING SYSTEM | global | Swap space size (GB) | SQL: "HANA_Hosts_Overview" | Performance degradations in case of swapping | 1999997 |
It is usually better if an OOM is triggered when the memory is scarce
on OS side, because swapping can reduce the system performance
significantly. Therefore it is recommended to use a rather small swap
space. |
| 250 | OPERATING SYSTEM | global | Max. used disk size (%) | SQL: "HANA_Hosts_Disks" | Stuck situations, failing backups |
1870858 |
Take appropriate actions in order to make sure that SAP HANA file system overflows will not happen |
| 260 | OPERATING SYSTEM | global | Open files limit (OS) | SQL: "HANA_Hosts_Overview" | Terminations |
1771873 |
Configure an open files limit of at least 100,000 on operating system side. |
| 270 | OPERATING SYSTEM | global | Unknown hardware components | SQL: "HANA_Hosts_Overview" | Missing information |
1828631 |
Make sure that properly named and filled hardware information files are available. |
| 280 | OPERATING SYSTEM | scale-out | Maximum time variation between hosts (s) | SQL: "HANA_Hosts_Time" | Various risks in areas like supportability and recovery |
|
Make sure that the system clocks on the hosts of a SAP HANA scale-out solution provide a nearly identical time. |
| 310 | DISK I/O | global | I/O read throughput data min. (MB/s, last day) |
SQL: "HANA_IO_KeyFigures_Total_History" SQL: "HANA_IO_KeyFigures_Detail_History" |
Performance degradations |
1999930 |
Check for bottlenecks and possible optimizations in the I/O stack related to read accesses to the data area (operating system, file system, network to I/O sub system, I/O sub system). |
| 311 | DISK I/O | global | I/O read throughput data avg. (MB/s, last day) |
SQL: "HANA_IO_KeyFigures_Total_History" SQL: "HANA_IO_KeyFigures_Detail_History" |
Performance degradations |
1999930 |
Check for bottlenecks and possible optimizations in the I/O stack related to the read accesses to data area (operating system, file system, network to I/O sub system, I/O sub system). |
| 315 | DISK I/O | global | I/O write throughput data min. (MB/s, last day) |
SQL: "HANA_IO_KeyFigures_Total_History" SQL: "HANA_IO_KeyFigures_Detail_History" |
Performance degradations |
1999930 |
Check for bottlenecks and possible optimizations in the I/O stack related to write accesses to the data area (operating system, file system, network to I/O sub system, I/O sub system). |
| 316 | DISK I/O | global | I/O write throughput data avg. (MB/s, last day) |
SQL: "HANA_IO_KeyFigures_Total_History" SQL: "HANA_IO_KeyFigures_Detail_History" |
Performance degradations |
1999930 |
Check for bottlenecks and possible optimizations in the I/O stack related to write accesses to the data area (operating system, file system, network to I/O sub system, I/O sub system). |
| 320 | DISK I/O | global | I/O read throughput log min. (MB/s, last day) |
SQL: "HANA_IO_KeyFigures_Total_History" SQL: "HANA_IO_KeyFigures_Detail_History" |
Performance degradations |
1999930 |
Check for bottlenecks and possible optimizations in the I/O stack related to read accesses to the log area (operating system, file system, network to I/O sub system, I/O sub system). |
| 321 | DISK I/O | global | I/O read throughput log avg. (MB/s, last day) |
SQL: "HANA_IO_KeyFigures_Total_History" SQL: "HANA_IO_KeyFigures_Detail_History" |
Performance degradations |
1999930 |
Check for bottlenecks and possible optimizations in the I/O stack related to read accesses to the log area (operating system, file system, network to I/O sub system, I/O sub system). |
| 325 | DISK I/O | global | I/O write throughput log min. (MB/s, last day) |
SQL: "HANA_IO_KeyFigures_Total_History" SQL: "HANA_IO_KeyFigures_Detail_History" |
Performance degradations |
1999930 |
Check for bottlenecks and possible optimizations in the I/O stack related to write accesses to the log area (operating system, file system, network to I/O sub system, I/O sub system). |
| 326 | DISK I/O | global | I/O write throughput log avg. (MB/s, last day) |
SQL: "HANA_IO_KeyFigures_Total_History" SQL: "HANA_IO_KeyFigures_Detail_History" |
Performance degradations |
1999930 |
Check for bottlenecks and possible optimizations in the I/O stack related to write accesses to the log area (operating system, file system, network to I/O sub system, I/O sub system). |
| 330 | DISK I/O | global | Max. trigger read ratio (data) | SQL: “HANA_IO_KeyFigures_Total” | Performance degradations |
1930979 |
Make sure that asynchronous queuing of I/O requests is activated. |
| 331 | DISK I/O | global | Max. trigger write ratio (data, log) | SQL: “HANA_IO_KeyFigures_Total” | Performance degradations |
1930979 |
Make sure that asynchronous queuing of I/O requests is activated. |
| 340 | DISK I/O | global | Log switch wait count ratio (%) | SQL: “HANA_Log_LogBuffers” | Performance degradations | Increase the size and / or number of the log buffers in order to reduce the contention. | |
| 341 | DISK I/O | global | Log switch race count ratio (%) | SQL: “HANA_Log_LogBuffers” | Performance degradations | Increase the size and / or number of the log buffers in order to reduce the contention. | |
| 350 | DISK I/O | global | Blocking savepoint phases > 10 s (last day) | SQL: “HANA_IO_Savepoints” | Performance degradations |
2100009 |
Check for bottlenecks and possible optimizations in the I/O stack related to write accesses to the data area (operating system, file system, network to I/O sub system, I/O sub system). |
| 351 | DISK I/O | global | Blocking savepoint phase avg. (s, last day) | SQL: “HANA_IO_Savepoints” | Performance degradations |
2100009 |
Check for bottlenecks and possible optimizations in the I/O stack related to write accesses to the data area (operating system, file system, network to I/O sub system, I/O sub system). |
| 352 | DISK I/O | global | Blocking savepoint phase max. (s, last day) | SQL: “HANA_IO_Savepoints” | Performance degradations |
2100009 |
Check for bottlenecks and possible optimizations in the I/O stack related to write accesses to the data area (operating system, file system, network to I/O sub system, I/O sub system). |
| 355 | DISK I/O | global | Time since last savepoint (s) | SQL: "HANA_IO_Savepoints" | Increased recovery times |
2100009 |
Check for bottlenecks and possible optimizations in the I/O stack
related to write accesses to the data area (operating system, file
system, network to I/O sub system, I/O sub system). Make sure that global.ini -> [persistence] -> savepoint_interval_s is not set to very high values. |
| 357 | DISK I/O | global | Savepoint write throughput (MB/s) | SQL: "HANA_IO_Savepoints" | Performance degradations |
2100009 |
Check for bottlenecks and possible optimizations in the I/O stack related to write accesses to the data area (operating system, file system, network to I/O sub system, I/O sub system). |
| 358 | DISK I/O | global | Savepoints taking longer than 900 s (last day) | SQL: "HANA_IO_Savepoints" | Performance degradations |
2100009 |
Check for bottlenecks and possible optimizations in the I/O stack related to write accesses to the data area (operating system, file system, network to I/O sub system, I/O sub system). |
| 360 | DISK I/O | global | Number of failed I/O reads |
SQL: "HANA_IO_KeyFigures_Total" SQL: "HANA_IO_KeyFigures_Total_History" |
Terminations | 1999930 | Check when and on which disk areas the errors happened and take appropriate actions to avoid failed reads in the future. |
| 361 | DISK I/O | global | Number of failed I/O writes |
SQL: "HANA_IO_KeyFigures_Total" SQL: "HANA_IO_KeyFigures_Total_History" |
Terminations | 1999930 | Check when and on which disk areas the errors happened and take appropriate actions to avoid failed writes in the future. |
| 370 | DISK I/O | global | Unused space in data files (%) | SQL: "HANA_Disks_Fragmentation" | Increased data disk space requirements | 1870858 | Reorganize the disk area using ALTER SYSTEM RECLAIM DATAVOLUME. |
| 380 | DISK I/O | global | Age of oldest backup snapshot (days) | SQL: "HANA_IO_Snapshots" | Increased data disk space requirements |
2100009 |
Database snapshots can increase the data disk space requirements and so you should delete them when they are no longer required for a specific reason. |
| 410 | MEMORY | global | Allocation limit used (%) | SQL: "HANA_Memory_Overview" | Unloads, out of memory, performance degradations | 1999997 | Check the current memory utilization and identify and resolve reasons which are responsible for the used memory coming close to the allocation limit. |
| 411 | MEMORY | global | Current allocation limit used by tables (%) | SQL: "HANA_Memory_Overview" | Unloads, out of memory, performance degradations | 1999997 | Check if the amount of memory allocated by tables can be reduced and if the system is properly sized in terms of memory. |
| 413 | MEMORY | global | Time since allocation limit used > 80 % (h) | SQL: "HANA_Resource_CPUAndMemory_History" | Unloads, out of memory, performance degradations | 1999997 | Check the historic memory utilization and identify and resolve reasons which are responsible for the used memory coming close to the allocation limit. |
| 415 | MEMORY | global | Curr. max. service allocation limit used (%) | SQL: "HANA_Services_Memory" | Unloads, out of memory, performance degradations | 1999997 | Check the current memory utilization of the critical services and identify and resolve reasons which are responsible for the used memory coming close to the allocation limit or increase the service specific allocation limit. |
| 417 | MEMORY | global | Time since service alloc. limit used > 80 % (h) | SQL: "HANA_Services_Memory" | Unloads, out of memory, performance degradations | 1999997 | Check the historic memory utilization of the critical services and identify and resolve reasons which are responsible for the used memory coming close to the allocation limit or increase the service specific allocation limit. |
| 420 | MEMORY | global | Heap areas currently larger than 50 GB | SQL: "HANA_Memory_TopConsumers" | Unloads, out of memory, performance degradations | 1999997 | Check if you can reduce the memory allocation of the largest heap areas. |
| 421 | MEMORY | global | Heap areas larger than 100 GB (last day) | SQL: "HANA_Memory_TopConsumers_History" | Unloads, out of memory, performance degradations | 1999997 | Check if you can reduce the memory allocation of the largest heap areas. |
| 422 | MEMORY | global | Heap areas larger than 200 GB (history) | SQL: "HANA_Memory_TopConsumers_History" | Unloads, out of memory, performance degradations | 1999997 | Check why the heap allocators have grown to significant sizes in the past and if you can reduce the memory allocation of the largest heap areas. |
| 425 | MEMORY | global | Pool/RowEngine/CpbTree leak size (GB) | SQL: "HANA_RowStore_TotalIndexSize" | Unloads, out of memory, performance degradations | 1999997 | Monitor the growth and restart SAP HANA if required. Upgrade to revision 83 or higher in order to eliminate a related bug. |
| 430 | MEMORY | global | Number of column store unloads (last day) | SQL: "HANA_Tables_ColumnStore_Unloads" | Performance degradations | 2127458 | Check why unloads were triggered (e.g. high memory consumption or inadequate configuration of unload_lower_bound) |
| 431 | MEMORY | global | Time since last column store unload (days) | SQL: "HANA_Tables_ColumnStore_Unloads" | Performance degradations | 2127458 | Check why unloads were triggered (e.g. high memory consumption or inadequate configuration of unload_lower_bound) |
| 440 | MEMORY | global | Shared memory utilization of nameserver (%) | SQL: "HANA_Memory_SharedMemory" | Terminations | 1977101 | Increase the nameserver topology size or reduce the number of (temporary) tables. |
| 450 | MEMORY | Rev. >= 70 | Tables with memory LOBs > 2 GB | SQL: "HANA_Configuration_HybridLOBActivation_CommandGenerator" | Increased memory consumption | 1994962 | Make sure that large memory LOBs are converted into hybrid LOBs in order to reduce the memory footprint. |
| 453 | MEMORY | Rev. >= 70 | Size of non-unique concat attributes (GB) | SQL: "HANA_Tables_ColumnStore_Columns" | Increased memory consumption | 1986747 | Check if the largest non-unique concat attributes are really required or if they can be eliminated (e.g. by switching to single column indexes or by dropping no longer required relicts). |
| 460 | MEMORY | global | Calculation engine cache utilization (%) | SQL: "HANA_CalculationEngine_CalculationScenarios" | Performance degradations |
Increase the size of the calculation engine cache size, so that it can concurrently keep all required calculation scenarios:indexserver.ini -> [calcengine] -> max_cache_size_bytes = <byte> |
|
| 510 | TABLES | global | Tables with > 100 partitions | SQL: "HANA_Tables_PartitionedTables" | Performance degradations, high resource consumption | 2044468 | Check if the high amount of partitions is really required. |
| 520 | TABLES | global | Tables / partitions > 1.5 billion rows | SQL: "HANA_Tables_ColumnStore_Records" | Terminations | 1921694 |
Activate or extend partitioning in order to keep the amount of
records per table (partition) permanently below the two billion limit. BW SID tables (typically following the naming convention /B%/S% are excluded from this check, because they can never grow beyond two billion records and partitioning can be critical. See Check ID 2022 instead. |
| 521 | TABLES | global | Table histories > 1.5 billion rows | SQL: "HANA_Tables_ColumnStore_Records" | Terminations | 1921694 | Activate or extend partitioning in order to keep the amount of records per table history (partition) permanently below the two billion limit. |
| 530 | TABLES | global | Row store size (GB) | SQL: "HANA_RowStore_Overview" | Increased startup time, terminations |
2050579 1963779 |
Check for largest tables in row store whether data can be cleaned or tables can be moved to column store. |
| 533 | TABLES | global | Row store tables with more than 1 container | SQL: "HANA_Tables_RowStore_TablesWithMultipleContainers" | Risk of performance regressions | 2000002 |
Reorganize the mentioned tables during a time of low system load in order to avoid overhead and performance regressions:ALTER TABLE "<table_name>" RECLAIM DATA SPACE |
| 535 | TABLES | global | Row store (> 10 GB) fragmentation (%) | SQL: "HANA_RowStore_Overview" | Increased memory consumption | 1813245 | Reorganize the row store in order to minimize fragmentation. |
| 538 | TABLES | global | Delta merges > 900 s (last day) | SQL: "HANA_Tables_ColumnStore_Merges" | Increased resource consumption | 2057046 | Check if the long running merges are normal or if they can be avoided (e.g. by using default merge parameter settings or by reducing the data volume) |
| 540 | TABLES | global | Failing delta merges (info messages, last day) | SQL: "HANA_Tables_ColumnStore_Merges" | Increased resource consumption, terminations | 2057046 | Check if it is possible to reduce the amount of terminations of merges with informational messages. |
| 541 | TABLES | global | Failing delta merges (error messages, last day) | SQL: "HANA_Tables_ColumnStore_Merges" | Increased memory consumption, terminations | 2057046 | Check the individual error messages and take appropriate actions to prevent critical terminations. |
| 542 | TABLES | global | Auto merge tables with delta storage > 5 GB | SQL: "HANA_Tables_ColumnStore_DeltaStorage" | Increased memory consumption | 1977314 | Check if delta storages permanently remain on a high level. If yes, check why no merge happens and take appropriate actions to make sure that merges are performed. |
| 543 | TABLES | global | Auto merge tables with many delta records | SQL: "HANA_Tables_ColumnStore_DeltaStorage" | Increased memory consumption | 1909742 | Check if delta storages permanently remain on a high level. If yes, check why no merge happens and take appropriate actions to make sure that merges are performed. |
| 544 | TABLES | global | Non-auto merge tables with delta storage > 5 GB | SQL: "HANA_Tables_ColumnStore_DeltaStorage" | Increased memory consumption | 1977314 | Check if delta storages permanently remain on a high level. If yes, check why no merge happens and take appropriate actions to make sure that merges are performed. |
| 545 | TABLES | global | Non-auto merge tables with many delta records | SQL: "HANA_Tables_ColumnStore_DeltaStorage" | Increased memory consumption | 1909742 | Check if delta storages permanently remain on a high level. If yes, check why no merge happens and take appropriate actions to make sure that merges are performed. |
| 546 | TABLES | global | Sum of delta size of small tables (GB) | SQL: "HANA_Tables_ColumnStore_DeltaStorage" | Increased memory consumption | 2057046 | Consider adjusting the auto merge decision function so that smaller tables are merged more frequently. See "Is the default auto merge decision function already optimal?" in SAP Note 2057046 for more details. |
| 547 | TABLES | global | Non BW tables with disabled auto merge | SQL: “HANA_Tables_ColumnStore_AutoMergeDisabled” | Increased memory consumption | 1977314 | Check if auto merge is disabled on purpose and reactivate it if possible. |
| 548 | TABLES | global | Tables with disabled persistent merge | SQL: “HANA_Tables_ColumnStore_PersistentMergeDisabled” | Increased memory consumption | 2057046 | Check if persistent merge is disabled on purpose and reactivate it if possible. |
| 549 | TABLES | global | Non BW tables with disabled auto compression | SQL: "HANA_Tables_ColumnStore_AutoCompressionDisabled" | Increased memory consumption |
2112604 |
Check if the auto compression is disabled on purpose and reactivate it if possible. |
| 550 | TABLES | global | Row store tables with ST_POINT columns | SQL: "HANA_Columns" | Crashes, terminations | 2038897 | Make sure that ST_POINT columns don't exist for row store tables. |
| 555 | TABLES | Rev. <= 82 | Tables / partitions moved only logically | SQL: “HANA_Tables_ColumnStore_TablesMovedLogically” | Risk of corruptions | 2066313 | Make sure that tables are moved physically. |
| 560 | TABLES | global | Tables > 10 Mio. rows not compressed | SQL: "HANA_Tables_ColumnStore_TablesWithoutCompressionOptimization" | Increased memory consumption |
2105761 2112604 |
Make sure that an optimization of compression happens for large tables. |
| 561 | TABLES | global | Columns > 10 Mio. rows not compressed | SQL: "HANA_Tables_ColumnStore_ColumnsWithoutCompressionOptimization" | Increased memory consumption |
2112604 |
Make sure that large columns are compressed efficiently. |
| 563 | TABLES | global | Indexes on large SPARSE / PREFIXED columns | SQL: "HANA_Indexes_ColumnStore_IndexesOnSparseAndPrefixedColumns" | Performance regressions |
2112604 |
Indexes can't be used on columns being compressed with SPARSE or PREFIXED type. Execute an optimize compression run in order to make sure that indexed columns are not compressed with SPARSE or PREFIXED. |
| 565 | TABLES | global | Tables > 10 Mio. rows and > 200 % UDIV rows | SQL: "HANA_Tables_ColumnStore_Records" | Increased memory consumption |
2112604 |
Force a compression optimization after large deletions of data if required. |
| 570 | TABLES | global | Number of temporary tables | SQL: "HANA_Tables_TemporaryTables" | Risk of administration overhead | Keep the amount of temporary SAP HANA tables at a limited level and drop temporary tables when they are no longer needed. | |
| 580 | TABLES | global | Tables assigned to wrong service | Risk of inconsistencies and terminations | Make sure that all tables are assigned to proper services, typically indexserver. | ||
| 582 | TABLES | global | Tables with empty table location | SQL: "HANA_Tables_TableLocations" | Risk of inconsistencies and terminations | Investigate why tables without a properly assigned location exist and eliminate the inconsistency. |
| Check ID | Area | Validity | Description | Analysis SQL statement (SAP Note 1969700) | Potential impact | SAP Note | Details |
| 610 | TRACES, DUMPS AND LOGS | Rev. >= 70 | Kernel profiler active | SQL: "HANA_Traces_ActivatedAndModified Traces" | Performance degradations |
1804811 |
Disable the kernel profiler traces unless really needed. |
| 612 | TRACES, DUMPS AND LOGS | global | Performance trace enabled | SQL: "HANA_Traces_ActivatedAndModified Traces" | Performance degradations | 1787489 | Disable the performance trace unless really needed. |
| 614 | TRACES, DUMPS AND LOGS | global | SQL trace enabled | SQL: "HANA_Traces_ActivatedAndModified Traces" | Performance degradations | 2031647 | Disable the SQL trace unless really needed. |
| 616 | TRACES, DUMPS AND LOGS | global | Indexserver python trace enabled | SQL: "HANA_Traces_ActivatedAndModified Traces" | Performance degradations |
1935113 |
Disable the indexserver python trace unless really needed. |
| 617 | TRACES, DUMPS AND LOGS | global | Executor python trace enabled | SQL: "HANA_Traces_ActivatedAndModified Traces" | Performance degradations |
2020877 |
Disable the executor python trace unless really needed. |
| 618 | TRACES, DUMPS AND LOGS | global | SQL client network trace enabled | SQL: "HANA_Traces_ActivatedAndModified Traces" | Performance degradations |
|
Disable the SQL client network trace unless really needed. |
| 620 | TRACES, DUMPS AND LOGS | global | Minimum expensive SQL trace threshold (ms) | SQL: "HANA_Traces_ActivatedAndModified Traces" | Performance degradations | 2180165 | Don't use an expensive statement trace threshold below 1000 ms unless really needed. |
| 630 | TRACES, DUMPS AND LOGS | global | Existence of database trace settings | SQL: "HANA_Traces_ActivatedAndModified Traces" | Performance degradations | 2119087 | Don't configure special database trace settings unless really needed. |
| 635 | TRACES, DUMPS AND LOGS | global | Existence of user-specific trace settings | SQL: "HANA_Traces_ActivatedAndModified Traces" | Performance degradations | 2119087 | Don't configure user-specific trace settings unless really needed. |
| 637 | TRACES, DUMPS AND LOGS | global | Existence of end-to-end trace settings | SQL: "HANA_Traces_ActivatedAndModified Traces" | Performance degradations | 2119087 | Don't configure end-to-end trace settings unless really needed. |
| 640 | TRACES, DUMPS AND LOGS | global | Number of tracefiles (total) | SQL: "HANA_TraceFiles_Overview" | Increased disk space requirements | 1977162 | Take appropriate actions to limit the amount of existing trace files. |
| 641 | TRACES, DUMPS AND LOGS | global | Number of tracefiles (last day) | SQL: "HANA_TraceFiles_Overview" | Increased disk space requirements | 1977162 | Take appropriate actions to limit the amount of existing trace files. |
| 642 | TRACES, DUMPS AND LOGS | global | Size of tracefiles (GB, total) | SQL: "HANA_TraceFiles_Overview" | Increased disk space requirements | 1977162 | Take appropriate actions to limit the size of existing trace files. |
| 643 | TRACES, DUMPS AND LOGS | global | Size of tracefiles (GB, last day) | SQL: "HANA_TraceFiles_Overview" | Increased disk space requirements | 1977162 | Take appropriate actions to limit the size of existing trace files. |
| 644 | TRACES, DUMPS AND LOGS | global | Size of largest trace file (MB) | SQL: "HANA_TraceFiles_Overview" | Increased disk space requirements | 1977162 | Take appropriate actions to limit the size of existing trace files. |
| 645 | TRACES, DUMPS AND LOGS | global | Number of OOM tracefiles (last day) | SQL: "HANA_TraceFiles_Overview" | Memory bottlenecks | 1999997 | Check the historic memory utilization and identify and resolve reasons which are responsible for the out of memory situations. |
| 646 | TRACES, DUMPS AND LOGS | global | Number of statement OOM tracefiles (last day) | SQL: "HANA_TraceFiles_Overview" | Terminations | 1999997 | Optimize SQL statements with a particularly high memory consumption. |
| 650 | TRACES, DUMPS AND LOGS | global | Number of crash dumps (last day) | SQL: "HANA_TraceFiles_Overview" | Unplanned downtime | 2177064 | Check for reasons responsible for crashes and eliminate them. |
| 652 | TRACES, DUMPS AND LOGS | global | Number of page dumps (last day) | SQL: "HANA_TraceFiles_Overview" | Corruptions | 1977242 | Analyze and repair corruptions on persistence level. |
| 655 | TRACES, DUMPS AND LOGS | global | Number of RTE dumps (last day) | SQL: "HANA_TraceFiles_Overview" | Increased system load | Check why the RTE dumps were generated and make sure that they are only triggered manually when really required. | |
| 658 | TRACES, DUMPS AND LOGS | global | Time since last dump (days) | SQL: "HANA_TraceFiles_Overview" | Various | 2119087 | Check why the dump was created and eliminate the root cause. |
| 660 | TRACES, DUMPS AND LOGS | global | CPU time measurement enabled | SQL: "HANA_Configuration_Parameters" | Increased system load | 2100040 | Disable the CPU time measurement unless really needed. |
| 710 | STATISTICS SERVER | global | Open alerts (high priority) | SQL: "HANA_StatisticsServer_Alerts_Current" | Various | 2053330 | Check the reported alerts and resolve the root cause or adjust the alert thresholds. |
| 711 | STATISTICS SERVER | global | Open alerts (error state) | SQL: "HANA_StatisticsServer_Alerts_Current" | Monitoring limitations | 2053330 | Check why retrieving alerts runs into an internal error and eliminate the root cause. |
| 712 | STATISTICS SERVER | global | Internal statistics server errors (last day) | SQL: "HANA_StatisticsServer_Alerts_History" | Monitoring limitations | 2147247 | Analyze and eliminate the root cause of the internal statistics server errors. |
| 715 | STATISTICS SERVER | global | Number of actions not executed as expected | SQL: "HANA_StatisticsServer_Schedule" | Monitoring limitations | 2147247 | Check why alert checks and / or history collection runs were not executed with the defined interval frequency (e.g. high system load, disabled checks) and eliminate the root cause. |
| 717 | STATISTICS SERVER | ESS | Number of disabled actions | SQL: "HANA_StatisticsServer_Schedule" | Monitoring limitations | 2113228 | Check why the actions are disabled and activate them again if possible. |
| 718 | STATISTICS SERVER | ESS | Number of relevant inactive actions | SQL: "HANA_StatisticsServer_Schedule" | Monitoring limitations | 2147247 | Check why the actions are inactive and activate them again if possible. |
| 720 | STATISTICS SERVER | global | Events not acknowledged since >= 1800 s | SQL: "HANA_Events" | Monitoring limitations | 2126236 |
Check why the events are not acknowledged (e.g. statistics server
problem) and take appropriate actions to make sure that an
acknowledgement happens in time. Often this problem is caused by the bug described in SAP Note 2126236 and it can be fixed by implementing patch_check_21_add_info_events.sql. |
| 730 | STATISTICS SERVER | ESS | Pending e-mails older than 3 days | SQL: "HANA_StatisticsServer_MailNotifications" | Monitoring limitations | 2133799 | Check why sending e-mails doesn't work, clean up old mail send requests and take appropriate actions to activate sending e-mails. |
| 735 | STATISTICS SERVER | ESS | Alerts older than 42 days | SQL: "HANA_Data_ColumnValueCounter_CommandGenerator" | Performance regressions, increased memory consumption | 2170779 | Keep the amount of alerts in table STATISTICS_ALERTS_BASE at a reasonable level. |
| 736 | STATISTICS SERVER | ESS | Alerts reported frequently | SQL: "HANA_Data_ColumnValueCounter_CommandGenerator" | Performance regressions, increased memory consumption | 2147247 | Delete frequently reported alerts and make sure that the reporting frequency is reduced. |
| 740 | STATISTICS SERVER | global | Time since statistics server run (s) | SQL: "HANA_StatisticsServer_Schedule" | Monitoring limitations | 2147247 | Check why the statistics server isn't active since a significant time and take appropriate actions to activate it. |
| 745 | STATISTICS SERVER | global | Total size of statistics server tables (GB) | SQL: "HANA_Tables_LargestTables" | Increased memory consumption | 2147247 | Make sure that data collection and deletion is configured optimally for the statistics server histories. |
| 747 | STATISTICS SERVER | SSS | Number of zero entries in HOST_SQL_PLAN_CACHE | SQL: "HANA_Data_ColumnValueCounter_CommandGenerator" | Increased memory consumption | 2084747 | Delete records with EXECUTION_COUNT = 0 from HOST_SQL_PLAN_CACHE and adjust the data collection by adding "WHERE EXECUTION_COUNT > 0" to the query parameter. |
| 748 | STATISTICS SERVER | global | History of M_CS_UNLOADS collected | SQL: "HANA_StatisticsServer_Schedule" | Increased memory consumption | 2084747 | Monitoring view M_CS_UNLOADS it already a history, because it is based on load trace files. As a consequence a historization is not useful and - even worse - it can result in a high memory consumption of the statistics server if many unloads have happened in the past. Therefore you should disable the history collection for M_CS_UNLOADS. |
| 750 | STATISTICS SERVER | global | Stat. server tables with retention < 42 days | SQL: "HANA_StatisticsServer_RetentionTime" | Monitoring limitations | 2147247 | Check to what extent you can increase the retention times to 42 days (exception: HOST_CS_UNLOADS) without suffering from large table growth and high memory requirements. |
| 755 | STATISTICS SERVER | Rev. >= 74 | Embedded statistics server used | SQL: "HANA_Configuration_Parameters" | Monitoring limitations | 2092033 | Consider a migration to the embedded statistics server. |
| 760 | STATISTICS SERVER | ESS | Status of embedded statistics server migration | SQL: "HANA_StatisticsServer_Properties" | Monitoring limitations | 2092033 | Complete a successful migration to the embedded statistics server. |
| 765 | STATISTICS SERVER | SSS | Log segment size of statisticsserver (MB) | SQL: "HANA_Logs_LogBuffers" | Backup problems | 2019148 | Increase the log segment size of the statistics server to 1024 MB. |
| 770 | STATISTICS SERVER | ESS | Number of stat. server tables not on master | SQL: "HANA_Tables_LargestTables" | Monitoring limitations | 2091256 | Move all statistics server tables to the master node. |
| 810 | TRANSACTIONS AND THREADS | global | Current MVCC versions in row store | SQL: "HANA_GarbageCollection_Overview" | Performance degradations | 2169283 | Avoid long running transactions responsible for a significant amount of MVCC versions. |
| 811 | TRANSACTIONS AND THREADS | Rev. >= 90 | Max. MVCC versions in row store (last day) | SQL: "HANA_LoadHistory_Services" | Performance degradations | 2169283 | Avoid long running transactions responsible for a significant amount of MVCC versions. |
| 812 | TRANSACTIONS AND THREADS | global | Max. versions per record in row store |
SQL: "HANA_GarbageCollection_Overview" |
Performance degradations | 2169283 | Avoid long running transactions responsible for a significant amount of MVCC versions. |
| 815 | TRANSACTIONS AND THREADS | global | Age of transaction blocking row store MVCC (s) | SQL: "HANA_Transactions_UncommittedUpdateTransactions" | Performance degradations | 2169283 | Avoid long running transactions responsible for a significant amount of MVCC versions. |
| 830 | TRANSACTIONS AND THREADS | global | Active commit ID range |
SQL: "HANA_GarbageCollection_Overview" |
Performance degradations | 2169283 | Optimize particularly long running transactions. |
| 832 | TRANSACTIONS AND THREADS | Rev. >= 90 | Max. active commit ID range (last day) |
SQL: "HANA_LoadHistory_Services" |
Performance degradations | 2169283 | Optimize particularly long running transactions. |
| 840 | TRANSACTIONS AND THREADS | global | Active update transaction ID range |
SQL: "HANA_GarbageCollection_Overview" |
Performance degradations | 2169283 | Optimize particularly long running update transactions (i.e. transactions with change operations). |
| 841 | TRANSACTIONS AND THREADS | global | Oldest active update transaction (s) | SQL: "HANA_Transactions_UncommittedUpdateTransactions" | Performance degradations | 2169283 | Optimize particularly long running update transactions (i.e. transactions with change operations). |
| 842 | TRANSACTIONS AND THREADS | Rev. >= 90 | Max. active update trans. ID range (last day) | SQL: "HANA_LoadHistory_Services" | Performance degradations | 2169283 | Optimize particularly long running update transactions (i.e. transactions with change operations). |
| 845 | TRANSACTIONS AND THREADS | Rev. 64 - 69.04 | Maximum update transaction ID |
SQL: "HANA_GarbageCollection_Overview" |
Restart and recovery termination | 2001840 | Proceed according to the steps in the referenced SAP Note in order to make sure that you don't run into restart and recovery issues. |
| 850 | TRANSACTIONS AND THREADS | global | Persistence garbage collection history count | SQL: "HANA_GarbageCollection_Persistence" | Increased memory and disk space requirements | 2169283 | Avoid long running transactions and open idle cursors blocking garbage collection. |
| 852 | TRANSACTIONS AND THREADS | global | Undo and cleanup files | SQL: "HANA_Transactions_UndoCleanupFiles" | Increased memory and disk space requirements | 2169283 | Avoid long running transactions and open idle cursors blocking garbage collection. |
| 854 | TRANSACTIONS AND THREADS | global | Undo and cleanup file size (GB) | SQL: "HANA_Transactions_UndoCleanupFiles" | Increased memory and disk space requirements | 2169283 | Avoid long running transactions and open idle cursors blocking garbage collection. |
| 856 | TRANSACTIONS AND THREADS | global | Max. undo size of current transaction (MB) | SQL: "HANA_Transactions_UndoAndRedoLog" | Increased memory and disk space requirements, increased I/O and system replication load, longer restart times | 2169283 | Check if you can reduce the amount of performed change operations or if you can reduce the COMMIT frequency in order to split the change load into several smaller pieces. |
| 857 | TRANSACTIONS AND THREADS | global | Max. redo size of current transaction (MB) | SQL: "HANA_Transactions_UndoAndRedoLog" | Increased memory and disk space requirements, increased I/O and system replication load, longer restart times | 2169283 | Check if you can reduce the amount of performed change operations or if you can reduce the COMMIT frequency in order to split the change load into several smaller pieces. |
| 860 | TRANSACTIONS AND THREADS | Rev. >= 90 | Current pending sessions | SQL: "HANA_LoadHistory_Services" | Performance regressions | 2000000 | Check why sessions are currently pending and eliminate SAP HANA overload situations. |
| 863 | TRANSACTIONS AND THREADS | Rev. >= 90 | Avg. pending sessions (last day) | SQL: "HANA_LoadHistory_Services" | Performance regressions | 2000000 | Check why sessions were recently pending and eliminate SAP HANA overload situations. |
| 870 | TRANSACTIONS AND THREADS | global | SelfWatchDog activity time (%, last hour) | SQL: "HANA_Threads_ThreadSamples_FilterAndAggregation" | Performance regressions | 1999998 | A high activity time of the SelfWatchDog thread can indicate problems accessing the lock file for shared memory accesses in the /tmp directory. See SAP Notes 1999998 and 2100296 for more information. |
| 880 | TRANSACTIONS AND THREADS | global | Open connections (%) | SQL: "HANA_Connections_Overview" | Terminations | 1910159 |
This check compares the number of open connections with the maximum_connections limitation (see SAP Note 2154870). Check if it is possible to reduce the number of open connections or consider increasing the maximum_connections parameter. |
| 882 | TRANSACTIONS AND THREADS | global | Max. parked job worker ratio | SQL: "HANA_Jobs_Executors" | Performance regressions | 2256719 |
In rare situations a bug with Rev. <= 102.02 and Rev. 110 can
result in an increased amount of parked job workers. As a consequence
the system performance can suffer and in the worst case it comes to
standstill. Consider an upgrade to a more recent SAP HANA Revision level
or alternatively restart SAP HANA. |
| 910 | BACKUP | global | Age of last data backup (days) | SQL: "HANA_Backups_BackupRuns" | Recoverability limitations | 2091951 | Make sure that at least one successful data backup is created per day. |
| 911 | BACKUP | global | Age of last data backup error (days) | SQL: "HANA_Backups_BackupRuns" | Recoverability limitations | 2091951 | Identify and eliminate the root causes of the backup failures and make sure that at least one successful data backup is created per day. |
| 915 | BACKUP | global | Min. data backup throughput (GB/h, last week) | SQL: "HANA_Backups_BackupRuns" | Disk I/O performance regressions | 1999930 | Check and eliminate bottlenecks in the disk I/O area to the data files. |
| 916 | BACKUP | global | Avg. data backup throughput (GB/h, last week) | SQL: "HANA_Backups_BackupRuns" | Disk I/O performance regressions | 1999930 | Check and eliminate bottlenecks in the disk I/O area to the data files. |
| 918 | BACKUP | global | Maximum data backup duration (h, last week) | SQL: "HANA_Backups_BackupRuns" | Disk I/O performance regressions | 1999930 | Check if it is possible to improve the backup runtime (e.g. by improving the I/O performance). |
| 920 | BACKUP | global | Age of last log backup (hours) | SQL: "HANA_Backups_BackupRuns" | Recoverability limitations | 2091951 | Make sure that regular successful log backups are created. |
| 921 | BACKUP | global | Age of last log backup error (days) | SQL: "HANA_Backups_BackupRuns" | Recoverability limitations | 2091951 | Identify and eliminate the root causes of the backup failures. |
| 922 | BACKUP | global | Maximum log backup duration (s, last day) | SQL: "HANA_Backups_BackupRuns" | Recoverability limitations | 1999930 | Check why the runtime of the log backups is increased and eliminate potential bottlenecks. |
| 930 | BACKUP | global | Automatic log backup | SQL: "HANA_Configuration_Parameters" | Recoverability limitations | 1645183 | Configure the automatic log backup based on the SAP recommendations. |
| 935 | BACKUP | global | Database log mode | SQL: "HANA_Configuration_Parameters" | Recoverability limitations | 1645183 | Configure the log mode based on the SAP recommendations. |
| 940 | BACKUP | global | Size of backup catalog (MB) | SQL: "HANA_Backups_CatalogSize" | Increased I/O load, log full situations | 2083715 | Keep the backup catalog at a reasonable size. |
| 942 | BACKUP | global | Catalog size share (last day, %) | SQL: "HANA_Backups_BackupRuns" | Increased backup size and duration | 2083715 | Keep the backup catalog at a reasonable size so it doesn't increase the overall backup size significantly. |
| 945 | BACKUP | global | Age of oldest backup in catalog (days) | SQL: "HANA_Backups_CatalogSize" | Increased I/O load, log full situations | 2083715 | Regularly remove old backups from the catalog (but keep at least backups of the recent 28 days). |
| 950 | BACKUP | global | Log segments not free for reuse | SQL: "HANA_Logs_LogSegments" | Log full situations | 2083715 | Take appropriate actions to make sure that log segments are freed for reuse. |
| 952 | BACKUP | global | Log segments free for reuse | SQL: "HANA_Logs_LogSegments" | Log full situations | 2083715 | Consider reducing the number of free log segments using ALTER SYSTEM RECLAIM LOG. |
| 955 | BACKUP | global | Max. number of log backups / service (last day) | SQL: "HANA_Backups_BackupRuns" | Backup bottlenecks and increased backup catalog sizes | 2083715 | Increase the log segment size of the involved services in order to keep the amount of log backups on a reasonable level. |
| 1010 | LOCKS | global | Age of oldest active trans. lock wait (s) | SQL: "HANA_Locks_Transactional_Current" | Performance regressions and stuck situations | 1999998 | Analyze and eliminate long running current record or table lock wait situations. |
| 1011 | LOCKS | global | Trans. lock wait durations > 600 s (last day) | SQL: "HANA_Locks_Transactional_History" | Performance regressions and stuck situations | 1999998 | Analyze and eliminate long running historic record or table lock wait situations. |
| 1020 | LOCKS | global | Threads currently waiting for locks | SQL: "HANA_Threads_CurrentThreads" | Performance regressions and stuck situations | 1999998 | Analyze and eliminate current transactional and internal lock wait situations. |
| 1021 | LOCKS | global | Maximum threads waiting for locks (last day) | SQL: "HANA_Threads_ThreadSamples_AggregationPerTimeSlice" | Performance regressions and stuck situations | 1999998 | Analyze and eliminate historic transactional and internal lock wait situations. |
| 1030 | LOCKS | Rev. >= 90 | Concurrently blocked transactions (last hour) |
SQL: "HANA_LoadHistory_Services"SQL: "HANA_Locks_Transactional_History" |
Performance regressions and stuck situations | 1999998 | Analyze and eliminate long running record or table lock wait situations. |
| 1031 | LOCKS | Rev. >= 90 | Concurrently blocked transactions (last day) |
SQL: "HANA_LoadHistory_Services"SQL: "HANA_Locks_Transactional_History" |
Performance regressions and stuck situations | 1999998 | Analyze and eliminate long running record or table lock wait situations. |
| 1110 | SQL | global | SQL using in average > 1 connection (last day) | SQL: "HANA_SQL_SQLCache" | Long runtimes, high CPU consumption | 2000002 | Analyze and SQL statements with a high total elapsed time. |
| 1112 | SQL | global | SQL using in average > 1 thread (last hour) | SQL: "HANA_Threads_ThreadSamples_FilterAndAggregation" | Long runtimes, high CPU consumption | 2000002 | Analyze and SQL statements with a high total elapsed time. |
| 1113 | SQL | ESS | SQL using in average > 1 thread (last day) | SQL: "HANA_Threads_ThreadSamples_FilterAndAggregation" | Long runtimes, high CPU consumption | 2000002 | Analyze and SQL statements with a high total elapsed time. |
| 1115 | SQL | global | Longest running current SQL statement (s) | SQL: "HANA_SQL_ActiveStatements" | Long runtimes, high CPU consumption | 2000002 | Analyze and optimize current long running SQL statements. |
| 1118 | SQL | global | Longest running current job (s) | SQL: "HANA_Jobs_JobProgress" | Long runtimes, high rsource consumption | 2000002 | Analyze and optimize current long running jobs and background activities. |
| 1120 | SQL | global | Exp. stmt. trace: SQL running > 1 h (last day) | SQL: "HANA_SQL_ExpensiveStatements" | Long runtimes, high CPU consumption | 2000002 | Analyze and optimize long running SQL statements. |
| 1130 | SQL | global | SQL cache evictions / h | SQL: "HANA_SQL_SQLCache_Overview" | SQL parse overhead | 2124112 | Check if more SQL statements with bind variables can be used or if the SQL plan cache can be increased. |
| 1131 | SQL | global | SQL cache hit ratio of indexserver (%) | SQL: "HANA_SQL_SQLCache_Overview" | SQL parse overhead | 2124112 | Check if more SQL statements with bind variables can be used or if the SQL plan cache can be increased. |
| 1132 | SQL | ABAP, SPS <= 08 | SQL cache size per SQL connection (MB) | SQL: "HANA_SQL_SQLCache_Overview" | SQL parse overhead | 2124112 | Make sure that at least 15 MB of SQL cache is available for each ABAP SQL connection, because all statements in the ABAP SQL statement cache will be pinned in the SQL cache of SAP HANA up to SAP HANA SPS 08. |
| 1140 | SQL | global | SQL preparation runtime share (%) | SQL: "HANA_SQL_SQLCache_Preparation" | SQL parse overhead | 2124112 | Check if more SQL statements with bind variables can be used or if the SQL plan cache can be increased. |
| 1142 | SQL | global | Table(s) using > 10 % of SQL cache | SQL: "HANA_Memory_SQLCache_TopConsumers" | SQL parse overhead, increased memory consumption | 2124112 | Check which classes of SQL statements are responsible and eliminate the root cause (e.g. by introducing bind variables or limiting the mount of IN lists with different lengths). |
| 1145 | SQL | ABAP | SQL cache used by IN LISTs >= 100 elements (%) | SQL: "HANA_Memory_SQLCache_TopConsumers" | SQL parse overhead, increased memory consumption | 2124112 |
Consider reducing ABAP profile parameter rsdb/max_in_blocking_factor in order to reduce the number of very long IN LISTs. |
| 1147 | SQL | global | Duplicate statement hashes in SQL cache (%) | SQL: "HANA_Memory_SQLCache_TopConsumers" | SQL parse overhead, increased memory consumption | 2124112 |
Check why certain SQL statement exist multiple times in the SQL cache and check for optimizations. |
| 1148 | SQL | global | Statements existing > 100 times in SQL cache | SQL: "HANA_SQL_SQLCache" | SQL parse overhead, increased memory consumption, increased translation table load | 2124112 | Check if you can reduce the amount of instantiations of these SQL statements in the SQL cache, e.g. by optimizing stacked SQL statements. |
| 1149 | SQL | global | Statements with SESSION LOCAL sharing type (%) | SQL: "HANA_Memory_SQLCache_TopConsumers" | SQL parse overhead, increased memory consumption | 2124112 | Check why significant amounts of SQL plans exist with plan sharing type SESSION LOCAL (e.g. temporary table accesses). |
| 1150 | SQL | global | Pinned statements in SQL cache (%) | SQL: "HANA_SQL_SQLCache" | SQL parse overhead, increased memory consumption | 2124112 | Check why a high amount of SQL cache entries is still referenced and as a consequence pinned in memory. |
| 1160 | SQL | SPS >= 11 | Average COMMIT time (ms) | SQL: "HANA_Connections_Statistics" | Performance regressions | 2000000 | Analyze the increased COMMIT times and put a particular focus on bottlenecks in the disk I/O area (SAP Note 1999930) and system replication area (SAP Note 1999880). |
| 1210 | APPLICATION | ABAP | DDLOG sequence cache size | SQL: "HANA_Configuration_Sequences" | Memory and performance issues | 2000002 | Buffer the DDLOG sequence. |
| 1220 | APPLICATION | ABAP client | QCM conversion tables | SQL: "HANA_Tables_SpecialTables" | Increased space requirements, Issues during system copies | 9385 | Remove QCM tables that are no longer needed. |
| 1250 | APPLICATION | BPC | Physical BPC tables | SQL: "HANA_Tables_SpecialTables" | Increased nameserver topology size | 1835219 | Remove BPC tables that are no longer needed. |
| 1260 | APPLICATION | ABAP | QIWKTAB update rate (updates / h) | SQL: "HANA_SQL_SQLCache" | Risk of performance problems due to row store version consolidation | 2000002 |
Go to transaction SMQ2 and check all clients for unnecessary entries in STOP state and process or remove them. Implement SAP Note 2125972 in order to optimize the reprocessing logic of SMQ2. |
| 1310 | SECURITY | global | Secure store (SSFS) status | Authentication issues | 1977221 | Make sure that the secure store is available and consistent. | |
| 1330 | SECURITY | global | Number of users with expiration date | SQL: "HANA_Security_Users" | Authentication issues | Make sure that important users don't expire. | |
| 1335 | SECURITY | global | Number of SAP users with password expiration | SQL: "HANA_Security_Users" | Authentication issues | 1702224 | Make sure that the password of technical SAP users don't expire. |
| 1340 | SECURITY | global | CATALOG READ privilege granted to current user | SQL: "HANA_Security_GrantedRolesAndPrivileges" | Analysis limitations, performance overhead | 1640741 | Assign the CATALOG READ role to all users required for SAP HANA monitoring and analysis. |
| 1342 | SECURITY | global | Users with SAP_INTERNAL_HANA_SUPPORT role | SQL: "HANA_Security_GrantedRolesAndPrivileges" | Security violation | 2081857 | SAP_INTERNAL_HANA_SUPPORT provides comprehensive privileges. Make sure that this role isn't assigned to users unless really required. |
| 1345 | SECURITY | global | DATA ADMIN privilege granted to users or roles | SQL: "HANA_Security_GrantedRolesAndPrivileges" | Security violation | 863362 | DATA ADMIN allows users to access and modify data of other users. Avoid granting the DATA ADMIN privilege to users and roles. |
| 1350 | SECURITY | global | SQL trace including results configured | SQL: "HANA_Configuration_Parameters" | Security violation | 863362 | For data security reasons SQL traces should not be done with level ALL_WITH_RESULTS. |
| 1360 | SECURITY | gobal | Size of audit log table (GB) | SQL: "HANA_Tables_LargestTables" | Increased memory requirements | 706478 | Implement the recommendations for the SAP HANA audit log table CS_AUDIT_LOG_ as described in SAP Note 706478. |
| 1410 | LICENSE | global | License usage (%) | SQL: "HANA_License_LicenseUsage" | License violation | 1704499 | Make sure that the license usage doesn't exceed 100 % by adjusting the licensing conditions or reconfiguring SAP HANA or the application. |
| 1415 | LICENSE | global | License expiration (days) | SQL: "HANA_License_Overview" | Risk of license violation and system unavailability | 1644792 | Extend the license before it expires. Consider a permanent license. |
| 1420 | LICENSE | global | Permanent license | SQL: "HANA_License_Overview" | Risk of license violation and system unavailability | 1644792 | Consider using a permanent license rather than a temporary one. |
| 1510 | NETWORK | global | Avg. intra node send throughput (MB/s) | SQL: "HANA_Network_Services" | Performance degradations | 2222200 | Eliminate bottlenecks impacting the communication between the processes within one SAP HANA node. |
| 1512 | NETWORK | scale-out | Avg. inter node send throughput (MB/s) | SQL: "HANA_Network_Services" | Performance degradations | 2222200 | Eliminate bottlenecks impacting the communication between the processes of different SAP HANA nodes. |
| 1520 | NETWORK | Rev. >= 100 | Retransmitted TCP segments (%) | SQL: "HANA_Network_Statistics" | Performance degradations | 2222200 | Check from network perspective if you can reduce the retransmission rate of TCP segments related to the SAP HANA nodes. |
| 1522 | NETWORK | Rev. >= 100 | Bad TCP segments (%) | SQL: "HANA_Network_Statistics" | Performance degradations | 2222200 | Check from network perspective if you can reduce the number of bad TCP segments related to the SAP HANA nodes. |
| 1610 | XS ENGINE | global | Log segment size of xsengine (MB) | SQL: "HANA_Logs_LogBuffers" | Backup problems | 2062080 | Check if a high number of small logs is generated for the XS engine. If yes, increase the log segment size of the XS engine to at least 64 MB. |
| 1710 | NAMESERVER | Rev. >= 90 | Avg. indexserver ping time (ms, last hour) | SQL: "HANA_LoadHistory_Services" | Performance degradations | 2000000 | The measured ping times include the communication between nameserver and indexserver and KPI collection within the indexserver. Increased times can be a symptom of an underlying performance issue. Check for general issues impacting the performance (e.g. resource bottlenecks). |
| 1712 | NAMESERVER | Rev. >= 90 | Avg. indexserver ping time (ms, last day) | SQL: "HANA_LoadHistory_Services" | Performance degradations | 2000000 | The measured ping times include the communication between nameserver and indexserver and KPI collection within the indexserver. Increased times can be a symptom of an underlying performance issue. Check for general issues impacting the performance (e.g. resource bottlenecks). |
| 1720 | NAMESERVER | Rev. >= 91 | Supported nameserver lock file location | SQL: "HANA_TraceFiles_Content" | Performance degradations | 2100296 | Make sure that the nameserver lock file is located on a supported file system as delays in accessing this file can heavily impact the performance of SAP HANA. |
| 1810 | SYSTEM REPLICATION | SR | Services with replication error | SQL: "HANA_Replication_SystemReplication_Status" | High availability limitations | 1999880 | Analyze the related trace files in order to understand the reason for the problems and make sure that system replication is again established. |
| 1811 | SYSTEM REPLICATION | SR | Service with unknown replication state | SQL: "HANA_Replication_SystemReplication_Status" | High availability limitations | 1999880 |
Analyze the related trace files in order to understand the reason for
the problems and make sure that system replication is again
established. |
| 1813 | SYSTEM REPLICATION | SR, Rev. >= 90 | Replication connection closed (last day) | SQL: "HANA_StatisticsServer_Alerts_Reported" | High availability limitations | 1999880 |
Check why the connection to the secondary system has been lost
(temporarily or permanently) and make sure that it remains connected
permanently. |
| 1815 | SYSTEM REPLICATION | SR | Current log shipping delay (s) | SQL: "HANA_Replication_SystemReplication_Status" | High availability limitations, performance degradations | 1999880 | Check for errors or communication issues that can be responsible for the log shipping delays and make sure that log can be shipped in time. |
| 1816 | SYSTEM REPLICATION | SR | Filling level of async shipping buffer (%) | SQL: "HANA_Replication_SystemReplication_Status" | High availability limitations, performance degradations | 1999880 |
Check for errors or communication issues that can be responsible for
the log shipping delays and make sure that log can be shipped in time.
If required, increase the asynchronous log shipping buffer:global.ini -> [system_replication] -> logshipping_async_buffer_size |
| 1820 | SYSTEM REPLICATION | SR | Parameter deviations primary vs. secondary site | SQL: "HANA_Replication_SystemReplication_ParameterDeviations" | Risk of problems during / after failover | 1999880 |
Make sure that in system replication environments all relevant SAP
HANA parameters are maintained consistently on the primary and secondary
system. If parameters need to deviate, you can maintain them in the
exclusion list parameters at:global.ini -> [inifile_checker] |
| 1830 | SYSTEM REPLICATION | SR | Age of oldest replication snapshot (h) | SQL: "HANA_IO_Snapshots" | Increased data disk space requirements | 1999880 |
Database snapshots can increase the data disk space requirements and
so you should make sure that system replication related snapshots are
not kept longer than necessary. The snapshot retention time is
controlled by the following parameter:global.ini -> [system_replication] -> datashipping_snapshot_max_retention_time |
| 1840 | SYSTEM REPLICATION |
SR |
Avg. sync log shipping time (ms/req, last hour) | SQL: "HANA_Replication_SystemReplication_KeyFigures" | Performance regressions | 1999880 |
In order to avoid negative impact on the performance on primary
system replication side, the average log buffer shipping time shouldn't
exceed a few milli seconds. If you face higher values, you should
analyze the network between the primary and secondary side. |
| 1843 | SYSTEM REPLICATION |
SR |
Avg. sync log shipping time (ms/req, last day) | SQL: "HANA_Replication_SystemReplication_KeyFigures" | Performance regressions | 1999880 |
In order to avoid negative impact on the performance on primary
system replication side, the average log buffer shipping time shouldn't
exceed a few milli seconds. If you face higher values, you should
analyze the network between the primary and secondary side. |
| 1846 | SYSTEM REPLICATION |
SR |
Max. sync log shipping time (ms/req, history) | SQL: "HANA_Replication_SystemReplication_KeyFigures" | Performance regressions | 1999880 |
In order to avoid negative impact on the performance on primary
system replication side, the average log buffer shipping time shouldn't
exceed a few milli seconds. If you face higher values, you should
analyze the network between the primary and secondary side. |
| 1910 | OBJECTS |
Rev. <= 85.03, Rev. 90 - 95 |
Maximum object ID | SQL: "HANA_Objects_Overview" | Risk of terminations | 2155663 |
Upgrade to SPS 08 Rev. >= 85.04 or Rev. >= 96 in order to
eliminate the risk of terminations in case of large object IDs for
sequences. |
| 1920 | OBJECTS |
global |
Number of invalid procedures | SQL: "HANA_Objects_Procedures" | Risk of terminations |
Check for invalid procedures and recompile them:ALTER PROCEDURE "<procedure>" RECOMPILEIf a recompilation isn't possible consider adjusting or dropping them. |
|
| 2010 | BW |
BW, scale-out |
Empty TABLE_PLACEMENT table in BW scale-out | Risk of performance overhead due to inadequate table placement | 1908075 |
Make sure that optimal table placement and landscape redistribution is configured. |
|
| 2020 | BW |
BW |
Partitioned SID tables | SQL: "HANA_Tables_PartitionedTables" | Risk of locks and hang situations | 2044468 |
Avoid partitioning of SID tables in order to rule out risks related to uniqueness checks. |
| 2022 | BW |
BW |
SID tables > 1.5 billion rows | SQL: "HANA_Tables_ColumnStore_Records" | Risk of terminations | 1331403 |
Make sure from BW side that the SID number range in a single info object doesn't exceed 2 billion. |
| 2025 | BW |
BW |
Partitioned special BW tables < 1.5 bill. rows | SQL: "HANA_Tables_PartitionedTables" | Risk of locks and performance regressions | 2044468 |
BW tables with H, I, J, K, P, Q, T, X or Y immediately after the
second slash (e.g. /BIC/P...) shouldn't be partitioned unless really
required, because partitioning typically causes more trouble than having
benefits. If there is a risk that the 2 billion record limit is
reached, partitioning should nevertheless be considered. |
| 2030 | BW |
BW, scale-out |
BW scale-out installation on 2 nodes | SQL: "HANA_Configuration_Overview" | Inefficient table distribution | 1702409 |
Consider to use at least three nodes in BW scale-out scenarios in order to guarantee optimal table distribution. |
| 2110 | CONSISTENCY |
Rev. >= 100 |
CHECK_TABLE_CONSISTENCY errors (last month) | SQL: "HANA_Consistency_CheckTableConsistency_Results" | Data inconsistencies and terminations | 1977584 |
Analyze the reported errors and repair corruptions if required. |
| 2120 | CONSISTENCY |
Rev. <= 102.03, 110 |
Large tables with risk of index corruption | SQL: "HANA_Tables_ColumnStore_Records" | Wrong results | 2264778 |
Consider upgrading to a more recent Revision level or partitioning the potentially impacted tables (SAP Note 2044468). |
Your blog is in a convincing manner, thanks for sharing such an information with lots of your effort and time
ReplyDeletesql server dba online training
sql dba training