Category: Server Executables: Performance and Tuning

Query Processing Metrics, QP Metrics, Query Metrics

Query Processing Metrics or just QP Metrics is a new feature in ASE version 15.0.

QP Metrics recognizes and compares empirically acquired metrics values during an ad-hoc query or stored procedure execution.

• measure the performance of a query.
• determine information on logical I/O.
• determine informationen on physical I/O.
• determine CPU execution time of a query in milliseconds.
• identify frequently executed queries, thus determining how often a query was executed.
• count how often a query was interupted by the Resource Governor due to resource bottlenecks.
catch performance bottlenecks.

Each database keeps its own collection of QP Metrics data in the view sysquerymetrics This view is generated by querying the table sysqueryplans which exists in every database. Query Processing Metrics are an extension of abstract query plans and has therefore no negative impact on running monitoring processes of the MDA tables or the sp_sysmon stored procedure.

Query Processing Metrics can be activated server-wide with the stored procedure

    sp_configure  enable metrics capture , 1
    

or session wide with

    set metrics_capture on
    

The collected QP Metrics data can be retrieved through the view sysquerymetrics using a select statement.

Finding the statement or query with the highest resource usage.

In general, the queries using the most  logical I/O ,  physical I/O ,  CPU execution time  or  elapsed time  are the largest consumers of resources and can be good candidates for performance tuning.

Finding the query with the highest logical I/O

    select lio_avg, rtrim(qtext) as sql_query_text from sysquerymetrics order by lio_avg
    

Finding the query with the highest physical I/O

    select pio_avg, rtrim(qtext) as sql_query_text from sysquerymetrics order by pio_avg
    

Finding the query with the longest execution time

    select exec_avg, rtrim(qtext) as sql_query_text from sysquerymetrics order by exec_avg
    

Finding the most frequently executed query

    select elap_avg, cnt, rtrim(qtext) as sql_query_text from sysquerymetrics order by cnt
    

Backing up existing QP Metrics / Compare performance of queries

Especially when migrating to a new ASE-Server it can happen that customers feel that response time for queries has become slower, i.e. complaining about performance hits for certain statements. It is thus recommended to save QP Metrics data and back it up. After an upgrade or migration, this enables the administrator to compare the performance of individual queries. This is done by copying the data of the old server into a so-called backup group, using the stored procedure

    sp_metrics 'backup', '@gid'
    

where '@gid' is the number of the backup group. For example:

    sp_metrics 'backup', '3'
    

Then recording of QP Metrics data is switched on on the new server using

    sp_configure  enable metrics capture , 1.
    

Acquiring the SQL text of encrypted objects

CAVEAT: Just as sp_hidetext conceals the source code of an object, QP Metrics can be used to reveal the SQL of an encrypted object

See also:
dbcc traceon 311,
set metrics_capture,
set plan for show_execio_xml,
set statistics plancost,
sp_configure "enable metrics capture",
sp_configure "metrics lio max",
sp_configure "metrics pio max",
sp_configure "metrics elap max",
sp_configure "metrics exec max" and
statistics time as well as
statistics io.