I was just reading a post last week where a DBA wanted to know if he could force tempdb into memory somehow to alleviate some contention and speed up his system. Then a day or two later I found this note about Sybase releasing an in-memory version of their flagship database.They join Oracle and IBM, who have their own versions of in-memory databases, but Sybase has made their completely compatible with their other versions of Sybase ASE. No separate API calls are needed from an application using the in-memory version as opposed to the traditional version.
With those three large vendors having a database product that runs in-memory, is it time for Microsoft to add these capabilities to the SQL Server platform?
I think ther is some research in this area is taking place. In fact, I believe that part of the reason that Powerpivot works so quickly is that it's creating an in-memory database, a version of SSAS that can perform the calculations needed for analyze large amounts of data quickly. That's a great start, and if you haven't seen any of the PowerPivot demos, I'd urge you to check them out. They are some of the more impressive ones I've seen in a long time.
But what about the relational engine, the core of SQL Server? Should we get some in-memory features there? I think we should, but perhaps in a limited sense. One of the primary functions of a relational engine is to ensure the ACID properties, the last of which is durability. Once the transaction is committed, it has to stay there. I worry that so many people will write bad code, run their database in memory, and then wonder why they've lost committed transactions when the server fails. It's one thing to load up data in memory for analysis and go through what-if scenarios; If the server tails, you just reload the data. It's quite another when you are taking orders for products and lose a number of them.
I would, however, like to see tempdb slide into memory. That seems like the perfect place to start implementing this. After all, the data in tempdb is temporary by nature; it's not supposed to survive a restart. Just like memory.
The idea of getting better performance, just from slipping your database into RAM is intriguing, but I'm not sure that's the best way to improve performance. I'd argue training, time spent on resources like SQLServerCentral, and better code being written is the way to make your system run better.