1 / 1

SQL 2014 MEMORY - OPTIMIZED TABLES (MOTS) AND STATISTICS

Unlike the statistics for traditional disk-based tables, statistics for memory optimized tables (MOTs) don’t get updated automatically. Should we start configuring a new job to periodically update statistics for MOTs? Not necessarily.

satishkartan
Download Presentation

SQL 2014 MEMORY - OPTIMIZED TABLES (MOTS) AND STATISTICS

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL 2014 MEMORY - OPTIMIZED TABLES (MOTS) AND STATISTICS Unlike the statistics for traditional disk-based tables, statistics for memory optimized tables (MOTs) don’t get updated automatically. Should we start configuring a new job to periodically update statistics for MOTs? Not necessarily. If the MOTs are being accessed only by traditional interpreted Stored Procedures, we should update the statistics whenever significant data changes occur (DML – delete, update and insert operations). However, if MOTs are being accessed only by natively compiled stored procedures, just updating the statistics will not help – you should also drop and recreate the natively compiled stored procedures for them to make use of newly updated statistics. There are many other caveats when updating statistics for MOTs (See http://msdn.microsoft.com/en-us/library/dn232522.aspx for more information): NORECOMPUTE clause must be specified when updating stats for MOTs to disable automatic stats update Sp_updatestats against MOTs always updates the stats regardless of any table updates MOTs only support FULLSCAN updates After any major data loading/changes into MOTs, native compiled stored procedures need to be recompiled (droped and recreated). Note that all native compiled stored procedures are recompiled when a database is restarted (offlined then onlined) or when a server is restarted Satish Kartan has been working with SQL Server for the past 20 years. To read more, please visit Satish Kartan's blog at http://www.sqlfood.com/ where he has shared more details on this.

More Related