SQL execution plan

A query execution plan is the optimized set of steps used to access data in a SQL relational database management system. It is retrieved from the database cache on demand and is not stored in AppMon.

By examining it in AppMon, find answers if, why and where a query is slow and indices are used or not ‐ or, setting timings in relation, a different component is the culprit.

You can access execution plans from the context menu Show SQL Execution Plan of a SQL statement on the Database Dashlet or a PurePath.

The following limitations apply:

  • Stored procedures and functions are supported only for MSSQL. For other databases, statements that start with call, exec or {call should have the Show SQL Execution Plan option disabled.
  • If the DB cache has been cleared, prior execution plans are not available.
  • Not supported for the Oracle Database setting cursor_sharing set to force
  • In order to request an execution plan the user must have the DB permission to view SQL statements and the AppMon one (Settings menu > Dynatrace Server > Settings item > Confidential Strings horizontal tab ) to view confidential strings.
  • Using the jTDS driver for MSSQL enabling query preprocessing (JtdsStatement.setEscapeProcessing() method) may cause problems with looking up query execution plans as the query text gets modified.
  • On the Database dashlet the presented SQLs have their whitespace normalized. Disable whitespace normalization from the SQL query's context menu (see screenshot below)
  • To optimize performance, DB-related sensors such as JDBC, ADO.NET, and PHP store only the first 512 characters of a query by defaut. Truncated queries do not match explain plans. You can modify this value in the JDBC and ADO.NET sensor configurations, in the agent section of a system profile. (see screenshot below)

Denormalize sql JDBC Sensor properties