back to article Fusion-io: Ah, Microsoft. I see there's in-memory in SQL Server 2014... **GERONIMO!**

Microsoft has given SQL Server2014 an in-memory operation ability and server flasher Fusion-io has quickly jumped in to take advantage. Its ioMemory product can run the in-memory flavour of SQL Server and deliver, Fusion claims, ”up to 4x improvements in transactions per second and a significant reduction in data latencies in …

COMMENTS

This topic is closed for new posts.
Anonymous Coward

Unfortunately, MS broke other stuff (thread scheduling) so the product is virtually unusable.

I am a very unhappy camper now, and NoSQL and their ilk are starting to look good for DW applications

5
1

Extra cache

"offloading clean data pages from traditional storage to flash" meaning that it's a big read only cache.

For resilience some traditional (shared?) fault tolerant storage is still needed.

Also, if the main DB server does crash what's the performance going to be like while the HA DB server catches up? Time for AlwaysOn Failover Clusters I guess.

3
0
Silver badge

Ah yes, the "in-memory database" that's effectively crippled due to lack of support for many standard and commonly used SQL operators.

However I'm sure that if you had specific data requirements that you need to run at an acceptable speed, you could redesign your database, separate the data that you need fast access for and then work around the dependencies. In general it may be useful for specific new cases, useless for speeding up existing databases.

4
0
Silver badge

"effectively crippled "

Unless you ported from MySQL 3.23 in the first place and have access to the original code, in which case you should feel right at home -

oh, it doesn't do "LIKE" does it?

Microsoft - forcing everybody to use integer keys because it's what they know is good for you.

1
0
Anonymous Coward

10 years+ later

that feature is available in the mysql world since year 2K ...

SQL server product is now like others MS products, toys for non IT peoples and clicking kiddies that like to see shinny frozen progress bar.

1
6
Silver badge
FAIL

Re: 10 years+ later

Quality trollings!!!!

Not.

Must try a bit less hard.

4
2
Bronze badge

Re: 10 years+ later

I'd have to agree in that the MySQL offering isn't quite the same thing. This isn't just a RAM disk being put behind a DB.

On the other hand Sybase, for example, has had an in memory database without restrictions for about 7 years.

On the other, other hand each mainstream enterprise RDBMS has got features the others haven't, then the others catch up and then new features are added..........

2
0
Silver badge

Re: 10 years+ later

Somewhere I have an old 50Mhz 486 with a gig of ram in it. I used to use it to annoy a system admin cos I could run copies of our work database in ram using MySQL and easily outperform SQLserver (4.5?) running on dual core 300Mhz pentiums with 512Meg of ram.

I'm trying to remember exactly but this was pre 1999 so thats 15 years ago...

2
1
Bronze badge

Re: 10 years+ later @Tom 7

"Somewhere I have an old 50Mhz 486 with a gig of ram in it."

Fantastic story, but I'm confident that there has never ever been a 486 system with support for that much RAM. The top-end 486 systems (that I know of) had support for maybe 256MB and even that would have been ludicrously expensive way back then (1993ish?), and still very expensive even in 1998.

- The bandwidth of the 486 memory system (assuming FPM mem) is well over 100MB/s and the latency in that 486 memory is thousands of times lower than on hard drives. Not that big of a deal and compares to these in-memory SQL systems that are popping up these days (HANA, SQL et al.) - very quick and very expensive.

0
0

Re: 10 years+ later

Another 3-handed economist! I don't think we've met before?

0
0
Silver badge

Perhaps I'm misunderstanding what they've done...

... but Oracle DBs have kept an in memory buffer of recent queries, plans and data rows for more versions than I can remember. How does this differ?

0
0
Bronze badge

Re: Perhaps I'm misunderstanding what they've done...

The buffer or cache is common to all ACID compliant RDBMSs I know of. However, it's a read cache, all writes eventually find their way onto disk. Information which would be needed in case of a rollback/rollforward after a hardware failure is written at the point of commit. This, in effect, means performance is limited by the speed the disk can write this essential data (other data being written at a time convenitent to the system, to generalise a bit).

What MS SQL Server is now trying to offer is a DB which sits in memory so writes will never go to disk. As the RDBMS knows writes will never go to disk it can also ditch some of the logic around logging etc. This provides added benefits over simply using a RAM disk as people have done in the past.

MS hasn't quite got this 100% yet and there are therefore limits to the SQL you can use in such a DB. Sybase, as mentioned above, have no limits and there are even extra possibilities, like mixing and matching tables in memory, on disk, on disk but limited recovery, using a template DB with data etc.

I'm sure there are other RDBMSs which can do the same.

0
0

Re: Perhaps I'm misunderstanding what they've done...

Yes, all RDBMS like Oracle keep a history of recent queries and their execution plans, and they also have a sizable block buffer cache in which they store the data fetched from storage into memory. These block buffer caches have algorithms to determine where data is placed in memory upon fetch and how the data will be "aged" out of memory. Without giving you the full 3-day Oracle Performance Tuning lecture suffice it to say these algorithms favor data on disk. If you ever tried to pin all of your tables and indexes in memory you'd notice just a fraction of the data actually stays in memory long term.

Along comes this up-start company saying "we will go ten times faster than Oracle or quit". They developed entirely new logic for keeping data in memory and reducing the communication or synchronization between those in-memory buffers and storage. And so Oracle bought them and offers two completely separate database products: Oracle Database Server, and Oracle TimesTen In-Memory Database. (These will be merging, but that's another story).

Microsoft is expanding its portfolio to match its rivals.

0
0
Anonymous Coward

PCIe or RAM

Why can't SQL Server use normal memory? Just wondering.

0
0
Bronze badge

Re: PCIe or RAM

sounds like they can, but it can be pretty costly to install several hundreds of gigs or TBs of DRAM vs using Fusion IO..

0
0

Lot of nonsense being said on here

Not surprised the "I love my product" trolls are coming out.

Anyway, some facts around this: SQL Server has and has always had a buffer pool, data is read off storage into the buffer pool (in RAM); buffer pool extension is simply a way of tiering data access performance within the confines of the server box i.e. you can add an SSD (yes, doesn't need to be a PCI flash card - I can demo it on my laptop), works in standard edition of SQL Server as well.

The "in-memory" comes in two flavours - OLAP (column store indexing) and OLTP which is the new stuff implemented with hash and range indexing. SQL Server is playing catch up, however, unlike other it's built into the one product, so I can in one product, in one SQL statement join a normal storage based table with an in-memory table (e.g select count(*) from mynormaltable a inner join myinmemtable b on b.key = a.key. Also, the in-memory is not a separate database, it sits within the normal databases we have today but as a separate file group.

We aren't limited to "integer" keys, you can have an index on varchar columns with the new in-memory stuff - the only restriction it needs to be a specific collation.

With SQL Server 2014, I can go round with my ACER V5 laptop (come and see me present in Bristol next Wed :)) that cost £500 and demo buffer pool extensions and the in-memory stuff; that differs entirely from other vendors where their in-memory databases are entirely separate products with some only working on specialist (expensive) hardware.

0
0
This topic is closed for new posts.

Forums