Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
With SQL Server 2016, support for automatic update statistics in Memory Optimized Tables is available. If the database is created with compatibility level 130, nothing is required. However, if the database was created with an earlier compatibility level, it is necessary to update the statistics manually once the compatibility level has been upgraded to 130.
You may be interested in the article “What is SQL Server Database Compatibility Level and How To Change Database Compatibility Level”
I also recommend you to read the following articles about statistics.
“Statistic Concept and Performance Effect on SQL Server“,
“UPDATE STATISTICS in SQL Server“,
“DBCC SHOW STATISTICS in SQL Server”
Check Auto Update Statistics Enabled
You need to check whether automatic statistics update is enabled in the database. If not, you must enable. Right-click on the database, select properties, and then click on the options tab to see if Auto Update Statistics is True.
Enable Auto Update Statistics
1 | ALTER DATABASE your_database_name SET AUTO_UPDATE_STATISTICS ON; |
Check Auto Update Statistics Enabled in Memory Optimized Tables
You can check whether automatic update statistics is enabled in memory optimized tables with the help of the following script.
1 2 3 4 5 6 7 8 9 | SELECT QUOTENAME(SCHEMA_NAME(tbl.schema_id)) + N'.' + QUOTENAME(tbl.name) AS [tablo_ismi], st.name AS [istatistik ismi], CASE 1-st.no_recompute WHEN 0 THEN 'HAYIR' WHEN 1 THEN 'EVET' END AS [otomatik güncelleme aktif mi?] FROM sys.stats st JOIN sys.tables tbl ON st.object_id=tbl.object_id WHERE tbl.is_memory_optimized=1 |
Update Statistics for Memory-Optimized Tables
If automatic statistics update is not enabled, you can use the following script to manually update statistics. The query will give you a script. You should run this script.
1 2 3 4 5 6 7 8 9 10 11 12 | DECLARE @query NVARCHAR(MAX) = N''; SELECT @query += N'UPDATE STATISTICS ' + quotename(schema_name(tbl.schema_id)) + N'.' + quotename(tbl.name) + ';' + CHAR(13) + CHAR(10) FROM sys.tables AS tbl WHERE tbl.is_memory_optimized = 1 AND tbl.object_id IN (SELECT object_id FROM sys.stats WHERE no_recompute=1) SELECT @query |
Finally, you must also manually recompile Natively compiled stored procedures to take advantage of the statistics update.
