Statistics for Memory-Optimized Tables
The query optimizer uses statistics about columns to create query plans that improve query performance. Statistics are collected from the tables in the database and stored in the database metadata.
Statistics are created automatically, but can also be created manually. For example, statistics are created automatically for index key columns when the index is created. For more information about creating statistics see Statistics.
Table data typically changes over time as rows are inserted, updated, and deleted. This means statistics need to be updated periodically. By default, statistics on disk-based tables are updated automatically when the optimizer determines they might be out of date.
Statistics on memory-optimized tables are not updated by default. Instead, you need to update them manually. Use UPDATE STATISTICS (Transact-SQL) for individual columns, indexes, or tables. Use sp_updatestats (Transact-SQL) to update statistics for all user and internal tables in the database.
When using CREATE STATISTICS (Transact-SQL) or UPDATE STATISTICS (Transact-SQL), you must specify NORECOMPUTE to disable automatic statistics update for memory-optimized tables. For disk based tables, sp_updatestats (Transact-SQL) only updates statistics if the table has been modified since the last sp_updatestats (Transact-SQL). For memory-optimized tables, sp_updatestats (Transact-SQL) always generates updated statistics. sp_updatestats (Transact-SQL) is a good option for memory-optimized tables; otherwise you need to know which tables have significant changes so you can update statistics individually.
Statistics can either be generated by either sampling the data or performing a full scan. Sampled statistics only use a sample of the table data to estimate the data distribution. Fullscan statistics scan the entire table to determine the data distribution. Fullscan statistics are usually more accurate but take longer to compute. Sampled statistics can be collected faster.
Disk-based tables use sampled statistics by default. Memory-optimized tables only support fullscan statistics. When using CREATE STATISTICS (Transact-SQL) or UPDATE STATISTICS (Transact-SQL), you must specify the FULLSCAN option for memory-optimized tables.
Additional considerations for statistics on memory-optimized tables:
Indexes on memory-optimized tables are created with the table. The statistics on the index key columns are created when the table is empty. Therefore, these statistics need to be updated after data is loaded into the table.
For natively compiled stored procedures, execution plans for queries in the procedure are optimized when the procedure is compiled. This happens only when the procedure is created and when the server restarts, not when statistics are updated. Therefore, the tables need to contain a representative set of data and statistics need to be up-to-date before the procedures are created. (Natively compiled stored procedures are recompiled if the database is taken offline and brought back online, or if there is a server restart.)
Guidelines for Statistics When Deploying Memory-Optimized Tables
To ensure that the query optimizer has up-to-date statistics when creating query plans, deploy memory-optimized tables using these five steps:
Create tables and indexes. Indexes are specified inline in the
CREATE TABLEstatements.Load data into the tables.
Update statistics on the tables.
Create stored procedures that access the tables.
Run the workload, which can contain a mix of natively compiled and interpreted Transact-SQL stored procedures, as well as ad hoc batches.
Creating natively compiled stored procedures after you load the data and update the statistics ensures that the optimizer has statistics available for the memory-optimized tables. This will ensure efficient query plans when the procedure is compiled.
Guidelines for Maintaining Statistics on Memory-Optimized Tables
To keep statistics up-to-date, regularly update the statistics on memory-optimized tables.
If data changes frequently, you should update statistics frequently. For example, update table statistics after a batch update. After you update statistics, drop and recreate natively compiled stored procedures so they can benefit from the updated statistics.
.
Do not update statistics during peak workload.
To update statistics:
Use SQL Server Management Studio to Create a Maintenance Plan with an Update Statistic Task
Or, update statistics through a Transact-SQL script, as discussed below.
To update the statistics for a single memory-optimized table (myschema. Mytable), run the following script:
UPDATE STATISTICS myschema.Mytable WITH FULLSCAN, NORECOMPUTE
To update statistics for all memory-optimized tables in the current database, run the following script:
DECLARE @sql NVARCHAR(MAX) = N''
SELECT @sql += N'
UPDATE STATISTICS ' + quotename(schema_name(schema_id)) + N'.' + quotename(name) + N' WITH FULLSCAN, NORECOMPUTE'
FROM sys.tables WHERE is_memory_optimized=1
EXEC sp_executesql @sql
To update statistics for all tables in the database, run sp_updatestats (Transact-SQL).
The following sample reports when the statistics on memory-optimized tables were last updated. This information can help you decide if you need to update the statistics.
select t.object_id, t.name, sp.last_updated as 'stats_last_updated'
from sys.tables t join sys.stats s on t.object_id=s.object_id cross apply sys.dm_db_stats_properties(t.object_id, s.stats_id) sp
where t.is_memory_optimized=1