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 |
| Every 15 Seconds |
| Every 1 Minute |
| Every 1 Hour |
| Every 1 Hour |
| Every 1 Hour |
| Every 10 Minutes |
| Every 1 Hour |
| Every 6 Hours |
| Every 1 Minutes |
| 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:
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 |
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.
Blocked Process Monitor
In order for SQLWATCH to record blocking chains we have to enable The blocked process threshold
for a specific time threshold.
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:
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:
Recreate agent jobs
To create all default SQLWATCH agent jobs you can run:
Last updated