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.

SQL SERVER - Listing All Memory Optimized Files with Logical Name and Physical Name Physical-Name-800x192

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.

SQL SERVER - Listing All Memory Optimized Files with Logical Name and Physical Name resultofin-memoryquery

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

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)

, , , ,
Previous Post
SQL SERVER – Override Server’s Configuration of Max Degree of Parallelism
Next Post
SQL SERVER – Identifying and Fixing PREEMPTIVE_OS_RSFXDEVICEOPS Wait Type

Related Posts

Leave a Reply

Menu