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
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2029 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 (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)

Group: General Forum Members
Points: 228481 Visits: 46342
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
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

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

Group: General Forum Members
Points: 218379 Visits: 41995
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 (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)

Group: General Forum Members
Points: 228481 Visits: 46342
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 Guru
SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)

Group: General Forum Members
Points: 99587 Visits: 33014
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
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63580 Visits: 13298
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 (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218379 Visits: 41995
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
Old Hand
Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)

Group: General Forum Members
Points: 397 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 (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218379 Visits: 41995
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