SAP with Microsoft SQL Server 2005: Best Practices for High Availability, Maximum Performance, and Scalability44
set working set size
The set working set size option was initially used to keep memory allocated by SQL Server from being paged out to the pagefile. In the past, mySAP used a set working set size value of 1 during the SAP product installation. The current recommendation is to set the option to 0.
Within the last two years, there have been concerns about the effect of having this option set to 1. It has been the source of some problems in high-end database servers. In addition, this option no longer has an effect on SQL Server 2005. At present, this parameter is used only to avoid setup application failures.
SAP settings for the SQL Server lock manager
The SQL Server 2005 lock manager provides improved lock-handling for increased performance when a large number of concurrent users are accessing SAP applications. SQL Server 2005 uses row-level locks by default, although in some cases SQL Server can also use the following alternative locking options:
Table locks. Locking granularity to the table level saves memory. In this case, when memory pressure results from lock structures using too much of the buffer pool, SQL Server tries to escalate the lock granularity to table locks. Typically, this situation does not occur in SAP product installations given the size of SAP database servers and the amount of memory available (from a few hundred megabytes to a few gigabytes of buffer pool) compared to the small number of bytes used for each lock (~100 bytes).
Table locks if over 5,000 locks are requested. If SQL Server estimates that over 5,000 locks could be acquired by executing a specific query, a table lock is requested. If another transaction already has locks conflicting with a table lock, row-level locks are used instead.
Trace flag 611. Start SQL Server 2005 with trace flag 611 to report each lock escalation with the handle of the originating statement to the errorlog. Trace flag 611 is new in SQL Server 2005.
Trace flag 1211. In very specific cases, start SQL Server with trace flag 1211 to suppress the request for table locks. This also disables lock escalation because of memory pressure.
Trace flag 1224. As alternative, use trace flag 1224 in SQL Server 2005 to suppress lock escalation rooted in the estimated number of locks, but allow lock escalation because of memory pressure. Trace flag 1224 is new in SQL Server 2005 and it does not exist in earlier SQL Server versions.
Page locks. The SQL Server query optimizer can request page-level locks, instead of row locks, when it estimates that almost the entire table will be locked anyway. The current practice for SAP installations is to disable page-level locks on SAP queuing tables. For example, typical queuing tables include VBHDR, VBMOD, and VBDATA, which store update requests, and ARFCRDATA, ARFCSDATA, TRFCQUEUE. For more information, see SAP OSS Note 327494.
Page locks can be eliminated or disallowed on a per table basis by using the Transact-SQL command:
sp_indexotion <table_name> ‘DisAllowPageLocks’, TRUE
Microsoft Corporation © 2005