locked
General question about in memory feature RRS feed

  • Question

  • Hi community

    I read about the in memory technology in different databases during the last days and asked myself how the data is handled inside MS SQL Server 2014/2016 with in-memory feature.

    1: Is only the often used data (hot data) stored inside the memory (like Oracle), or the whole data (like SAP HANA)?

    2: If the memory crashes, what will happen with the data inside the memory: 

    - is the data stored twice (in another memory section) and could be read out of this?

    - has the data to be loaded again from of the hard-disk space inside the memory.

    - is the data lost (in case that the whole memory is dead)

    Greetings

    Jesfreric

    Wednesday, March 22, 2017 10:06 AM

Answers

All replies

  • 1. Your first case is used for buffer pool data (Cache), in-Memory data is hold completely in Memory

    2. If "memory crashes", then the complete system will Crash and what happens on a system crash? Data loss.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, March 22, 2017 11:10 AM
    Answerer
  • https://www.simple-talk.com/sql/learn-sql-server/introducing-sql-server-in-memory-oltp/

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, March 22, 2017 11:46 AM
  • Hi Olaf

    Thanks for your reply. Some further questions:

    1. Did I understand it correctly? Not the whole data of the database is copied into memory, but only the "in-memory (hot)" data? The rest "not in memory (cold)" data is stored inside HDD. Because HANA stores its whole data inside the memory (if that makes sense or not depends on use case..).

    2. That's correct. But I meant if there is a "copy" of the in-memory data inside the HDD. Of cause it is possible that some of the "new" in memory data will be lost. But I suppose there are some data stored "in memory" for a high reading speed and was not used before a crash. But if there is a crash will this data lost too, or is there still a "copy" stored inside the HDD.

    Wednesday, March 22, 2017 11:50 AM
  • Hi Uri

    I read on the website you sent to me. Is MS SQL Server not regularly making a HDD-backup of the in-memory data?

    Wednesday, March 22, 2017 11:53 AM
  • Is it that difficult to post clickable links?
    Wednesday, March 22, 2017 1:37 PM
  • Is that difficult copy -paste in the browser preferred by you?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, March 22, 2017 1:45 PM
  • 1. The tables created as memory optimized will be read entirely in-memory at start-up and the indexes will also be created at that time, also entirely in memory.

    2. SQL Server does checkpoint now and then when it flushes the in-memory data to disk. Also, transaction logging logs modifications (just as for disk-based tables) guaranteeing that a shutdown will not mean any loss of committed transactions. Unless where the data is stored is lost, of course - in which case you do a restore (again, just as for regular disk based tables).


    Tibor Karaszi, SQL Server MVP (Web Blog)

    Thursday, March 23, 2017 7:52 AM