The amount of space used by SQLWATCH depends on the retention period and the number of databases on a server. On a server with 10 databases and 7 days retention, the size is around 1.8 GB with majority occupied by wait_stats and performance_counters
There are several ways to reduce this requirement that I am currently testing:
- Dynamically reduce the number of snapshots based on age and keep every 10th snapshot after 1 day. This would mean that for the past 24 hours we have 1-minute resolution and anything older than 24 hours would be reduced to 10-minute resolution.
- Change data type to REAL instead of BIGINT. This would reduce accuracy for very large numbers but will not give any significant deviation for what we need. The saving would be around 50%.