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

select * from master..monSysStatement;
select * from master..monSysSQLText;
select * from master..monSysPlanText;

Key Features

Dependencies and Cleanup

drop table [dbw_sql_stat_current];

Reports and Presentations

Scheduled Jobs

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

What Users Need to Know