59.9.3.4 Queries Statistical Usage

The "Queries Statistical Usage" tab provides an interface to retrieve from the SQL Server some key statistics regarding the performance of executed queries, thus allowing to individuate potentially slow/expensive queries (i.e. which execution is taking too much time, memory or CPU usage).

Queries_Statistical_Usage

In order to start retrieving statistics configure the following parameters and press the [Start] button:

Data ordered by: Specify the criteria for ordering retrieved data
- Average CPU consuming (cycles)
- Average I/O consuming (cycles)
- Average query duration (seconds)
- Last query duration (seconds)
- Execution number (seconds)
Refresh freq. (min): Set the frequency (minutes) with which data will be retrieved.
Max. get records: Set a limit for the number of records (single statistics) retrieved by the application.
Include query plan: If enabled, the query execution plan will be displayed as a "Query Plan" column, containing the ShowPlanXML tag that details the query plan used by each query record.
Free cache plan on start: If enabled, the Query plan cache would be cleared before retrieving statistics.
NB: Please note that the cache plan is automatically cleared when the SQL Server is restarted.
Write data to log file: If enabled, all retrieved data will be recorded in a log file (i.e. dbutils.statqueyusage.log).
[Database]: Shows the database connection parameters (set in Etere Configuration > Settings > Basic > System), allowing to change them (e.g. to analyze a database different from the production one).
[Start]: Start retrieving statistics from queries.
[Stop]: Interrupt the retrieval of statistics.
[Log]: Open the captured log (i.e. dbutils.statqueyusage.log).

Information analysis
Query information retrieved in the right part of the window can be analyzed based on the following key columns:

Avg. CPU consumption - Average CPU cycles taken by the server to complete the execution of the query.
Avg. I/O consumption - Average I/O storage Mb taken by the server to complete the execution of the query.
Avg. duration - Average time taken by the server to complete the execution of the query.
Last query duration - Time taken by the server to complete the last execution of the query.
Execution count - Number of times the query has been executed.
Total row - Total number of records.
Used plan - Indicates the number of times a query has been cached for execution.
1 means that the query has been cached the first time only and then it has been reused, 2 or more means that the query has been re-cached at some (or every) execution.

NB: High consumption does not always mean a problem as it depends on several factors including system size, network, hardware, query code, etc.

For instance, if you are experience slowdowns on your system on a certain time interval (e.g. from 10am to 13pm), the I/O consumption report will provide the information required to analyze and determine if the encountered issues are due to an huge I/O traffic on disk.