揭秘SQL Server 2014有哪些新特性

发布网友 发布时间：2022-04-23 12:35

我来回答

共3个回答

懂视网 时间：2022-04-29 21:50

SQL Server 2014中的内存引擎(代号为Hekaton)将OLTP提升到了新的高度。

现在，存储引擎已整合进当前的数据库管理系统，而使用先进内存技术来支持大规模OLTP工作负载。

就算如此，要利用此新功能，数据库必须包含“内存优化”文件组和表

即所配置的文件组和表使用Hekaton技术。

幸运的是，SQL Server 2014使这一过程变得非常简单直接。

要说明其工作原理，我们来创建一个名为TestHekaton的数据库，然后添加一个内存优化文件组到此数据库

测试环境：Microsoft Azure 版 虚拟机

4核 ，7G内存，Windows2012R2

SQLSERVER2014企业版

实验

第一个实验：内存表的简单使用

步骤1：创建数据库和MEMORY_OPTIMIZED_DATA文件组

USE master;

GO

CREATE DATABASE TestHekaton;

GO

ALTER DATABASE TestHekaton

ADD FILEGROUP HekatonFG CONTAINS MEMORY_OPTIMIZED_DATA;

GO

注意ALTER DATABASE语句中的ADD FILEGROUP 语句包含文件组的名称(HekatonFG)和关键字CONTAINS MEMORY_OPTIMIZED_DATA

它会指导SQL Server去创建支持内存OLTP引擎所必需的文件组类型。

注意：每个数据库只能有一个MEMORY_OPTIMIZED_DATA文件组！！

要确认此文件组已经创建，可以访问SSMS中数据库属性的Filegroups 界面，如下图所示。

步骤2：

添加一个数据文件到文件组，可以通过ALTER DATABASE语句来实现。

添加一个新数据文件到HekatonFG文件组：

ALTER DATABASE TestHekaton

ADD FILE
(
 NAME = ‘HekatonFile‘,
 FILENAME =‘C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLDATAHekatonFile‘
)

TO FILEGROUP [HekatonFG];
GO

注意：在ADD FILE 语句中，我们只为文件路径和文件名提供了一个友好的名称。

并且，在TO FILEGROUP 语句中，为新文件组指定名称。

然后可以去往数据库属性的 Files 界面来查看刚刚添加的文件，如图所示。

步骤3：

在为数据库设置了必需的文件组和文件之后，就可以创建自己的内存优化表了。

当在定义表的时候，会指定其“持久性”。

一个内存优化表可以是持久的或非持久的。

（1）对于一个持久表是将数据存储在内存中，而且也保存在内存优化文件组中。

（2）对于一个非持久表，数据是仅存储在内存中的，所以，如果系统崩溃或重启，数据就会丢失。

在SQL Server 2014中默认用的是持久表，接下来我们来深入了解一下。

当定义一个持久内存优化表的时候，你还必须定义一个基于非聚集哈希索引的主键。

在一个哈希索引中，数据是通过一个内存散列表进行访问的，而非固定大小页。

哈希索引是在内存优化表中唯一支持的索引类型。

除了在表定义中定义主键外，还必须将表配置为内存优化的，如下CREATE TABLE 语句所示：

USE TestHekaton;
GO

CREATE TABLE Reseller
 (
 [ResellerID] INT NOT NULL
   PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1024),
 [ResellerName] NVARCHAR(50) NOT NULL ,
 [ResellerType] NVARCHAR(20) NOT NULL
 )
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

INSERT INTO Reseller
VALUES ( 1, ‘A Bike Store‘, ‘Value Added Reseller‘ );

ResellerID 字段定义包含了定义为非聚集哈希的主键。

注意，必须包含一个WITH 语句来指定BUCKET_COUNT 的设置，它表明了在哈希索引中应该创建的bucket数量。

(每个bucket是一个槽，可以用来存放一组键值对。)

微软建议bucket的数量应是一到两倍于你所期望的表所要包含的唯一索引键的数量。

此表定义以第二个WITH 语句结束。

这里你指定MEMORY_OPTIMIZED 选项为ON 以及DURABILITY 选项为SCHEMA_AND_DATA，此选项是针对持久表的。

接着在表中插入一条记录，这样就可以进行测试了。

数据已经插入到表中

这就是创建一个内存优化表的全部步骤，其他的一切都会发生在幕后。

但是，要记住，SQL Server 2014对这些表有着很多。例如，它们不支持外键或约束检查（感觉类似于MYSQL的memory存储引擎），

它们也不支持IDENTITY 字段或DML触发器。最为重要的是，内存耗尽会导致写活动停止。

步骤4：

另一方面，内存优化表支持本地编译存储过程，只要那些存储过程只引用内存优化表。

在这种情况下，存储过程可以转化为本地代码，这样会执行更快且要比典型存储过程需要更少的内存。

除了只引用内存优化表，一个本地编译存储过程必须是模式绑定的并运行在一个特定执行内容内。

另外，每个本地编译存储过程必须完全由一个原子块组成。

下面的CREATE PROCEDURE 语句定义了一个本地编译存储过程，它从前例中所创建的Reseller表中检索数据

CREATE PROCEDURE GetResellerType ( @id INT )
 WITH NATIVE_COMPILATION,
  SCHEMABINDING,
  EXECUTE AS OWNER
AS
 BEGIN
 ATOMIC WITH(TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = ‘us_english‘)
 SELECT ResellerName ,
  ResellerType
 FROM dbo.Reseller
 WHERE ResellerID = @id
 END;
GO

在定义了参数之后，包含一个WITH 语句来指定NATIVE_COMPILATION 选项。

注意：此语句还包含SCHEMABINDING 选项和EXECUTE AS 选项，以及指定了OWNER 作为执行环境。

而WITH 语句负责实现本地编译存储过程的三大需求。

要解决原子块需求，可以在BEGIN 关键字后指定ATOMIC ，之后是另一个包含有事务隔离级别和语言的WITH 语句。

对于访问内存优化表的事务，可以使用SNAPSHOT，REPEATABLEREAD 或SERIALIZABLE 作为隔离级。

而且，对于此语言必须使用一个可用的语言或语言别名。

这是在定义存储过程时所需要包含的全部内容。一旦创建，就可以通过执行EXECUTE 语句来对其加以测试，如下例中所示：

EXEC GetResellerType 1;

此语句会返回经销商的姓名和类型，在本例中分别是ABike Store和Value Added Reseller。

第一个实验：内存表的数据查询速度比较

聚集索引表和内存优化表的比较

建表语句

USE TestHekaton;
GO

--内存优化表
CREATE TABLE testmemory1
 (
 [ID] FLOAT NOT NULL
   PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1024),
 [Name] NVARCHAR(50) NOT NULL 
 )
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

USE TestHekaton;
GO

--聚集索引表
CREATE TABLE testmemory2
 (
 [ID] FLOAT NOT NULL PRIMARY KEY,
 [Name] NVARCHAR(50) NOT NULL 
 )

---------------------------------------------------------------

插入性能比较

内存优化表

SET STATISTICS IO ON 
SET STATISTICS TIME ON
INSERT into testmemory1([id],[name]) SELECT [id] ,[name] from sysobjects
SET STATISTICS IO OFF
SET STATISTICS TIME OFF

Table ‘sysschobjs‘. Scan count 1, logical reads 33, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
 SQL Server Execution Times:
 CPU time = 0 ms, elapsed time = 20 ms.
(90 row(s) affected)
 SQL Server Execution Times:
 CPU time = 0 ms, elapsed time = 0 ms.
 SQL Server Execution Times:
 CPU time = 0 ms, elapsed time = 0 ms.

聚集索引表

SET STATISTICS IO ON 
SET STATISTICS TIME ON
INSERT into testmemory2([id],[name]) SELECT [id] ,[name] from sysobjects
SET STATISTICS IO OFF
SET STATISTICS TIME OFF

Table ‘testmemory2‘. Scan count 0, logical reads 183, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘sysschobjs‘. Scan count 1, logical reads 33, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
 SQL Server Execution Times:
 CPU time = 0 ms, elapsed time = 10 ms.
(90 row(s) affected)
 SQL Server Execution Times:
 CPU time = 0 ms, elapsed time = 0 ms.

-------------------------------------------------------------------------------

查询性能比较

内存优化表

SET STATISTICS IO ON 
SET STATISTICS TIME ON
SELECT * FROM testmemory1 ORDER BY [ID] DESC
SET STATISTICS IO ON
SET STATISTICS TIME ON

SQL Server parse and compile time: 
 CPU time = 0 ms, elapsed time = 1 ms.
 SQL Server Execution Times:
 CPU time = 0 ms, elapsed time = 0 ms.
 SQL Server Execution Times:
 CPU time = 0 ms, elapsed time = 0 ms.
(90 row(s) affected)
 SQL Server Execution Times:
 CPU time = 0 ms, elapsed time = 0 ms.
 SQL Server Execution Times:
 CPU time = 0 ms, elapsed time = 0 ms.

聚集索引表

SET STATISTICS IO ON 
SET STATISTICS TIME ON
SELECT * FROM testmemory2 ORDER BY [ID] DESC
SET STATISTICS IO ON
SET STATISTICS TIME ON

(91 row(s) affected)
Table ‘testmemory2‘. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
 SQL Server Execution Times:
 CPU time = 0 ms, elapsed time = 0 ms.
 SQL Server Execution Times:
 CPU time = 0 ms, elapsed time = 0 ms.

可以看到内存优化表读写数据（insert 、select）的时候都看不到IO读写

补充测试：

我们先删除刚才插入的数据，内存优化表是不支持truncate table的，只能用delete from 表

只能够delete

插入测试

内存优化表

聚集索引表

 -------------------------------------------------------------------------------------------------

查询测试

内存优化表

聚集索引表

我们看一下事务日志

CHECKPOINT
GO

SELECT Context ,
Operation,
AllocUnitName
FROM sys.fn_dblog(NULL, NULL)

总结

内存优化表也会写事务日志的，在读写操作的时候发现内存优化表没有I/O次数，应该是数据都已经在内存里了

更多详细资料可以参考：

SQL Server 2014 新特性——内存数据库

SQL Server 2014新特性：分区索引和内存优化表

MSDN:内存优化表

SQLSERVER2014的内存优化表

标签：

热心网友 时间：2022-04-29 18:58

1、内存数据库

   在传统的数据库表中，由于磁盘的物理结构*，表和索引的结构为B-Tree，这就使得该类索引在大并发的OLTP环境中显得非常乏力，虽然有很多办法来解决这类问题，比如说乐观并发控制，应用程序缓存，分布式等。但成本依然会略高。而随着这些年硬件的发展，现在服务器拥有几百G内存并不罕见，此外由于NUMA架构的成熟，也消除了多CPU访问内存的瓶颈问题，因此内存数据库得以出现。

   内存的学名叫做RandomAccess Memory（RAM），因此如其特性一样，是随机访问的，因此对于内存，对应的数据结构也会是Hash-Index，而并发的隔离方式也对应的变成了MVCC，因此内存数据库可以在同样的硬件资源下，Handle更多的并发和请求，并且不会被锁阻塞，而SQLServer 2014集成了这个强大的功能，并不像Oracle的TimesTen需要额外付费，因此结合SSDAS Buffer Pool特性，所产生的效果将会非常值得期待。

SQLServer内存数据库的表现形式

   在SQL Server的Hekaton引擎由两部分组成：内存优化表和本地编译存储过程。虽然Hekaton集成进了关系数据库引擎，但访问他们的方法对于客户端是透明的，这也意味着从客户端应用程序的角度来看，并不会知道Hekaton引擎的存在。如图1所示。

图1.客户端APP不会感知Hekaton引擎的存在

   首先内存优化表完全不会再存在锁的概念（虽然之前的版本有快照隔离这个乐观并发控制的概念，但快照隔离仍然需要在修改数据的时候加锁），此外内存优化表Hash-Index结构使得随机读写的速度大大提高，另外内存优化表可以设置为非持久内存优化表，从而也就没有了日志（适合于ETL中间结果操作，但存在数据丢失的危险）

   下面我们来看创建一个内存优化表：

   首先，内存优化表需要数据库中存在一个特殊的文件组，以供存储内存优化表的CheckPoint文件，与传统的mdf或ldf文件不同的是，该文件组是一个目录而不是一个文件，因为CheckPoint文件只会附加，而不会修改，如图2所示。

图2.内存优化表所需的特殊文件组

我们再来看一下内存优化文件组的样子，如图3所示。

图3.内存优化文件组

   有了文件组之后，接下来我们创建一个内存优化表，如图4所示。

图4.创建内存优化表

  目前SSMS还不支持UI界面创建内存优化表，因此只能通过T-SQL来创建内存优化表，如图5所示。

图5.使用代码创建内存优化表

   当表创建好之后，就可以查询数据了，值得注意的是，查询内存优化表需要snapshot隔离等级或者hint，这个隔离等级与快照隔离是不同的，如图6所示。

图6.查询内存优化表需要加提示

   此外，由创建表的语句可以看出，目前SQLServer 2014内存优化表的HashIndex只支持固定的Bucket大小，不支持动态分配Bucket大小，因此这里需要注意。

与内存数据库不兼容的特性

   目前来说，数据库镜像和复制是无法与内存优化表兼容的，但AlwaysOn，日志传送，备份还原是完整支持。

性能测试

  上面扯了一堆理论，大家可能都看郁闷了。下面我来做一个简单的性能测试，来比对使用内存优化表+本地编译存储过程与传统的B-Tree表进行对比，B-Tree表如图7所示，内存优化表+本地编译存储过程如图8所示。

图7.传统的B-Tree表

图8.内存优化表+本地编译存储过程

  因此不难看出，内存优化表+本地编译存储过程有接近几十倍的性能提升。

热心网友 时间：2022-04-29 20:16

主要在BI和云计算两个方向有大的改动。

以下是转载：
SQL Server 2014中已经增加了对物理IO资源的控制，这个功能在私有云的数据库服务器上的作用体现得尤为重要，它能够为私有云用户提供有效的控制、分配，并隔离物理IO资源。下面来详细了解一下SQL Server 2014的新特性。
　　方法/步骤
　　内置内存技术：

　　集成内存OLTP技术，针对数据仓库而改善内存列存储技术;通过 Power Pivot实现内存BI等。美国一家*企业，通过内置存储技术，将每秒请求量从15000增加到250000，不仅大幅改善了用户体验，而且还获得了压倒对手的竞争力。
　　安全方面：

　　连续5年漏洞最少的数据库，市场占有率是46%，全球使用率极高。
　　扩展性方面：

　　计算扩展，高达0颗逻辑处理器，每虚拟机颗vCPU，没虚拟机1TB内存，没集群个节点;网络扩展：网络虚拟化技术提升灵活性与隔离;分配最小和最大带宽;有以及存储扩展都有很大提升。
　　BI：

　　企业可以通过熟悉的工具，如Office中的Excel以及Office 365中的Power BI，加速分析以快速获取突破性的洞察力，并提供基于移动设备的访问。
　　混合云方面：
　　跨越客户端和云端，Microsoft SQL Server 2014为企业提供了云备份以及云灾难恢复等混合云应用场景，无缝迁移关键数据至Microsoft Azure。企业可以通过一套熟悉的工具，跨越整个应用的生命周期，扩建、部署并管理混合云解决方案，实现企业内部系统与云端的自由切换。
　　与闪存卡搭配：

　　与LSI Nytro闪存卡相结合使用，则可满足云中最苛刻工作负载对性能的要求，消除企业I/O瓶颈，加速交易，充分挖掘数据价值，使客户受益。