SQL Server 2014 introduced a major feature called "In Memory - Hekaton" Tables, in which even the fundamental concepts of SQL Server Database Transaction Processing have been revamped. Starting from today, I will be doing a series of posts, explaining Hekaton tables and its intricate details, features, pros and cons and just about anything on Hekaton.
As a start, I thought of making this one a FAQ style intro.
1) What is Hekaton - In Memory tables?
Tables and Indexes are placed in memory. Never pushed out of memory. Hekaton aims to provide a performance boost of 100 times. Customers with 25 to 30 times performance improvement have been reported
2) Are all tables of the database in the memory?
No. The one's that are specified as "in Memory" during creation are placed in memory
3) Is it similar to DBCC Pintable of SQL 2000?No. Though both technologies place the data in memory, the storage structures and transaction processing concepts are entirely different
4) What is different about transaction processing? Transactions on Hekaton tables don't use locks or latches at all. It is a lock and latch free architecture
5) What do you mean by "storage structures"?
Instead of storing data as pages, they are stored as "rows" in memory. It helps in providing "latch free" transaction processing
6) Do Hekaton tables persist after restart? Are they written to disks?
Yes. They are written to disk just like disk based tables but logging, checkpoint and recovery processes are entirely different and improved
7) Do they adhere to ACID properties?
Yes. All the properties of Atomicity, Consistency, Isolation and Durability are met just like disk based tables
8) Sounds too good to be true? Are there any restrictions? Drawbacks?
Many many restrictions and drawbacks. If applications can be redesigned / adjusted to use hekaton tables they can gain rich dividends.
9) Is it something I can just turn on in my Database server to make my application faster?
No. Usage of in memory tables, involves changes at design and development phase of the application. Tables redesigned, stored procedures rewritten, application carefully planned and tested..
10) Is it available at all editions?
Only Enterprise
Guess a good appetizer for a heavy meal 🙂 Watch this space for a series of posts on more , more and more details on "Hekaton"
As a start, I thought of making this one a FAQ style intro.
1) What is Hekaton - In Memory tables?
Tables and Indexes are placed in memory. Never pushed out of memory. Hekaton aims to provide a performance boost of 100 times. Customers with 25 to 30 times performance improvement have been reported
2) Are all tables of the database in the memory?
No. The one's that are specified as "in Memory" during creation are placed in memory
3) Is it similar to DBCC Pintable of SQL 2000?No. Though both technologies place the data in memory, the storage structures and transaction processing concepts are entirely different
4) What is different about transaction processing? Transactions on Hekaton tables don't use locks or latches at all. It is a lock and latch free architecture
5) What do you mean by "storage structures"?
Instead of storing data as pages, they are stored as "rows" in memory. It helps in providing "latch free" transaction processing
6) Do Hekaton tables persist after restart? Are they written to disks?
Yes. They are written to disk just like disk based tables but logging, checkpoint and recovery processes are entirely different and improved
7) Do they adhere to ACID properties?
Yes. All the properties of Atomicity, Consistency, Isolation and Durability are met just like disk based tables
8) Sounds too good to be true? Are there any restrictions? Drawbacks?
Many many restrictions and drawbacks. If applications can be redesigned / adjusted to use hekaton tables they can gain rich dividends.
9) Is it something I can just turn on in my Database server to make my application faster?
No. Usage of in memory tables, involves changes at design and development phase of the application. Tables redesigned, stored procedures rewritten, application carefully planned and tested..
10) Is it available at all editions?
Only Enterprise
Guess a good appetizer for a heavy meal 🙂 Watch this space for a series of posts on more , more and more details on "Hekaton"
