From BOL, my understanding is that DBAs do not need to administer DLLs created for memory optimized tables, or natively compiled stored procedures, as they are recompiled automatically, when the SQL Server service starts and are removed when no longer needed. But I am witnessing, that even after a memory optimized table has been dropped, and the service restarted, the DLLs still exist in the file system AND are still loaded into SQL memory and attached to the process. This can be witnessed by the fact that they are still visible in sys_dm_os_loaded_modules, and are locked in the file system if you try to delete them, whilst the SQL Service is running.
Is this a bug? Or are they cleaned up at a later date? If at a later date, what triggers the clean-up, if it isn't an instance restart?
Best Answer
No it is not a bug. Its by design. They are kept for troubleshooting and supportability purposes.
From the SQL_Server_2014_In-Memory_OLTP White_Paper
I tried to repro your scenario on
SQL Server 2014 + RTM + (Build12.0.2000.8)
- Dev Edition server by creating a test memory optimized table and checking the dll loaded usingAfter I dropped my table, the
dll
still appears in the output of above select statement and the files are still in the folder and after restart they are still there too.From Books Online -
SO I guess, we just have to follow what Microsoft says - SQL server will manage them for us :-)
ONLY FOR EDUCATIONAL PURPOSE :
I managed to clean up the old files by
CHECKPOINT
on the database.Ideally, you should not be restarting the server instance, just manual checkpoint and offline/online of the database will clear the files.
e.g. Repro :
--- now check if the dll is loaded or not
SELECT name, description FROM sys.dm_os_loaded_modules WHERE description = 'XTP Native DLL'
--- now drop table and do a manual checkpoint
Still the module is loaded in memory (even server restart will load the module sometimes)
The (
.c, .obj, .xml, .pdb., .dll
) are still present in the folder :Now take the database offline and then bring it online - the (
.c, .obj, .xml, .pdb., .dll
) are all gone ...