SQL SERVER – Listing All Memory Optimized Files with Logical Name and Physical Name

I love my job as SQL Server Performance Tuning Expert and I also appreciate how I am able to help many different organizations with Comprehensive Database Performance Health Check. However, once in a while I end up working with clients who have a very bureaucratic system in the place where everything and every step one has to document. Recently I had to work with a client for whom I have to build a script for Listing All Memory Optimized Files with Logical Name and Physical Name.

Here is the script which discusses How to List All Memory Optimized Tables in SQL Server? In this blog post, we will look at how to list All Memory Optimized Files with Logical Name and Physical Name.

SELECT 
       df.[name] As [LogicalName],
       df.[physical_name] As [PhysicalName],
	   CAST(SUM(xcf.file_size_in_bytes)/1024/1024*1.0 
				AS DECIMAL(9,3)) As [SizeGB]
FROM sys.dm_db_xtp_checkpoint_files As xcf
INNER JOIN sys.database_files As df On DF.file_id = xcf.container_id
GROUP BY df.[file_id], df.[name], df.[physical_name]

When I ran the above script for my database I got the following results.

Here are a few additional blog posts related to this topic:

• SQL SERVER – Identity and Filter In-Mem Optimized Tables – SQL in Sixty Seconds #079
• SQL SERVER – Beginning In-Memory OLTP with Sample Example
• SQL SERVER – Filter In-Memory OLTP Tables in SSMS
• SQL SERVER 2016 – InMemory OLTP LOB Datatype Enhancement
• SQL SERVER – SSMS: Memory Usage By Memory Optimized Objects Report
• SQL SERVER – Creating Clustered ColumnStore with InMemory OLTP Tables

Looking at the market trends of the tumbling cost of RAM (USD/MB) and performance implication of reading data from memory vs disk, it’s evident that people would love to keep the data in memory. With this evolution in the hardware industry, the software has to be evolved and modified so that they can take advantage and scale as much as possible. On the other hand, businesses also don’t want to compromise the durability of data – restart would clear RAM, but data should be back in the same state as it was before the failure.

Reference: Pinal Dave (https://blog.sqlauthority.com)