Hello Geeks and welcome to the Day 68 of the long series of One DMV a day. In this series of blogs I will be exploring and explaining the most useful DMVs in SQL Server. As we go ahead in this series we will also talk about the usage, linking between DMVs and some scenarios where these DMVs will be helpful while you are using SQL Server. For the complete list in the series please click here.
After my trip to US for 3 weeks I returned to home today. It was a great, short, eventful, fun, learning and productive trip for me. The greatest part was the PASS summit. You can read my First Timer PASS Experience if you want to know what I did at the PASS Summit. In this whole travel and hectic trip, I have not stopped the One DMV a Day series. Thanks to the SQLServerGeeks community which has always kept me going.
Yesterday I have covered sys.dm_db_xtp_checkpoint_files.It showed how to see the checkpoint file pairs and some overview on how the data and delta files are saved in In-Memory OLTP tables. Today I will be covering sys.dm_db_xtp_checkpoint_stats. As the name suggests it is an aggregated view of the stats related to checkpoints.
Let’s start by looking at the straight forward output from sys.dm_db_xtp_checkpoint_stats.
SELECT log_to_process_in_bytes AS pendingLogB, total_log_blocks_processed AS logBlocksProcessed, total_log_records_processed AS logRecsProcessed, xtp_log_records_processed AS xtpRecsProcessed, total_wait_time_in_ms/1000 AS waitTime_ms, waits_for_io AS ioWaits, io_wait_time_in_ms AS ioWait_ms, waits_for_new_log AS newLogWaits, new_log_wait_time_in_ms AS newLogWait_ms, log_generated_since_last_checkpoint_in_bytes AS logsFromLastChkpntB, time_since_last_checkpoint_in_ms AS lastChkpnt_ms FROM sys.dm_db_xtp_checkpoint_stats
I have renamed the columns to keep them shorter without losing the meaning of it. This keeps me from lesser cropping and smaller images. So let’s get to know the columns of sys.dm_db_xtp_checkpoint_stats.
log_to_process_in_bytes – The number of log records which are between the current LSN of the Checkpoint thread and the end of log file.
total_log_blocks_processed – Count of log blocks processed since SQL instance started.
total_log_records_processed – Count of log records processed since SQL instance started.
xtp_log_records_processed – Count of In-Memory OLTP log records processed since SQL instance started.
total_wait_time_in_ms – Total amount of time in milliseconds that the Checkpoint thread has waited.
waits_for_io – Number of waits by checkpoint thread on the IO.
io_wait_time_in_ms – Time waited by checkpoint thread on the IO.
waits_for_new_log – Number of waits for the new log to be generated.
new_log_wait_time_in_ms – Total wait time while generating the new log.
log_generated_since_last_checkpoint_in_bytes – Total size of log records in bytes since the last checkpoint.
time_since_last_checkpoint_in_ms – Time since the last checkpoint in miliseconds.
As with any other stats DMV sys.dm_db_xtp_checkpoint_stats is aggregated data over time since the SQL instance started. So to make any sense of this data it can be collected periodically for later troubleshooting.
The bottleneck with log records can be identified easily with regular monitoring of sys.dm_db_xtp_checkpoint_stats. Tomorrow I will be covering another In-Memory optimized tables related DMV. So stay tuned. Till then
Happy Learning,
Manu
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook |
Follow me on Twitter | Follow me on FaceBook
