Can the SentryOne Client - SQL Server Setting 'Minimum Duration' be scripted out with T-SQL?

   In Sentryone, can you script out the SQL Server Setting 'Minimum Duration' so that it can have a certain minimum duration time for a specific period of time?

   Example: Minimum Duration of 3 seconds but for only at 2:00 AM ET to 4:00 AM ET and the rest of the time the minimum duration setting is set to 5 seconds.

Thanks, Greg

  • Hey there Greg!  Thanks for your patience in getting back to you on this.  This was a difficult one to track down and it took some work with the engineering team to get the last leg finished.

    In general, the Minimum Duration setting that you see in Top SQL Source is stored in [dbo].[ISqlServerTraceLogSourceSettings].[MinimumDurationThreshold].  There's nothing preventing you from modifying that value directly, but you'll need to consider your settings inheritance.  Said differently, it'll be easy to change the value globally, but significantly more complicated if you want to do it at a lower level.  Especially if the override doesn't already exist.  The following query gets you to the value, which can be modified into an UPDATE:

    	[SettingID] = isstlss.ID
    	, [SettingSection] = st.[Name]
    	, [SettingSubSection] = stc.[Name]
    	, vo.[ObjectName]
    	, isstlss.MinimumDurationThreshold
    FROM dbo.ISqlServerTraceLogSourceSettings isstlss
    JOIN dbo.IEventSourceSettings iess ON iess.ID = isstlss.ID
    JOIN dbo.SettingType st ON st.ID = iess.SettingTypeID
    JOIN dbo.SettingTypeCategory stc ON stc.ID = st.SettingTypeCategoryID
    JOIN dbo.vwObjects vo ON vo.ObjectID = iess.ObjectID
    	st.[Name] = 'Top SQL Source'
    	AND stc.[Name] = 'SQL Server'	--Can modify for Azure SQL DB or RDS
    	AND vo.ObjectName = 'Global'	--Can modify to specific object if override already exists

    This setting then controls how our XE objects are defined on target systems in order to pull back Top SQL events.  In order to do this, we need to fake a signal to the monitoring services that settings have been updated.  That can be done using the following query:

    UPDATE [dbo].[TableUpdateTimestamps]
    SET [LastUpdated] = GETUTCDATE()
    WHERE [TableName] IN (
    	, 'IEventSourceConnectionSettings'
    	, 'ISqlServerTraceLogSourceSettings'
    	, 'PerformanceAnalysisTraceFilter'

    Once I ran the settings update, shortly followed by the TableUpdateTimestamps update, I was able to achieve the desired behavior.  One item of note is that in my small sample size, I would sometimes get the XE updated within seconds on the target, other times up to 90 seconds.  I don't think we'll be able to control that aspect of things, but you may need to consider up to a ~2 minute lag for the setting to get "honored" if you're trying to schedule it.

    Let us know how it goes!