Scenario: You have a SQL Server database for your SAP system, and you know that right clicking the database server name in SQL Server Management Studio, selecting “Properties” and then “Memory”, will show you the SQL Server memory settings, but you want to know how you can see/change the same detail in SAP…
In SAP, you can use transaction ST04 to see the SQL Server database settings.
The memory details are visible in the “Overview” screen.
You will see the “Current Memory MB” equals the amount of memory allocated to SQL Server, and if the “Min server memory” and “Max server memory” settings have been set equal (recommended by SAP), then the overview screen will show “FIXED” for the “SQL Memory Setting”:
It is possible to modify this setting directly from ST04.
You will need to expand the “Diagnostics” branch and then double click “SQL Command Editor”:
On the right hand side, enter the SQL Server commands to resize the memory (notice that these are slightly different to the SQL statements when using them in SSMS):
exec sp_configure 'show advanced options', 1
RECONFIGURE
exec sp_configure 'max server memory', 4096
RECONFIGURE
The output window will be displayed:
If you noticed, I didn’t change the “Min” memory setting, only the “Max”.
When I check in the “Performance -> Overview” screen in ST04, I can now see that the “Current Memory MB” setting has not changed, but the “SQL Memory Setting” is now showing “RANGE”:
Now if I use the SQL Command Editor to also change the “Min” memory, we will see the ST04 overview screen update:
exec sp_configure ‘show advanced options’, 1
RECONFIGURE
exec sp_configure ‘max server memory’, 4096
exec sp_configure ‘min server memory’, 4096
RECONFIGURE
And the overview screen:
Well, we’re at “FIXED” again, but the amount of memory has not changed.
Yet in SSMS, I can see the allocation has changed:
This is a weird, because the Microsoft documentation for SQL Server 2008R2 (my version) says that the setting should take effect straight away.
I guess there’s something within the ST04 screen that doesn’t update.
There is another way…
You can use the “Configuration -> Overview” screen and the “Configuration Options” tab to see both the Min and Max memory settings.
As per out change, these correctly reflect the current memory settings:
WARNING: You should be aware that during testing, I was able to set the Min memory value higher than the Max memory value in ST04.
I was then unable to change this through ST04, as the store procedures just produced errors and refused to let me change the values.
In the end I had to change the Max value using SSMS.
