SQL execution plans, part 1: SQL statement execution on an SAP system

The most common root causes of a performance issue in an SAP system are slow I/O subsystems and inadequate execution plans. Rarely do we encounter cases where CPU resources would be scarce. In order to analyze slow SQL statements you first need to figure out the statement text and the execution plan. SAP provides a rich set of database monitoring tools which makes it easy to figure out a slow SQL statement. It is also easy to get an execution plan for it. However, this might not be the currently used execution plan, but an estimated plan based on the given parameters. This causes a lot of confusion for database administrators and SAP consultant. Without a deeper knowledge how SAP executes SQL statements and how the SAP database monitors retrieve an execution plan you will not be able to understand performance issues based on bad or unstable execution plans.

Since this will be an extensive discussion, I had to divide it into several parts. Today we want to discuss how SAP executes SQL statements on SQL Server.

SAP was originally ported to Microsoft SQL Server on version 6.0 in the year 1995. In these days SQL Server had no SQL statement cache, but only a Stored Procedure Cache. Each time an ad-hoc query or prepared statement was sent to SQL Server, a new execution plan had to be created. However, without re-using execution plans the overall SAP performance on SQL Server (and other database platforms) would not have been acceptable. Therefore the SAP database interface always wrapped a stored procedure (SP) around an ABAP Open SQL Statement for execution against Microsoft SQL Server.

This had several advantages: The statement text sent to SQL Server (over the network) was shorter. It only contained the SP name and the parameters. Existing execution plans could be reused (which was the main intention of using SPs). In addition, it was quite simple to get the current execution plan of an existing SP.

On the other hand, using SPs also resulted in a lot of overhead in the SAP database interface. A mapping between the Open SQL statement of ABAP and the SP executing the native SQL statement on SQL Server had to be implemented. Once the Open SQL statement in ABAP changed, SAP had to make sure that also the SP changed. Before executing a SP, you first have to create it. This has to be performed on a separate database connection in order to commit the SP creation. To minimize the SP creation attempts SAP implemented a SP name cache which contained the existing SPs already created on the database. As you can clearly see, this resulted in a quite complex SAP database interface.

Just as the SQL Server features increased with new releases (SQL Server 6.5, 7.0, 2000, 2005, 2008), the SAP database interface made several improvements within new releases. It was decided to simplify the SAP database interface and get rid of SPs, because the original limitations of SQL Server 6.0 regarding re-usage of execution plans do not exist anymore.

Up to SAP release 4.6D SAP always used stored procedures:

· Permanent stored procedures for SQL statements which have a statement id (a SAP internally well-defined identification of the corresponding Open SQL statement). Those stored procedures usually started with a 'Y' in their name.

· Global temporary stored procedures (starting with “##”) for SQL statements which do not have a statement id (this is typically the ABAP command SELECT FOR ALL ENTRIES)

As a result the global temporary stored procedures have been replaced with parameterized statements as of SAP kernel version 620. Starting with SAP kernel version 700 all stored procedures have been replaced with parameterized statements.

There are two special cases. In all SAP releases there is a small set of stored procedures (starting with “sap_“) used by the SAP database monitors. Secondly you may execute Native SQL statements using the ABAP command EXEC SQL. This is a very rarely used feature in ABAP. On SQL Server it results in ad-hoc queries without any parameter.

The following table gives an overview how SQL statements are executed on Microsoft SQL Server for the particular SAP NetWeaver releases.

Using parameterized statements simplified the SAP database interface. However, it is very difficult to figure out the current execution plan of a parameterized SQL statement on SQL Server 2000. Starting with SQL Server 2005 you can easily query the SQL Server dynamic management views (or use the SAP database monitors). The only practicable method on SQL Server 2000 is a SQL Server profiler trace, which results in a huge overhead. Therefore it is recommended to upgrade a SQL Server 2000 system to SQL Server 2005 or SQL Server 2008 when running on SAP Basis releases of 6.20 or newer independent of ABAP or JAVA usage. Next week we will discuss when and how SQL Server creates an execution plan.