Skip to main content
Database Administrators

Return to Answer

added 806 characters in body
Source Link
Aaron Bertrand
Aaron Bertrand
  • 181.5k
  • 28
  • 402
  • 619

One of the problems with new technology - especially a V1 release that has been disclosed quite loudly as not feature-complete - is that everyone jumps on the bandwagon and assumes that it is a perfect fit for every workload. It's not. Hekaton's sweet spot is OLTP workloads under 256 GB with a lot of point lookups on 2-4 sockets. Does this match your workload?

Many of the limitations have to do with in-memory tables combined with natively compiled procedures. You can of course bypass some of these limitations by using in-memory tables but not using natively compiled procedures, or at least not exclusively.

Obviously you need to test if the performance gain is substantial in your environment, and if it is, whether the trade-offs are worth it. If you are getting great performance gains out of in-memory tables, I'm not sure why you're worried about how much maintenance you're going to perform on INCLUDE columns. Your in-memory indexes are by definition covering. These should only really be helpful for avoiding lookups on range or full scans of traditional non-clustered indexes, and these operations aren't really supposed to be happening in in-memory tables (again, you should profile your workload and see which operations improve and which don't - it's not all win-win). How often do you muck with INCLUDE columns on your indexes today?

Basically, if it's not worth it for you yet in its V1 form, don't use it. That's not a question we can answer for you, except to tell you that plenty of customers are willing to live with the limitations, and are using the feature to great benefit in spite of them.

SQL Server 2016

If you are on your way toward SQL Server 2016, I have blogged about the enhancements you will see in In-Memory OLTP, as well as the elimination of some of the limitations. Most notably:

  • Increase in maximum durable table size: 256 GB => 2 TB
  • LOB/MAX columns, indexes on nullable columns, removal of BIN2 collation requirements
  • Alter & recompile of procedures
  • Some support for ALTER TABLE - it will be offline but you should be able to alter and/or drop/re-create indexes (this does not seem to be supported on current CTP builds however, so do not take this as a guarantee)
  • DML triggers, FK/check constraints, MARS
  • OR, NOT, IN, EXISTS, DISTINCT, UNION, OUTER JOINs
  • Parallelism

One of the problems with new technology - especially a V1 release that has been disclosed quite loudly as not feature-complete - is that everyone jumps on the bandwagon and assumes that it is a perfect fit for every workload. It's not. Hekaton's sweet spot is OLTP workloads under 256 GB with a lot of point lookups on 2-4 sockets. Does this match your workload?

Many of the limitations have to do with in-memory tables combined with natively compiled procedures. You can of course bypass some of these limitations by using in-memory tables but not using natively compiled procedures, or at least not exclusively.

Obviously you need to test if the performance gain is substantial in your environment, and if it is, whether the trade-offs are worth it. If you are getting great performance gains out of in-memory tables, I'm not sure why you're worried about how much maintenance you're going to perform on INCLUDE columns. Your in-memory indexes are by definition covering. These should only really be helpful for avoiding lookups on range or full scans of traditional non-clustered indexes, and these operations aren't really supposed to be happening in in-memory tables (again, you should profile your workload and see which operations improve and which don't - it's not all win-win). How often do you muck with INCLUDE columns on your indexes today?

Basically, if it's not worth it for you yet in its V1 form, don't use it. That's not a question we can answer for you, except to tell you that plenty of customers are willing to live with the limitations, and are using the feature to great benefit in spite of them.

One of the problems with new technology - especially a V1 release that has been disclosed quite loudly as not feature-complete - is that everyone jumps on the bandwagon and assumes that it is a perfect fit for every workload. It's not. Hekaton's sweet spot is OLTP workloads under 256 GB with a lot of point lookups on 2-4 sockets. Does this match your workload?

Many of the limitations have to do with in-memory tables combined with natively compiled procedures. You can of course bypass some of these limitations by using in-memory tables but not using natively compiled procedures, or at least not exclusively.

Obviously you need to test if the performance gain is substantial in your environment, and if it is, whether the trade-offs are worth it. If you are getting great performance gains out of in-memory tables, I'm not sure why you're worried about how much maintenance you're going to perform on INCLUDE columns. Your in-memory indexes are by definition covering. These should only really be helpful for avoiding lookups on range or full scans of traditional non-clustered indexes, and these operations aren't really supposed to be happening in in-memory tables (again, you should profile your workload and see which operations improve and which don't - it's not all win-win). How often do you muck with INCLUDE columns on your indexes today?

Basically, if it's not worth it for you yet in its V1 form, don't use it. That's not a question we can answer for you, except to tell you that plenty of customers are willing to live with the limitations, and are using the feature to great benefit in spite of them.

SQL Server 2016

If you are on your way toward SQL Server 2016, I have blogged about the enhancements you will see in In-Memory OLTP, as well as the elimination of some of the limitations. Most notably:

  • Increase in maximum durable table size: 256 GB => 2 TB
  • LOB/MAX columns, indexes on nullable columns, removal of BIN2 collation requirements
  • Alter & recompile of procedures
  • Some support for ALTER TABLE - it will be offline but you should be able to alter and/or drop/re-create indexes (this does not seem to be supported on current CTP builds however, so do not take this as a guarantee)
  • DML triggers, FK/check constraints, MARS
  • OR, NOT, IN, EXISTS, DISTINCT, UNION, OUTER JOINs
  • Parallelism
Source Link
Aaron Bertrand
Aaron Bertrand
  • 181.5k
  • 28
  • 402
  • 619

One of the problems with new technology - especially a V1 release that has been disclosed quite loudly as not feature-complete - is that everyone jumps on the bandwagon and assumes that it is a perfect fit for every workload. It's not. Hekaton's sweet spot is OLTP workloads under 256 GB with a lot of point lookups on 2-4 sockets. Does this match your workload?

Many of the limitations have to do with in-memory tables combined with natively compiled procedures. You can of course bypass some of these limitations by using in-memory tables but not using natively compiled procedures, or at least not exclusively.

Obviously you need to test if the performance gain is substantial in your environment, and if it is, whether the trade-offs are worth it. If you are getting great performance gains out of in-memory tables, I'm not sure why you're worried about how much maintenance you're going to perform on INCLUDE columns. Your in-memory indexes are by definition covering. These should only really be helpful for avoiding lookups on range or full scans of traditional non-clustered indexes, and these operations aren't really supposed to be happening in in-memory tables (again, you should profile your workload and see which operations improve and which don't - it's not all win-win). How often do you muck with INCLUDE columns on your indexes today?

Basically, if it's not worth it for you yet in its V1 form, don't use it. That's not a question we can answer for you, except to tell you that plenty of customers are willing to live with the limitations, and are using the feature to great benefit in spite of them.