Using Google Chrome?

Download my
free Chrome Extension, Power Notes Searcher, to make searching for and evaluating SAP notes, much easier.

Recent Posts

Thursday, February 13, 2014

HowTo: Dynamic SQL Server Memory Change from SAP ST04

Print Friendly and PDF
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...

SQL Server Management Studio - Dynamic Memory

SQL Server Management Studio - Minimum memory

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":

SAP SQL Server memory settings

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":

image

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):

SAP ST04 SQL code execute

exec sp_configure 'show advanced options', 1
RECONFIGURE
exec sp_configure 'max server memory', 4096
RECONFIGURE


Click Execute:

image

The output window will be displayed:

SAP ST04 SQL code execute

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":

SAP ST04 SQL Server memory settings

Now if I use the SQL Command Editor to also change the "Min" memory, we will see the ST04 overview screen update:

SAP ST04 SQL execute

exec sp_configure 'show advanced options', 1
RECONFIGURE
exec sp_configure 'max server memory', 4096
exec sp_configure 'min server memory', 4096
RECONFIGURE


image

And the overview screen:

image

Well, we're at "FIXED" again, but the amount of memory has not changed.
Yet in SSMS, I can see the allocation has changed:

SQL Server Management Studio memory settings

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:

SAP ST04 SQL Server 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.

No comments: