Help: SQL Server

Have you ever had a chance to see how athletes train? The coach is constantly watching the stopwatch while the athletes make multiple runs. The stopwatch is used to check the performance of students as they perform the run. In a similar way, SQL Server In-Memory OLTP performance can be monitored using Performance Monitor (PerfMon). In this blog we will discuss about some of the performance counters we can use to monitor various parameters of In-Memory OLTP.

We must call out a difference between the way In-Memory OLTP counters (also known as XTP) vs. traditional counters are laid out in perfmon. If we look at earlier performance counters, the object is Instance specific and under that we have counter (Refer Image#1)

Image#1 Layout of Traditional SQL Counters

On the other hand, for XTP the counter objects are listed first and under that we have instance names listed. (Refer Image#2)

Image#2 Layout of XTP related SQL Counters

On my machine I only have one default instance and that’s why we see only MSSQLSERVER under “Instance of selected object”. In CTP2, we can see below performance monitor objects.

XTP Cursors
XTP Garbage Collection
XTP Phantom Processor
XTP Transaction Log
XTP Transactions

The list of various counters can be achieved using below SQL Server query.

SELECT * 
FROM sys.dm_os_performance_counters 
WHERE object_name LIKE '%xtp%'


  
Image#3 SQL query to get list of XTP related SQL Counters

The meaning of various counters is available on books online.

To demonstrate one of the theory which we learned earlier, we would use “XTP Transaction Log” counter for demo. In this blog about transaction logging concepts, we read “Transaction log records are written only if the transaction on in-memory table is committed. This would mean that no UNDO information would be logged“.

Here is the information about counters under “XTP Transaction Log” taken from book online

Here is the script which we can run and capture perform at the same time.

Select Getdate() AS 'begin of Transaction 1'
INSERT INTO [dbo].[MyFirstMemporyOptimizedTable]
    ([iID],[vFName],[vSite])
     VALUES
    (1,'Balmukund','SQLServer-Help.com')
Select Getdate() AS 'End of Transaction 1'

BEGIN TRAN
Select Getdate()  AS 'begin of Transaction 2'
INSERT INTO [dbo].[MyFirstMemporyOptimizedTable]
    ([iID],[vFName],[vSite])
     VALUES
    (2,'Balmukund','SQLServer-Help.com')
-- Wait for 10 second
waitfor delay '00:00:10'
COMMIT TRAN
Select Getdate()  AS 'commit of Transaction 2'

BEGIN TRAN
Select Getdate() AS 'begin of Transaction 3'
INSERT INTO [dbo].[MyFirstMemporyOptimizedTable]
    ([iID],[vFName],[vSite])
     VALUES
    (3,'Balmukund','SQLServer-Help.com')
-- Wait for 10 second
waitfor delay '00:00:10'
ROLLBACK TRAN
Select Getdate() AS 'rollback of Transaction 3'

Here is the output and perfmon data.

Image#4 perfmon counter and transaction

Here are the conclusion we can make from the graph.

• Logging is done at commit time of transaction. We introduced 10 second delay in our script and due to that second spike appears.
• For uncommitted/rollback of transaction nothing is logged in transaction log.  

Stay tune for learning more on Hekaton / In-Memory OLTP.