Understanding In memory OLTP

Question

Understanding In memory OLTP

Divine Flame

SSCoach

Points: 15943
More actions
October 9, 2014 at 5:50 pm

#300044

Hi friends,
My assumption regarding the in-memory tables is that we create the tables & we configure which tables we want to make as in-memory tables. These tables use the ssd disks which acts as the RAM & provides fast data processing.
My question is What will happen if we put our whole database on a SSD Disk? Since the complete database now resides in the SSD itself the data processing time should be the same now for all the tables, irrespective of whether a table has been configured as in-memory table or not. So, in that case if we put our database on SSD, then we don't need to enable or configure the table to be in-memory table pr is there anything else to it( such as memory grants etc will come into play?)
Thanks in advance.
Sujeet Singh

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Vishal Singh SSCertifiable Points: 5830 More actions · Answer 1

Divine Flame (10/9/2014)
Hi friends,
My assumption regarding the in-memory tables is that we create the tables & we configure which tables we want to make as in-memory tables. These tables use the ssd disks which acts as the RAM & provides fast data processing.
My question is What will happen if we put our whole database on a SSD Disk? Since the complete database now resides in the SSD itself the data processing time should be the same now for all the tables, irrespective of whether a table has been configured as in-memory table or not. So, in that case if we put our database on SSD, then we don't need to enable or configure the table to be in-memory table pr is there anything else to it( such as memory grants etc will come into play?)
Thanks in advance.

Solid state Drives or SSDs are used by SQL Server 2014 as Buffer Pool Extension to handle the cache more efficiently and in optimized way. With this advent comes the in-memory OLTP.

Even if the entire database is stored in SSD they won't behaves like an in memory OLTP but a normal physical OLTP. However, since the SSDs are said to be faster drives this in turns make the IO operation quicker which may automatically boost some bit of performance.

hope this helps!

-Forum Etiquette: How to post Performance Problems[/url]

-Forum Etiquette: How to post data/code to get the best help [/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Divine Flame (10/9/2014)
My assumption regarding the in-memory tables is that we create the tables & we configure which tables we want to make as in-memory tables. These tables use the ssd disks which acts as the RAM & provides fast data processing.

In memory != SSD

In-memory tables are literally that. In memory. Not on disk (SSD or otherwise)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Divine Flame SSCoach Points: 15943 More actions · Answer 3

GilaMonster (10/10/2014)
Divine Flame (10/9/2014)
My assumption regarding the in-memory tables is that we create the tables & we configure which tables we want to make as in-memory tables. These tables use the ssd disks which acts as the RAM & provides fast data processing.
In memory != SSD
In-memory tables are literally that. In memory. Not on disk (SSD or otherwise)

Hi Gail,

Thanks for the reply. I think I have not been clear in my question earlier. So, here I take an example to clear it.

Suppose we have a server which has 16 GB RAM. Now, we have configured a table as in-memory table & the size of this table is 25 GB. Therefore, this table can't exist in existing 16 GB RAM. So, to solve that we added a SSD (for better processing speed) to our server & SQL Serevr uses this SSD as extra memory space.

Now SQL Server will have the in-memory table in space allocated from SSD as it can't keep that big table in 16 GB physical RAM. All the input-output will be from this SSD (although SSD will be working as virtual RAM).

Now, suppose we have another table which is also stored on similar SSD however this table is not defined as in-memory table.

So, will there be any performance gap when fetching data from these two tables (one table is in-memory table & the other is normal however they both are put on similar SSD) ?

Please correct me if I am wrong somewhere (or maybe everywhere?) .:cool:

Sujeet Singh

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Divine Flame (10/10/2014)
Suppose we have a server which has 16 GB RAM. Now, we have configured a table as in-memory table & the size of this table is 25 GB. Therefore, this table can't exist in existing 16 GB RAM.

Correct, the table can't exist and SQL will throw an error if you try to do that. In-memory tables MUST be in physical memory.

So, to solve that we added a SSD (for better processing speed) to our server & SQL Serevr uses this SSD as extra memory space.

SQL using the SSD is buffer-pool extensions. Emphasis buffer-pool. In-memory tables don't use the buffer pool, so while that SSD can be used as an extra part of the buffer pool to compensate for the physical memory taken up by the in-memory tables, they can't be used by in-memory tables.

Now SQL Server will have the in-memory table in space allocated from SSD as it can't keep that big table in 16 GB physical RAM.

No, it won't. If an in-memory table won't fit into physical memory, you get errors.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Divine Flame SSCoach Points: 15943 More actions · Answer 5

Thanks Gail for taking time to explain it. Helpfull indeed. Everything is clear now 🙂

Sujeet Singh