BT

SQL Server 2016: Memory Optimized Tables Made Easier

| by Jonathan Allen Follow 577 Followers on Jun 05, 2015. Estimated reading time: 1 minute |

Memory Optimized Tables promise significant performance gains, but tend to be difficult to work with. Much of this difficulty comes from the fact that the table schema and indexes for a Memory Optimized Table couldn’t be altered.

The workaround was to create a temp table, copy the data across, drop the original Memory Optimized Table, and then create and load the new one. That is no longer necessary for the following operations:

  • Changing the bucket count. A bucket count that is too high wastes memory while one that is too low hurts performance.
  • Adding and removing an index. Note that you cannot create or remove an index outside of an ALTER Table command.
  • Changing, adding, and removing a column.
  • Adding and removing a constraint.

Memory Optimized Tables are usually subject to schema binding from Natively Compiled Stored Procedures. You can use the Schema-bound Dependency chart to see if a given ALTER statement is allowed.

Note that ALTER TABLE has some additional limitations involving workloads:

Before starting the ALTER TABLE operation, the workload needs to be halted. Any user transaction that started before the ALTER TABLE started, and that accesses the table, can cause the ALTER TABLE to fail with a serializable validation failure (error code 41325).

Natively Compiled Stored Procedures

In 2016, Natively Compiled Stored Procedures can also be altered. While the new version of the stored procedure is being compiled, the original version will continue to be used. Once compilation is complete, and the pending requests executed, the database will switch over to the new version.

As with tables, this previously required the stored procedure to be dropped before the new one was created. During the compilation window, this could result in failed executions.

Note that you cannot use this feature to convert between normal and natively compiled stored procedures.

Rate this Article

Adoption Stage
Style

Hello stranger!

You need to Register an InfoQ account or or login to post comments. But there's so much more behind being registered.

Get the most out of the InfoQ experience.

Tell us what you think

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread
Community comments

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread

Discuss

Login to InfoQ to interact with what matters most to you.


Recover your password...

Follow

Follow your favorite topics and editors

Quick overview of most important highlights in the industry and on the site.

Like

More signal, less noise

Build your own feed by choosing topics you want to read about and editors you want to hear from.

Notifications

Stay up-to-date

Set up your notifications and don't miss out on content that matters to you

BT