SQL SERVER – In-Memory Databases and Caching Wars

The In-Memory Database and In-Memory Caching market is heating up and we (the users) are the real winners. Exciting!!!

  • SAP, the giant application vendor, but a database newbie, is pushing to the market its SAP HANA, a standalone in-memory database (in an appliance box).
  • Last year IBM released a set of acceleration technologies, code-named BLU (a development code name that stood for “big data, lightning fast, ultra-easy”), adding in-memory capabilities to DB2 and better integration of solidDB caching engine with DB2.
  • This April (2014) Microsoft released its SQL Server 2014 with the new In-Memory OLTP engine embedded inside. Adding an In-Memory data engine right inside the most used database engine, the SQL Server, opens up many great possibilities for us, the engineers, building new applications and software solutions.
  • This month Oracle CEO Larry unveiled Oracle In-Memory database engine to be available next month. This comes as a direct fight to IBM, Microsoft and SAP in the burgeoning in-memory database market with technologies that can deliver dramatic performance boosts, high-scale online transactions or complex data processing.

The new in-memory database engines enable users to build new apps with a new scale of speed and processing capabilities. Hurrah!!

But the new in-memory database engines come with a price: Long list of data limitations, syntax limitations, use-case limitations, migration complications etc.

Another fast growing approach is adding a Smart Data Caching layer, where SafePeak (www.SafePeak.com) appears to be the visionary leader. SafePeak’s automated caching solution for SQL Server based applications enables easy deployment of a smart caching layer between the application and the database. SafePeak performs automated dynamic caching SQL queries and stored procedures results in-memory, covering immediately all queries of the application. Its key features that make it an interesting solution to explore when performance is discussed are:

1) Automation and ease of use;
2) No code changes;
3) Cache awareness for any transactions and data changes;
4) Wide range of caching options and cache eviction strategies.

The SQL Analytics engine embedded in SafePeak is an excellent bonus, working all the time and collecting information about every query. It’s a bit like the well-known SQL Profiler, but without the performance drop and with conversion of the raw not-really-readable data into advanced analytics.

When it comes to applicability to existing production applications, SafePeak’s strategy is to be easy to use for already-in-production operational read-write (but read-intensive) applications, and to be optimized for virtual and cloud environments. For in-production systems, this gives SafePeak an interesting advantage over the in-memory technologies of database giants.

 

Any way to look at – these in-memory developments are exciting for us
Comparing main in-memory options for the SQL Server applications, a question I think worth exploring is this:

When would SQL Server 2014 In-Memory OLTP fit your needs better than SafePeak’s Caching, and vice versa?

The answer depends of course on the user needs and the application-database state (Greenfield, Production, or Commercial off the shelf application).

SafePeak caching focuses on “READs”, especially REPEATITIVE READS, therefore it fits well for classic multi-user applications.

If your need is mostly about speeding up the WRITES or COMPLEX PROCESSING long-running queries, then having  a full table (or tables) In-Memory (as in SQL Server 2014 IN-Memory OLTP) sounds a better option.

However, the answer of applicability depending on the state of the application/database is very interesting too. I think it splits between 3 use cases:

1) In “greenfield” applications in the development cycle / process
The SQL Server 2014 In-Memory OLTP is a great option to explore. Having the knowledge how the 2014 In-Memory Tables and Stored Procedures works and their various limitations – gives the development managers tools to plan and developer the new application based on available features. In such cases several few important tables (and procs accessing them) can become in-memory. The rest will be usual.

2) In live production homegrown applications needing to speed-up performance without major redesign
SQL Server 2014 In-Memory OTLP may be a complex option to apply, due to its many current limitations of the solution and the requirement to redesign and reprogram parts of your application. And then there is the migration process… For this reason, SafePeak Cache may be a better choice.

3) In commercial 3rd-party / off-the-shelf applications
Unfortunately, SQL Server 2014 In-Memory OTLP requires new code and design changes, so in most cases, the user cannot apply the new in-memory feature. SafePeak works without any need to touch the application, so SafePeak wins here.

Here’s a quick comparison of advantages and disadvantages of Microsoft’s In-Memory OLTP option with SafePeak’s caching:

1) SQL 2014 works very well for in write intensive processes and large data processing. SafePeak main gain is on repetitive-reads (queries and procs).

2) SQL 2014 has your full table in memory. Every query will be fast, even the first time it is called.
SafePeak has a module called Cache Warm-up, enabling to “pre-fetch data into cache”.

3) SQL 2014 In-Memory OLTP is fully embedded inside the SQL Server, so no extra layer is needed.

SafePeak’s advantages:

1) SafePeak #1 advantage is time to deployment. No code changes, takes only a few hours to deploy and configure;

2) SafePeak immediately covers 100% of the application. Not only tables designed to be in-memory;

3) No SQL syntax limitations. All tables, all existing code, any SQL syntax, any stored procedure can be cached. Most automatically, rest with easy configuration;

Bottom-line, only a customer can say what technology will best fit his/her situation. But definitely the In-Memory Competition will continue to benefit the industry and mostly us – the DBA’s, programmers and application managers!

Interested to trial SQL Server 2014 In-Memory OTLP?

Download SQL Server 2014 from the Microsoft site.
And see my post “Introduction and Quick-start guide to SQL Server 2014 In-Memory OLTP

Interested to trial SafePeak?

SafePeak is available for free trial: www.SafePeak.com/Download
The guys there love helping users with free training and setup assistance, allowing users to get results quicker and better – and allowing the company to learn real customers’ needs and challenges (if you ask me, the right way to build a product and a company).

What In-Memory category fits you? Let us know. 

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

5 thoughts on “SQL SERVER – In-Memory Databases and Caching Wars

  1. Very useful Pinal. I’m not currently in a position with a project to put your advice here to the test, but I’m sure I’ll come back to this when the time comes. You are correct that these are exciting times and this opens up possibilities. I remember 6 or so years ago really wishing SQL Server had something like this and using a MemCacheD caching layer with NHibernate for a solution that probably would have worked better with either of these.

  2. Hi Pinal,

    “Another fast growing approach is adding a Smart Data Caching layer, where SafePeak …appears to be the visionary leader. SafePeak’s automated caching solution …enables easy deployment of a smart caching layer between the application and the database. SafePeak performs automated dynamic caching SQL queries and stored procedures results in-memory, covering immediately all queries of the application.”

    Did you know that DB2 on the IBM i (AS/400, S/38) has been doing this for over three decades?

  3. Pinal,
    Given the proliferation of flash storage solutions such as fusion I/o or Pure Storage, where would you have use case of in memory db versus putting everything on flash storage and calling it a day?
    Jimmy

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s