SQLServer2014 memory optimization table (1) memory optimization table

The Memory optimization table (Memory-OptimizedTables) is a new feature of SQLServer2014. It is only applicable to EvaluationEdition, development edition, and Enterprise Edition ). This series of topics will discuss the memory optimization tables from the following five parts: (1) implementing the memory optimization table (2) Operations

Memory Optimization table (Memory-Optimized Tables) is a new feature of SQL Server 2014. It is only applicable to Evaluation Edition, Developer Edition, and Enterprise Edition ). This series of topics will discuss the memory optimization tables from the following five parts: (1) implementing the memory optimization table (2) Operations

Memory Optimization table (Memory-Optimized Tables) is a new feature of SQL Server 2014. It is only applicable to Evaluation Edition, Developer Edition, and Enterprise Edition ).

This series of topics will discuss the memory optimization table from the following five parts:

(1) memory optimization table

(2) operation memory optimization table

(3) Index Structure Analysis

(4) locally compiled stored procedures

(5) migrate to the memory optimization table

I. Overview

1. Disk table and Data Optimization table

Traditionally, Disk-Based Tables is stored on disks. You can perform the following operations on the data page of a table:

(1) When SQL Server needs to add, delete, modify, and query the table, read the required data pages from the disk and load them to the memory buffer.

(2) When the data page needs to be modified, it is first modified in the memory buffer, and the changes (transactions) are recorded in the transaction log file.

(3) When a Checkpoint is encountered, the modified data page in the memory buffer will be written back to the disk.

SQL Server 2014 introduces OLTP Data Optimization. The main feature is the introduction of a memory optimization table, which allows you to add, delete, modify, and query the table in the memory, thus improving the OLTP performance.

2. Types of memory optimized tables

Memory Optimization tables can be divided into the following two types:

(1) Persistent memory optimization table

You can use the "DURABILITY = SCHEMA_AND_DATA" parameter during creation to retain a copy (in FileStream mode) for "persistence" on the disk ). When the database is started, the structure and data of the entire table will be loaded from the disk to the memory again. During operations on such tables, data streams are written to the disk, and transaction logs are also written to the disk.

(2) Only structured memory optimization tables

When the "DURABILITY = SCHEMA_ONLY" parameter is used during creation, the data is only stored in the memory and there are no other copies. After the database is restarted, the structure of the table is rebuilt (an empty table), but the data in the table does not exist. In addition, such tables do not record transaction logs during operations. It can be used as a global temporary table or used to store intermediate data during ETL.

2. Prepare the database

1. Create a database

Prepare a database of SQL Server 2014 in advance, for example, "MOTDB ". To avoid the impact of transaction log files on performance, we put the log files on the second hard disk and changed the recovery mode to "simple ".

Create database [MOTDB] CONTAINMENT = NONE ON PRIMARY (NAME = N 'mdb', FILENAME = n'c: \ MSSQL \ Data \ MTODB. mdf ', SIZE = 102400KB, FILEGROWTH = 102400KB) LOG ON (NAME = n'mtodb _ log', FILENAME = n'd: \ MSSQL \ log \ MTODB_log.ldf ', SIZE = 51200KB, FILEGROWTH = 51200KB) GO Alter database [MOTDB] SET RECOVERY SIMPLE GO

2. Add memory optimization dataFile Group

Add a MEMORY_OPTIMIZED_DATA file group to the database to enable the memory-optimized data function. Each database can have only one memory-optimized data file group.

2.1 SSMS

650) this. width = 650; "title =" motfile group .png "alt =" wKiom1STzeHS8h9rAAJaQh4mVzM816.jpg "src =" http://www.68idc.cn/help/uploads/allimg/151209/11514952E-0.jpg "/>

2.2 T-SQL Mode

Alter database [MOTDB] Add filegroup [MOT_FileGroup] CONTAINS MEMORY_OPTIMIZED_DATA

3. Add a FileStream Data File

For a persistent memory optimization table, copies of the table are saved to the disk in the format of FileStream. Therefore, you must add a data file for FileStream.

3.1 SSMS

650) this. width = 650; "src =" http://www.68idc.cn/help/uploads/allimg/151209/1151494G7-1.jpg "title =" motfile .png "alt =" wKiom1SaOI2xLbBGAAJZAq0NRHc257.jpg "/>

650) this. width = 650; "src =" http://www.68idc.cn/help/uploads/allimg/151209/11514a032-2.jpg "title =" motfile 2.png "alt =" wKioL1SaOUaDrVdSAAGP9Nvb-qk360.jpg "/>

3.2 T-SQL Mode

Alter database [MOTDB] Add file (NAME = n'mot _ file', FILENAME = n'c: \ MSSQL \ Data \ MOT_File ') To filegroup [MOT_FileGroup]

III,ImplementationMemory Optimization table

1. Create a "persistent" memory optimization table

You can only create a memory-optimized table using a T-SQL, for example:

Create table [dbo]. [Table_SchemaData] ( [UserID] [int] not null primary key nonclustered hash with (BUCKET_COUNT = 204800 ), [UserName] [varchar] (10) COLLATE SQL _Latin1_General_CP1_CI_AS NOT NULL, [AddressLine1] [nvarchar] (20) NULL, [AddressLine2] [nchar] (3000) NULL, ) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)

The T-SQL statement must contain the following three clauses:

(1) "hash with (BUCKET_COUNT = 204800)" indicates that the number of HASH buckets is 204800. It is recommended that the number of HASH buckets be twice the total number of rows in the memory optimization table. Currently, SQL Server does not support dynamic Hash buckets. Therefore, you must manually set this value.

(2) "MEMORY_OPTIMIZED = ON" indicates that the table is a memory optimization table.

(3) "DURABILITY = SCHEMA_AND_DATA" specifies that the memory optimization table retains a copy on the hard disk.

Note: After a memory optimization table is created, the size of the FileStream folder increases from several hundred KB to 153 MB.

2. Create a memory optimization table with "only structure"

Create table [dbo]. [Table_SchemaOnly] ( [UserID] [int] not null primary key nonclustered hash with (BUCKET_COUNT = 204800 ), [UserName] [varchar] (10) COLLATE SQL _Latin1_General_CP1_CI_AS NOT NULL, [AddressLine1] [nvarchar] (20) NULL, [AddressLine2] [nchar] (3000) NULL, ) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY)

Note: The "only structured" memory optimization table does not require FileStream. In this case, the size of the FileStream folder remains unchanged.

Iv. Main technical restrictions on memory optimization tables

1. sorting rules

The sorting rules of memory optimized tables can be inherited from the database sorting rules, or explicitly specified using the COLLATE keyword. If the database contains a memory-optimized table or a locally compiled stored procedure, you cannot change the database sorting rules.

The sorting rule must be a 1252 code page, such as SQL _Latin1_General_CP1_CI_AS. Otherwise, an error is returned.

Message 12329, level 16, status 103, 1st rows The data types char (n) and varchar (n) of the code page of the sorting rules not supported by the memory optimization table are not 1252 ).

As a work und, you can use the data types nchar (n) and nvarchar (n ).

2. Data row width

Each row of data cannot exceed 1 page (8 KB ). Otherwise, an error is returned.

Message 41307, level 16, state 1, 1st rows The size of 8060-byte rows in a memory-optimized table is exceeded. Simplify the table definition.

3. Index

Non-clustered hash indexes are the only index types supported by memory optimized tables. In a hash index, data is accessed through a memory hash, rather than a fixed page size. (Further description)

5. Check the memory optimization table.

1. view the startup log

After the database is restarted, the following events are recorded in the startup Log (for example, C: \ Progra... \ MSSQL12.MSSQLSERVER \ MSSQL \ Log \ ERRORLOG file.

18:18:27. 16Spid24s Recovery of database 'motdb' (9) is 2% complete (approximately 288 seconds remain). Phase 1 of 3. This is an informational message only. No user action is required. 18:18:32. 10Spid8s Recovery completed for database MOTDB (database ID 9) in 13 second (s) (analysis 8539 ms, redo 0 ms, undo 4832 ms .) this is an informational message only. no user action is required. 18:18:32. 10Spid24s [INFO] HkCheckpointCtxtImpl: StartOfflineCkpt (): Database ID: [9]. Starting offline checkpoint worker thread on a hidden SOS scheduler. 18:18:32. 12Spid8s Recovery is complete. This is an informational message only. No user action is required.

2. View FileStream data files

The FileStream data file is actually a folder.

650) this. width = 650; "title =" filestreamfolder .png "alt =" wKiom1SZT9-w4LdKAAGwhQoY1qg428.jpg "src =" http://www.68idc.cn/help/uploads/allimg/151209/11514aH5-3.jpg "/>