Azure SQL性能调优实践

注：blog具备时效性，所有内容都是在当前时间为准，截止时间2017-6-5

首先，创建了一个测试数据库 MaxAadventureWorkTest 使用了 示例数据库 AdventureWorksLT

在Azure SQL性能调优的过程中，最方便的是使用SQL的新功能叫“查询存储”来进行调优。查询存储在SQL 2016的新功能，Azure SQL 的V12版本支持。什么是查询存储呢？

查询存储是SQL Server 2016推出的新功能。查询存储功能让你可以探查查询计划选项和性能。 它可帮助你快速找到查询计划更改所造成的性能差异，从而简化了性能疑难解答。 查询存储将自动捕获查询、计划和运行时统计信息的历史记录，并保留它们以供查阅。 它按时间窗口将数据分割开来，使你可以查看数据库使用模式并了解服务器上何时发生了查询计划更改。默认在数据库创建时候此功能没有开放，需要运行如下脚本来进行启用。关于SQL Server 2016 怎么使用，另外再做介绍。

ALTER DATABASE databasename SET QUERY_STORE = ON;  

Azure SQL 提供了一个功能叫Query Performance Insight 帮助进行数据库性能的管理和优化，此功能前提条件是数据库需要运行查询存储。

在Azure SQL创建好后，会默认开启这个功能。如果没有开启，会提醒你开启此功能。 Query Performance Insight主要帮助实现以下功能：

• 深入洞察数据库资源 (DTU) 的消耗。
• CPU 消耗排名靠前的查询经过优化后可以提升性能。
• 向下钻取查询详细信息的能力。

查询存储打开后可以通过数据库的属性查看到相关参数：

在SSMS数据库选项中也会有查询存储选项：

由于是新建数据库没有什么性能负载，先执行一些语句来制造一些负载,首先反复的执行了下面的语句，插入了几十万条数据。

INSERT INTO [SalesLT].[SalesOrderDetail]
           ([SalesOrderID]
           ,[OrderQty]
           ,[ProductID]
           ,[UnitPrice]
           ,[UnitPriceDiscount]
           ,[rowguid]
           ,[ModifiedDate])

       (select SalesOrderID,OrderQty
           ,ProductID
           ,UnitPrice
           ,UnitPriceDiscount
           , newid()
           ,ModifiedDate  from [SalesLT].[SalesOrderDetail])

再执行了一些“不合常理”的查询，目的就是产生一些负载，比如：

select *from [SalesLT].[SalesOrderDetail] a ,[SalesLT].[SalesOrderHeader] b,[SalesLT].[Product] c
where a.[SalesOrderID]=b.[SalesOrderID] and c.[ProductID]=a.[ProductID]

declare @n int
select @n=1
while @n<100
begin 
select * from sys.tables 
select * from [SalesLT].[Customer]
select * from [SalesLT].[ProductModel]
select * from [SalesLT].[ProductDescription]
select * from [SalesLT].[Product]
select * from [SalesLT].[ProductModelProductDescription]
select * from [SalesLT].[ProductCategory]
select * from [BuildVersion]
select * from [ErrorLog]
select * from [SalesLT].[Address]
select * from [SalesLT].[CustomerAddress]
select * from [SalesLT].[SalesOrderDetail]
select * from [SalesLT].[SalesOrderHeader]

select *from [SalesLT].[SalesOrderDetail] a ,[SalesLT].[SalesOrderHeader] b,[SalesLT].[Product] c
where a.[SalesOrderID]=b.[SalesOrderID] and c.[ProductID]=a.[ProductID]


select @n=@n+1
end

执行了1个小时左右，现在去看数据库，首先点到概述，可以看到资源利用率的整体情况：

点击性能概述，点击了放大，把统计放到了一个小时左右的区间，目前由于时间不够长，所以没有调优的建议：

点击 Query Performance Insight 显示了TOP 5 的查询情况，同样把查询区间放到一个小时。

按照CPU 的视图：

按照数据IO的视图：

按照日志IO的视图：

视图的下方 看到查询ID ，可以点击ID看更详细情况，比如查看ID 167的情况如下：

可以看到此语句是：

INSERT INTO [SalesLT].[SalesOrderDetail]
           ([SalesOrderID]
           ,[OrderQty]
           ,[ProductID]
           ,[UnitPrice]
           ,[UnitPriceDiscount]
           ,[rowguid]
           ,[ModifiedDate])

       (select SalesOrderID,OrderQty
           ,ProductID
           ,UnitPrice
           ,UnitPriceDiscount
           , newid()
           ,ModifiedDate  from [SalesLT].[SalesOrderDetail])

在12:30左右大量小号了CPU 和日志IO资源。通过这样的方法可以很轻松的获得数据库消耗性能的查询，然后抓取后进行调优，调优可以使用实时查询统计信息

比如：

实时查询统计信息打开的效果是这样的：

实时查询统计信息是动态的，会提供查询过程中的整个执行过程，很清楚反应数据流向、资源消耗等问题。

并且会给出建议，如上图，本次查询就有缺少索引的建议。

所以在Azure SQL调优过程中，可以使用Query Performance Insight获取到相关有性能问题的查询，然后使用实时查询统计信息，进行分析，从而解决性能问题。

如果还要进一步来进行调优，我们还有更好的利器，那就是上面提到的查询存储。

点击数据库->MaxAadventureWorkTest->查询存储：

有几个选项：

• 回归查询
• 总体资源使用
• 前几个资源使用查询
• 带有强制计划的查询
• 具有高度差异的查询
• 跟踪的查询

回归查询

回归查询可以查看历史的默认前25个查询情况，可以自己进行相应配置。如图，在统计中ID118是cpu总计最大的。在下面可以看到执行的查询的查询计划，并且看到到对此查询做出的相关建议。点击相应的查询语句可以得到不同的语句执行情况。

指标可以按照。CPU、持续时间、逻辑读取写入、内衬占用率等进行查询

也可以按照总计、平均值、最大、最小、标准偏差、总计

在右上角有个配置，点开后可以看到更多的配置。

总体资源使用

总体资源使用统计如下：

同样的相关配置可以进行更多设置。这里就不详细阐述使用。点击柱形图，可以进入到查询的相关信息。按照上面的方法进行查看和分析

前几个资源使用查询

带有强制计划的查询

由于我这没有强制执行的查询计划，这个报表是空的。

具有高度差异的查询

跟踪的查询

跟踪查询，可以输入相应的id号 ，单独跟踪相应的查询。也可以看到相应的查询语句，这是进行重点调优的报表！

总结：

Azure SQL 提供的Query Performance Insight，再加上SQL的查询存储功能，能够方便图形化的提供性能评估参考。轻松的进行调优工作。

作者简介： Max Shen（阿特） 微软解决方案专家, 系统运维工程师，数据库工程师；IT从业20年经验，最早接触和进行云计算工作的工程师之一，在云计算领域具有丰富的工作经验。熟悉Windows 、SQL Server数据库、虚拟化等技术，拥有多年企业IT运维和数据库运维管理实战经验；熟悉微软云架构、企业云服务解决方案，拥有微软Azure、O365等相关认证。