Microsoft–Gartner数据库管理系统魔力象限领导者.pdf

Microsoft – Gartner数据库管理系统魔力象限领导者

Microsoft在愿景和执行力两项评

比中位列第一.

Gartner报告在微软优势描述中特别提到客户

对SQL Server的性能，文档帮助，安装运维能

力给予微软的高度评价。报告显示，微软不仅

在非结构化数据，混合数据云平台，移动支持

和混合事务分析处理（HTAP）方面处于市场

领先地位，而且在企业级关键任务的平台上具

有竞争优势. { }

•INSERT / BULK INSERT •UPDATE •DELETE •MERGE DML •SELECT * FROM temporal Querying

•FOR SYSTEM_TIME •AS OF •FROM..TO •BETWEEN..AND •CONTAINED IN

Temporal Querying •CREATE temporal TABLE PERIOD FOR SYSTEM_TIME… •ALTER regular_table TABLE ADD PERIOD… DDL 新的语法

不需改动开发模型

* Old versions

Insert / Bulk Insert Update */ Delete *

正常查询(当前数据) * 包括历史版本

时态表查询 *

(time travel, etc.)

SELECT *FROM Department

FOR SYSTEM_TIME

AS OF '2010.01.01'Azure SQL Database

数据库出

问题了 网站没响

应 无法发现真正原因 性能突然下降 新系统的Bug

数据库升级

找到并修复regressions问题

定位消耗资源最多的查询

排除SQL Server 升级的风险

深度分析负载的

状况

Query Store

Check for Recompile Compile and Optimize Query Fetch Plan from Cache Plan Cache lookup

Execute Query

Finish Execution

Query Execution

Found Not found

Not needed

Recompile

Send text and plan Get forced plan

启用 Query

Store (ALTER DB)

让 Query

Store 收集书

数据

搜索有问题的

的查询

使用 FORCE

PLAN 策略

安装SQL Server •保持原有兼容级别运行Query

Store

(create a

baseline)

升到SQL

Server 2016的

兼容级别

用force plan

来修复

regressions

问题

SQL Server 2016

查询优化器的提升和数据库 compatibility level （兼容级别）相一致的

[ { "Number":"SO43659", "Date":"2011-05-31T00:00:00" "AccountNumber":"AW29825", "Price":59.99, "Quantity":1 }, { "Number":"SO43661", "Date":"2011-06-01T00:00:00“ "AccountNumber":"AW73565“, "Price":24.99, "Quantity":3 } ] Number

Date Customer Price Quantity SO43659

2011-05-31T00:00:00 AW29825 59.99 1 SO43661 2011-06-01T00:00:00 AW73565 24.99 3

SELECT * FROM myTable FOR JSON AUTO

SELECT * FROM OPENJSON (@json)

CREATE TABLE SalesOrderRecord(

Id int PRIMARY KEY IDENTITY,

OrderNumber NVARCHAR(25)NOT NULL,

OrderDate DATETIME NOT NULL,

JSalesOrderDetails NVARCHAR(4000)

CONSTRAINT SalesOrderDetails_IS_JSON

CHECK ( ISJSON(JSalesOrderDetails)>0 ),

AS

CAST(JSON_VALUE(JSalesOrderDetails, ‘$.Order.Qty’) AS int)，AS JSON_VALUE('$.Order.Price'),

)

GO

CREATE INDEX idxJson

ON SalesOrderRecord(Quantity)

INCLUDE (Price);

SELECT t.Id, t.OrderNumber, t.OrderDate,

JSON_VALUE(t.JSalesOrderDetails,'$.Order.ShipDate') AS ShipDate

FROM SalesOrderRecord AS t

WHERE ISJSON(t.JSalesOrderDetails)> 0

AND t.Price > 1000