locked
SQL server 2012 inmemmory for OLTP table used for read only RRS feed

  • Question

  • I would like to know if any in memory feature could be used in SQL 2012 in particular a read-only table from our ERP.
    Tuesday, September 29, 2015 1:08 AM

Answers

  • In-Memory OLTP (aka Hekaton) on SQL 2012 natively is not possible although you could probably get away with using a linked server to a 2014 instance hosting an in-memory table -however the network hop overhead would negate the point.

    Furthermore (and no-one has really mentioned this so far), is that if your table is read-only you will realise very little performance benefit to have that table in memory anyway, since that is not specifically the problem that Hekaton was trying to address. Please remember that if your server has enough memory and your read-only table is queried frequently enough, it will be served from buffer cache (memory) anyway. If your server is underprovisioned then things will be a different story!

    p.s. SQL 2016 is really the time to start thinking about implementing in-memory since many show stopping restrictions have been lifted.


    Regards,
    Mark Broadbent.
    Microsoft Certified Master
    Contact me through twitter | blog | sqlcloud


    Please click "Propose as answer" if a post solves your problem
    or/and vote the post up if it's been helpful.

    • Marked as answer by jbrotto Tuesday, March 6, 2018 4:58 PM
    Saturday, October 17, 2015 8:24 PM

All replies

  • It depends on what you mean with "in memory feature"... In some papers you will find the Columnstore Index as an in memory feature, in which case the answer would be: Yes. If you are thinking "in memory" in terms of the In Memory OLTP engine (=Hekaton), then the answer is No.
    Tuesday, September 29, 2015 5:58 AM
  • Hello,

    The only in-memory feature we have in SQL Server 2012 are Columnstore Indexes and  SSAS in tabluar mode.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, September 29, 2015 6:02 AM
  • Hi Jbrotto

    Its good to hear that the tables are read only because the only "In- memory" option you have in SQL 2012 is the non-clustered column store indexes. These are not true memory bound tables like hekaton but are what is call memory optimized.

    In 2012 you can only create a non clustered index and you will not be able to add data to the table while the index exists. This means dropping the index first before you can insert data then recreating the index. As you table is read only this should not be a problem and you may experience huge for certain workloads such as aggregates with group by's 

    Additional references 

    https://www.simple-talk.com/sql/database-administration/columnstore-indexes-in-sql-server-2012/

    https://msdn.microsoft.com/en-us/library/dn589806(v=sql.120).aspx

    Tuesday, September 29, 2015 6:07 AM
  • The answer is : NO

    In Memory OLTP is based on using a Memory file as this option is not available in SQL server 2012.

    If your ERP table is a Transnational table then this feature is not available. 

    If it is a Data warehouse table then you can have a column store index which is a kind of in memory type feature.

    Tuesday, October 13, 2015 4:20 AM
  • In-Memory OLTP (aka Hekaton) on SQL 2012 natively is not possible although you could probably get away with using a linked server to a 2014 instance hosting an in-memory table -however the network hop overhead would negate the point.

    Furthermore (and no-one has really mentioned this so far), is that if your table is read-only you will realise very little performance benefit to have that table in memory anyway, since that is not specifically the problem that Hekaton was trying to address. Please remember that if your server has enough memory and your read-only table is queried frequently enough, it will be served from buffer cache (memory) anyway. If your server is underprovisioned then things will be a different story!

    p.s. SQL 2016 is really the time to start thinking about implementing in-memory since many show stopping restrictions have been lifted.


    Regards,
    Mark Broadbent.
    Microsoft Certified Master
    Contact me through twitter | blog | sqlcloud


    Please click "Propose as answer" if a post solves your problem
    or/and vote the post up if it's been helpful.

    • Marked as answer by jbrotto Tuesday, March 6, 2018 4:58 PM
    Saturday, October 17, 2015 8:24 PM