SAP on SQL General Update for Customers & Partners January 2013
SAP and Microsoft are continuously adding new features and functionalities to the SAP on SQL Server platform. The key objective of the SAP on Windows SQL port is to deliver the best performance and availability at the lowest TCO. This blog includes updates, fixes, enhancements and best practice recommendations collated over recent months.
1. New Hardware Standards & Guidance
OSS Note 1612283 has been released with Standards & Guidance for customers to follow when purchasing new hardware for Windows systems.
Customers are strongly recommended to follow the content of the OSS Note. The hardware configurations documented in this note have been proved in the test lab and at customer sites. Customers who follow the guidance in this note typically achieve “in-memory” database performance, very fast application server performance and very high availability.
One key concept conveyed in Note 1612283 is that “bigger does not mean better”. A typical case would be purchasing large 8 way servers and “partitioning” these servers into many smaller VMs. The Note details why SAP application servers may not perform well on 4 way or 8 way systems. The Note also recommends against using larger 8 way servers Virtual Machine hosts.
Attached to the OSS Note is a PDF file with some sample landscapes and configurations. If a H/W configuration proposal is received that differs from the guidance in Note 1612283 then customers are strongly recommended to require the H/W partner to explain the reasons and justification for deviating from the generally accepted and proved standards and configurations.
The Note also highlights the performance advantage that low cost 2 socket commodity Intel servers have over larger Intel systems and high cost proprietary UNIX systems. Customers are generally recommended to use 2 socket servers with 384GB RAM & 10G Network.
Commodity Intel/AMD server configurations and prices are publically available at:
2. 7.20_EXT and Higher Kernel Downwards Compatible Kernel – Profile Parameters
SAP released the 7.20 Downwards Compatible Kernel some time ago. Kernels such as 7.00, 7.00 and 7.11 should be discontinued as soon as possible and replaced with the Downwards Compatible Kernel. Care must be taken to set the following profile parameters on some systems:
5.5 Special features for the syslog (if upgrading from 700/701/710/711 kernel versions)
Due to the situation described in note 1517379, you have to set the profile parameter
rslg/new_layout = 9.
If a syslog file already exists in the new format because this parameter has not been set from the very beginning, the syslog will still be written in the new format even if the parameter has been set in the meantime. In that case, the existing syslog files have to be deleted.
5.6 Dynamic work processes (Only for 7.00/7.01 NW releases)
The 720 and 721 kernel versions support the dynamic increase of the number of work processes at runtime. However, this function is not fully compatible with NW 7.00 and NW 7.01. To prevent errors from occurring, deactivate it by setting the following parameters:
rdisp/wp_no_restricted = 0
rdisp/configurable_wp_no = 0
rdisp/dynamic_wp_check = FALSE
Remember to download NTCLUST.SAR when updating the SAP kernel on Windows cluster systems.
1596496 - How to update the SAP Resource Type DLLs for the Cluster Resource Monitor
The NTCLUST.SAR contains three DLLs that reside in the C:\Windows\SAPCLUSTER directory:
sapclus.dll
saprc.dll
saprcex.dll
3. SQL Server 2012 Column Store for SAP BW – How to Deploy Documentation Links
Column Store technologies are popular in many DBMS available today. SQL Server 2012 supports column store technologies and these have been integrated with SAP BW. SAP ECC and other SAP components that are OLTP in nature would not benefit from Column Store.
Required Support Pack Levels:
-
- SPS 27 of SAP BW 7.0 (SAP NetWeaver 7.0)
- SPS 12 of SAP BW 7.01 (SAP enhancement package 1 for SAP NetWeaver 7.0)
- SPS 12 of SAP BW 7.02 (SAP enhancement package 2 for SAP NetWeaver 7.0)
- SPS 8 of SAP BW 7.3 (SAP NetWeaver 7.3)
- SPS 5 of SAP BW 7.31 (SAP enhancement package 1 for SAP NetWeaver 7.3)
Required SAP Notes:
Note 1771177 - SQL Server 2012 column-store support for SAP BW
Documentation Links:
https://scn.sap.com/docs/DOC-33129 https://blogs.msdn.com/b/saponsqlserver/archive/2012/10/29/sql-server-column-store-generally-available-for-sap-bw.aspx
4. BW Key Performance Indicators and Performance Monitoring – ST03
Effective Performance Analysis on BW systems requires the ST03 -> BI Workload functionality to be activated.
Unfortunately unlike ST03 for regular SAP ABAP systems this functionality does not work by default.
Follow Note 934848 - Collective note: (FAQ) BI Administration Cockpit for BW 7.0x systems
Follow Note 1543092 - Upgrade of BP_BIADMIN 1.0 to BP_BIADMIN 3.0(BP BW ADMINISTRA for BW 7.30 or 7.31 systems
5. Tempdb Size, Layout and Structure
SQL Server like other databases has a temporary structure for certain operations that require large amounts of temporary space for SORT, JOIN or other DBA type activities such as ONLINE index operations, compression, DB consistency checks etc. Different SAP applications use tempdb in different ways. SAP BW uses tempdb a great deal for user queries, however all SAP components use tempdb for DBA type activities. BW systems should follow Note 1174635 - TempDB sizing in SAP BW systems on Microsoft SQL Server
The number and size of datafiles and their location can impact the performance of a SAP system.
In the diagram below a customer is using mount points to hold 8 datafiles following our standard recommendation for a medium SAP system with 8 datafiles. Two datafiles are held on each disk (each disk is 1 LUN on the SAN)
The customer has correctly placed one small tempdb datafile on each of the disks used for the main SAP PRD database. The Tempdb Log file has also been “co-located” with the main PRD log file on I: drive.
This configuration is proven and works well at many hundreds of customer systems. Tempdb requires multiple datafiles and separation of the datafiles and log files as with any other database under heavy use.
Remember: for BW systems the number of SAP BW Database datafiles should be equal to the number of tempdb datafiles. For ECC or other systems between 4 to 8 is sufficient. Total Tempdb size is usually between 4-40GB.
Below is an example of incorrect configuration. This will dramatically impact the performance of a busy BW system
Moving the tempdb database files is described in this MSDN article
NOTE: SAP EarlyWatch and SQL Server Setup Check may warn about locating tempdb and SAP DB datafiles on the same disk (or drive letter).
6. Wait type CX_PACKET may be high on BW systems
SAP on SQL Systems must always have the “Max Degree of Parallelism” set to 1.
On BW systems enhancements in more recent support packs allow certain queries to run with parallelism even when the “Global” value is set to 1. These changes were introduced in SAP Support Packs and default values set. Some customers have noticed that the “Wait Time” in ST04 -> Performance ->Wait Events for CX_PACKET on BW systems has increased.
This is completely normal and nothing to worry about. CX_PACKET wait type would be normal on a SAP BW system after the Notes or Support Packs in this blog have been implemented
CX_PACKET should be almost 0ms/req on non-BW SAP systems such as ECC 6.0 but is normal on BW systems.
On powerful servers consider increasing the default values especially for MSS_MAXDOP_INDEXING. For example on a HP DL580 G7 with 4 Processors each with 10 cores/20 threads (total 80 threads) it may be worth increasing this value to higher. Customers have used values up to a maximum of 16-32. To check the values run SE16 on table RSADMIN
To change the values run the report SAP_RSADMIN_MAINTAIN in SE38 and create/update the value and press save.
Note 1654613 - SQL Server Parallelism for SAP BW
Note 1680315 - DDIC support for using DB parallelism during index/PK drop
Note 1454130 - DDIC support for using DB parallelism during index creation
Figure shows a server with 8 processors, 8 cores and 128 logical processors. MSS_MAXDOP_INDEXING has been increased from the default of 8 to 16. MSS_MAXDOP_QUERY has been increased from the default of 2 to 8.
7. Lock Pages in Memory, Volume Maintenance Tasks & non-Administrator Privilege for SQL Server Service Account
The Service Account that starts the SQL Server database and the SQL Server Agent should *never* be an administrator account. Doing so could create a serious security risk should a vulnerability be discovered in SQL Server. Although SQL Server has far fewer vulnerabilities than other databases and has only 1-2 security patches ever 2-4 years, it is still advisable never to start SQL Server with an account that is a local administrator on the server.
It is recommended to set the following properties for the SQL Service account:
- SQL Server Service Account should be a domain account but not be a local Administrator
- Deny Logon Interactively
- Allow Logon as a Service
- Allow “Perform Volume Maintenance Tasks”
- Set Lock Pages in Memory - Note 1134345 - Using locked pages for SQL Server
Steps 4 & 5 are mandatory and not setting these can lead to performance problems. Make sure to set PHYS_MEMSIZE if SQL Server and SAP ABAP app server are running on the same Operating System.
More information about Security and using Active Directory Group Policy to set these security properties is in the SAP on SQL Security Whitepaper
8. Switch on Blocking Lock Collector
SQL Server Blocking Lock Collector is very useful for troubleshooting performance problems after the fact. By default the Blocking Lock History SQL Agent Job is not activated during installation.
To enable SAP and customer Basis team to further tune and optimize a SAP on SQL system it is highly recommended to turn this job ON by pressing the button on the menu. This feature was originally intended to investigate issues on very old versions of SQL Server and since these problems are extremely rare on modern SQL Server releases this feature is delivered OFF by default. This collector does allow more advanced turning on larger and busier systems.
9. Recommend Buffer Profile Parameters – Generic Key & Single Record ST02
The SAP ABAP application server includes very powerful and efficient application server buffers. The three most important buffers on a system are the Program (PXA), Table Buffer and Nametab buffer.
Accessing data from the built in SAP buffers is potentially thousands of times faster than access the same data via the database layer. Configuring SAP buffers is therefore critical for good performance. Care must be taken to only use SAP buffering on tables or objects that do not change greatly
The SAP table buffer has three different kinds – Single Record, Generic Key and Fully Buffered
Program Buffer:
We recommend setting to 1.5GB on all systems. Occasional swaps after a ABAP instance has been running for many months is nothing to worry about.
abap/buffersize = 1500000
Table Buffer:
Generic Key – approximately 450MB has shown to be effective and useful zcsa/table_buffer_area = 450000000 (Dir size - zcsa/db_max_buftab = 30000)
Single Record - approximately 350MB has shown to be effective and useful rtbb/buffer_length = 350000 (Dir size -rtbb/max_tables = 1000)
Nametab:
rsdb/ntab/entrycount = 130000
rsdb/ntab/ftabsize = 175000
rsdb/ntab/irbdsize = 125000
The diagram shows the SAP application server side buffering concept. Accessing data from inside the SAP application server is the fastest possible mechanism. The next fastest is the SQL Server DB buffer, followed by SQL Server reading from the datafiles. SAN level cache and/or SSD can help speed this up, but even in the best scenarios accessing from SSD/SAN Cache will be hundreds or thousands of times slower than the SAP application server side buffer. Optimal performance is achieved when the data is found in SAP application service side buffer or SQL Server buffer. Any other access will be hundreds of times slower.
Recommendation: Keep application server configurations identical and move all profile parameters into the DEFAULT.PFL.
The only profile parameters needed in the INSTANCE Profile to start a SAP system are listed below.
SAPGLOBALHOST =
SAPSYSTEM =
SAPSYSTEMNAME =
INSTANCE_NAME =
DIR_CT_RUN =
DIR_EXECUTABLE =
Other parameters can be placed in the DEFAULT.PFL thereby keeping the configuration of all application servers the same.
10. Remove Zero Administration Memory Management Profile Parameters and dbs/mss/* Parameters RZ10
Do not set any dbs/mss/* parameters except for these values unless told to by SAP support in relation to a specific problem. Occasionally SAP go live checks will recommend setting these parameters to the default values. The correct values are already defaulted in the SAP kernel and there is no need to set them manually.
We recommend against setting the parameters as this will prevent changes introduced in the kernel from taking effect on a system (since the profile parameters will override the new defaulted values)
These profile parameters must be set in the
dbs/mss/server = <sql db server> or <sql db server\instance name>
dbs/mss/dbname = <SID>
dbs/mss/schema = <sid>
Note 1248222 - ODBC DBSL profile parameters and connect options
SAP on Windows platform has simple memory management and tuning.
SAP application server instances with approximately 50 work processes will generally run well with the default ZAMM parameters. The default value will be used if the parameter is not specified in the profile.
Unless a specific problem is encountered leave these values as the default values set automatically in the kernel. Make sure to set the "master" profile parameter for ZAMM – PHYS_MEMSIZE. PHYS_MEMSIZE determines the defaults for most of these parameters and should be set on most systems.
The profile parameters below should *NOT* need to be set on SAP on Win/SQL systems with modern kernels.
em/initial_size_MB
em/max_size_MB
em/address_space_MB
ztta/roll_first
ztta/roll_area
ztta/roll_extension
abap/heap_area_dia
abap/heap_area_nondia
abap/heap_area_total
rdisp/ROLL_MAXFS
rdisp/ROLL_SHM
rdisp/PG_MAXFS
rdisp/PG_SHM
Note 88416 - Zero administration memory management for the ABAP server
1563748 – “MEMORY_NO_MORE_PAGING" dump occurs
Note 1747566 - PHYS_MEMSIZE not used for calculation when in DEFAULT.PFL.
11. How to Find Longest Running SQL Statement – Transaction Log Usage
Long running transactions at the database level can be dangerous for a number of reasons.
Possible impacts/risks from long running DB transactions:
1. Transaction Log may become full – a transaction log backup or setting the recovery mode to SIMPLE will *NOT* reduce the size of the transaction log. The reason is SQL Server must keep *all* transaction log activity in the transaction log for active/running/uncommitted transactions
2. If the DB was shutdown when there was a very long running transaction, it will take SQL Server some time to recover at startup. During some of the recovery time the SAP database will be unavailable. Always check the SQL Server ERRORLOG for information about the status of the database and the estimated completion time of the recovery
SAP CCMS – transaction RZ20 -> SAP SQL Server Monitor -> SQL Server displays a summary of this information
DBA Cockpit transaction DB02 -> Overview displays the oldest open transaction (which is usually the transaction with the highest log consumption). Command DBCC OPENTRAN('<DB name>') will also display this information.
Use transaction ST04 -> Performance ->Database Processes and match the “SQL session ID” to the column “SID”. Command DBCC INPUTBUFFER(<SQL Server Session ID - eg. 459>) will display the statement running.
It is then possible to find out which SQL Statement and Workprocess is using excessive transaction log
The Workprocess can then be tracked in SM66 or SM50 to find which Batch Job is causing excessive transaction log consumption
Log use can be confirmed with:
print 'Queries that consume a large amount of log space'
select TOP(10)
T1.database_id,
DB_NAME(T1.database_id) as DbName,
T4.text,
T1.database_transaction_begin_time,
T1.database_transaction_state,
T1.database_transaction_log_bytes_used_system,
T1.database_transaction_log_bytes_reserved,
T1.database_transaction_log_bytes_reserved_system,
T1.database_transaction_log_record_count
from sys.dm_tran_database_transactions T1
join sys.dm_tran_session_transactions T2 on T2.transaction_id = T1.transaction_id
join sys.dm_exec_requests T3 on T3.session_id = T2.session_id
cross apply sys.dm_exec_sql_text(T3.sql_handle) T4
--where T1.database_transaction_state = 4 -- 4 : The transaction has generated log records.
--and T1.database_id = db_id()
order by T1.database_transaction_log_record_count desc
--order by T1.database_transaction_log_bytes_reserved desc
go
https://msdn.microsoft.com/en-us/library/ms186957.aspx
Small systems start with Logfile size 5 - 10 GB
Medium systems start with Logfile size 50 - 100 GB
Large systems start with Logfile size 300 - 500+ GB
Transaction log files can be > 2TB. 2TB is a limitation of MBR disks not SQL Server. GPT disks are generally recommended for SQL Server systems. Modern servers can boot from GPT disks.
12. Windows 2012 Hyper-V 3.0 Virtualization Links and Information
The following OSS Notes are for the setup and configuration of SAP on SQL on Virtualized systems.
Microsoft will release documentation detailing the setup and configuration of Windows 2012 Hyper-V 3.0 shortly
Note 1753578 - Windows Server 2012 in Virtual Environments
Note 1246467 - Hyper-V Configuration Guideline
Note 1409608 - Virtualization on Windows
Note 1752767 - Release planning for Windows Server 2012
Note1570141 - Key Figures of Virtualization on Hyper-V
Note 1409604 - Virtualization on Windows Enhanced monitoring
13. Reminder! Obsolete, Desupported or Unsupportable Software
Sun/Oracle JVM – immediately replace this software. This JVM is not supported by Sun/Oracle, Microsoft or SAP. SAP have released their own JVM for 1.4.2 - Note 1495160 - SAP JVM as replacement for Partner JDKs 1.4.2.This release works with 6.40 and 7.0x systems. Remove this software from all SAP installations as soon as possible.
Windows 2003 is now 10 years old at a time when hardware with 24, 48 or 64 processors and a lot of RAM where very rare. Windows 2003 is also unable to leverage new features in Network Cards and servers with huge amounts of RAM. Plan to move all SAP systems to Windows 2008 R2 SP1 or Windows 2012 immediately! Windows 2003 is not supported on more recent Netweaver releases and is out of mainstream support. Example: SAP ECC EHP 6 is not supported on Windows 2003. This blog on 10 Reasons to Upgrade off Win2003 details many issues with Windows 2003
SQL 2005 is now in extended maintenance and customers are strongly recommended to upgrade to SQL 2012. SQL 2012 includes features such as DB compression, Backup compression, performance and scalability features, faster ODBC based SAP interface, improved table partitioning handling and many other features. It is possible to backup/restore or detach/attach a SQL 2005 database onto SQL 2012. In place upgrade of SQL 2005 to SQL 2012 is supported as of SQL 2005 SP4. Note: SQL 2012 is *not* supported on Windows 2003.
14. Installing SAP Systems on Windows with IPv6
IPv6 is now popular in data centers. Customers wishing to install on Windows IPv6 should refer to these notes
Note 1759048 - Installations on Windows with enabled IPv6 protocol
Note 1365796 - Local host name resolution fails in Windows Failover Cluster
https://help.sap.com/saphelp_nwmobile71/helpdata/en/46/cd5ee2c45365dde10000000a155369/frameset.htm
15. Recommended Traceflags for SAP on SQL 2012 and SQL 2008
The following traceflags are recommended for:
SQL 2008 R2 Service Pack 1* & 2: 1117, 1118, 2371, 2562 & 2549
*CU4 needed
SQL 2012: 617, 1117, 1118, 2371, 2562 & 2549
1725220 - New Trace Flags set and recommended with SQL Server 2012
16. Pre Go Live Checklist – What to Check 2 Months Before Go Live
Prior to go live please make a checklist and validate all servers/system have been configured and checked:
- NTFS 64KB format size – check NTFS file system is 64k. chkdsk /i /c
- Disks GPT – use GPT disk and not MBR
- Latest Windows Service Pack – Always use the latest Windows Service Pack
- Latest SQL Server Service Pack – Always use the latest SQL Service Pack
- Traceflags should be set – as per section 15
- Datafiles must be all the same size and traceflag 1117 set
- Switch on blocking lock collector – as per section 8
- Schedule CHECKDB
- Latest SAP Kernel – Kernel should be no older than 3 months
- Check JDBC and JVM are up to date on Java systems
- Run ST04 -> Configuration -> SQL Server Setup Check - Note 1609057 - SQL Server Setup Check Overview and Explanation
- Check Disk Performance with backup database to nul: - command –
- BACKUP DATABASE <SID> TO DISK=’NUL:’ WITH COPY_ONLY;
- Throughput should be > 600MB/sec
- Cluster dependencies – if mountpoints are used set dependencies manually
- Netbios disabled on all Network interfaces Note 1431619 - Disable Netbios over TCPIP in Windows environments
- Windows Firewall enabled – follow the port exceptions on page 21 of the SAP on SQL Security Guide
- ZAMM profile parameters removed – see section 10 of this blog
- Set PHYS_MEMSIZE if more than one application server on a single host or DB/APP on single host
- SAP Table and Program buffer set – see section 9 of this blog
- On BW systems check latest BW Notes, MSS_MAXDOP_INDEXING & QUERY are set to optimal values
- Power savings – set Power Savings profile to High Performance. Set network card not to sleep – click on this blog for more info: Effect of Windows Power Mode on SAP Netweaver Applications
17. News, Links, Notes, Blogs and Interesting Information
SQL 2012 Service Pack 1 is released and fully supported for SAP systems.
As at December 2012 we recommend the following versions/releases:
1. SQL 2012 Service Pack 1 – Solution Manager 7.1 is now supported on SQL 2012
2. Windows 2008 R2 Service Pack 1 – current installations
3. Windows 2012 RTM – new projects and upgrades scheduled to go live after ~April/May/June 2013
4. SAP Kernel 7.21_EXT
As per Note 62988 SQL Server Service Packs are *always* automatically supported by SAP.
SAP Business Objects SP 4 is also supported on SQL 2012. If you have any questions about whether a SAP component is supported on SQL 2012 or Windows 2012 please feel free to post in this blog.
1733195 - Microsoft SQL Server version and download list
https://blogs.msdn.com/b/sqlreleaseservices/
Note 1702408 - Configuration Parameters for SQL Server 2012
Note 1676665 - Setting up Microsoft SQL Server 2012
Note 1772688 - SQL Server AlwaysOn and SAP applications
Note 1660220 - Microsoft SQL Server: Common misconceptions
Note 1755488 - Lightweight UPDATE STATISTICS in SAP BW
Additional information about cluster solutions and installing on a cluster
Note 1650897 - Cluster solution supported by SAP
Note 1321854 - Installation of Multiple SAP systems in MSCS
1564275 - How to Install an SAP System or SAP Components on Windows Using Virtual Host Names
Information for customers moving from Proprietary UNIX solutions to Win/SQL. Microsoft now offer a ODBC driver for Linux and SAP provide the SQL Server DBSL on Linux.
Note 1644499 - Database connectivity from Linux to SQL Server
Note 178949 - MSSQL: Database MultiConnect
Note 1774329 - Preparing your SAP instance to connect to remote SQL server
Please review this note and update to the latest ODBC drivers for Java based systems. Note the restriction on AlwayOn configurations for Java systems
Note 1745895 - Available Microsoft JDBC drivers
SWPM
General information on Win/SQL SAP Notes
https://scn.sap.com/docs/DOC-8286
High Tempdb consumption can be debugged with this script (must be run on tempdb database)
Comments
Anonymous
June 06, 2013
Nice blog,very usefullAnonymous
November 04, 2013
Also useful for log consumption per SPID SELECT tst.session_id,tdt.* FROM sys.dm_tran_database_transactions tdt INNER JOIN sys.dm_tran_session_transactions tst ON tdt.[transaction_id] = tst.[transaction_id] order by database_transaction_log_bytes_reserved descAnonymous
February 17, 2014
Hi All, We have our production environment running on virtual machine which is further hosted upon GPT or dynamic disks. Because of this configuration we are having full backups every time for these machines.When we checked this issue with our Backup Vendor, he recommended us to shift the virtual machines from GPT disk to basic disk. We did this activity for on one of our Sandbox and the issue was resolved.Now, the backups are running fine. We checked for this setting and found that it is not recommended to have SAP on MSSQL running on basic disks. I would like you to suggest further how to go with is problem for our production environment. Will shifting of our virtual machines to basic disk setting hamper the performance of our SAP systems. Current Setup: Application - SAP BO 4.0 Database - Microsoft SQL Server 2008 R2 Operating System - Windows Server 2008 R2Anonymous
January 15, 2016
Beautiful Blog. Question: Over the weekend our BW system got hug because of log full situation, so we had rebooted the sap and taht cleared the log space. We want to know which offending sap job/sql process which caused the log full situation. Any help please we are on sql 2012 and sap bw 7.31