Expert Answers to Top SQL Server 2014 Memory‑Optimized Table Questions

Upgrade Path and Performance Tuning

SQL Server 2000 must be upgraded first to SQL Server 2005 or 2008, then to SQL Server 2014. Use the built‑in Upgrade Assistant to run compatibility tests. For OLTP workloads on multi‑core servers, lower the max degree of parallelism (MAXDOP) to a modest value to avoid parallelism‑related slowdown. The Performance Tuning Advisor can provide guidance, but its recommendations should be treated as suggestions, not mandatory actions.

Memory‑Optimized Tables (Hekaton)

Memory‑optimized tables use row‑level multi‑version concurrency control (MVCC) and optimistic concurrency. Each transaction creates a row version with timestamps; the first committing transaction wins, and the other aborts. Applications must implement retry logic for aborted transactions.

Two storage modes exist:

Persistent (default) : Changes are written to checkpoint files on disk for crash recovery. Slow disks degrade performance; place the checkpoint filegroup on fast SSDs. Persistent tables support delayed durability , allowing commits to complete before checkpoint files are flushed, at the risk of data loss on failure.

Non‑persistent : No disk writes; data is lost on server restart, but offers the highest performance for workloads that can tolerate volatility.

Memory‑optimized tables cannot act as a publication server for replication, though they can be subscribers under certain conditions. Replication support is slated for removal in future releases. Combine memory‑optimized tables with Always On availability groups for high‑availability scenarios; however, querying memory‑optimized tables on secondary replicas is not yet supported in SQL Server 2014.

Key Benefits of the In‑Memory Engine (Hekaton)

Eliminates lock contention by using MVCC and optimistic concurrency.

All data resides in memory, removing buffer‑pool contention and disk I/O bottlenecks.

Native compiled stored procedures can accelerate execution up to ten‑fold.

Preparing for Migration to Memory‑Optimized Tables

Use the SQL Server Transaction Performance Collector to identify candidate tables. Follow the migration wizard documented at http://msdn.microsoft.com/zh-cn/library/dn247639.aspx. Estimate the memory footprint; Microsoft recommends keeping the total size of memory‑optimized tables below 256 GB.

Virtualization Impact

Testing shows negligible performance impact when moving SQL Server 2014 to Hyper‑V or VMware virtual machines for typical OLTP workloads. Real‑time, latency‑sensitive workloads may still benefit from bare‑metal deployment.

Upgrade Necessity

SQL Server 2000 and 2005 are out of mainstream support, exposing security and performance risks. Upgrading to SQL Server 2014 is strongly advised.

Flash/SSD Cache Extension

SQL Server 2014 includes extensive cache‑extension optimizations for flash storage. See the discussion at http://tech.it168.com/a2014/0513/1622/000001622470.shtml for configuration details.

Columnstore Indexes

Columnstore indexes are designed for OLAP workloads and are not recommended for OLTP. Use memory‑optimized tables instead to achieve high OLTP performance.

Bucket_Count Configuration

The bucket_count for a memory‑optimized table is determined by available server memory and is specified at table creation; it cannot be altered later. A common practice is to set bucket_count to roughly twice the estimated row count.

Additional Limitations and Guidance

Memory‑optimized tables require a primary key; indexes cannot be added after creation.

Partitioned tables and table‑partition exchange are not supported.

For identifying tables to migrate, consult the Transaction Performance Collector documentation at http://msdn.microsoft.com/zh-cn/library/dn205133.aspx.

Comprehensive list of memory‑optimized table restrictions is available at

http://msdn.microsoft.com/zh-cn/library/dn133181.aspx?sentenceGuid=bfbe351a4904f92c79b801d4151b2f27#mt40

.