In-Memory OLTP files –what are they and how can I relocate them?
In SQL 2014 and above, you can create memory optimized tables with In-Memory OLTP feature. When you use this feature, SQL Server actually generates native code to optimize performance. As a result, there will be dll, pdb files plus other intermediate files. In fact, native optimization is one of three pillars of high performance. The other two are no lock/no latch implantation and optimizing for in memory (no buffer pool handling).
Each stored procedure or table will have separate set of files generated. These are managed by SQL Server and you don’t need to worry about them normally. But we actually got a report from customer lately and they got the following error when starting their database
"Msg 41322, Level 16, State 13, Line 0
MAT/PIT export/import encountered a failure for memory optimized table or natively compiled stored procedure with object ID 214291823 in database ID 6. The error code was 0x80030070".
The error 0x80030070 is operating system error for ERROR_DISK_FULL “There is not enough space on the disk”.
It turned out that customer has lots of memory optimized objects (tables and stored procedures) and that resulted in lots of files generated.
Where do these files get stored?
They are stored in the default location of database file for the server instance.
SQL will always create a subfolder like <default data file location>\xtp\<dbid> and then store files. The file names follow the convention of xtp_<p or t>_<dbid>_<objected>.*. For example, when I created a sample In-Memory OLTP with just one memory optimized table named t, my instance of SQL Server generated the following files.
if you query sys.dm_os_loaded_modules, you will see the native dlls loaded. see a screenshot below.
Additionally, these files will be always deleted and recreated for the following conditions
- SQL Server restarts
- Offline/online database
- drop and recreate a table or procedure
How can I relocate these files?
If you want these files stored in a different location, all you need to do is to change default data file location. SQL Server Management Studio allows you to do that. But you will need to restart SQL Server after the change. Once you do that, the In-Memory OLTP related files will be in the new location.
Jack Li |Senior Escalation Engineer | Microsoft SQL Server