I’ve been seeing interest in the In Memory Database feature released in SQL Server 2014. Using a database maintained only in memory, available in SQL Server Enterprise edition for production systems, allows you optimized performance by only storing transaction logs to disk.
By storing transaction logs to disk, in memory database data may be re-constituted when the database is closed and later re-opened. I haven’t seen any performance metrics for the startup of an in-memory database of any size. That would be an interesting test.
The in memory tables technology differs from the Pin Table technique released in older versions of SQL Server. Using Pin Table you are simply altering the control of data cache. Your data is still written to disk in Pin Table using the Lazy Writer. When SQL Server requires memory, SQL Server will not drop data from pinned tables in the same way as other tables.
In Memory Databases, in contrast, have their own locking mechanisms. Stored Procedures are compiled to windows code. It has its own indexing and searching techniques. Blob data types are not supported, nor can you use triggers.
Don’t consider an in-memory-database to solve poorly designed solutions. It is a better fit, as I have observed, for writing applications requiring very fast processing of transactional data. With the lower cost of RAM today an in memory database may be something for you to consider. This will out-perform a traditional database running on SSD with a large amount of memory do to the optimized locking techniques.
Are you using In Memory Databases yet? Share your experience in our comments.
Cheers,
Ben