Statistics is very helpful when it comes to performance because query optimizer uses these statistics to create query plans to improve query performance. I recently implemented the statistics on memory optimized table and given below are my findings.
|
S.No |
Disk based tables |
Memory Optimized tables |
|
1 |
Statistics are updated automatically. |
Statistics are NOT updated automatically |
|
2 |
It uses by default sampled statistics. |
It has no default statistics, you must specify fullscan option. |
|
3 |
It supports sampled statistics & fullscan options. |
It supports ONLY fullscan option. |
Following are the steps you must be cognizant of while implementing statistics in memory optimized tables :
- First of all create memory optimized tables and indexes.
- After that, insert (update, delete) data into the memory optimized tables.
- Once you are done with the data manipulation, update statistics on the memory optimized tables. (Do not do this step in the peak hour)
- The last step is to create natively compiled stored procedures that access the memory tables.
Given below is the script to update memory optimized table statistics.
USE hkNorthwind GO UPDATE STATISTICS dbo.tbl_Product_Master WITH FULLSCAN, NORECOMPUTE
SQL Server Portal 
Leave a Reply