SQL statistics
Job details
| Name: |
SQL statistics |
| Platform: |
Sybase |
| Category: |
Performance |
| Premium package: |
SQL Performance package |
| Description: |
Collects performance statistics for the SQL statements. |
| Long description: |
Collects performance statistics for cached query plans from system dynamic views: master..monSysStatement, master..monSysSQLText and master..monSysPlanText. |
| Version: |
1.1 |
| Default schedule: |
0,5,10,15,20,25,30,35,40,45,50,55 * * * |
| Requires engine install: |
Yes |
| Compatibility tag: |
.[type=‘instance’ & databasetype=‘sybase’]/instance[maj_version > ‘12′ & hasengine=‘YES’] |
Parameters
| Name |
Default value |
Description |
| history threshold |
7 |
The maximum number of day to kept statistics for in the historic tables. |
| dbwatch database max size |
-1 |
Parameter not in use. |
| collect execution plan |
YES |
Collects execution plan into history table if set to “YES“. |
| collect internal statistics |
30 |
How often (in minutes) size of internal objects should be checked. “SQL statistics” job collects a large amount of data in the dbWatch schema tables, so it is important to keep track of space usage in the dbWatch database. |
| SQL repository max size |
80 |
Maximum space consumption (in percentage) of the dbWatch database for internal/repository objects that contain statistics for SQL statements. |
| min logical reads |
0 |
Minimum logical reads value (per execution) for a SQL statement to be registered into history tables (repository tables). |
| return status |
1 |
Return status value (ALARM – 2, WARNING – 1, or OK – 0) when reached maximum space consumption (in percentage) of the dbWatch database (parameter “SQL repository max size”). |
| max SQL statements |
100000 |
The maximum number of records to analyze from monSysStatement view which returns aggregate performance statistics for SQL statements in Sybase SQL Server. |
Job Summary
- Purpose: The purpose of this job is to collect and manage performance statistics for SQL statements in a Sybase database environment.
- Why: This job is important to monitor and analyze the performance of SQL statements to optimize database efficiency and troubleshoot issues. It captures various metrics such as logical reads, execution counts, and times, helping in identifying heavy and inefficient queries.
- Manual checking: You can check this manually in the database by issuing the following SQL commands:
select * from master..monSysStatement;
select * from master..monSysSQLText;
select * from master..monSysPlanText;
Key Features
- Collects data from dynamic system views related to SQL performance.
- Provides historical tracking of SQL execution statistics.
- Measures and stores the sizes of internal objects to manage space usage within the dbWatch schema.
- Periodically checks the maximum size configurations and adjusts them if necessary.
- Offers reports that illustrate various aspects of SQL performance through tables and charts.
Dependencies and Cleanup
- Dependencies include various views and tables such as master..monSysStatement, dbw_sql_stat_proc, etc.
- Cleanup processes are in place to ensure that tables do not keep redundant data which might lead to space issues. For example:
drop table [dbw_sql_stat_current];
Reports and Presentations
- The job generates detailed reports based on the gathered statistics. Reports include views like “Internal Objects Size” and “SQL Execution Statistics.”
- Presents data in various formats, e.g., tables and graphical plots, to provide intuitive insights into execution statistics, plan history, and SQL text history among others.
Scheduled Jobs
- The job is set to run at a fixed interval (every 5 minutes as per default settings) to ensure the database performance is continuously monitored.
Performance Metrics Collected
| Metric |
Description |
| --------------------- |
------------------------------------------------------------------- |
| SQL Statements |
Performance statistics for executed SQL statements |
| Execution Plans |
Details of query execution plans used by the database engine |
| Internal Statistics |
Size and health of internal DB objects maintaining these metrics |
Benefits
- Enhanced visibility into database operations concerning SQL statements.
- Helps in proactive monitoring and fine-tuning of database performance.
- Assists in identifying potential or existing performance bottlenecks.
What Users Need to Know
- Users should be familiar with basic database management and SQL query tuning.
- An understanding of Sybase system views and their roles in performance monitoring is beneficial.
- Knowing when to use the collected data to make tuning decisions can significantly improve the database performance and response times.