SAP with Microsoft SQL Server 2005: Best Practices for High Availability, Maximum Performance, and Scalability63
Special Considerations for SAP BW
Although SAP BW uses the same basic SQL Server configuration as other SAP products, using SAP BW with SQL Server 2005 has some special considerations. This section describes these considerations, including the layout of tempdb for SAP BW and the specific methods for investigating SAP BW performance issues.
SAP BW queries
The best way to investigate performance issues with SAP BW is to repeat and trace the queries. Similar to other OLAP warehouse products, SAP BW tries to buffer queries and associated results. The base foundations allow a query to be run manually for investigation. SAP BW queries are named and stored in the SAP BW cache.
Using the SAP BW transaction code RSRT, a SAP BW query can run repeatedly in order perform investigations. The RSRT transaction helps to rule out issues on the client side, such as the client reporting or network issues on the way to the client.
The RSRT transaction can be used with the option to Execute + Debug. To use this option:
Browse for a query or type the name of the query.
Select the query display option such as List, BEx Broadcaster (SAP BW Business Explorer Broadcaster), or HTML (HyperText Markup Language).
Click Execute + Debug to display a pop-up window that enables certain items to be selected. In addition, a second window requesting query parameters can appear.
The most important selections are:
Display Aggregate Found. Indicates if SAP BW is using any aggregates for the query.
Display Run Schedule. This selection is below Display SQL Query. The selection presents the Transact-SQL statement that is generated and sent to SQL Server and to the query plan.
Do Not Use Cache. Do not use SAP BW cache to execute the query in the database server.
When the transaction is executed, SAP BW shows the Transact-SQL statement and the query plan. After reviewing the information, click Back (green button) in the top SAP BW menu list to send the query to SQL Server.
When a multiprovider is used, the transaction shows and runs all SQL queries created for the different infocubes in the multiprovider. In some cases, more than one query is produced for each cube, such as in hierarchical processing. In this case, a single SAP BW query might be comprised of more than 20 SQL queries.
This type of investigation is performed instead of using SQL Server Profiler or the SAP ST05 SQL Trace because of the manner in which SAP BW functions. From the query side, SAP BW builds a view for the complex queries by executing joins, and then executes a SELECT statement without any further restrictions against that view (no Where clause in the SELECT statement). Then SAP BW deletes the view again.
Microsoft Corporation © 2005