Skip to Content
Microsoft SQL Server

SQL Statement History

Tags:

You want to use SQL Statement History to gather further information about the SQL statements which have produced high load on the server in the past.

DBACockpit -> Performance folder -> History folder -> SQL Statement History

You can find the SQL Statement History in SAP systems with MS SQL Server database and the following releases:

  • SAP enhancement package 2 for SAP Netweaver 7.0 SP 06 or newer
  • SAP Netweaver 7.3 SP 02 or newer
  • SAP enhancement package 1 for SAP Netweaver 7.3 or newer
  • SAP Netweaver 7.4 or newer

The upper half of the screen (Workload history) shows the overall statement load over time (measured every twenty minutes).

The list is sorted by the sample date/time with the latest measurements appearing first, which can be re-ordered.

The lower half of the screen (Period details) shows individual statements which were executed during the selected period. Initially the most recent period is selected.

A fragment of the statement text is displayed with the percentages of the load for each criteria. The cells where a particular statement was in the top 5 for a criteria are marked in red.

Further performance data can be viewed pressing the "Detailed view" button.

Double click on a line for a statement, or press the button "SQL statement" will bring up the statement analysis screen for the saved statement.

This informs the user that the saved plan is from history, and may be different from the current plan.

The data is collected within the database collector job which runs every twenty minutes.

The data is retained for 30 days by default.

This can be changed in transaction DBACOCKPIT -> Configuration folder -> Monitoring Environment -> Monitor configuration tab, under the section STMTHIST.

The tables which store the data are:

  • sap_stmt_history : The summary information for each collection data point (the upper half of the screen).
  • sap_stmt_workload: The statement information (the lower half of the screen)
  • sap_stmt_sqltext: The saved statement SQL handle and the actual SQL text for that handle.
  • sap_stmt_plan: The saved plan handle and the actual XML for the saved plan.
  • sap_workload_snapshot: A snapshot from sys.dm_exec_query_stats saved in the last execution.

The data is collected by stored procedure sap_collect_stmt_history and stored procedure sap_save_top_n.

All the tables and procedures needed for the job are created by the script named sap_stmt_history.

See also SAP Help Portal documentation:

Current DB Collector Data
SQL Statement History NW73
SQL Statement History NW70EHP2