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)
On the other hand, for XTP the counter objects are listed first and under that we have instance names listed. (Refer Image#2)
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 Garbage Collection
XTP Phantom Processor
XTP Transaction Log
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%'
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
Log bytes written/sec
The number of bytes written to the SQL Server transaction log by the XTP engine (on average), per second.
Log records written/sec
The number of records written to the SQL Server transaction log by the XTP engine (on average), per second.
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.