The configuration of a SQL Server database consists of these areas:
- Startup parameters
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.
See also: http://msdn.microsoft.com/en-us/library/ms162819.aspx - Configuration options
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.
See also: http://msdn.microsoft.com/en-us/library/ms189631.aspx - Database options
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”.
See also: http://msdn.microsoft.com/en-us/library/ms190249.aspx - Table and index options
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.
Startup parameters
Traceflags
The following trace flags should automatically be set by SWPM in installation or upgrade:
617, 1117, 1118, 2371
It is recommended to ensure these trace flags set.
Read this article on how to set a trace flag in SQL Server.
Configuration options
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. The SQL Server Buffer Pool contains almost all of the memory consumed by SQL Server. Its optimal size depends on many factors, which often cannot be estimated in 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 roughly give to SQL Server when you install it. Therefore it makes no difference whether you calculate 1000MB or 1024MB per GB. 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 for a long time during normal database load. You can check the data cache hit ratio in SAP transaction DBACOCKPIT.
min/max server memory (MB)
The size of the SQL Server Buffer Pool can be configured by means of the configuration options “min server memory (MB)” and “max server memory (MB)”. We recommend to uses “Locked pages in memory” as described in SAP note 1134345 and set min and max server memory to the same value.
The recommended value differs in each of the following scenarios:
1. Dedicated database server
On a dedicated database server no other application or service is running on the same hardware where SQL Server is installed. We recommend to roughly leave between 1.5GB (when having 8GB or less physical RAM) and 6GB (when having 64GB or more physical RAM) for the operating system and for SQL Server mem-to-leave memory (which is not part of the SQL Server buffer pool). For example, on a Windows Server with 64GB RAM running a dedicated SQL Server you might set min and max server memory to 58000MB using the following SQL commands:
exec sp_configure 'min server memory (MB)', 58000 exec sp_configure 'max server memory (MB)', 58000 reconfigure with override
2. SAP instance running on the database server
When the same hardware is shared by SQL Server and an SAP instance then a good portion of the memory is needed for the SAP instance. As a starting point we recommend to give roughly a third of the physical memory to SQL Server. For example, 5000MB on a machine with 16GB physical memory. For some SAP releases SAPINST configures automatically min and max server memory, when installing an SAP instance on the same box as SQL Server. You might reconfigure SQL Server memory settings after SAPINST has finished the installation.
If you have additional SAP application servers then you might need to increase the memory for SQL Server. This decreases the amount of memory for the local SAP instance. Therefore a dedicated database server might be the better choice for huge systems.
3. High-availability on Windows with Failover Clustering
If you run SQL Server on a cluster node where only the SAP central services (on NetWeaver 700 and newer) are running then configure SQL server similar to a dedicated database server (case 1). However, you should leave another 500MB up to 1GB for the SAP central services. If there is also an SAP instance installed on the same node then configure SQL Server as in case 2.
max degree of parallelism
The configuration option “max degree of parallelism” (MAXDOP) defines, how many processors SQL Server can use in parallel to execute a single SQL query. This may decrease the response time of a single, long running SQL command. However, 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
With the advent of increasing numbers of cores per physical socket and with the enhanced potential for SQL Server 2012 to be able to handle parallel queries more gracefully, the recommendation to only use a MAXDOP setting of 1 has been relaxed for this and later versions of the product. It is now acceptable to use a MAXDOP setting of 1 or 2 if the system has 16 to 63 CPUs available for SQL Server 2012 (or later) to use. And it is also acceptable to use a MAXDOP setting of 1, 2, 3, or 4 if there are 64 or more CPUs available for SQL Server to use. The SAP alerting functionality will leverage these new MAXDOP thresholds as of the basis support packages listed in SAP Note 2133120.
Setting parallelism for SAP BW systems is described in SAP Note 1654613.
xp_cmdshell
The SAP database monitor DBACOCKPIT uses 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
clr_enabled
For many years, SAP did not utilize SQL Server Common Language Runtime (CLR) functionality so we did not recommend a specific value for the configuration parameter clr_enabled. However, beginning with SAP NetWeaver 7.4 SP8 SAP has implemented functionality using SQL Server CLR and so clr_enabled must now be set to 1 when a system is at SAP NetWeaver 7.4 SP08 or higher. The parameter will be set to 1 automatically by SAP tools, therefore you must only ensure that you do not turn it off or the SAP application will be affected.
other
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.
In former releases of SQL Server SAP recommended to change some additional configuration options. This is not the case for SQL Server 2012.
It is 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
Database options
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:
Recovery model
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 the following database options:
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
However, on SAP BW systems we have seen a few performance issues with AUTO UPDATE STATISTICS ASYNC. These issues can not occur on other SAP systems.
Therefore you may turn off this feature on SAP BW systems:
alter database <SID> set auto_update_statistics_async off
Page verify
SQL Server 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 minimal, 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.
Transaction Isolation Level
The transaction isolation level controls the locking and row versioning behavior of Transact-SQL statements issued by a connection to SQL Server. The transaction isolation level defines the type of locks acquired on read operations.
The database default transaction isolation level is READ COMMITTED.
For NETWEAVER based applications, DO NOT CHANGE THIS.
Other non-NETWEAVER SAP applications might alter the database default transaction isolation level, but NETWEAVER should not. For instance, SAP products based on Business Objects Technology; some of them choose to use Snapshot isolation level.
No NETWEAVER basis or application testing has ever been done using Snapshot Isolation level or Read_Committed_Snapshot. We know that tempdb gets substantially stressed with those isolation levels. Therefore we do not recommend or support the usage of those isolation levels with Netweaver based products.
Other
For all other database options we recommend to keep the default settings.
