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’)]

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.

The SQL statistics job in dbWatch Control Center is designed to monitor and collect performance metrics for SQL statements that execute against a database. This includes tracking metrics such as execution counts, worker time, and reads and writes. The job ensures that performance data is accurate and routinely updated, allowing for proactive database performance management.

“Monitoring SQL Performance”: By consistently tracking SQL performance, the job helps in identifying slow or inefficient queries that may be impacting database performance. This is crucial for maintaining optimal performance and ensuring resource efficiency.
“Managing Database Size”: The job manages the size of the dbWatch database, ensuring it does not exceed predefined limits, thereby preventing unexpected database growth and potential performance degradation.
“Historical Analysis”: By storing historical data, it supports trend analysis and helps in understanding changes in the database workload over time, which is vital for capacity planning and performance tuning.

Manual checks can be performed by querying system views and tables such as sys.dm_exec_query_stats, sys.dm_exec_sql_text, and sys.dm_exec_text_query_plan to gather similar insights. However, dbWatch automates and simplifies this process significantly.

“Collection of Execution Plans”: Optional collection of query execution plans based on settings, which is critical for diagnosing poorly performing queries.
“Index Usage Analysis”: Checks for missing indexes that could potentially improve query performance. This provides targeted insights for database optimization.

The job involves critical operations related to the management of the internal SQL repository. It ensures the dbWatch internal database does not overrun its space allotments, purging old data as necessary to make room for new entries.

On failure, specific cleanup operations are automatically triggered to maintain the integrity and performance of the dbWatch system database. Multiple drop table commands and stored procedures are available to reset the state.

Creation and indexing of various tables such as dbw_sql_stat_tmp_current, dbw_sql_stat_current, dbw_sql_stat_histr, and others are maintained to keep the SQL statistics up-to-date.

“Current and Historical Storage”: System tables and custom tables (e.g., dbw_sql_stat_current, dbw_sql_stat_histr) store current and historical SQL execution data.
“Execution Plans and Texts”: Includes tables like dbw_sql_text_histr and dbw_sql_plan_histr for storing SQL text and plan data.

“dbw_sql_stat_proc”: This stored procedure is pivotal in collecting, updating, and managing the lifecycle of SQL performance data within dbWatch.

The job setup in dbWatch includes visualization configurations that allow the presentation of statistics through tables and possibly graphical formats (charts), making it easier to digest and analyze the SQL performance data.

While manual monitoring provides complete control to the DBA, automated jobs like this one by dbWatch enhance efficiency, accuracy, and timeliness, reducing the manual workload significantly.

The job parameters are highly configurable allowing adjustments based on the specific requirements of the database environment, which provides flexibility in how data is collected and analyzed.