sys.dm_db_xtp_checkpoint_stats (Transact-SQL)


Updated: June 10, 2016

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2014)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Returns statistics about the In-Memory OLTP checkpoint operations in the current database. If the database has no In-Memory OLTP objects, returns an empty result set.

For more information, see In-Memory OLTP (In-Memory Optimization).

select * from db.sys.dm_db_xtp_checkpoint_stats  

SQL Server 2014 is substantially different from SQL Server 2016 and is discussed lower in the topic at SQL Server 2014.

The following table describes the columns in sys.dm_db_xtp_checkpoint_stats, for SQL Server 2016.

Column nameTypeDescription
last_lsn_processedbigintLast LSN seen by the controller.
end_of_log_lsnnumeric(38)The LSN of the end of log.
bytes_to_end_of_logbigintLog bytes unprocessed by the controller, corresponding to the bytes between ‘last_lsn_processed’ and ‘end_of_log_lsn’.
log_consumption_ratebigintRate of transaction log consumption by the controller (in KB/sec).
active_scan_time_in_msbigintTime spent by the controller in actively scanning the transaction log.
total_wait_time_in_msbigintCumulative wait time for the controller while not scanning the log.
waits_for_iobigintNumber of waits for log IO incurred by the controller thread.
io_wait_time_in_msbigintCumulative time spent waiting on log IO by the controller thread.
waits_for_new_logbigintNumber of waits incurred by the controller thread for a new log to be generated.
new_log_wait_time_in_msbigintCumulative time spent waiting on a new log by the controller thread.
idle_attemptsbigintNumber of times the controller transitioned to an idle state.
tx_segments_dispatchedbigintNumber of segments seen by the controller and dispatched to the serializers. Segment is a contiguous portion of log that forms a unit of serialization. It is currently sized to 1MB, but can change in future.
segment_bytes_dispatchedbigintTotal byte count of bytes dispatched by the controller to serializers, since the database restart.
bytes_serializedbigintTotal count of bytes serialized since database restart.
serializer_user_timebigintTime spent by serializers in user mode.
serializer_kernel_timebigintTime spent by serializers in kernel mode.
hk_log_bytes_consumedbigintTotal count of log bytes consumed since the database restart.
checkpoints_closedbigintCount of checkpoints closed since the database restart.
last_closed_checkpoint_tsbigintTimestamp of the last closed checkpoint.
hardened_recovery_lsnnumeric(38)Recovery will start from this LSN.
hardened_root_fileuniqueidentifierGUID of the root file that hardened as a result of the last completed checkpoint.
hardened_root_watermarkbigintInternal Only. How far it is valid to read the root file up to (this is an internally relevant type only – called BSN).
hardened_truncation_lsnnumeric(38)LSN of the truncation point.
log_bytes_since_last_closebigintBytes from last close to the current end of log.
time_since_last_closebigintTime since last close of the checkpoint.
current_checkpoint_idbigintCurrently new segments are being assigned to this checkpoint. The checkpoint system is a pipeline. The current checkpoint is the one which segments from the log are being assigned to. Once it’s reached a limit, the checkpoint is released by the controller and a new one created as current.
current_checkpoint_segment_countbigintCount of segments in the current checkpoint.
recovery_lsn_candidatebigintInternally Only. Candidate to be picked as recoverylsn when current_checkpoint_id closes.
outstanding_checkpoint_countbigintNumber of checkpoints in the pipeline waiting to be closed.
closing_checkpoint_idbigintID of the closing checkpoint.

Serializers are working in parallel, so once they’re finished then the checkpoint is a candidate to be closed by close thread. But the close thread can only close one at a time and it must be in order, so the closing checkpoint is the one that the close thread is working on.
recovery_checkpoint_idbigintID of the checkpoint to be used in recovery.
recovery_checkpoint_tsbigintTime stamp of recovery checkpoint.
bootstrap_recovery_lsnnumeric(38)Recovery LSN for the bootstrap.
bootstrap_root_fileuniqueidentifierGUID of the root file for the bootstrap.
internal_errorbigintError seen by any of the controller, serializer, close, and merge threads.

The following table describes the columns in sys.dm_db_xtp_checkpoint_stats, for SQL Server 2014.

Column nameTypeDescription
log_to_process_in_bytesbigintThe number of log bytes between the thread's current log sequence number (LSN) and the end-of-log.
total_log_blocks_processedbigintTotal number of log blocks processed since server startup.
total_log_records_processedbigintTotal number of log records processed since server startup.
xtp_log_records_processedbigintTotal number of In-Memory OLTP log records processed since server startup.
total_wait_time_in_msbigintCumulative wait time in ms.
waits_for_iobigintNumber of waits for log IO.
io_wait_time_in_msbigintCumulative time spent waiting on log IO.
waits_for_new_logbigintNumber of waits for new log to be generated.
new_log_wait_time_in_msbigintCumulative time spend waiting on new log.
log_generated_since_last_checkpoint_in_bytesbigintAmount of log generated since the last In-Memory OLTP checkpoint.
ms_since_last_checkpointbigintAmount of time in milliseconds since the last In-Memory OLTP checkpoint.
checkpoint_lsnnumeric (38)The recovery log sequence number (LSN) associated with the last completed In-Memory OLTP checkpoint.
current_lsnnumeric (38)The LSN of the log record that is currently processing.
end_of_log_lsnnumeric (38)The LSN of the end of the log.
task_addressvarbinary(8)The address of the SOS_Task. Join to sys.dm_os_tasks to find additional information.

Requires VIEW DATABASE STATE permission on the server.

Memory-Optimized Table Dynamic Management Views (Transact-SQL)

Community Additions