SQL statistics


Job details

Name: SQL statistics
Platform: Sqlserver
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 management views: sys.dm_exec_query_stats, sys.dm_exec_sql_text and sys.dm_exec_text_query_plan
Version: 2
Default schedule: 0 * * *
Requires engine install: Yes
Compatibility tag: .[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2012′ & hasengine=‘YES’ & (engine_edition = ‘Microsoft SQL Server’ engine_edition = ‘Azure SQL Managed Instance’)]

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 10000 Maximum size (in MB) of dbwatch database.
collect execution plan NO 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 maximum size of the dbWatch database for internal/repository objects that contain statistics for SQL statements.
min logical reads 100 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 maximum size of the dbWatch database (parameter “SQL repository max size”)
check similarities NO Check similarities between SQL statements while collecting statistics. This option can be switched off (set parameter to NO) if the procedure consumes too much time and resources. To calculate similarities between SQL statements the Levenshtein Distance Algorithm is used (set parameter to YES to enable this option).
check missing indexes YES Check for missing indexes in cached execution plans.

Job Summary

Purpose

Importance

Manual Checking

Technical Details

Key Operations

SQL Repository Management

Data Cleanup

Tables and Indexes

Dependencies and References

Tables

Maintenance Scripts

Presentation and Data Visualization

Data Representation

Additional Notes

Automated vs. Manual Monitoring

Configurability