Ideal table for Memory Optimization ?

Question

Ideal table for Memory Optimization ?

isuckatsql

SSChampion

Points: 10381
More actions
April 1, 2014 at 4:10 pm

#287179

I have a simple id, name table with six million records, that i converted to a "memory optimized' table.
It now runs six times slower with a 'select count(*)' or 'select like' statement.
I ran the following command
select object_name(object_id), * from sys.dm_db_xtp_table_memory_stats
and it claims 'memory used by table' as 1444239 kb !
Is it the number of rows that is killing the performance or lack or memory ?
Thanks

Viewing 15 posts - 1 through 15 (of 17 total)

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

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

In-memory OLTP (hekaton) is for heavy concurrent load (tens of thousands of concurrent connections with thousands of inserts/sec). The advantage is the lock-less, latch-less optimistic concurrency. It's not a silver bullet for performance in all circumstances.

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

isuckatsql SSChampion Points: 10381 More actions · Answer 2

isuckatsql

SSChampion

Points: 10381

April 1, 2014 at 4:35 pm

#1702283

Thanks !

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 3

GilaMonster (4/1/2014)
In-memory OLTP (hekaton) is for heavy concurrent load (tens of thousands of concurrent connections with thousands of inserts/sec). The advantage is the lock-less, latch-less optimistic concurrency. It's not a silver bullet for performance in all circumstances.

I'm curious. Why wouldn't it run as fast as a normal table that's been cached?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

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

Jeff Moden (4/1/2014)
I'm curious. Why wouldn't it run as fast as a normal table that's been cached?

No idea (and no 2014 to play with). Could have to do with the way he defined the indexes, which indexes he defined. There's a fair bit more that goes into index design for these tables compared to normal ones. Not enough available info (and not enough experience with Hekaton)

In my tests, inserts were around 0%-5% slower at low concurrency levels, I just don't have the hardware to test out thousands of concurrent users.

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

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 5

Not questioning Gail at all, because I'm sure she's right, but, you did you try compiling those queries natively? That's where some of the bigger performance results are achieved. Gail is still 100% correct about the use of Memory Optimized tables and, from the little we know, it doesn't sound like you're in that area.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 6

GilaMonster (4/1/2014)
In-memory OLTP (hekaton) is for heavy concurrent load (tens of thousands of concurrent connections with thousands of inserts/sec). The advantage is the lock-less, latch-less optimistic concurrency. It's not a silver bullet for performance in all circumstances.

Another (smaller) advantage is that you can mark tables as memory-only, so you don't have any IO to the disk subsystem at all. Downside is that you lose the data when SQL Server shuts down. Could be ideal for staging tables though.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 7

Koen Verbeeck (4/2/2014)
GilaMonster (4/1/2014)
In-memory OLTP (hekaton) is for heavy concurrent load (tens of thousands of concurrent connections with thousands of inserts/sec). The advantage is the lock-less, latch-less optimistic concurrency. It's not a silver bullet for performance in all circumstances.
Another (smaller) advantage is that you can mark tables as memory-only, so you don't have any IO to the disk subsystem at all. Downside is that you lose the data when SQL Server shuts down. Could be ideal for staging tables though.

Or... a Tally Table and a Calendar Table... but only if things don't slow down that way. 😛

Shifting gears, I'm really surprised to hear that anything dealing with Hekaton might be a little slower regardless of use.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

milos.radivojevic Default port Points: 1499 More actions · Answer 8

As with disk tables we would need your CREATE TABLE statement for the memory optimized table to check the table and index definition.

Since the ALTER TABLE statement is not supported at all you need to define at the creation time not only table structure but also all indexes you would need later for queries against the table. In addition for hash indexes you have to provide boucket count. If you miss any of this (indexes and bucket_count) performance can be significantly decreased compared to disk tables.

In-memory tables are optmized for point lookup queries and these queries use hash indexes. Range queries (operator LIKE) require range indexes. If you did not create them a full scan is performed. The same happens for SELECT COUNT(*) - index scan (scanning of hash index on primary key). A hash index seek is used only for predicates with Equals operator. If you need more concrete answer please provide the CREATE TABLE statement for MO table.

___________________________
Do Not Optimize for Exceptions!

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 9

milos.radivojevic (4/2/2014)
As with disk tables we would need your CREATE TABLE statement for the memory optimized table to check the table and index definition.
Since the ALTER TABLE statement is not supported at all you need to define at the creation time not only table structure but also all indexes you would need later for queries against the table. In addition for hash indexes you have to provide boucket count. If you miss any of this (indexes and bucket_count) performance can be significantly decreased compared to disk tables.
In-memory tables are optmized for point lookup queries and these queries use hash indexes. Range queries (operator LIKE) require range indexes. If you did not create them a full scan is performed. The same happens for SELECT COUNT(*) - index scan (scanning of hash index on primary key). A hash index seek is used only for predicates with Equals operator. If you need more concrete answer please provide the CREATE TABLE statement for MO table.

Thanks for taking the time to post that.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

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

Jeff Moden (4/2/2014)
Koen Verbeeck (4/2/2014)
GilaMonster (4/1/2014)
In-memory OLTP (hekaton) is for heavy concurrent load (tens of thousands of concurrent connections with thousands of inserts/sec). The advantage is the lock-less, latch-less optimistic concurrency. It's not a silver bullet for performance in all circumstances.
Another (smaller) advantage is that you can mark tables as memory-only, so you don't have any IO to the disk subsystem at all. Downside is that you lose the data when SQL Server shuts down. Could be ideal for staging tables though.
Or... a Tally Table and a Calendar Table... but only if things don't slow down that way. 😛

I suspect you'd want those durable, not their contents disappearing after a restart.

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

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 11

GilaMonster (4/3/2014)
Jeff Moden (4/2/2014)
Koen Verbeeck (4/2/2014)
GilaMonster (4/1/2014)
In-memory OLTP (hekaton) is for heavy concurrent load (tens of thousands of concurrent connections with thousands of inserts/sec). The advantage is the lock-less, latch-less optimistic concurrency. It's not a silver bullet for performance in all circumstances.
Another (smaller) advantage is that you can mark tables as memory-only, so you don't have any IO to the disk subsystem at all. Downside is that you lose the data when SQL Server shuts down. Could be ideal for staging tables though.
Or... a Tally Table and a Calendar Table... but only if things don't slow down that way. 😛
I suspect you'd want those durable, not their contents disappearing after a restart.

Maybe you can launch a job at startup that creates those tables in memory. It doesn't take long to create them and they are possibly frequently joined against.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

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

Koen Verbeeck (4/3/2014)
GilaMonster (4/3/2014)
Jeff Moden (4/2/2014)
Koen Verbeeck (4/2/2014)
GilaMonster (4/1/2014)
In-memory OLTP (hekaton) is for heavy concurrent load (tens of thousands of concurrent connections with thousands of inserts/sec). The advantage is the lock-less, latch-less optimistic concurrency. It's not a silver bullet for performance in all circumstances.
Another (smaller) advantage is that you can mark tables as memory-only, so you don't have any IO to the disk subsystem at all. Downside is that you lose the data when SQL Server shuts down. Could be ideal for staging tables though.
Or... a Tally Table and a Calendar Table... but only if things don't slow down that way. 😛
I suspect you'd want those durable, not their contents disappearing after a restart.
Maybe you can launch a job at startup that creates those tables in memory. It doesn't take long to create them and they are possibly frequently joined against.

Why would you do that over making them DURABILITY = SCHEMA_AND_DATA?

I doubt they're the target of heavy changes, so there won't be logging overhead.

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

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 13

GilaMonster (4/3/2014)
Koen Verbeeck (4/3/2014)
GilaMonster (4/3/2014)
Jeff Moden (4/2/2014)
Koen Verbeeck (4/2/2014)
GilaMonster (4/1/2014)
In-memory OLTP (hekaton) is for heavy concurrent load (tens of thousands of concurrent connections with thousands of inserts/sec). The advantage is the lock-less, latch-less optimistic concurrency. It's not a silver bullet for performance in all circumstances.
Another (smaller) advantage is that you can mark tables as memory-only, so you don't have any IO to the disk subsystem at all. Downside is that you lose the data when SQL Server shuts down. Could be ideal for staging tables though.
Or... a Tally Table and a Calendar Table... but only if things don't slow down that way. 😛
I suspect you'd want those durable, not their contents disappearing after a restart.
Maybe you can launch a job at startup that creates those tables in memory. It doesn't take long to create them and they are possibly frequently joined against.
Why would you do that over making them DURABILITY = SCHEMA_AND_DATA?
I doubt they're the target of heavy changes, so there won't be logging overhead.

Good point.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

milos.radivojevic Default port Points: 1499 More actions · Answer 14

Koen Verbeeck (4/3/2014)
GilaMonster (4/3/2014)
[
Why would you do that over making them DURABILITY = SCHEMA_AND_DATA?
I doubt they're the target of heavy changes, so there won't be logging overhead.
Good point.

Yep. Whenever you can recreate data you can (and should) specify SCHEMA_ONLY. Whenever you need to alter table or restart the server you will definitely have a down time and in both cases it will take some time to create tables in memory from other sources or scripts or from saved, persistent data for the same table.

The big adventage when you specify SCHEMA_ONLY is that there is no transaction logging at all and therefore you have to specify SCHEMA_AND_DATA only if you cannot restore missed data and you need them all (non transient data).

With durable tables (SCHEMA_AND_DATA) transactions are written in the same transaction log as for disk tables and with a suboptimal desicions about transaction log configuration this can be a serious bottleneck. Less log entries will be in the transaction log for MO tables than for disk tables since indexes are not persisted at all (exist only in memory) and only committed transactions are written to the log. But still, it's written.

Generally, for great numbers (30x faster etc.) you would need to have a typical use cases with a know issue you cannot solve good enough in SQL 2012 (read/write contention, too much inserts...) or when data are recoverable (transient data, ETL...). Significant performance improvements you can have if you specify SCHEMA_ONLY or/and use native compiled stored procedures with complex, CPU intensive business logic inside. Just simply converting any disk table to MO table to get it faster could disappoint you. If you configure the table (bucket counts, indexes) suboptimal you should not be surprised if performance goes down.

___________________________
Do Not Optimize for Exceptions!