Heim >Datenbank >MySQL-Tutorial >SQLServer2014内存优化表(1)实现内存优化表

SQLServer2014内存优化表(1)实现内存优化表

WBOY
WBOYOriginal
2016-06-07 14:53:161311Durchsuche

内存优化表(Memory-Optimized Tables)是SQL Server 2014的新特性,目前仅适用于评估版(Evaluation Edition)、开发版(Developer Edition)和企业版(Enterprise Edition)。 本系列专题将从以下 5 个部分探讨内存优化表: (1)实现内存优化表 (2)操作

  内存优化表(Memory-Optimized Tables)是SQL Server 2014的新特性,目前仅适用于评估版(Evaluation Edition)、开发版(Developer Edition)和企业版(Enterprise Edition)。

  本系列专题将从以下 5 个部分探讨内存优化表:

(1)实现内存优化表

(2)操作内存优化表

(3)索引结构分析

(4)本机编译存储过程

(5)迁移到内存优化表


一、概述

1. 磁盘表与数据优化表

  传统意义上的磁盘表(Disk-Based Tables)是保存在磁盘上的。针对表的数据页(page),主要有以下操作:

(1)当SQL Server需要对这个表进行增删改查的时候,从磁盘读取需要的数据页并加载到内存缓冲区。

(2)当数据页需要被修改时,首先在内存缓冲区中修改,同时修改的情况(事务)被记录到事务日志文件。

(3)当遇到检查点(Checkpoint)时,内存缓冲区中被修改过的数据页将回写到磁盘。


  SQL Server 2014引入了OLTP数据优化,主要特色是引入了内存优化表,在内存中实现对该表的增删改查操作,从而提高OLTP的性能。


2. 内存优化表的类型

  内存优化表可以分为以下2种类型:

(1)持久化的内存优化表

  在创建时使用“DURABILITY = SCHEMA_AND_DATA”参数,可以在磁盘上保留了一个用于“持久化”的副本(FileStream方式)。在数据库启动时,整个表的结构和数据都将再次从磁盘装载到内存中。这类表在操作时会有数据流写入磁盘,同时也有事务日志写入磁盘。


(2)仅结构的内存优化表

  在创建时使用“DURABILITY = SCHEMA_ONLY”参数,那么数据将只保留在内存中,没有其它副本。当数据库重启后,该表的结构被重建(一张空表),但表中的数据都已经不存在了。而且这类表在操作时没有记录事务日志。可以用作全局临时表,或者ETL时用于存储中间数据。



二、准备数据库

1. 创建一个数据库

  事先准备好一个SQL Server 2014的数据库,例如,“MOTDB”。为了避免事务日志文件对性能的影响,我们将日志文件放在第二块硬盘,并且将恢复模式修改为“简单”。

CREATE DATABASE [MOTDB]

 CONTAINMENT = NONE

 ON  PRIMARY 

( NAME = N'MTODB', 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. 添加内存优化数据文件组

  为这个数据库添加一个内存优化数据(MEMORY_OPTIMIZED_DATA)文件组,从而启用了内存优化数据的功能。每个数据库只能有一个内存优化数据文件组。


2.1 SSMS方式

wKiom1STzeHS8h9rAAJaQh4mVzM816.jpg


2.2 T-SQL方式

ALTER DATABASE [MOTDB] 

ADD FILEGROUP [MOT_FileGroup] 

CONTAINS MEMORY_OPTIMIZED_DATA 



3. 添加FileStream数据文件

  对于“持久化”的内存优化表,表的副本将以FileStream的格式保存到磁盘,因此需要为FileStream添加一个数据文件。


3.1 SSMS方式

wKiom1SaOI2xLbBGAAJZAq0NRHc257.jpg

wKioL1SaOUaDrVdSAAGP9Nvb-qk360.jpg


3.2 T-SQL方式

ALTER DATABASE [MOTDB] 

ADD FILE ( NAME = N'MOT_File', FILENAME = N'C:\MSSQL\Data\MOT_File' ) 

TO FILEGROUP [MOT_FileGroup]



三、实现内存优化表

1. 创建“持久化”内存优化表

  只能使用 T-SQL 创建内存优化表,例如:

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 )

  T-SQL语句必须包含以下3个子句:

(1)“HASH WITH (BUCKET_COUNT = 204800)”指定 HASH 存储桶的数目为204800。建议 HASH 存储桶的数量为整个内存优化表的总行数的两倍。目前SQL Server不支持动态的Hash Bucket,因此必须手动设置该值。

(2)“MEMORY_OPTIMIZED = ON”指定表为内存优化表。

(3)“DURABILITY = SCHEMA_AND_DATA”指定内存优化表同时在硬盘上保留一个副本。

 

注:创建内存优化表之后,FileStream 文件夹的大小从数百 KB 增长到 153MB。


 

2. 创建“仅结构”的内存优化表

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 )

 

注:“仅结构”的内存优化表不需要 FileStream,此时 FileStream 文件夹的大小基本不变。

 


四、内存优化表的主要技术限制

1. 排序规则

  内存优化表的排序规则可以从数据库的排序规则继承下来,也可使用 COLLATE 关键字显式指定。 如果数据库包含内存优化表或本机编译存储过程,则无法更改数据库排序规则。

  排序规则必须是1252代码页,例如 SQL_Latin1_General_CP1_CI_AS。否则报错。

消息 12329,级别 16,状态 103,第 1 行

内存优化表 不支持使用的排序规则所具有的代码页并非 1252 的数据类型 char(n) 和 varchar(n)。

  作为一种变通的方案,可以使用数据类型 nchar(n) 和 nvarchar(n) 。


2. 数据行的宽度

  每一行数据不能超过1个页(8KB)。否则报错。

消息 41307,级别 16,状态 1,第 1 行

已超过内存优化的表的 8060 字节行大小限制。请简化表定义。


3. 索引

  非聚集哈希索引是内存优化表唯一支持的索引类型。在一个哈希索引中,数据是通过一个内存散列表进行访问的,而非固定大小页。(后文详叙)



五、确认内存优化表

1. 查看启动日志

  重启数据库之后,启动日志(例如:C:\Progra...\MSSQL12.MSSQLSERVER\MSSQL\Log\ERRORLOG 文件)会记录以下事件。

2014-12-23 18:18:27.16 spid24s     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.

2014-12-23 18:18:32.10 spid8s      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.

2014-12-23 18:18:32.10 spid24s     [INFO] HkCheckpointCtxtImpl::StartOfflineCkpt(): Database ID: [9]. Starting offline checkpoint worker thread on a hidden SOS scheduler.

2014-12-23 18:18:32.12 spid8s      Recovery is complete. This is an informational message only. No user action is required.


2. 查看 FileStream 数据文件

  FileStream 数据文件实际上是一个文件夹。

wKiom1SZT9-w4LdKAAGwhQoY1qg428.jpg



Stellungnahme:
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn