Azure SQL Database 使用Query Store對(duì)Azure SQL Database監(jiān)控

我們?cè)谑褂肁zure SQL Database的時(shí)候，需要對(duì)數(shù)據(jù)庫的性能進(jìn)行監(jiān)控，這時(shí)候就可以有兩種方法：

1.第一種方法，是通過Azure SQL Database的監(jiān)控界面，來查看數(shù)據(jù)庫的性能，在本章會(huì)簡單的介紹一下

2.第二種方法，是通過Query Store來進(jìn)行監(jiān)控，在本章會(huì)詳細(xì)介紹

首先，我們介紹一下使用Azure SQL Database的監(jiān)控界面。

1.我們登錄Azure Portal: https://portal.azure.cn/

2.查看到我們使用的Azure SQL Database，選擇概述，然后點(diǎn)擊下圖紅色部分

3.頁面跳轉(zhuǎn)后，我們可以在下圖的Last Hour，設(shè)置監(jiān)控的時(shí)間段

在Add Metric里面，增加新的監(jiān)控指標(biāo)，比如CPU Percentage, Data IO Percentage等

4.我們還可以在性能概述里面，查看到微軟云Azure對(duì)我們當(dāng)前數(shù)據(jù)的優(yōu)化建議

接下來，我們?cè)敿?xì)介紹一下使用Query Store來進(jìn)行監(jiān)控，實(shí)際上我們?cè)谏厦婵吹降耐ㄟ^Azure Portal的可視化監(jiān)控，其實(shí)也是通過Query Store來進(jìn)行監(jiān)控的。

Query Store是SQL Server 2016里面新的功能，同時(shí)在微軟云Azure平臺(tái)上，也提供了該功能

Query Store是從內(nèi)存中讀取數(shù)據(jù)，并異步寫入到Azure SQL Database的磁盤上的

這里我們假設(shè)一個(gè)場(chǎng)景，如果Azure SQL Databse的DTU利用率很高，我們?nèi)绾尾樵兂鼍唧w是哪些語句，占用了過多的資源呢？

1.首先，我們通過Azure Portal，查看到問題發(fā)生的時(shí)間，如下圖在9月2日的凌晨開始，發(fā)生了該問題

我們點(diǎn)擊下圖的紅色部分

2.DTU和CPU Time，DataIO都有關(guān)。我們點(diǎn)擊下圖的Add Metric

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

8點(diǎn)以后都是DATA IO

4.我們?cè)诒镜豍C上安裝SQL Server Management Studio，訪問上面的數(shù)據(jù)庫，并且找到Query Store

我們點(diǎn)擊下圖的Top Resource Consuming Queries

5.點(diǎn)擊上圖右上角的Config，設(shè)置查詢時(shí)間

6.在彈出的窗口中，選擇查詢時(shí)間，我們也可以使用默認(rèn)的

7.我們查詢CPU Time，Static 選擇Avg。可以查看到缺少索引

8.在下圖，我們右鍵Miss Index，設(shè)置索引

9.如果我們需要查詢所有缺少索引的表結(jié)構(gòu)，可以在SSMS執(zhí)行下面的語句

--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.如果我們發(fā)現(xiàn)數(shù)據(jù)庫發(fā)生死鎖，可以嘗試以下語句（master庫）執(zhí)行查看死鎖，更多信息可參考：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.當(dāng)我們需要手動(dòng)Kill死鎖的Session時(shí)候，需要注意：當(dāng)前執(zhí)行完kill 會(huì)話后，為什么執(zhí)行kill語句完成，但查看會(huì)話進(jìn)程還在？

在執(zhí)行kill殺會(huì)話時(shí)候，命令執(zhí)行完成并不代表會(huì)話即時(shí)被kill掉，會(huì)話中有大事務(wù)操作的話，為保證數(shù)據(jù)的一致性，未提交的事務(wù)首先要做回滾，執(zhí)行回滾時(shí)間的依據(jù)事務(wù)操作的大小。

建議：一般在Kill會(huì)話，建議采用KILL session ID WITH STATUSONLY 方式，這樣我們?cè)趉ill動(dòng)作操作結(jié)束，可以實(shí)時(shí)看到當(dāng)前處理的進(jìn)度百分比。

詳細(xì)介紹可參考：https://docs.microsoft.com/zh-cn/sql/t-sql/language-elements/kill-transact-sql?view=sql-server-2017