SQL Server2019智能查询和性能优化简介

简介

SQL Server2019 主要的新特性可以用官方的一张图说明：

SQL Server Intelligent Performance

2019 版本主要在几方面提供查询性能优化：

Intelligent Query Processing智能查询处理
Lightweight Query Profiling轻量级查询分析
Sequential Key Insert Performance顺序键插入性能优化
In-Memory Database
- Hybrid Buffer Pool混合缓冲池
- Memory Optimized Tempdb Metadata内存优化的 Tempdb 元数据
- Persistent Memory Support持久化内存支持

Intelligent Query Processing

使用智能查询处理，用户可以不改动应用层代码，只需将 T-SQL 跑在兼容等级 150 的 MSSQL 数据库上(这意味着想要充分使用这个特性，只能使用 2019 版本和云上 Azure, 2017 有部分功能)。MSSQL 的兼容等级见下表:

Intelligent Query Processing主要包含如下特性：

几个优化技术简介：

Adaptive QP 自适应查询处理， 150 会使用这个技术自适应地选择 join,交错执行Interleaved execution,内存授予反馈Memory Grant Feedback.
Table Variable Deferred Compilation表变量延迟编译，这个技术会传输表的实际行数估计到后续的执行计划，用于优化执行计划和整体性能。
Batch Mode on RowStore批量模式
Scalar UDF Inlining
Approximate QP

修改数据库的兼容等级：alter database tobereplaceddb set compatibility_level=150

Query Store 性能分析

首先开启查询存储SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE), 使用 SSMS 从左边栏目可以看到资源消耗最大的几个查询：

运行如下 SQL, 从右边的查询存储分析一下不同兼容等级的查询性能和执行计划。

USE WideWorldImporters
GO
CREATE or ALTER PROCEDURE [Sales].[CustomerProfits]
AS
BEGIN
-- Declare the table variable
DECLARE @ilines TABLE
(       [InvoiceLineID] [int] NOT NULL primary key,
        [InvoiceID] [int] NOT NULL,
        [StockItemID] [int] NOT NULL,
        [Description] [nvarchar](100) NOT NULL,
        [PackageTypeID] [int] NOT NULL,
        [Quantity] [int] NOT NULL,
        [UnitPrice] [decimal](18, 2) NULL,
        [TaxRate] [decimal](18, 3) NOT NULL,
        [TaxAmount] [decimal](18, 2) NOT NULL,
        [LineProfit] [decimal](18, 2) NOT NULL,
        [ExtendedPrice] [decimal](18, 2) NOT NULL,
        [LastEditedBy] [int] NOT NULL,
        [LastEditedWhen] [datetime2](7) NOT NULL
)

-- Insert all the rows from InvoiceLines into the table variable
INSERT INTO @ilines SELECT * FROM Sales.InvoiceLines

-- Find my total profile by customer
SELECT TOP 1 COUNT(i.CustomerID) as customer_count, SUM(il.LineProfit) as total_profit
FROM Sales.Invoices i
INNER JOIN @ilines il
ON i.InvoiceID = il.InvoiceID
GROUP By i.CustomerID
END
GO

-- Pull these pages into cache to make the comparison fair based on a warm buffer pool cache
SELECT COUNT(*) FROM Sales.Invoices
GO

-- Step 2: Run the stored procedure under dbcompat = 130
USE master
GO
ALTER DATABASE wideworldimporters SET compatibility_level = 130
GO
USE WideWorldImporters
GO
SET NOCOUNT ON
GO
EXEC [Sales].[CustomerProfits]
GO 25
SET NOCOUNT OFF
GO

-- Step 3: Run the same code under dbcompat = 150
USE master
GO
ALTER DATABASE wideworldimporters SET compatibility_level = 150
GO
USE WideWorldImporters
GO
SET NOCOUNT ON
GO
EXEC [Sales].[CustomerProfits]
GO 25
SET NOCOUNT OFF
GO

-- Step 4: Restore dbcompat for WideWorldImporters
USE master
GO
ALTER DATABASE wideworldimporters SET compatibility_level = 130
GO

![](/images/2021-02/query_store_130.png) ![](/images/2021-02/store_query_150.png)

我们可以看到上面第一张图是 130 兼容性使用nested loop join, 虽然只用了一行 join, 但是性能不如 150 兼容性的Adaptive join自适应选择 join 方式（此例用了 hash join+clustered index scan); 同时用了Table Variable Deferred Compilation和Batch mode for rowstore技术。

Tempdb

Tempdb系统数据库可以作为共享资源用于临时表和临时表变量。由于是共享的就存在竞争的问题，访问和修改 GAM(Global Allocation Map), SGAM(Shared Global Allocation Map)和 PFS(Page Free Space)可能存在竞争问题。
存在系统表的临时表元信息也存在相同问题。MSSQL 采用page latch页锁的方式来物理上保护并发临时表的 page 访问, 但是有锁就意味着可能有等待。
MSSQL2019 采用 tempdb 有如下优化：

多文件 partition 先在物理上减轻 page 访问压力，每个文件会以相同的增量同步增大
临时表和临时变量会变缓存
allocation page锁协议改善
减少 tempdb 的日志文件 IO 开销
tempdb 中所有的 allocation 使用同一的 extents
对于主要fileground, AUTOGROW_ALL_FILES属性不能被修改，默认打开

开启内存优化的tempdb

SELECT SERVERPROPERTY('IsTempdbMetadataMemoryOptimized')
ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;