Auto Update Statistics in Memory Optimized Tables

dbtut

5 years ago

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

ALTER DATABASE your_database_name SET AUTO_UPDATE_STATISTICS ON;

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.

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

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.

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

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.