SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Ideal table for Memory Optimization ?


Ideal table for Memory Optimization ?

Author
Message
isuckatsql
isuckatsql
SSC Eights!
SSC Eights! (905 reputation)SSC Eights! (905 reputation)SSC Eights! (905 reputation)SSC Eights! (905 reputation)SSC Eights! (905 reputation)SSC Eights! (905 reputation)SSC Eights! (905 reputation)SSC Eights! (905 reputation)

Group: General Forum Members
Points: 905 Visits: 1110
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)

Group: General Forum Members
Points: 90957 Visits: 45284
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
isuckatsql
SSC Eights!
SSC Eights! (905 reputation)SSC Eights! (905 reputation)SSC Eights! (905 reputation)SSC Eights! (905 reputation)SSC Eights! (905 reputation)SSC Eights! (905 reputation)SSC Eights! (905 reputation)SSC Eights! (905 reputation)

Group: General Forum Members
Points: 905 Visits: 1110
Thanks !
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)

Group: General Forum Members
Points: 90147 Visits: 41146
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
GilaMonster
GilaMonster
SSC Guru
SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)

Group: General Forum Members
Points: 90957 Visits: 45284
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
Grant Fritchey
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42195 Visits: 32666
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Koen Verbeeck
Koen Verbeeck
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28291 Visits: 13268
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.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)

Group: General Forum Members
Points: 90147 Visits: 41146
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. :-P

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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
milos.radivojevic
milos.radivojevic
SSC Veteran
SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)

Group: General Forum Members
Points: 265 Visits: 774
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
Jeff Moden
SSC Guru
SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)

Group: General Forum Members
Points: 90147 Visits: 41146
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search