-
Notifications
You must be signed in to change notification settings - Fork 5
/
UpdateResolutionAndRetention.sql
311 lines (265 loc) · 9.89 KB
/
UpdateResolutionAndRetention.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
/*** Script to increase SentryOne chart resolution and performance data retention ***
IMPORTANT:
The SentryOne Scalability Pack must be installed first (partitioned CCI + In-mem OLTP)
https://docs.sentryone.com/help/recommendations#scalabilitypack
USAGE:
You can simply run the entire script as-is, or adjust parameters as follows:
- @MinDatapoints and @MaxDatapoints to affect chart range sizes
- @BaseRetentionHours and @ScaleFactor to affect data retention
- Optionally disable 2- and 3- day rollup tables (see notes inline below)
DISCLAIMER: These updates are considered preview, and should be used at your own risk.
Copyright 2020 SQL Sentry, LLC
*/
set nocount on;
--Chart range size variables:
declare @MinDatapoints int = 50;
declare @MaxDatapoints int = 480;
--Retention variables:
declare @BaseRetentionHours float; --Retention hours for raw data. Rollup retention is calc'd off of this as the baseline.
declare @ScaleFactor float; --Scales retention of rollup data relative to raw data. Lower values increase retention. Keep between 1.5 and 4.0.
declare @MinResolutionMinutes float = 1.0/3.0; --20 sec avg for raw data--shouldn't need to change. Higher numbers will reduce rollup retention and increase max chart range size for raw data.
/* Uncomment the line below for the desired combination, or adjust parameters as needed: */
--select @BaseRetentionHours = 240.0, @ScaleFactor = 3.0; --conservative
select @BaseRetentionHours = 360.0, @ScaleFactor = 2.5; --default
--select @BaseRetentionHours = 480.0, @ScaleFactor = 2.0; --extreme
--Preview calcs:
select
LevelBreakMinutes
,MinChartRangeSizeMinutes = LevelBreakMinutes * @MinDatapoints
,MaxChartRangeSizeMinutes = LevelBreakMinutes * @MaxDatapoints
,RetentionDays = CAST(1.0/(@MinResolutionMinutes / CAST(LevelBreakMinutes as float)) * (@BaseRetentionHours / 24)
* (POWER(CAST(LevelBreakMinutes as float), 1.0/@ScaleFactor) / CAST(LevelBreakMinutes as float)) as int)
from PerformanceAnalysisDataRollupLevel
where Enabled = 1
order by LevelBreakMinutes;
select
RawDataTable = N'PerformanceAnalysisData' + Suffix
,RetentionDays = @BaseRetentionHours / 24
from Partitioning.PartitionTracking pt
where TableType = 1
and Enabled = 1
and Suffix <> N'Aggregate';
--Update min/max chart ranges:
update PerformanceAnalysisDataRollupLevel
set MinChartRangeSizeMinutes = LevelBreakMinutes * @MinDatapoints
,MaxChartRangeSizeMinutes = LevelBreakMinutes * @MaxDatapoints
where LevelBreakMinutes > 0;
--Add new placeholder row for raw data:
delete PerformanceAnalysisDataRollupLevel
where LevelBreakMinutes = 0;
insert into PerformanceAnalysisDataRollupLevel
(ID, LevelBreakMinutes, MinChartRangeSizeMinutes, MaxChartRangeSizeMinutes, Enabled)
values
(0, 0, 0, ROUND(@MaxDatapoints * @MinResolutionMinutes, 0), 0);
--Increase performance data retention:
update Partitioning.PartitionTracking
set RetentionHours = 1.0/(@MinResolutionMinutes / CAST(LevelBreakMinutes as float)) * @BaseRetentionHours
* (POWER(CAST(LevelBreakMinutes as float), 1.0/@ScaleFactor) / CAST(LevelBreakMinutes as float))
from Partitioning.PartitionTracking pt
join PerformanceAnalysisDataRollupLevel rl
on 'Rollup' + cast(rl.ID as nvarchar(2)) = Suffix;
update Partitioning.PartitionTracking
set RetentionHours = @BaseRetentionHours
from Partitioning.PartitionTracking pt
where TableType = 1
and Enabled = 1
and Suffix <> 'Aggregate';
/*** OPTIONAL *** [comment out this line to enable this section of the script]
--Will clear out all data from the 2- and 3-day rollup tables.
--Partitioning scheme will be left intact in case they are ever reenabled.
--Data can always be repopulated from 1-day rollups by reenabling and setting LastRollupTimestamp=0 for these levels.
update PerformanceAnalysisDataRollupLevel
set Enabled = 0
where ID IN (12, 13);
truncate table [dbo].[PerformanceAnalysisDataRollup12];
go
truncate table [dbo].[PerformanceAnalysisDataRollup13];
go
--Remove upper range limit for new max level (1-day):
update PerformanceAnalysisDataRollupLevel
set MaxChartRangeSizeMinutes = 999999999
where ID = 11;
--*** OPTIONAL ***/
--Verify updates:
select
LevelBreakMinutes
,MinChartRangeSizeMinutes
,MaxChartRangeSizeMinutes
from PerformanceAnalysisDataRollupLevel
where Enabled = 1
or LevelBreakMinutes = 0
order by LevelBreakMinutes;
select
Suffix
,RollupMinutes = rl.LevelBreakMinutes
,RetentionDays = (RetentionHours / 24)
from Partitioning.PartitionTracking pt
left join PerformanceAnalysisDataRollupLevel rl
on N'Rollup' + cast(rl.ID as nvarchar(2)) = Suffix
where pt.TableType = 1
or rl.Enabled = 1
order by TableType, rl.LevelBreakMinutes, Suffix;
/*** Schema Updates ***/
--Update rollup function definitions:
DROP FUNCTION [dbo].[fnRollupLevelForRangeSizeInMinutes]
GO
DROP FUNCTION [dbo].[fnRollupLevelForRangeSizeAndCounterInMinutes]
GO
CREATE FUNCTION [dbo].[fnRollupLevelForRangeSizeInMinutes]
(
@RangeSizeMinutes int
,@StartTimestamp int = 999999999
)
RETURNS smallint
AS
BEGIN
DECLARE @RollupLevelID AS smallint;
DECLARE @SmallestMatchingLevelID AS smallint;
DECLARE @SmallestMatchingLevelIDWithFullData AS smallint;
DECLARE @MinPartitionBoundaryValue AS int;
SET @SmallestMatchingLevelID =
(
SELECT TOP 1 ID
FROM dbo.PerformanceAnalysisDataRollupLevel
WHERE MinChartRangeSizeMinutes < @RangeSizeMinutes
AND MaxChartRangeSizeMinutes >= @RangeSizeMinutes
AND (Enabled = 1 OR LevelBreakMinutes = 0)
ORDER BY LevelBreakMinutes ASC
);
IF (@SmallestMatchingLevelID = 0) --Raw Data
BEGIN
--Get the oldest boundary timestamp -- won't include older data in this partition but it's the best we can do and stay lightweight.
SET @MinPartitionBoundaryValue =
(
SELECT CAST(MIN(prf.value) as int)
FROM sys.partition_functions pf
JOIN sys.partition_range_values prf
ON pf.function_id = prf.function_id
WHERE pf.name = 'PerformanceDataCurrentFunction'
);
IF (@MinPartitionBoundaryValue < @StartTimestamp)
BEGIN
SET @SmallestMatchingLevelIDWithFullData = 0;
END
END
IF (@SmallestMatchingLevelIDWithFullData IS NULL)
BEGIN
SET @SmallestMatchingLevelIDWithFullData =
(
SELECT TOP 1 ID
FROM dbo.PerformanceAnalysisDataRollupLevel
WHERE MinChartRangeSizeMinutes < @RangeSizeMinutes
AND MaxChartRangeSizeMinutes >= @RangeSizeMinutes
AND LastPurgeBeforeTimestamp <= @StartTimestamp
AND Enabled = 1
ORDER BY LevelBreakMinutes ASC
);
END
SELECT @RollupLevelID = COALESCE(@SmallestMatchingLevelIDWithFullData, @SmallestMatchingLevelID, 0);
DECLARE @MinUploadRollupLevelID AS smallint;
SET @MinUploadRollupLevelID =
(
SELECT TOP 1 MinimumUploadRollupLevelID
FROM ApplicationSettings
);
IF(@MinUploadRollupLevelID IS NOT NULL AND @MinUploadRollupLevelID > @RollupLevelID)
BEGIN
SET @RollupLevelID = @MinUploadRollupLevelID;
END
RETURN @RollupLevelID
END
GO
CREATE FUNCTION [dbo].[fnRollupLevelForRangeSizeAndCounterInMinutes]
(
@RangeSizeMinutes int
,@StartTimestamp int = 999999999
,@CounterID smallint
)
RETURNS smallint
AS
BEGIN
DECLARE @RollupLevelID AS smallint
DECLARE @SmallestMatchingLevelID AS smallint
DECLARE @SmallestMatchingLevelIDWithFullData AS smallint
DECLARE @MinBreakLevelMinutes AS smallint
DECLARE @MinPartitionBoundaryValue AS int;
SET @MinBreakLevelMinutes =
(
SELECT MAX(GetMinutes)
FROM
(
SELECT GetMinutes = SI.IntervalInTicks / 10000000 / 60
FROM PerformanceAnalysisCounter PC
INNER JOIN PerformanceAnalysisSampleInterval SI
ON SI.ID = PC.PerformanceAnalysisSampleIntervalID
WHERE PC.ID = @CounterID
UNION ALL
SELECT GetMinutes = Cat.MinRollupLevelBreakMinutes
FROM PerformanceAnalysisCounter PC
INNER JOIN PerformanceAnalysisCounterCategory Cat
ON Cat.ID = PC.PerformanceAnalysisCounterCategoryID
WHERE PC.ID = @CounterID
) MinBreakLevelMinutes
)
SET @SmallestMatchingLevelID =
(
SELECT TOP 1 ID
FROM dbo.PerformanceAnalysisDataRollupLevel
WHERE MinChartRangeSizeMinutes < @RangeSizeMinutes
AND MaxChartRangeSizeMinutes >= @RangeSizeMinutes
AND @MinBreakLevelMinutes <= LevelBreakMinutes
AND (Enabled = 1 OR LevelBreakMinutes = 0)
ORDER BY LevelBreakMinutes ASC
);
IF (@SmallestMatchingLevelID = 0) --Raw Data
BEGIN
--Get the oldest boundary timestamp -- won't include older data in this partition but it's the best we can do and stay lightweight.
SET @MinPartitionBoundaryValue =
(
SELECT cast(MIN(prf.value) as int)
FROM sys.partition_functions pf
JOIN sys.partition_range_values prf
ON pf.function_id = prf.function_id
WHERE pf.name = 'PerformanceDataCurrentFunction'
);
IF (@MinPartitionBoundaryValue < @StartTimestamp)
BEGIN
SET @SmallestMatchingLevelIDWithFullData = 0;
END
END
IF (@SmallestMatchingLevelIDWithFullData IS NULL)
BEGIN
SET @SmallestMatchingLevelIDWithFullData =
(
SELECT TOP 1 ID
FROM dbo.PerformanceAnalysisDataRollupLevel
WHERE MinChartRangeSizeMinutes < @RangeSizeMinutes
AND MaxChartRangeSizeMinutes >= @RangeSizeMinutes
AND LastPurgeBeforeTimestamp <= @StartTimestamp
AND @MinBreakLevelMinutes <= LevelBreakMinutes
AND Enabled = 1
ORDER BY LevelBreakMinutes ASC
);
END
SELECT @RollupLevelID = COALESCE(@SmallestMatchingLevelIDWithFullData, @SmallestMatchingLevelID, 0);
DECLARE @MinUploadRollupLevelID AS smallint;
SET @MinUploadRollupLevelID =
(
SELECT TOP 1 MinimumUploadRollupLevelID
FROM ApplicationSettings
);
IF(@MinUploadRollupLevelID IS NOT NULL AND @MinUploadRollupLevelID > @RollupLevelID)
BEGIN
SET @RollupLevelID = @MinUploadRollupLevelID;
END
RETURN @RollupLevelID
END
GO
GRANT EXECUTE ON [dbo].[fnRollupLevelForRangeSizeInMinutes] TO [allow_all]
GO
GRANT EXECUTE ON [dbo].[fnRollupLevelForRangeSizeInMinutes] TO [allow_least_privilege]
GO
GRANT EXECUTE ON [dbo].[fnRollupLevelForRangeSizeAndCounterInMinutes] TO [allow_all]
GO
GRANT EXECUTE ON [dbo].[fnRollupLevelForRangeSizeAndCounterInMinutes] TO [allow_least_privilege]
GO