2

Our most frequently used database is relatively small (5GB), and as a consequence, our 16GB memory dedicated SQL Server 2012 database server chooses to cache all used tables in memory (Buffer Cache Hit Ratio is 100% constantly). Some people in our team want to move to SQL Server 2014 to increase performance with In-Memory Tables, however I'm not convinced that doing such will increase performance since our tables are already in memory.

Is there more to the new In-Memory Tables in SQL Server 2014, or in my scenario there is indeed little to gain?

  • In-Memory Tables are for very specific needs. If you are having performance issues on a 5GB database you need to look at your queries and table design. – paparazzo Aug 24 '17 at 14:59
  • Which is exactly what my suggestion would be instead. There are so many ways to optimize our system, but people seem to want to chase new fancy technologies because they're advertised to "increase performance 20 times". – Kornél Regius Aug 24 '17 at 15:02
  • 1
    Have you done any performance monitoring? Do you know if there is blocking happening? I'd bet that if you have blocking, and you can alleviate that issue, you may very well see huge improvements in perceived speed. How do you know what's wrong, if you aren't monitoring performance at every level of the application? – Max Vernon Aug 24 '17 at 15:20
  • 1
    Also don't go to SQL 2014, if you can help it. SQL 2016 is an easier transition, has significantly better functionality, is a more long-lived platform. – David Browne - Microsoft Aug 24 '17 at 15:30
  • 1
    Yes, there is much more to in-memory than just ensuring data are memory resident. As to whether or not moving to in-memory will improve performance depends much on your workload. – Dan Guzman Aug 24 '17 at 15:38
3

Some people in our team want to move to SQL Server 2014 to increase performance with In-Memory Tables ...

That entirely depends on:

  1. If more performance is needed
  2. What the current bottleneck is

however I'm not convinced that doing such will increase performance since our tables are already in memory.

You should be suspect that it may or may not improve performance, however the reason isn't because the data is already in memory. It is mainly due to being lock and latch free and less code execution with native compilation.

Thus, if your bottleneck isn't around locks or latches... the actual gain you receive will be minimal to none. This can be exacerbated if the actual bottleneck is the client.

There is a great article about use cases for IMOLTP, and while we are at it you may want to look at 2016 and the uses of IMOLTP for advanced real time analytics.

Is there more to the new In-Memory Tables in SQL Server 2014, or in my scenario there is indeed little to gain?

In-Memory objects (in this case, tables) are not just pinning data into memory. They have entirely different memory structures and are lock and latch free.

There are additional differences in how the data is stored, how it affects database startup, etc.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.