本文属于Azure SQL DB/DW系列
上一文:Azure SQL DB/DW 系列(4)——Query Store案例(1)——缺失索引
本文演示如何使用Query Store来找到计划回归并处理
环境准备
本文以自建数据库作为演示,使用SQL Server 2019的兼容级别(150)。
--创建数据库
USE MASTER
GO
CREATE DATABASE QS --For Query Store
GO
USE QS
GO
--启用Query Store
ALTER DATABASE QS SET QUERY_STORE = ON
GO
--配置Query Store
ALTER DATABASE QS SET QUERY_STORE
(
OPERATION_MODE = READ_WRITE,
CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 367),
DATA_FLUSH_INTERVAL_SECONDS = 900,
INTERVAL_LENGTH_MINUTES = 1,
MAX_STORAGE_SIZE_MB = 100,
QUERY_CAPTURE_MODE = ALL,
SIZE_BASED_CLEANUP_MODE = OFF
)
GO
--创建一个简单的表,并创建一个非聚集索引,然后插入10万行数据
CREATE TABLE Customers
(
CustomerID INT NOT NULL PRIMARY KEY CLUSTERED,
CustomerName CHAR(10) NOT NULL,
CustomerAddress CHAR(10) NOT NULL,
Comments CHAR(5) NOT NULL,
Value INT NOT NULL
)
GO
--创建索引
CREATE UNIQUE NONCLUSTERED INDEX idx_Test ON Customers(Value)
GO
-- 插入10万数据
DECLARE @i INT = 1
WHILE (@i <= 100000)
BEGIN
INSERT INTO Customers VALUES
(
@i,
CAST(@i AS CHAR(10)),
CAST(@i AS CHAR(10)),
CAST(@i AS CHAR(5)),
@i
)
SET @i += 1
END
GO
为了生成计划回归,创建一个存储过程来实现:
--创建一个存储过程来访问
CREATE PROCEDURE QueryCustomers
(
@Value INT
)
AS
BEGIN
SELECT * FROM Customers
WHERE Value < @Value
END
GO
计划回归
计划回归(Plan Regression)指特定的查询的执行计划被改变了。比如某个时间SQL Server缓存了一个合理的执行计划,但是过了一段时间之后生成了一个不合理的执行计划,并缓存了,同时还被重用,表现出来就是某个功能之前运行的挺好,突然间就很慢。
这种现象一直都存在,但是很难捕获,毕竟很多时候你只能获取当前的执行计划或者最近缓存的执行计划。从SQL 2016引入Query Store之后,这个问题就可以得到解决,最起码可以缓解。它可以找到是否有计划回归导致了你的系统出现性能问题,一旦你找到了存在计划回归,也可以很简单地,透明地使用强制特定计划来解决。
引出问题
首先使用100000作为参数并打开实际执行计划,调用存储过程:
SET STATISTICS IO ON
EXEC QueryCustomers 100000
go
执行计划如上所示,出现了聚集索引扫描,产生了523个逻辑读,由于数据量的巨大到达了临界点,SQL Server并没有选择使用非聚集索引。
然后我们假设现在SQL Server出现了一些问题,比如重启或者failover,这会导致服务器缓存的计划丢失。这里使用“DBCC FREEPROCCACHE”来模拟重启。
DBCC FREEPROCCACHE
GO
重启之后,有人调用这个存储过程,但是这次使用了1作为参数,大家可以想象得到,这次应该会有非聚集索引的参与:
SET STATISTICS IO ON
EXEC QueryCustomers 1
go
执行计划如上,确实使用了索引查找(索引查找就是非聚集索引查找),同时搭配了Key Lookup,因为非聚集索引没有覆盖所有列而存储过程中定义了SELECT *,所以需要聚集索引来辅助。不过由于需要查找的数据量很小,所以这个问题不大。
接下来再次执行:
SET STATISTICS IO ON
EXEC QueryCustomers 100000
go
可以看到逻辑读变成了 200174, 而之前只是 523,如果在生产环境中出现,绝对是紧急事件。然后如果没有Query Store你得花很多时间去找问题,不过现在可以使用Query Store来发现这个问题。
同一个存储过程,由于参数得不同执行计划也不同,同时因为缓存导致计划被重用到不合适的场景,这个也叫参数嗅探,也叫计划回归。下面来看看怎么用Query Store解决。
使用Query Store
我们打开SSMS中的Query Store界面,首先我们打开【Top Resource Consuming Queries,资源消耗量最多的几个查询】查看最耗资源的查询,在【Metri,指标】那里我们选择逻辑读。
你可以看到有几个查询,其中第一名的是刚才构造环境时用的循环10万次插入,这里说明即使服务器被重启,Query Store还是可以捕获到,不像执行计划那样内存缓存机制。
现在看第二名,这次是我们刚才的查询了。下图展示的是计划 ID 28的执行计划,右上方可以kan’daokandao 有两个计划ID,分别是因为参数不同导致的两个查询,三个点代表了三个不同的计划,我们可以看到橙色的应该就是参数是10万的,但是因为计划回归导致执行计划的变动。
鼠标分别移到计划ID为28的两个点中,可以看到下面的两个图,注意各种逻辑读的值之间的差异。
如果把鼠标移到计划ID25处,注意计划ID并非固定。可以看到执行计划完全不同,而且逻辑读也不同。
虽然ID 28的下面那个逻辑读远小于其他两个,但是从平均来看,计划ID25的更加适合大部分情况,所以我们用ID25来强制替代这个语句的所有执行计划。
可以看到打了一个勾。现在一起来执行下面的命令看看执行计划:
此时已经强制了使用聚集索引扫描作为所有参数的执行方式。而且I/O都是一样的:
现在看上去是解决问题了,但是我个人认为,完善索引,表设计和语句写法才是最终解决问题的办法,这个仅仅是“强制执行计划”而已,如果没有任何一个执行计划可以覆盖所有场景,那这个功能是没有起到效用的。