# Configuration

> User configuration defined in this section will never be replaced by subsequent SQLWATCH updates.&#x20;

## 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.&#x20;

## 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]`.&#x20;

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

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]`.&#x20;

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.&#x20;

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.

![](https://560149426-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-LamHUpIpVXoBUUDZZpO%2F-LrZ46cYGRw5ueZer7Iq%2F-LrZ54ftHYfTEwfmjr0Y%2Fimage.png?alt=media\&token=830a376d-5290-4a0d-8ffd-f78d25107484)

```
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 threshold`for a specific time threshold.&#x20;

{% embed url="https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/blocked-process-threshold-server-configuration-option?view=sql-server-ver15" %}

```
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:&#x20;

```
--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
```
