In the next few sections, we shall discuss In-Memory OLTP fundamentals, architecture, concepts, terminology, hardware and software requirements, and some myths about how it is implemented in SQL Server 2014.
Before thinking about how to use In-Memory OLTP, it is important to understand the underlying architecture. In-Memory OLTP is built on four pillars. The pillars were developed in the context of industry hardware and business trends to offer customer benefits. FIGURE 2.3 summarizes the four pillars and its associated customer benefits.
As a result of the steady decline in the price of memory and the rapid rate at which the size of memory was growing, putting tables in main memory became feasible, thereby replacing the need to place and access tables on disk. With this change, came a significant reduction in the time required to gain access to tables, because the pages were no longer required to be read into the cache from disk. New functionality, such as hash indexes and non-clustered range indexes, can exploit data that is in physical memory rather than on disk, which allows for faster access and higher performance in data operations.
Natively compiled stored procedures are Transact-SQL stored procedures compiled to native code that access memory-optimized tables. Natively compiled stored procedures allow for efficient execution of the queries and business logic in the stored procedure.
Natively compiled stored procedures do not implement the full Transact-SQL programmability and query surface area. There are certain Transact-SQL constructs that cannot be used inside natively compiled stored procedures.
The SQL Server product group recognized that if it could reduce the number of instructions needed to execute the same logic, it could do the same work at a lower processing time. The product group implemented this idea of transforming SQL stored procedures to a C program and then compiling the program into a DLL by using the Visual C compiler. The resulting machine code replaced stored procedures and the usual interpretation of logic through query execution. This made it possible to run a stored procedure by using fewer instructions, leading to more efficient business-logic processing that was significantly faster. With the optimization in processing time, internal testing at the lower level of the storage engine verified that, machine code could reduce the instruction count by 30 to 50 times, which resulted in a proportional increase in throughput and in lower latency.
Related Page: Using T-SQL With SSIS
Here is a partial list of some of the other TSQL constructs that cannot be used in a natively compiled stored procedure:
SQL Server has scaled extremely well because of the performance and scalability improvements made on it over the past releases. Unfortunately, certain application patterns— for example, a last page insert in the clustering key order or concurrent updates of hot pages—still suffered latch contention and did not scale as well as a result.
When many people attempt to modify data in a database at the same time, a system of controls must be implemented so that modifications made by one person do not adversely affect those of another person. This is called concurrency control.
The concurrency control theory has two classifications for the methods of instituting concurrency control:
The additional improvements implemented through In-Memory OLTP in SQL Server 2014 allows for higher concurrency. First, the product group did away with page structures for accessing memory-optimized tables. This means that no paging or latching occurs to create bottlenecks. Second, the core engine uses lock-free algorithm structures that are based on multiversion optimistic concurrency control with full ACID (atomic, consistent, isolated, and durable) support. These improvements remove common scalability bottlenecks and provide high concurrency and frictionless scale-up opportunities to increase overall performance when memory–optimized tables are used.
Related Page: How To Create SSIS Package In SQL Server 2014
Microsoft Integration Services is a platform for building enterprise-level data integration and data transformation solutions. You use Integration Services to solve complex business problems by copying or downloading files, sending e-mail messages in response to events, updating data warehouses, cleaning and mining data, and managing SQL Server objects and data. The packages can work alone or in concert with other packages to address complex business needs. Integration Services can extract and transform data from a wide variety of sources such as XML data files, flat files, and relational data sources, and then load the data into one or more destinations.
Integration Services includes a rich set of built-in tasks and transformations; tools for constructing packages; and the Integration Services service for running and managing packages.
Microsoft says that SQL Server Integration Services (SSIS) “is a platform for building high-performance data integration solutions, including extraction, transformation, and load (ETL) packages for data warehousing.”
Here, the SQL Server product group decided that In-Memory OLTP should be easy to consume and that performance-critical tables should take advantage of this feature. What evolved from this idea is an In-Memory OLTP engine that is fully integrated into the SQL Server Database Engine and managed with a familiar set of tools. People who are familiar with SQL Server can quickly make use of the benefits of In-Memory OLTP because the management, administration, and development experiences are the same. Moreover, In-Memory OLTP works seamlessly with other features, such as Always On Availability Groups, Always On Failover Cluster Instances, replication, backups, and restores.
Frequently Asked SQL Server Interview Questions & Answers
The following section reviews In-Memory OLTP concepts and terminology:
This is the traditional way through which SQL Server has stored data since the product’s inception. Data in a table is stored in 8-KB pages and read and written to a disk. Each table also had its own data and index pages.
Memory-optimized tables are the alternative to traditional disk-based tables and follows the new structures associated with In-Memory OLTP. The primary store for memory-optimized tables is the main memory, but the second copy in a different format is maintained on disk for durability purposes.
Native compilation: Native compilation allows the tables and the stored procedures accessing them to be loaded in the memory. The tables and stored procedures loaded on the memory tends to be faster than the regular tables and stored procedures. The output of native compilation is a DLL. These dlls are loaded into the SQL Server Process. This dll creation process happens during the creation of memory-optimized tables and memory optimized stored procedures. So there is no additional step for creating the dlls.
This will be handled during the memory optimized object creation. To achieve faster data access and efficient query execution, SQL Server natively compiles stored procedures that access memory-optimized tables into native DLLs. When stored procedures are natively compiled, the need for additional compilation and interpretation is reduced. Also, compilation provides additional performance enhancements, as compared with using memory-optimized tables alone.
In this process, interpreted Transact-SQL batches and stored procedures are used instead of a natively compiled stored procedure when accessing data in a memory-optimized table. Interop is used to simplify application migration.
Cross-container transactions are either implicit or explicit user transactions that include calls to natively-compiled stored procedures or operations on memory-optimized tables. In SQL Server, calls to stored procedures do not initiate a transaction. Executions of natively compiled procedures in auto-commit mode (not in the context of a user transaction) are not considered cross-container transactions. Any interpreted query that references memory-optimized tables is considered a part of a cross-container transaction, whether executed from an explicit or implicit transaction or in auto-commit mode. This is a hybrid approach in which transactions use both memory-optimized tables and disk-Based tables.
By default, memory-optimized tables are completely durable and offer full ACID support. Note that memory-optimized tables that are not durable are still supported by SQL Server, but the contents of a table exist only in the memory and are lost when the server restarts. The syntax DURABILITY=SCHEMA_ONLY is used to create nondurable tables.
Unified experience for organizations has been created in every area—including but not limited to, deployment and support—through the tight integration of In-Memory OLTP with SQL Server 2014. However, before you try this new capability, you should become acquainted with the requirements for using memory-optimized tables. In addition to the general hardware and software requirements for installing SQL Server 2014, (“SQL Server 2014 editions and engine enhancements”), there are also the requirements for using memory-optimized tables:
Many use cases show the benefits of In-Memory OLTP. Consider these scenarios:
Before moving on to the next section and walking through some In-Memory OLTP examples, it’s useful to rectify some of the misconceptions surrounding In-Memory OLTP.
|SSAS||SQL Server DBA|
|Team Foundation Server||BizTalk Server Administrator|
Stay updated with our newsletter, packed with Tutorials, Interview Questions, How-to's, Tips & Tricks, Latest Trends & Updates, and more ➤ Straight to your inbox!
|SQL Server Training||Jun 03 to Jun 18|
|SQL Server Training||Jun 06 to Jun 21|
|SQL Server Training||Jun 10 to Jun 25|
|SQL Server Training||Jun 13 to Jun 28|
Arogyalokesh is a Technical Content Writer and manages content creation on various IT platforms at Mindmajix. He is dedicated to creating useful and engaging content on Salesforce, Blockchain, Docker, SQL Server, Tangle, Jira, and few other technologies. Get in touch with him on LinkedIn and Twitter.
Copyright © 2013 - 2023 MindMajix Technologies