SQL Server – ErrorLog – [INFO] HkHostLogCheckpointRecord()

Background

Taking a looking at the error log of one of our SQL Server Instances and found an entry that I wanted to know about.

 

Entry

The entry takes different variables based on timestamp but, it looks like this:

GUI

logfileviewer

Text

[INFO] HkHostLogCheckpointRecord(): Database ID: [10]. Logged XTP checkpoint (v4) for timestamp 2748041, serialize timestamp 2748043: { Root ==> GUIDs = { Rowset {DC99368D-9675-46D7-B97F-EA8D78BA5FD0}, Column {808DBB15-5B8E-4C43-88C1-2611B813FC29} }, Recovery LSN = 00018717:00001FC6:002A, Previous complete checkpoint LSN = 00018717:0000207B:0050, Close LSN = 0001874A:00001F44:000E, Complete LSN = 0001874A:00001F89:0002, Root LSN = 0001874A:00001F44:000D, MRT LSN = 00000000:00000000:0000, Delta Watermark LSN = 00000000:00000000:0000, Backup File Collection LSN = 0001874A:00001F89:0001 }

Explanation

  1. HkHostLogCheckpointRecord()
    • Something about checkpoint
  2. Logged XTP checkpoint
    • XTP points to InMemory

 

TroubleShooting

Metadata

Server Version

Let us confirm the Version and Service Pack that we are running…

Code

SELECT 
			  [version]
				= @@version

			, [productVersion]
				= serverproperty('ProductVersion')			

			, [IsXTPSupportedServer]
			  = serverproperty('IsXTPSupported')


 

Output

serverversion

Explanation

  1. @@server is Server Version is v2014 SP2
  2. Product Version is 12.0.5000.00 ( v2012 SP 2)
  3. ServerProperty-IsXTPSupported is 1

 

Processes that are running

Let us check on the processes that are running and see if we can track which one is emitting the messages

Code


select 
		  tblSP.spid

		, tblSP.[dbid]

		, [database]
			= db_name(tblSP.[dbid])

		, [IsXTPSupported]
			= databasepropertyex
			(
				  db_name(tblSP.[dbid])
				, 'IsXTPSupported'
			)

		, [IsMemoryOptimizedElevateToSnapshotEnabled]
			= databasepropertyex
			(
				  db_name(tblSP.[dbid])
				, 'IsMemoryOptimizedElevateToSnapshotEnabled'
			)

		, tblSP.cmd
		, tblSP.cpu
		, tblSP.memusage
		, tblSR.granted_query_memory
		, tblSP.net_library
		, tblSP.waittype
		, tblSP.lastwaittype
		, tblSP.waitresource
		, tblSP.physical_io
		, tblSR.reads
		, tblSR.logical_reads
		, tblSR.writes


from   master.dbo.sysprocesses tblSP

left outer join sys.dm_exec_requests tblSR

		on tblSP.spid = tblSR.[session_id]
 

where  (

			(
				tblSP.cmd in 
					( 
						'XTP_OFFLINE_CKPT'
					)
			)

			or
			(
				( tblSP.cmd like '%XTP%' )
			)

	   ) 

order by
		tblSP.[spid] asc

 

Output

sysprocesses

 

Explanation

  1. We looked for processes whose payload contains XTP
  2. And, we found the following
    • XTP_THREAD_POOL
    • XTP_CKPT_AGENT
    • XTP_OFFLINE_CKPT
  3. Last Wait Types ( lastWaittypes ) are
    • DISPATCHER_QUEUE_SEMAPHORE
    • WAIT_XTP_HOST_WAIT
    • WAIT_XTP_OFFLINE_CKPT_NEW_LOG

 

Stumbling Blocks

As always ran into a couple of stumbling blocks.

DatabasePropertyEx

IsXTPSupported

Starting with v2014, the DatabasePropertyEx function takes an argument, IsXTPSupported, that one can query to determine whether the targeted database supports XTP (In Memory).

The documentation reads:

Text

Indicates whether the database supports In-Memory OLTP, i.e., creating and using memory-optimized tables and natively compiled modules.

Image

isxtpsupporteddocumentation

Explanation

  1. Unfortunately, IsXTPSupported is returning Null even though our databases contain “In Memory” Objects

 

IsMemoryOptimizedElevateToSnapshotEnabled

Textual

Memory-optimized tables are accessed using SNAPSHOT isolation when the session setting TRANSACTION ISOLATION LEVEL is set to a lower isolation level, READ COMMITTED or READ UNCOMMITTED.

Image

ismemoryoptimizedelevatetosnapshotenableddocumentation

 

Lab

We triggered Snapshot Isolation by issuing “MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT”  on one of the databases that contain In_memory objects

Code

use [QA_rbpivr1]
go

ALTER DATABASE CURRENT SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON
GO

Output
ismemoryoptimizedelevatetosnapshotenabledsampled
Explanation

It is a good thing we triggered Snapshot as the above shows that “IsMemoryOptimizedElevateToSnapshotEnabled” reports 1 or On.

While IsXTPSupported continues to be stuck at NULL.

Null means non-applicable.

 

Database Property

Database – QA_rbpivr1

In the screenshot pasted below, we can deduct that the targeted database, QA_rbpivr1, is allocated and has memory assigned to it.

databaseproperties-qa_rbpivr1

Database – ppsivr

databaseproperties-ppsivr

 

Microsoft Connect

Opened up a Connect Item to Track.

  1. Title :- databasepropertyex([dbname], IsXTPSupported) incorrectly returns null
    Number :- 3120570
    Type :- Bug
    Date :- 2/6/2017
    Status :- Active
    Link : Link

 

References

  1. Waittype
    • Microsoft
      • Description of the waittype and lastwaittype columns in the master.dbo.sysprocesses table in SQL Server 2000 and SQL Server 2005
        Link
    • Pediy.com
      • Windows System Call Table (NT/2000/XP/2003/Vista)
        The Metasploit Project
        Link

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s