sp_property_history system procedure

Returns values for all database server properties tracked by the database.

Syntax

sp_property_history( property, min_ticks )

Parameters

property Use this VARCHAR(255) to specify the name of the database server property to report. If NULL, then all currently monitored properties are reported. The default is NULL.
min_ticks Specify a tick value to return all recorded property values with a ticks value that is equal to or greater than the specified tick value. The default is NULL.

Result set

Column name	Data type	Description
name	VARCHAR(255)	The name of the database server property.
ticks	UNSIGNED BIGINT	A monotonically increasing value that chronologically orders property values.
time_recorded	TIMESTAMP WITH TIME ZONE	The system time when this value was recorded.
time_delta	UNSIGNED INTEGER	The number of milliseconds since the previous recording, independent of system time.
value	DOUBLE	The current value of the database server property.
value_delta	DOUBLE	The change in the database property value since the previous recording.

Remarks

This system procedure returns results for database server properties being tracked by any database running on the database server, as well as by the -phl database server option. The database server uses ticks, measured by your computer's system clock, to track the chronological order in which property values are recorded. Each recorded value has a tick value that increases monotonically, along with an associated timestamp measured in GMT.

If property-name is NULL, then all database server property values are returned.

If min_ticks is NULL, then all property values for the selected properties (or all properties if property-name is NULL) are returned.

If the database is restarted, then property history data is only kept for properties currently being tracked by another running database.

If the database server is restarted, then property history data and tracking settings are lost. Desired tracking settings must be re-supplied.

Database-specific property tracking settings are also lost if all [of] the following are true:

The database is restarted.
No other database running on the database server is tracking the database server property.
The database server property is not being tracked at the database server level.

Tip To maintain database-specific tracking settings, create a database start-up event to mimic the persistence of these settings.

Privileges

You must have EXECUTE privilege on the system procedure.

You must have the MANAGE ANY PROPERTY HISTORY system privilege.

Side effects

None

Example

To list all of the recorded database server property values in descending order, execute the following statement:

SELECT * FROM sp_property_history( )
	ORDER BY ticks desc;

To pivot the property history information so that each row shows the delta changes per tick/time, execute the following query:

BEGIN
    DECLARE @props VARCHAR(120) ARRAY;
    SELECT *
        INTO #propdata
        FROM sp_property_history();
    SET @props = ( SELECT ARRAY_AGG( DISTINCT name ORDER BY name ) FROM #propdata );
    SELECT *
        FROM ( SELECT name, ticks, time_recorded, time_delta, value_delta FROM #propdata ) mysourcedata
        PIVOT ( SUM( value_delta ) delta FOR NAME IN @props ) mydata
        ORDER BY ticks DESC;
END;

To pivot the property history information so that each row shows the value changes per tick/time, execute the following query:

BEGIN
    DECLARE @props VARCHAR(120) ARRAY;
    SELECT *
        INTO #propdata
        FROM sp_property_history();
    SET @props = ( SELECT ARRAY_AGG( DISTINCT name ORDER BY name ) FROM #propdata );
    SELECT *
        FROM ( SELECT name, ticks, time_recorded, value FROM #propdata ) mysourcedata
        PIVOT ( SUM( value ) value FOR NAME IN @props ) mydata
        ORDER BY ticks DESC;
END;