The configuration of a SQL Server database consists of three areas:
Startup parameters are the optional arguments of the executable sqlservr.exe. They are read from the registry, when starting SQL Server as a service. You can modify the startup parameters by using the SQL Server Configuration Manager. The most common startup parameter is "-T" which allows to set a trace flag. To get a list of all startup parameters, search the index of SQL Server Books Online for "sqlservr".
SQL Server configuration options are used to configure an instance of SQL Server. You can set a configuration option by means of the system stored procedure sp_configure. This stored procedure can also be used, to see all configuration options and their values. To get a list of all configuration options, search the index of SQL Server Books Online for "sp_configure".
SQL Server database options are used to configure a user database, for example the <sid>-database. You can use the SQL command ALTER DATABASE to set a database option. To see, what database options are set in each database, execute the SQL command "select * from sys.databases". To get a list of all database options, search the index of SQL Server Books Online for "ALTER DATABASE".
In addition to this, SAP configures some database tables (using the system stored procedures sp_autostats and sp_indexoption). As an administrator you do not need to take care about this, because it's automatically done in the script sap_z_set_parameters during SAP installation. You can view or execute the script by means of SAP report mssprocs.
SAP does not require or generally recommend to modify any SQL Server startup parameter.
Some configuration options are advanced options. You can only see and modify them, if the configuration option "show advanced options" is set. This is automatically done by SAP NetWeaver. The following SQL script performs this manually:
exec sp_configure 'show advanced options', 1
reconfigure with override
The most important thing to configure, is the amount of memory given to SQL Server. It is dependent on many factors, which often cannot be estimated beforehand. These factors are the number of concurrent SAP users, the number and type of used SAP modules, the number of application servers and the size of the database. The recommendations in this note are a starting point, means they tell you how much memory you should give to SQL Server when you install it. A fine tuning has to be performed once the system is running. You should give more memory to SQL Server, if you observe a bad database performance and a low data cache hit ratio at the same time. You can check the data cache hit ratio in SAP transaction ST04. It is not good, if it's lower than 98% for a long time during normal database load.
min/max server memory (MB)
The amount of memory given to SQL Server can be configured by means of the configuration options "min server memory (MB)" and "max server memory (MB)". The recommended values differ in each of the following scenarios:
Dedicated database server
On a dedicated database server no other application or service is running on the same hardware where SQL Server is installed. In this scenario you can leave the default settings of minand max server memory. Actually the only thing you should take care, is that max server memory is not smaller than the physical memory available on the server
SAP central system
On a SAP central system the same hardware is shared by SQL Server and the SAP central instance. As a starting point we recommend to give about a third of physical memory to SQL Server. For example, on a server with 4GB physical memory you give 1200MB to 1500MB to SQL Server. On a SAP central system we recommend to set min and max server memory to the same value. The following SQL script sets min and max server memory to 1500MB:
exec sp_configure 'min server memory (MB)', 1500
exec sp_configure 'max server memory (MB)', 1500
reconfigure with override
If you have additional SAP application servers then you may need to increase the amount of memory for SQL Server. This decreases the amount of memory for the SAP central instance. Therefore it may be a good idea to have a dedicated database server for huge systems.
A typical Windows Cluster environment consists of of two nodes. Per default SQL Server is running on one node and the SAP central instance is running on the other node. Normally you configure SQL Server in such an environment the same way as a SAP central system. This ensures that SQL Server still has the same amount of memory available, once the SAP central instance is switching to the other node. On the other hand, this approach results in waste of physical memory in the default case.
Configure a 32-bit SQL Server for more than 2GB
A 32-bit Windows process can use up to 2GB virtual memory. The physical memory used by SQL Server is always somewhat less than the virtual memory. When running the Enterprise Edition of Windows you may set the /3GB option in the boot.ini file. This allows SQL Server to use up to 3GB virtual address space and about 2.7GB physical RAM.
Configure SQL Server for more than 3GB
On a 32-bit hardware we recommed to install up to 4GB physical RAM. Therefore there is no need to set the /PAE option in boot.ini or use SQL Server AWE memory. For details about AWE memory and the boot.ini options see note 327494. If you need more than 3GB physical memory for SQL Server then we strongly recommend to use a 64-bit edition of SQL Server running on 64-bit edition of Windows (x64 or IA64).
max degree of parallelism
The configuration option "max degree of parallelism" defines, how many processors SQL Server can use to execute a single SQL command in parallel. This may decrease the response time of a single, long running SQL command. But this will also decrease the overall throughput of SQL Server. Therefore it only makes sense to use parallelism, if the number of CPUs used by SQL Server is greater than the number of all concurrently running SAP work processes. Since this is typically not the case, SAP strongly recommends to turn off parallel query execution. You can do this by executing the following SQL script:
exec sp_configure 'max degree of parallelism', 1
reconfigure with override
The only common SAP scenario, which benefits from parallel query execution, is the aggregate building in a SAP BW system. You may set "max degree of parallelism" to 0 during aggregate building and set it to 1 during normal operation of SAP BW.
Some SAP transactions like the database monitor ST04 need to execute the stored procedure xp_cmdshell. This fails unless you have set the configuration option "xp_cmdshell" to 1. You can do this by executing the following SQL script:
exec sp_configure 'xp_cmdshell', 1
reconfigure with override
Some 3rd. party products use SQL-DMO. In order to avoid problems, you may allow the execution of SQL-DMO extended stored procedures by executing the following SQL script:
exec sp_configure 'SMO and DMO XPs', 1
reconfigure with override
For all other configuration options we do not generally recommend to change the default values. This does not mean, that SAP support may never give other recommendations in individual cases based on special customer scenarios.
A list of all configuration options and their default value is available in SQL Server Books Online and on http://msdn.microsoft.com/en-us/library/ms130214
In former releases of SQL Server SAP recommended to change some additional configuration options. This is not the case for SQL Server 2005. We recommend to keep the default values for these configuration options: "network packet size (B)"=4096, "set working set size"=0, "priority boost"=0, "max worker threads"=0,
"index create memory (KB)"=0
SAP does not require or generally recommend to modify any SQL Server database option. Nevertheless, you should double check that the following database options are set for the <sid>-database:
The recovery model affects the ability to backup or restore transaction logs and to perform a point-in-time recovery. SAP works fine independent from the choosen recovery model. Nevertheless, SAP strongly recommends never to use the simple recovery model in a productive database. The safest recovery model is the full recovery model. You can set it for the <sid>-database by executing the following SQL command:
alter database <sid>set recovery full
Auto update/create statistics
SAP strongly recommends to set the database options "auto create statistics" and "auto update statistics". SQL Server 2005 introduced a new database option, which is also recommended: "auto update statistics async". You can set all three database options for the <sid>-database by executing the following SQL script:
alter database <sid>set auto_create_statistics on
alter database <sid>set auto_update_statistics on
alter database <sid>set auto_update_statistics_async on
SQL Server 2005 can write a checksum to each database page written to disk. This allows to detect disk errors and database corruptions as soon as the page is read from disk the next time. SAP recommends to turn on this option by executing the following SQL command:
alter database <sid>set page_verify checksum
The overhead of the checksum will result in a few percent additional CPU usage on the database server. We think that value added justifies this overhead. Nevertheless, you may not be the same opinion. In this case we strongly recommend to turn on the less effective torn page detection, which does not result in any measurable overhead. You can do this by means of the SQL command "alter database <sid>set page_verify torn_page_detection".
For all other database options we recommend to keep the default settings.
- Installing and configuring SAP Monitoring server
- BASIS Role in SAP?
- Configuring Outgoing Email in SAP Basis Services
- DB6: Inst. of SAP NetWeaver 7.3 - Windows
- SAP BASIS & Security Concepts
- File management with SQL Server
- Secure Configuration of Application Server ABAP
- Secure configuration of the message server
- Connection Problems Occur between R/3 and SQL Server
- Which versions of SQL Server are supported by SAP NetWeaver based systems?
- Install SAP 4.7E IDES with SQL server
- Fetch Data From SQL Server to SAP
- SAP NetWeaver 04 - SAP Web Application Server on Linux and MaxDB
- How to configure sap netweaver single sign-on for sap gui for aix with kerberos integration.
- Adding / Configuring Server in SAP Logon
- Netweaver Installation log destination
- Difference between SAP BASIS and Application Server
- How to Configure ITS (Internet Transaction Server)
- Unique SAP NetWeaver Gateway 2.0 Central Hub for SAP Multiple Systems?