Returns values for all database server properties tracked by the database.
sp_property_history( property, min_ticks )
|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.|
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:
You must have EXECUTE privilege on the system procedure.
You must have the MANAGE ANY PROPERTY HISTORY system privilege.
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;