Measuring Query Performance

In query tuning, there are two motivations for measuring performance: 

• To compare the relative performance of your tuning experiments. This enables you to determine if an experiment improves performance compared to the baseline.

• To measure the amount of work being performed when executing the query. This can help identify unnecessary work, for example database records that are read, but not used. The gold standard for monitoring performance is to use Measure.

You can use Measure to collect very detailed information on all aspects of query performance. However, it is quite labor intensive to gather and analyze a complete set of Measure data for a query.
This is especially true for non-trivial queries that access many tables and involve many ESP and DP2 processes.

A more practical approach is to use the execution statistics provided by SQL. SQL execution statistics include the elapsed time for compiling and executing the query, as well as per-table statistics. The per-table statistics include:

• Records read and used. The number of records read from a table and the number that were actually used.

• Number of messages and message bytes. This is a measure of message system activity, and is a good analog of IO load.

• Lock waits and escalations. This is an indicator of lock contention. The response time of the query will suffer if it encounters lock waits. Unfortunately, neither VQP nor VQA will gather the SQL execution statistics for you. If you are using one of these tools for the execution plan analysis, you will need to use MXCI to gather the execution statistics. The easiest way to do this is to use the SET STATISTICS command. (MP users can use the SQLCI SET SESSION STATISTICS command.)

Once the statistics option is turned on, the execution statistics will be displayed automatically after preparing and/or executing the query. The SQLXPress Visual Query Tuner automatically collects and stores execution statistics as part of performing a tuning experiment