Configuration

User configuration defined in this section will never be replaced by subsequent SQLWATCH updates.

Local collector jobs

All jobs are enabled by default with the exception of the SQLWATCH-LOGGER-WHOISACTIVE which is only enabled when sp_WhoIsActive is found either in the master or the SQLWATCH database. If not found in either, the job is deployed in a disabled state.

Default collection schedule

Data collection relies on SQL Agent jobs. Although it recommended to use default schedules, they can be modified to fit specific requirements.

Default schedules:

Job Name

Schedule

SQLWATCH-INTERNAL-ACTIONS

Every 15 Seconds

SQLWATCH-INTERNAL-CHECKS

Every 1 Minute

SQLWATCH-INTERNAL-CONFIG

Every 1 Hour

SQLWATCH-INTERNAL-RETENTION

Every 1 Hour

SQLWATCH-INTERNAL-TRENDS

Every 1 Hour

SQLWATCH-LOGGER-AGENT-HISTORY

Every 10 Minutes

SQLWATCH-LOGGER-DISK-UTILISATION

Every 1 Hour

SQLWATCH-LOGGER-INDEXES

Every 6 Hours

SQLWATCH-LOGGER-PERFORMANCE

Every 1 Minutes

SQLWATCH-LOGGER-WHOISACTIVE

Every 15 Seconds

Data retention periods

Data retention is executed by Agent job SQLWATCH-INTERNAL-RETENTION. Each snapshot_type has its own retention defined in table [dbo].[sqlwatch_config_snapshot_type].

This approach allows setting different retention for performance data, disk utilisation, query performance and son on.

Default retention periods:

SELECT * FROM [dbo].[sqlwatch_config_snapshot_type]

Snapshot Type

Description

Retention Days

Trend Retention Days

1

Performance

8

2

Disk Utilisation Database

365

3

Missing indexes

32

6

XES Waits

8

7

XES Long Queries

8

8

XES Waits

32

9

XES Blockers

32

10

XES Query Processing

32

11

WhoIsActive

3

14

Index Stats

90

15

Index Histogram

90

16

Agent History

365

17

Disk Utilisation OS

365

18

Checks

7

19

Actions

2

730

20

Reports

2

730

21

Log

2

730

Performance counters

Performance counters collected by SQLWATCH are defined in table [dbo].[sqlwatch_config_performance_counters].

Collection of individual performance counters can be set using the Boolean collect column. Disabling the default performance counter collectors will stop them from being collected and therefore shown on the default dashboard. New collectors can be added to the list if required.

Please note the instance_name is dynamic and contains actual names of objects i.e. database names. The collection definition takes this into account with a dynamic approach:

Definition

Description

Literal valid instance name i.e. 'Elapsed Time:Total(ms)' or ''

Will translate to literal 'Elapsed Time:Total(ms)' or '' Not e that instance name must a valid instance

_Total

Will only include '_Total' instances. This is useful if we only want to collect high level aggregates and are not interested in low level objects i.e. database

<* !_Total>

Will not include '_Total' instances, i.e. it will collect any other instances for this particular counter_name but not totals. This is useful if we want to collect low level objects i.e. database performance and will be aggregating and calculating totals, for example in Power BI. In this case there is no value in collecting totals.

Extended Events Sessions (XES)

A number of Extended Events Sessions are also deployed with SQLWATCH in an enabled state. This is because some DBAs will have their own XES sessions and we would not want to interfere without prior notice. However, certain functionality will not be available, for the full experience please enable SQWLATCH sessions.

ALTER EVENT SESSION SQLWATCH_blockers ON SERVER
STATE = start;
ALTER EVENT SESSION SQLWATCH_long_queries ON SERVER
STATE = start;
ALTER EVENT SESSION SQLWATCH_waits ON SERVER
STATE = start;

Blocked Process Monitor

In order for SQLWATCH to record blocking chains we have to enable The blocked process thresholdfor a specific time threshold.

exec sp_configure 'show advanced options', 1 ;
RECONFIGURE ;
exec sp_configure 'blocked process threshold', 20 ;
RECONFIGURE ;

To make this easier, a procedure has been included in SQLWATCH that will enable the Blocked Process Monitor and set the threshold to 15 (or user specified) seconds:

--default threshold will be 15 seconds:
exec [dbo].[usp_sqlwatch_config_set_blocked_proc_threshold]
--to applly different threshold:
exec [dbo].[usp_sqlwatch_config_set_blocked_proc_threshold] @threshold_seconds = x

Table and Index compression

You may wish to compress data in SQLWATCH to improve storage utilisation and I/O performance at the cost of CPU utilisation. You can do so by running:

exec [dbo].[usp_sqlwatch_config_set_table_compression];
exec [dbo].[usp_sqlwatch_config_set_index_compression];

Recreate agent jobs

To create all default SQLWATCH agent jobs you can run:

--add any missing SQLWATCH jobs, will not remove existing SQLWATCH jobs:
exec [dbo].[usp_sqlwatch_config_set_default_agent_jobs]
--remove existing and recreate all SQLWATCH jobs:
exec [dbo].[usp_sqlwatch_config_set_default_agent_jobs] @remove_existing = 1