HANA Cloud Sizing

SAP HANA Database Downsizing

You can reduce the memory size of an SAP HANA database instance.

To determine the minimum amount of memory required for stable performance of your SAP HANA database instance, execute the following statement:

SELECT 
	"Current GAL [GB]",
	"Memory In Use by Instance [GB]",
	"Buffer Cache Current [GB]",
	"Buffer Cache Max [GB]",
	"Column Store Hot Memory [GB]",
	"NSE Data [GB]",
	"Memory for Services [GB]",
	"ROW Store Hot Memory [GB]",
	GREATEST(
		30.0, 
		( "ROW Store Hot Memory [GB]" + "Column Store Hot Memory [GB]" + GREATEST( "Buffer Cache Current [GB]", "NSE Data [GB]" * 0.1 ) ) * 2 + "Memory for Services [GB]"
	) AS "min size"
FROM 
	(
		(SELECT *
		FROM 
			(
				(SELECT 
					UTIL.HOST,
					ROUND( UTIL.ALLOCATION_LIMIT / 1024 / 1024 / 1024, 3 ) AS "Current GAL [GB]",
					ROUND( UTIL.INSTANCE_TOTAL_MEMORY_USED_SIZE / 1024 / 1024 / 1024, 3 ) AS "Memory In Use by Instance [GB]",
					ROUND(
						( SUM(W) + UTIL.INSTANCE_SHARED_MEMORY_ALLOCATED_SIZE + UTIL.INSTANCE_CODE_SIZE ) / 1024 / 1024 / 1024, 
						3
					) AS "Min GAL [GB]"
				FROM 
					(
						(SELECT 
							HOST,
							CASE
								WHEN B > V THEN B
								ELSE V
							END AS W
						FROM 
							(
								(SELECT 
									HOST,
									B,
									CASE
										WHEN ( A - F ) > 1.3 * U THEN ( A - F )
										ELSE 1.3 * U
									END + 1024 * 1024 * 1024 AS V
								FROM 
									(
										(SELECT 
											HOST,
											BLOCKED_MEMORY_LIMIT AS B,
											HEAP_MEMORY_USED_SIZE AS U,
											HEAP_MEMORY_ALLOCATED_SIZE AS A,
											FREE_MEMORY_SIZE AS F
										FROM M_SERVICE_MEMORY)
									))
							))
					) AS HEAP
					INNER JOIN
					M_HOST_RESOURCE_UTILIZATION AS UTIL
					ON HEAP.HOST = UTIL.HOST
				GROUP BY 
					UTIL.HOST,
					UTIL.INSTANCE_SHARED_MEMORY_ALLOCATED_SIZE,
					UTIL.INSTANCE_CODE_SIZE,
					UTIL.INSTANCE_TOTAL_MEMORY_USED_SIZE,
					UTIL.ALLOCATION_LIMIT)
			) AS a
			INNER JOIN
			(
				(SELECT 
					host,
					round( USED_SIZE / 1024 / 1024 / 1024, 3 ) AS "Buffer Cache Current [GB]",
					round( MAX_SIZE / 1024 / 1024 / 1024, 3 ) AS "Buffer Cache Max [GB]"
				FROM M_BUFFER_CACHE_STATISTICS
				WHERE volume_id = ( 					(SELECT volume_id
					FROM M_VOLUMES
					WHERE service_name = 'indexserver'
						AND (VOLUME_REPLICATION_ROLE = 'SOURCE'
							OR VOLUME_REPLICATION_ROLE = 'NONE')) ))
			) AS b
			ON a.host = b.host
			INNER JOIN
			(
				(SELECT 
					(
						(SELECT host
						FROM M_VOLUMES
						WHERE service_name = 'indexserver'
							AND (VOLUME_REPLICATION_ROLE = 'SOURCE'
								OR VOLUME_REPLICATION_ROLE = 'NONE'))
					) AS host,
					round(
						sum(estimated_max_memory_size_in_total) / 1024 / 1024 / 1024, 
						3
					) AS "Column Store Hot Memory [GB]"
				FROM m_cs_tables
				WHERE load_unit = 'COLUMN')
			) AS c
			ON c.host = b.host
			INNER JOIN
			(
				(SELECT 
					(
						(SELECT host
						FROM M_VOLUMES
						WHERE service_name = 'indexserver'
							AND (VOLUME_REPLICATION_ROLE = 'SOURCE'
								OR VOLUME_REPLICATION_ROLE = 'NONE'))
					) AS host,
					round(
						sum(estimated_max_memory_size_in_total) / 1024 / 1024 / 1024, 
						3
					) AS "NSE Data [GB]"
				FROM m_cs_tables
				WHERE load_unit = 'PAGE')
			) AS d
			ON d.host = c.host
			INNER JOIN
			(
				(SELECT 
					(
						(SELECT host
						FROM M_VOLUMES
						WHERE service_name = 'indexserver'
							AND (VOLUME_REPLICATION_ROLE = 'SOURCE'
								OR VOLUME_REPLICATION_ROLE = 'NONE'))
					) AS host,
					( count(*) * 15 ) AS "Memory for Services [GB]"
				FROM M_SERVICES
				WHERE (SERVICE_NAME IN ('docstore', 'scriptserver')))
			) AS e
			ON d.host = e.host
			INNER JOIN
			(
				(SELECT 
					host,
					round( sum(allocated_size) / 1024 / 1024 / 1024, 3 ) AS "ROW Store Hot Memory [GB]"
				FROM m_rs_memory
				WHERE (category NOT IN ('VERSION', 'LOCKTABLE'))
				GROUP BY host)
			) AS f
			ON e.host = f.host)
	);

Change the Size of an SAP HANA Database Instance Using the CLI

SAP Help Portal | SAP Online Helphelp.sap.com