3

I try to unterstand In-Memory...

I got the CTP1 and tried In-Memory. The Whitepaper describes the Syntax for In-memory tables. I wrote some queries to find out the performance gain.

A normal table with 1 million entries take the same time as a memory-optimized table??

Only a insert, delete and update is much faster...

I tried it with native_compilation procedures too, but noway, same time.

How can i reach a lower latency with queries? How have the queries look like to gain performance with select?

create table dbo.Ord (
  OrdNo integer not null primary key 
    nonclustered hash with (bucket_count=1000000), 
  OrdDate datetime not null, 
  CustCode nvarchar(5) not null
) 
with (memory_optimized=on) 
go

This table filled with 1 Mio entries. And a normal table 

create table dbo.Ordnormal (
  OrdNo integer not null primary key nonclustered , 
  OrdDate datetime not null, 
  CustCode nvarchar(5) not null
) 
go

this table too.

Select with the first table takes the same time as the second. Measuring it with set statistics Time on.

Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
Marius Ilg
  • 39
  • 1
  • 11
    Please provide some code so we can see what you were testing. – Martin Smith Sep 04 '13 at 10:52
  • create table dbo.Ord (OrdNo integer not null primary key nonclustered hash with (bucket_count=1000000), OrdDate datetime not null, CustCode nvarchar(5) not null) with (memory_optimized=on) go this table filled with 1 Mio entries and a normal table create table dbo.Ordnormal (OrdNo integer not null primary key nonclustered , OrdDate datetime not null, CustCode nvarchar(5) not null) go this table too Select with the first table takes the same time as the second Measuring it with set statistics Time on – Marius Ilg Sep 04 '13 at 11:13
  • 3
    Post the entire test code, including DDL, and test methodology you used, in the post, not as a comment. – Remus Rusanu Sep 04 '13 at 11:18
  • Two points: 1) Cache, 2) Read-Ahead. Conclusion: On-Disk tables already aggressively leverage available memory. – RBarryYoung Sep 05 '13 at 01:16

3 Answers3

3

You haven't described the nature of the SELECT statements (e.g. are you performing lookups or aggregates) that you're measuring nor what it is you're measuring (elapsed time, CPU time, etc.) so it's difficult to provide a direct answer. For example, if you're sending those 1 million rows to the client, your performance is highly likely to be network bound and you're unlikely to see much advantage from in-memory in terms of elapsed execution time. Performance optimisation is a subject full of nuances.

Regardless, this paper describes Hekaton in detail and should help with your understanding of SQL Server's in-memory implementation.

David
  • 391
  • 1
  • 3
1

Different operations have different performance characteristics. This should be a pretty obvious statement.

Hekaton does not accelerate everyting by exactly a factor of 10. This also should be pretty obvious.

To find out what Hekaton can and cannot do, measure different operations and workloads. Get yourself familiar with how fast the individual DML operations are for example. When you understand how fast the individual primitives are, you have built a mental model of Hekaton performance in your head. That allows you to estimate how more complex workloads are going to behave, and why.

This methodology is how you get to know the performance characteristics of any system. It is important to understand, why things behave the way they do.

Nobody has time to test and measure everything. You often need to estimate how different approaches perform in your head. For that case you need an understanding of the performance model.

usr
  • 168,620
  • 35
  • 240
  • 369
-1

InMemory can help you if (and only if) your query needs less IO to a disk because the data can be found in memory. In your case I expect that the time is spent transmitting the resulting 1 mio rows to the client.

A lot of things around inMemory can go wrong. First of all it's a huge marketing hype. If you have too much data it does not fit into memory unless you have huge amounts of memory (for huge amounts of money) available. Sometimes a more intelligent approach with three levels or more for access would be the better way: Memory, SSD, fast disk, slow disk, ...

The only database I'm aware of, that is able to handle such levels is Teradata. But maybe others can do that too. Comments are welcome :-)

jboi
  • 11,324
  • 4
  • 36
  • 43
  • ok thanks, but is less IO the 16x performance gain in the whitepaper? – Marius Ilg Sep 04 '13 at 11:24
  • I would expect, that the number 16 came up by comparing speed of memory access compared to average speed if disk access compared to average time a query needs for IO. As every query is different and every Hardware is different, it cannot be more then a rough estimate. What database, table and query result sizes are you working with? And what hardware do you have available? – jboi Sep 04 '13 at 11:31
  • Where does the 16x number come from anyway?! See my answer for why no single number makes sense. – usr Sep 04 '13 at 11:50
  • -1 There is a lot more to Hekaton than simply removing some IO. The data structures are entirely different (lock and latch free) and the code path is different too with much fewer CPU instructions required to perform an operation than for "traditional" disc based tables (even where all pages from those tables are already in memory) – Martin Smith Mar 11 '14 at 09:57