Memory Optimized tables:
It is my understanding that statistics on memory optimized (MO) tables are created at the time the table is created and is empty, and it never updates. In order to update the MO table statistics on the indexes I need to manually force to update statistics once there is data in the table.
Most of my MO tables are replacement for #temp tables in stored procedures and are defined with schema-only durability.
Each call to the stored procedure is given a 'Session ID', and I have a job that periodically cleans up the MO tables based on old 'Session IDs'.
With that said, the time that the MO tables have the most data is just prior to clean up. Therefore it makes sense to update the table statistics within the clean up procedure and immediately prior to the delete statements.
Natively-Compiled stored procedures:
Much like MO tables, the query plan for natively-compiled (NC) stored procedures are generated at the time the procedure is created and updated only when SQL Server restarts. At the time the procedure is created or SQL Server restarts the MO tables are empty as they are defined with schema-only durability. The only way to recreate the query plan is to drop the NC stored procedure and recreate them once there is data in the MO tables. That means the procedure must run at least once without an appropriate query plan. This is not acceptable - neither having to run the procedure once without an appropriate query plan, nor having to recreate the NC procedures during normal operation.
Another option is to use index hints and force order on every query in the NC stored procedure. This forces the developer to build the query plan and ignore SQL Server. If that's the case, we may as well work in C++ or C#, and not bother with NC procedures.
Is there a suggestion on how to better make use of MO tables and NC stored procedures? I'm using SQL Server 2014, but I believe these hold true for more recent versions of SQL Server.