Last modified on 5th September 2018

Data Retention

Data retention is executed by SQL Agent scheduled job:


The job is scheduled to run every 1 hour, 20 seconds past full hour to avoid increased workload at full minute/hour.

The job executes the retention stored procedure:

Some people prefer to run retention job out of hours. The rule of thumb is that the more often the job runs the less data it will have to delete however your mileage may vary. The retention relies on Cascade DELETE and therefore you will have to assess impact in your environment. Large deletions can prevent new data from being inserted and therefore can impact data collector.

Setting up retention will vary depending on the version. In older versions retention period in days was being passed directly into the procedure:

execute [dbo].[sp_sql_perf_mon_retention] @retention_period_days = 14

As of September 2018, a new configuration table was introduced to hold different snapshot types and retention values:

snapshot_type_id snapshot_type_desc snapshot_retention_days
1 Performance 7
If you are upgrading from old version and have changed the default retention you will have to update the above table after upgrading to the recent version. When this table is created we insert the default value of 7. If you already have this table and modified default value we will not change it.