Session 4 Using Non-Session Tools for Tuning | SAP ASE Tutorial
Using Non-Session Tools for Tuning | SAP ASE Tutorial
Describe graphical views of the query execution plan
Gather statistics about query processing performance with non-session tools
Gather metrics about query execution with a query processing tool.
Graphical Plans: As query complexity increases, showplan output becomes difficult to interpret. Graphical views of the query execution plan are a feature of Interactive SQL.
Query Process Metrics : QPMs are used to identify and compare empirical metric values in query execution. Each metric has three values: minimum, maximum, and average. Count and abort count are not included for the metrics. You can use QPM to identify specific queries for tuning and regression on performance.
QPM Captured Metrics
CPU execution time - The time, in milliseconds, it takes to execute a query, not including waits, blocks, or delays
Elapsed time - The difference, in milliseconds, between the time the command started and the current time, as taken from the operating system clock.
Logical I/O reads - The number of LIO reads
Physical I/O reads - The number of PIO reads
Count - The number of times a query is executed
Aborted count - The number of times a query is aborted by the resource governor due to a resource limit being exceeded
To learn latest technology online, visit https://www.skillpedia.co
0:00 Introduction
0:29 Section 3. The Tuning Toolbox SAP ASE
5:32 Query Process Metrics (QPM) • QPMs are used to identify and compare empirical metric values in query execution. • Each metric has three values: minimum, maximum, and average. • Count and abort count are not included for the metrics. • You can use QPM to identify specific queries for tuning and regression on performance.
8:17 Query Tuning Practices • Before QPM, you were required to find candidate queries for tuning using a time-intensive process. • Locating Candidate Tuning Queries Before QPM 1. Find the bottleneck statements inside the applications, as the candidates for
10:14 QPM Filtering • You can use sp_metrics to filter out queries that have been captured after the capture session has ended. • You can apply four metrics using sp_metrics. •In the sp_metrics syntax, filter acts as a where clause on the metrics, and can remove any queries whose value falls below the filter. • Applied Metrics Using sp_metrics