When it comes to achieving fast application response times, high performance, and database scalability, developers and IT teams have traditionally relied on basic code tuning and hardware upgrades. However, these approaches have their limitations. Code rewrites and database redesigns can be time-consuming and introduce bugs, while upgrading hardware can be expensive and lead to increased licensing costs.

Fortunately, in-memory solutions have emerged as a popular alternative for improving application performance and meeting SLA goals. By storing, accessing, and maintaining data in-memory instead of on disks, these solutions greatly enhance application performance and scalability.

There are several flavors of in-memory technology available, each with its own advantages and use cases. One popular approach is to use API/code-based data grids and distributed caching solutions. These solutions enable complex parallel processing and event processing, making them ideal for building highly scalable, high-performance applications like large social networks and real-time stock-trading platforms. However, they require significant code development effort and may not be feasible for most production databases and applications.

Another approach is in-memory dynamic caching, which is well-suited for accelerating live in-production applications and databases. Unlike data grids and distributed caching solutions, in-memory dynamic caching does not require additional code in applications or modifications to databases. This makes it much easier to deploy and can accelerate third-party applications that use SQL Server, such as SharePoint or Dynamics.

Microsoft’s SQL Server 2014 introduced the In-Memory OLTP engine, which revolutionized database operations by leveraging in-memory transaction processing. This engine uses a new lock-free and latch-free design, resulting in faster and more scalable performance compared to traditional disk-based tables. By storing tables in memory and compiling stored procedures to native code, SQL Server 2014 In-Memory OLTP achieves excellent performance for DML write commands and significant benefits for read commands as well.

To start using SQL Server 2014 In-Memory OLTP, a database must have certain tables declared as memory-optimized. These tables can be used as conventional database tables or substitutes for temporary tables. Additional speed improvements are achieved by keeping a table in memory, as reads and writes only lock individual rows and row-level locks are handled in a way that doesn’t cause concurrency issues.

While SQL Server 2014 In-Memory OLTP offers impressive performance improvements, it does have limitations. Replication, mirroring, and database snapshot are not supported, and there is a 250GB limit per server for total data in all loaded in-memory tables. Additionally, certain T-SQL operators and functionalities are not supported, and there are restrictions on schema, keys, indexes, triggers, and data types.

For those looking for an alternative or complementary solution to SQL Server 2014 In-Memory OLTP, in-memory dynamic caching offers a compelling option. SafePeak, a leading vendor in this category, provides query-level automated caching middleware for SQL Server-based operational applications. SafePeak’s solution combines result-based data caching, in-memory databases, and a software-only approach that requires no code changes or specialized hardware.

SafePeak’s in-memory dynamic caching accelerates the performance of applications and databases by caching results from repetitive SQL queries and stored procedures. It maintains 100% ACID compliance and data integrity while significantly reducing I/O, CPU, and memory usage. With its smart auto-learning capabilities, SafePeak automatically analyzes queries and stored procedures, identifies logic and dependent objects, and evolves with the application.

Comparing SafePeak’s in-memory dynamic caching with SQL Server 2014 In-Memory OLTP, it becomes clear that they serve different purposes. SafePeak is a better fit for general cases of production and custom applications, especially where there are significantly more reads than writes. It also accelerates third-party applications like SharePoint and Dynamics. On the other hand, SQL Server 2014 In-Memory OLTP is focused on specific tables and procedures, providing high-speed performance for intensive write-and-read operations.

In conclusion, in-memory data cache solutions like SQL Server 2014 In-Memory OLTP and SafePeak’s in-memory dynamic caching offer powerful tools for improving performance and scalability. While SQL Server 2014 In-Memory OLTP is ideal for specific use cases and requires careful planning and migration, SafePeak’s solution provides a more flexible and easy-to-deploy option for accelerating a wide range of applications and databases.