Site icon Database Tutorials

Auto Update Statistics in Memory Optimized Tables

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

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.

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.

Finally, you must also manually recompile Natively compiled stored procedures to take advantage of the statistics update.

Exit mobile version