Azure SQL Database (26) 使用Query Store对Azure SQL Database监控

　　《Windows Azure Platform 系列文章目录》

 

　　我们在使用Azure SQL Database的时候，需要对数据库的性能进行监控，这时候就可以有两种方法：

　　1.第一种方法，是通过Azure SQL Database的监控界面，来查看数据库的性能，在本章会简单的介绍一下

　　2.第二种方法，是通过Query Store来进行监控，在本章会详细介绍

 

　　首先，我们介绍一下使用Azure SQL Database的监控界面。

　　1.我们登录Azure Portal: https://portal.azure.cn/

　　2.查看到我们使用的Azure SQL Database，选择概述，然后点击下图红色部分

　　

　　

　　3.页面跳转后，我们可以在下图的Last Hour，设置监控的时间段

　　在Add Metric里面，增加新的监控指标，比如CPU Percentage, Data IO Percentage等

　　

 

　　4.我们还可以在性能概述里面，查看到微软云Azure对我们当前数据的优化建议

　　

 

 

　　接下来，我们详细介绍一下使用Query Store来进行监控，实际上我们在上面看到的通过Azure Portal的可视化监控，其实也是通过Query Store来进行监控的。

　　Query Store是SQL Server 2016里面新的功能，同时在微软云Azure平台上，也提供了该功能

　　Query Store是从内存中读取数据，并异步写入到Azure SQL Database的磁盘上的

　　

　　

　　这里我们假设一个场景，如果Azure SQL Databse的DTU利用率很高，我们如何查询出具体是哪些语句，占用了过多的资源呢？

　　1.首先，我们通过Azure Portal，查看到问题发生的时间，如下图在9月2日的凌晨开始，发生了该问题

　　我们点击下图的红色部分

　　

 

　　2.DTU和CPU Time，DataIO都有关。我们点击下图的Add Metric

　　

 

　　3.DTU是和CPU Time，Data IO叠加的因素，我们可以看到下面的CPU Time和DataIO都很高，

　　8点以后都是DATA IO

　　

 

　　4.我们在本地PC上安装SQL Server Management Studio，访问上面的数据库，并且找到Query Store

　　我们点击下图的Top Resource Consuming Queries

　　

 

　　5.点击上图右上角的Config，设置查询时间

　　

 

　　6.在弹出的窗口中，选择查询时间，我们也可以使用默认的

　　

 

　　7.我们查询CPU Time，Static 选择Avg。可以查看到缺少索引

　　

 

　　8.在下图，我们右键Miss Index，设置索引

　　

 

　　9.如果我们需要查询所有缺少索引的表结构，可以在SSMS执行下面的语句

--Search Missing Index Directly
SELECT
    SUM(qrs.count_executions) * AVG(qrs.avg_logical_io_reads) as est_logical_reads,
    SUM(qrs.count_executions) AS sum_executions,
    AVG(qrs.avg_logical_io_reads) AS avg_avg_logical_io_reads,
    SUM(qsq.count_compiles) AS sum_compiles,
    (SELECT TOP 1 qsqt.query_sql_text FROM sys.query_store_query_text qsqt
        WHERE qsqt.query_text_id = MAX(qsq.query_text_id)) AS query_text,    
    TRY_CONVERT(XML, (SELECT TOP 1 qsp2.query_plan from sys.query_store_plan qsp2
        WHERE qsp2.query_id=qsq.query_id
        ORDER BY qsp2.plan_id DESC)) AS query_plan,
    qsq.query_id,
    qsq.query_hash
FROM sys.query_store_query qsq
JOIN sys.query_store_plan qsp on qsq.query_id=qsp.query_id
CROSS APPLY (SELECT TRY_CONVERT(XML, qsp.query_plan) AS query_plan_xml) AS qpx
JOIN sys.query_store_runtime_stats qrs on qsp.plan_id = qrs.plan_id
JOIN sys.query_store_runtime_stats_interval qsrsi on qrs.runtime_stats_interval_id=qsrsi.runtime_stats_interval_id
WHERE    
    qsp.query_plan like N'%<MissingIndexes>%'
    and qsrsi.start_time >= DATEADD(HH, -24, SYSDATETIME())
GROUP BY qsq.query_id, qsq.query_hash
ORDER BY est_logical_reads DESC
GO

　　

　　10.如果我们发现数据库发生死锁，可以尝试以下语句（master库）执行查看死锁，更多信息可参考：https://blogs.msdn.microsoft.com/azuresqldbsupport/2017/04/19/deadlock-analysis-for-sql-azure-database/

WITH CTE AS (
       SELECT CAST(event_data AS XML)  AS [target_data_XML] 
   FROM sys.fn_xe_telemetry_blob_target_read_file('dl', null, null, null)
)
SELECT target_data_XML.value('(/event/@timestamp)[1]', 'DateTime2') AS Timestamp,
target_data_XML.query('/event/data[@name=''xml_report'']/value/deadlock') AS deadlock_xml,
target_data_XML.query('/event/data[@name=''database_name'']/value').value('(/value)[1]', 'nvarchar(100)') AS db_name
FROM CTE 

 

　　11.当我们需要手动Kill死锁的Session时候，需要注意：当前执行完kill 会话后，为什么执行kill语句完成，但查看会话进程还在？

　　在执行kill杀会话时候，命令执行完成并不代表会话即时被kill掉，会话中有大事务操作的话，为保证数据的一致性，未提交的事务首先要做回滚，执行回滚时间的依据事务操作的大小。
　　建议：一般在Kill会话，建议采用KILL session ID WITH STATUSONLY 方式，这样我们在kill动作操作结束，可以实时看到当前处理的进度百分比。
　　详细介绍可参考：https://docs.microsoft.com/zh-cn/sql/t-sql/language-elements/kill-transact-sql?view=sql-server-2017