You have read that Microsoft came up with this new thing .. Hekaton they say. The new kid on the block. (Not that new actually .. been there for the better part of the last 10 years, but people still do not know too much about it)

And from time to time people blog about it. Some say that it can fix TempDB issues. So let’s see then, what can and what can’t it do for TempDB:

• Switch table variables with in memory table variable.
  • Yes it works, and is almost one to one.
  • You need to come up with a type (give it a good name, have a good naming convention for them as they are there to stay).
  • Choose good indexes for it. It really makes a difference.
  • Be careful with parallelism. You will not get a parallel plan. So do your homework well
• Switch table valued parameters with in memory table valued parameters. Should really be one to one.
• Switch temp tables to in memory tables or in memory table variables. This is a more interesting solution as temporary tables present more options. Temp tables are persistent through the life of the session in all objects created after their creation irrespective of database.
  • Temp tables used locally as buffers
    • Types are easily created and instantiated.
    • If you have a lot of data, well be careful with the memory. Be sure you have enough
    • If you insert a lot of data, be careful that it will be serialized. So it might be considerably slower than with a temporary table.
    • No statistics, that’s a pity. Be careful with the plans that result.
    • They can be used for cross database usage (more on this later)
  • Temp tables used to share information across the session
    • Well, table variables are not persistent across the session unless you specifically pass them to the next object.
    • So to fix this, we need a little bit more code. Using an in memory table. Build your table, write the data, the session and presto .. you are done. It will be available across your session.
    • But in memory tables are not available though cross databases. So still not the full functionality of temp tables you would say. However types are working cross database, as they are non transactional. So hack it ..

Conclusion

As seen, not everything can be switched out of the box. There is some share of code changed needed to be done. But as long as the above are acknowledged and maybe mitigated, switching to in memory objects should provide a huge increase in performance.