Expensive SQL statements and their consequences

This article answers the following queries :

• What are expensive SQL statements ?
• What are the consequences of expensive SQL statements ?
• What factors to be considered to tune expensive SQL statements?
• What are the transactions or tools available in SAP to identify expensive SQL statements?

-----------------------------------------------------------------------------------------

A few SQL statements may reduce the performance of the database as well as the SAP system.It is the responsibility of Basis administrator/ Database administrator to identify those statements and action them accordingly. 

Expensive SQL statements are caused by many reasons like

i) Bad programming

ii) Inappropriate use of functions of SAP (like not specifying proper selection criteria while running reports)

Expensive SQL :

Expensive SQL statements are also known as expensive selects. Expensive statements are those statements which cause more disk reads or buffer reads to the database. 

As a rule of thumb, the statements which contribute to more than 5% of total block reads for answering are considered as expensive SQL statements.

Consequences of expensive SQL statements :

A few expensive statements can hamper the performance of the SAP system. Few consequences are listed below :

i) While an expensive SQL statement is being executed, as it has to fetch more blocks, already existing blocks in the database buffer are displaced to accommodate the blocks of this statement. This impacts performance of the other SQL statements and could impact performance of following requests

ii) As database will be busy ready many blocks to satisfy expensive SQL statement, there are chances of slowdown of other database tasks which eventually may impact the performance of the SAP system

iii) While an expensive SQL is being executed, a work process is blocked till the completion of the request. So, it is not available for other requests which increases the wait time of other processes (like waiting in the dispatcher queue)

Factors to be considered to tune expensive SQL 

It is not practically possible to tune each and every expensive SQL. So, we have to identify the statements to be tuned based on the impact of those. Impact of expensive SQL statements on the SAP system performance depends on many factors like 

i) On what frequency the expensive SQL is running?

(If an expensive SQL is running once in 6months, then it is acceptable to run but it an expensive statement is running more frequently like 24 times a day then it is worth tuning that statement)

ii) What is the time of expensive SQL execution?

(If an expensive SQL is running during peak load than it cause more performance issues leading to high response time and wait times to other processes/jobs. So it is important to consider at what time these expensive SQL are being executed. It is best to execute expensive SQL during periods of low work load on the system)

iii) Impact of the expensive SQL on the performance of the system

( It is worth taking note of the impacts the expensive SQL statement is creating on the response time, throughput and performance of the system. Also the duration of the performance problem also to be considered to figureout the impact of an expensive SQL)

SAP transactions to identify expensive SQL

Based on the above factors, an expensive SQL statement can be selected for tuning.

SAP provides so many tools or transactions to identify Expensive SQL statements.

Some of them are SM50, SM66, DBACOCKPIT, ST03, ST04 and ST05

Enter your email address:

Delivered by FeedBurner