转载

在SQL Server 2016里使用查询存储进行性能调优

作为一个DBA，排除SQL Server问题是我们的职责之一，每个月都有很多人给我们带来各种不能解释却要解决的性能问题。

我就多次听到，以前的SQL Server的性能问题都还好且在正常范围内，但现在一切已经改变，SQL Server开始糟糕，疯狂的事情不能解释。在这个情况下我介入，分析下整个SQL Server的安装，最后用一些神奇的调查方法找出性能问题的根源。

但很多时候问题的根源是一样的：所谓的 计划回归（Plan Regression） ，即特定查询的执行计划已经改变。昨天SQL Server已经缓存了在计划缓存里缓存了一个好的执行计划，今天就生成、缓存最后重用了一个糟糕的执行计划——不断重复。

进入SQL Server 2016后，我就变得有点多余了，以为微软引进了 查询存储（Query Store） 。这是这个版本最热门的功能！查询存储帮助你很容易找出你的性能问题是不是计划回归造成的。如果你找到了计划回归，这很容易强制一个特定计划不使用计划向导。听起来很有意思？让我们通过一个特定的场景，向你展示下在SQL Server 2016里，如何使用查询存储来找出并最终修正计划回归。

查询存储（Query Store）——我的对手

在SQL Server 2016里，在你使用查询存储功能前，你要对这个数据库启用它。这是通过ALTER DATABASE语句实现，如你所见的下列代码：

  1 CREATE DATABASE QueryStoreDemo  2 GO  3   4 USE QueryStoreDemo  5 GO  6   7 -- Enable the Query Store for our database  8 ALTER DATABASE QueryStoreDemo  9 SET QUERY_STORE = ON 10 GO 11  12 -- Configure the Query Store 13 ALTER DATABASE QueryStoreDemo SET QUERY_STORE 14 ( 15     OPERATION_MODE = READ_WRITE,  16     CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 367),  17     DATA_FLUSH_INTERVAL_SECONDS = 900,  18     INTERVAL_LENGTH_MINUTES = 1,  19     MAX_STORAGE_SIZE_MB = 100,  20     QUERY_CAPTURE_MODE = ALL,  21     SIZE_BASED_CLEANUP_MODE = OFF 22 ) 23 GO

在线帮助为你提供了各个选项的详细信息。接下来我创建一个简单的表，创建一个非聚集索引，最后插入80000条记录。

  1 -- Create a new table  2 CREATE TABLE Customers  3 (  4     CustomerID INT NOT NULL PRIMARY KEY CLUSTERED,  5     CustomerName CHAR(10) NOT NULL,  6     CustomerAddress CHAR(10) NOT NULL,  7     Comments CHAR(5) NOT NULL,  8     Value INT NOT NULL  9 ) 10 GO 11  12 -- Create a supporting new Non-Clustered Index. 13 CREATE UNIQUE NONCLUSTERED INDEX idx_Test ON Customers(Value) 14 GO 15  16 -- Insert 80000 records 17 DECLARE @i INT = 1 18 WHILE (@i <= 80000) 19 BEGIN 20     INSERT INTO Customers VALUES 21     ( 22         @i, 23         CAST(@i AS CHAR(10)), 24         CAST(@i AS CHAR(10)), 25         CAST(@i AS CHAR(5)), 26         @i 27     ) 28      29     SET @i += 1 30 END 31 GO

为了访问我们的表，我额创建了一个简单的存储过程，传入value值作为过滤谓语。

  1 -- Create a simple stored procedure to retrieve the data  2 CREATE PROCEDURE RetrieveCustomers  3 (  4     @Value INT  5 )  6 AS  7 BEGIN  8     SELECT * FROM Customers  9     WHERE Value < @Value 10 END 11 GO

现在我用80000的参数值来执行存储过程。

 1 -- Execute the stored procedure. 2 -- This generates an execution plan with a Key Lookup (Clustered). 3 EXEC RetrieveCustomers 80000 4 GO

现在当你查看实际的执行计划时，你会看到查询优化器已经选择了有419个逻辑读的聚集索引扫描运算符。SQL Server并没有使用非聚集索引，因为这样没有意义，由于临界点。这个查询结果并没有选择性。

在SQL Server 2016里使用查询存储进行性能调优

现在假设SQL Server发生了些事情（例如重启，故障转移），SQL Server忽略已经缓存的计划，这里我通过执行 DBCC FREEPROCCACHE 从计划缓存里抹掉每个缓存的计划来模拟SQL Server重启（不要在生产环境里使用！）。

 1 -- Get rid of the cached execution plan... 2 DBCC FREEPROCCACHE 3 GO

现在有人再次调用你的存储过程，这次输入参数值是1。这次执行计划不一样，因为现在在执行计划里你会有书签查找。SQL Server估计行数是1，在非聚集索引里没有找到任何行。因此与非聚集索引查找结合的书签查找才有意义，因为这个查询是有选择性的。

在SQL Server 2016里使用查询存储进行性能调优

现在我再执行用80000参数值的查询。

 1 -- Execute the stored procedure 2 EXEC RetrieveCustomers 1 3 GO 4  5 -- Execute the stored procedure again 6 -- This introduces now a plan regression, because now we get a Clustered Index Scan 7 -- instead of the Key Lookup (Clustered). 8 EXEC RetrieveCustomers 80000 9 GO

当你再次看 STATISTICS IO 的输出，你会看到这个查询现在产生了160139个逻辑读——刚才的查询只有419个逻辑读。这个时候DBA的手机就会响起，性能问题。但今天我们要不同的方式解决——使用刚才启用的查询存储。

当你再次看实际的执行计划，在你面前你会看到有一个计划回归，因为SQL Server刚重用了书签查找的的计划缓存。刚才你有聚集索引扫描运算符的执行计划。这是SQL Server里参数嗅探的副作用。

让我们通过查询存储来详细了解这个问题。在SSMS里的对象资源管理器里，SQL Server 2016提供了一个新的结点叫查询存储，这里你会看到一些报表。

在SQL Server 2016里使用查询存储进行性能调优

【前几个资源使用查询】向你展示了最昂贵的查询，基于你选择的维度。这里切换到【逻辑读取次数】。

在SQL Server 2016里使用查询存储进行性能调优

这里在你面前有一些查询。最昂贵的查询生成了近500000个逻辑读。这是我们的初始语句。这已经是第一个WOW效果的的查询存储：SQL Server重启后，查询存储的数据还是存在的！第2个是你存储过程里的SELECT语句。在查询存储里每个捕获的查询都有一个标示号——这里是7。最后当你看报告的右边，你会看这个查询的不同执行计划。

在SQL Server 2016里使用查询存储进行性能调优