Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Ideal table for Memory Optimization ? Expand / Collapse
Author
Message
Posted Tuesday, April 1, 2014 4:10 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Saturday, July 19, 2014 9:15 PM
Points: 537, Visits: 1,110
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
Post #1557258
Posted Tuesday, April 1, 2014 4:19 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:52 AM
Points: 42,445, Visits: 35,501
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 2008, MVP
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

Post #1557259
Posted Tuesday, April 1, 2014 4:35 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Saturday, July 19, 2014 9:15 PM
Points: 537, Visits: 1,110
Thanks !
Post #1557272
Posted Tuesday, April 1, 2014 6:32 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:50 PM
Points: 36,748, Visits: 31,195
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1557285
Posted Wednesday, April 2, 2014 12:05 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:52 AM
Points: 42,445, Visits: 35,501
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 2008, MVP
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

Post #1557321
Posted Wednesday, April 2, 2014 3:51 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 3:51 AM
Points: 15,517, Visits: 27,897
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1557388
Posted Wednesday, April 2, 2014 5:50 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:51 AM
Points: 13,252, Visits: 10,135
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1557416
Posted Wednesday, April 2, 2014 2:48 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:50 PM
Points: 36,748, Visits: 31,195
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1557702
Posted Wednesday, April 2, 2014 4:19 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 7:52 AM
Points: 156, Visits: 613
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!
Post #1557728
Posted Wednesday, April 2, 2014 5:17 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:50 PM
Points: 36,748, Visits: 31,195
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1557741
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse