In Part 1 and Part 2 of this post I talked about how to setup memory optimized tables and use natively compiled stored procedures with SQL Server In-Memory OLTP. Now I want to talk about how do you monitor and troubleshoot this feature, especially around memory usage with possible memory exhaustion.
Performance monitor logging is built into Windows and can be very useful to monitor SQL Server, as SQL Server exposes number of counter objects to Perfmon. With In-Memory OLTP SQL Server added number of counters with XTP in the name.
Following counter objects are present per each SQL Server 2014 instance on the machine:
XTP Garbage Collection
XTP Phantom Processor
XTP Transaction Log
You can also query counters straight from SQL Server, like:
SELECT * FROM sys.dm_os_performance_counters WHERE object_name LIKE '%xtp%'
Results on my machine are quite expectable:
Some more details on new XTP counter objects that
- XTP Cursor. The XTP Cursors performance object contains counters related to internal XTP engine cursors. Cursors are the low-level building blocks the XTP engine uses to process Transact-SQL queries. As such, you do not typically have direct control over them.
- XTP Garbage Collection. The XTP Garbage Collection performance object contains counters related to the XTP engine’s garbage collector.
- XTP Phantom Processor. The XTP Phantom Processor performance object contains counters related to the XTP engine’s phantom processing subsystem. This component is responsible for detecting phantom rows in transactions running at the SERIALIZABLE isolation level.
- XTP Storage. The XTP Storage performance object contains counters related to XTP storage in SQL Server.
- XTP Transaction Log. The XTP Transaction Log performance object contains counters related to XTP transaction logging in SQL Server.
- XTP Transactions. The XTP Transactions performance object contains counters related to XTP engine transactions in SQL Server.
In-Memory OLTP engine is fully integrated into SQL Server Memory Manager. So new values are added to familiar SQL Server DMVs to reflect XTP values. Here is example in good old memory clerks DMV:
The first row shows the memory allocated by system threads. The second row with name DB_ID_8 represents the consumers in the database objects and the third row with memory node-id 64 represents memory allocated to DAC (Dedicated Admin Connection).
There are also newly introduced XTP DMVs to monitor In-Memory usage. These are documented in SQL BOL.
Some of the interesting one’s to me:
- sys. dm_db_xtp_checkpoint_stats is DMV will return information about In-Memory OLTP checkpoint operations in the current database. By using this DMV you can you explore the log statistics since SQL Server started up for your databases that contain In-Memory OLTP tables. With this DMV you can determine stats related to log blocks written, wait statistics, LSN information, and more. If this DMV is run against a database that doesn’t contain any In-Memory OLTP tables then it will return no data.
- sys.dm_db_xtp_checkpoint_files. This DMV shows information about In-Memory checkpoint files. With this DMV you can determine information about the DATA and DELTA files associated with your In-Memory tables. It can be very useful in estimating storage space allocated to your memory optimized tables or determining number of inserted and deleted rows
- sys.dm_db_xtp_memory_consumers. This DMV reports the amount of memory allocated and used based on memory_consumer_id’s. With this view you can identify the amount of allocated verses used memory for a given database object
This DMV will show you the amount of memory used by your In-Memory OLTP tables. This DMV exposes the memory information for both user and system tables. By using this DMV you can see the amount of space allocated, and used for both row data and index data. Here is a sample query that will return the amount of space allocated and used for In-Memory OLTP objects in a database.
SELECT OBJECT_NAME(object_id) ObjectName, Object_Id, SUM( memory_allocated_for_indexes_kb + memory_allocated_for_table_kb) AS memoryallocated_object_in_kb, SUM( memory_used_by_indexes_kb + memory_used_by_table_kb) AS memoryused_object_in_kb FROM sys.dm_db_xtp_table_memory_stats GROUP by object_id;
sys.dm_xtp_gc_stats provides information on In-Memory OLTP garbage collection . For example sys.dm_xtp_gc_stats So running –
select * from sys.dm_xtp_gc_statsyou gets you information on number of rows examined by the garbage collection subsystem since your instance of SQL Server has started, etc.
Number of DMVs will have columns labeled as “dusty corner..” . So what is “dusty corner”? I was wondering that as well until I found an answer in Bob Beauchemin’s blog (he rocks as usual) – “The term has to do with how the In-Memory OLTP feature stores data in memory and how its garbage collection works. Because a single copy of a row’s data is stored along with multiple index pointers (both hash and BwTree indexes are ultimately pointer-based), all of index pointers must be “unlinked” before an old row can be garbage collected. Since threads can unlink old pointers while running queries, any index ranges with a lot of activity will quickly unlink the appropriate pointers. However, if an index or index range is rarely used, special scans by the system garbage collector will be needed to find these pointers. They’re “hiding” in the dusty corners (apparently dust forms on unused index ranges, I envision Carol Burnett, duster in hand
So the presence of dusty corner scans means some index ranges aren’t being used much. If, by looking at index usage, you can determine that an entire index is almost never being used (Database Engine Tuning Advisor doesn’t tune indexes for in-memory tables, that I’m aware of), that index would be a candidate for removal. However, in-memory tables don’t support filtered indexes so, if another part of the index range is frequently used, you’ll have to decide if it’s worth letting old versions hang around for longer. Until those dusty corner scans unlink that last pointer.”
Finally, SQL Server introduced number of new xEvents for in-Memory OLTP as well. Extended Events is an event infrastructure that is highly scalable and configurable for server systems. Extended Events is a light weight performance monitoring system that uses very few performance resources. As you may know these are destined to replace SQL Server Profiler since SQL Server 2012: To begin with there are 3 new packages, all belonging to the new In-Memory OLTP Engine: XTP, split up into 3 dlls:
|XtpRuntime||Extended events for the XTP Runtime||
|XtpEngine||Extended events for the XTP Engine||
|XtpCompile||Extended events for the XTP Compile||
The number of Extended Events increased from 625 in SQL Server 2012 (SP1) to 870 in SQL Server 2014 (RTM). For more on xEvents see SQL BOL.