SQL Server supports two forms of tables: disk-based tables and memory-optimized tables. Disk-based tables are stored on pages, and are read from and written to disk. These are traditional tables that the Database Engine has always supported.
Memory-optimized tables are stored completely in memory. This means that pages do not need to be read from disk when these tables are accessed, because the content of such tables is always stored in memory. (As you will see shortly, these tables do not even use pages as storage units.) Two other properties of these tables also improve performance significantly. First, delays caused by waiting for locks to be released are eliminated by the use of multiversion optimistic concurrency control. Second, the amount of data written to the transaction log is reduced to a minimum, using new algorithms.
Before we explore memory-optimized tables further, we’ll review the similar feature supported in previous versions of SQL Server, pinned tables.
1. Pinned Tables as Predecessor of Memory-Optimized Tables
Earlier versions of SQL Server allow users to “pin” certain database objects into memory.
This means that the system prevents such objects from being removed from the buffer cache, allowing fast access to the pages that must be read from the disk into memory. The objects that could be pinned are user-defined tables and indices.
The DBCC PINTABLE command is used to pin a database object. For instance, if a table is pinned, the DBCC command does not load pages from the table into memory. Instead, all tables’ pages that are read into memory stay there and cannot be flushed by the Database Engine when it needs memory for new pages. However, if a pinned page is modified, the Database Engine logs the updated page and, if necessary, writes the modified page to disk.
As you can see from the description of pinned tables, these tables are just a special type of disk-based tables, and therefore require the same amount of locks and logging as any other table. As will be discussed later in the chapter, memory-optimized tables have a very different design and use different data storage structures. Also, no locking is used on memory-optimized tables.
2. Creating a Memory-Optimized Filegroup
To create memory-optimized tables, you must first create a memory-optimized filegroup. This filegroup stores, among other things, the checkpoint files that are used by the Database Engine in case of recovery, and these files have a different form than traditional checkpoint files. The memory-optimized filegroup holds one or more containers. Each container contains data files or delta files, or both. (Delta files will be explained in detail later in this chapter.) Example 21.1 shows creation of the new database called sample2 with the sample2_fg memory-optimized filegroup.
Example 21.1
CREATE DATABASE sample2
ON PRIMARY
(NAME = sample2_data,
FILENAME = ‘C:\temp\sample2_data.mdf’, size=500MB),
FILEGROUP sample2_fg CONTAINS MEMORY_OPTIMIZED_DATA
(NAME = sample2_dir,
FILENAME = ‘C:\temp\sample2_dir’)
LOG ON
(NAME = sample2_log,
FILENAME=’C:\temp\sample2_log.ldf’, size=500MB);
The CREATE DATABASE statement in Example 21.1 first creates a new database called sample2 and, as a part of that database, the sample2_fg filegroup, which is memory-optimized. Note that you can create only one memory-optimized filegroup per database. As can be seen in the example, such a filegroup must be specified with the CONTAINS MEMORY_ OPTIMIZED_DATA clause.
It is possible to add a memory-optimized filegroup to an existing database, as shown in Example 21.2.
NOTE While attempting to add a memory-optimized filegroup to the sample database, you might get the following error message: “A database cannot be enabled for both Change Data Capture (CDC) and MEMORY_OPTIMIZED_DATA storage.” In that case, disable CDC before you continue.
Example 21.2
ALTER DATABASE sample
ADD FILEGROUP sample_fg CONTAINS MEMORY_OPTIMIZED_DATA;
GO
ALTER DATABASE sample
ADD FILE (NAME = sample_dir, FILENAME = ‘C:\temp\sample_file’)
TO FILEGROUP sample_fg;
Adding a new memory-optimized filegroup to an existing database in Example 21.2 is accomplished in two steps. In the first step, the ALTER DATABASE statement is used to add a filegroup to the database. In the second step, a new file is added to the memory-optimized filegroup. (If you do not have the directory C:\temp on your computer, please change the path to point to the directory where the file called sample_file will be stored.)
3. Creating Memory-Optimized Tables
Next, Example 21.3 creates a new memory-optimized table that will be stored in the sample2_fg filegroup.
Example 21.3
USE sample2;
CREATE TABLE employee_mem
( emp_no int not null, emp_lname CHAR(20) NOT NULL , emp_fname CHAR(20) NOT NULL, dept_no CHAR(4) NOT NULL,
PRIMARY KEY NONCLUSTERED HASH (emp_no)
WITH (BUCKET_COUNT = 1000))
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
The employee_mem table is similar to the employee table of the sample database that we have used up to this point in the book. The only difference is that the content of the new table will be stored in memory instead of on disk.
The CREATE TABLE statement has been extended with several clauses to allow the creation of memory-optimized tables. We will start with the final clause, DURABILITY. This clause specifies which part of a table is memory-optimized. There are two possible options for this clause:
- SCHEMA_AND_DATA
- SCHEMA_ONLY
The SCHEMA_AND_DATA option is the default value and indicates that the content of a memory-optimized table is persistently stored in memory. In other words, this option provides durability of both schema and data. If SCHEMA_ONLY is specified, the content of the table is volatile. This means that the table’s schema is persistently stored, but any data updates are not durable upon a restart of the database with memory-optimized objects.
The MEMORY_OPTIMIZED clause indicates whether the table is memory-optimized (the value ON) or nonoptimized (the value OFF). The SCHEMA_ONLY option of the DURABILITY clause is not allowed with MEMORY_OPTIMIZED = OFF.
Memory-optimized tables have a few limitations. First, several data types, such as all LOB data types and XML and MAX data types, cannot be used to specify any column of a memory-optimized table. Also, the row length is limited to 8060 bytes.
All other options of the CREATE TABLE statement will be explained in “Index Storage” within the next section.
The ALTER TABLE statement can be used for the following tasks: changing the bucket count; adding and removing an index; changing, adding; and removing a column; and adding and removing a constraint.
Source: Petkovic Dusan (2020), Microsoft SQL Server 2019: A Beginner’s Guide, Seventh Edition-McGraw-Hill Education.